Using jOOQ’s DiagnosticsConnection to detect N+1 Queries

N+1 queries are a popular problem in many applications that run SQL queries. The problem can be described easily as follows: 1 query fetching a parent value is runN queries fetching each individual child values are run This problem isn't limited to SQL, it can happen with any poorly designed API that does not allow … Continue reading Using jOOQ’s DiagnosticsConnection to detect N+1 Queries

The Useful BigQuery * EXCEPT Syntax

One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulations. One of these syntaxes is BigQuery's * EXCEPT syntax. Everyone who ever wrote ad-hoc SQL queries would have liked … Continue reading The Useful BigQuery * EXCEPT Syntax

3.16.0 Release with a new Public Query Object Model API, Spatial Support, YugabyteDB Support and Much More

This release tackles two long standing and complex feature requests that usershave asked us to offer for a long time: a public API for manipulating jOOQ'squery object model (QOM), and spatial support. New Query Object Model (QOM) Every jOOQ query is modeled as an expression tree constructed via our intuitiveDSL. For some use-cases there exist … Continue reading 3.16.0 Release with a new Public Query Object Model API, Spatial Support, YugabyteDB Support and Much More

How to customise a jOOQ Configuration that is injected using Spring Boot

Starting from Spring Boot 2.5, there's a handy new callback that you can implement, called DefaultConfigurationCustomizer, where the word DefaultConfiguration corresponds to jOOQ's DefaultConfiguration. You can simply create a class like this in your project: import org.jooq.conf.RenderQuotedNames; import org.jooq.impl.DefaultConfiguration; import org.springframework.boot.autoconfigure.jooq.*; import org.springframework.context.annotation.*; @Configuration public class Config { @Bean public DefaultConfigurationCustomizer configurationCustomiser() { return (DefaultConfiguration … Continue reading How to customise a jOOQ Configuration that is injected using Spring Boot

Using JDK Collectors to De-duplicate parent/child nested collections

In classic SQL (i.e. before jOOQ's awesome MULTISET operator), nested collections were fetched using ordinary (outer) joins. An example of such a query would be a query running against the sakila database to fetch actors and their films. Using jOOQ: Result<?> result = ctx.select( ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME, FILM.FILM_ID, FILM.TITLE) .from(ACTOR) .leftJoin(FILM_ACTOR).on(ACTOR.ACTOR_ID.eq(FILM_ACTOR.ACTOR_ID)) .leftJoin(FILM).on(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID)) .orderBy( ACTOR.ACTOR_ID, FILM.FILM_ID) … Continue reading Using JDK Collectors to De-duplicate parent/child nested collections

Why You Should Use jOOQ With Code Generation

I'm answering many jOOQ questions on Stack Overflow, and a lot of times. The problem has the same cause: People not using jOOQ's code generator. The main reason people seem not to be using it, is because it takes some extra time to set up, but as with anything well designed, the initial investment will … Continue reading Why You Should Use jOOQ With Code Generation

Fun with PostGIS: Mandelbrot Set, Game of Life, and More

The upcoming jOOQ 3.16 will finally offer support for the various RDBMS GIS extensions via issue #982. This is great news per se, and will be covered in a future blog post, when the integration is ready. This post here is about something else. Adding support for such a feature is a great source of … Continue reading Fun with PostGIS: Mandelbrot Set, Game of Life, and More

The jOOQ Parser Ignore Comment Syntax

jOOQ's parser can't parse every possible SQL syntax. Try this random PostgreSQL syntax: ALTER SYSTEM RESET ALL And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, TYPE, or VIEW expected: [1:7] ALTER [*]SYSTEM RESET ALL That's perfectly fine. The goal of the jOOQ parser isn't to understand all vendor specific syntax. The … Continue reading The jOOQ Parser Ignore Comment Syntax

Using Testcontainers to Generate jOOQ Code

Database first is at the core of jOOQ's design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think "data have mass" https://twitter.com/ChrisRSaxon/status/1093122970254536704 This not only translates to moving logic closer to the data (see our previous posts about … Continue reading Using Testcontainers to Generate jOOQ Code

Using jOOQ to write vendor agnostic SQL with JPA’s native query or @Formula

If your legacy JPA application is using occasional native queries or Hibernate @Formula or Spring Data @Query annotation with vendor specific native SQL embedded in it, you can use jOOQ's parsing connection and parsing data source to translate between dialects, without having to go all in on your jOOQ adoption - though I think it's … Continue reading Using jOOQ to write vendor agnostic SQL with JPA’s native query or @Formula