A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I've blogged about the powerful MULTISET operator many times, which allows for nesting collections in jOOQ. This time, instead of nesting data into a List<UserType>, why not nest it in … Continue reading How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ
Category: jooq-in-use
This category contains posts about “jOOQ seen in the wild”, interesting feedback, success stories, articles by end users
How to Fetch Sequence Values with jOOQ
A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: CREATE SEQUENCE s; The following is how you could fetch a value from this sequence, using jOOQ, assuming you're using the code generator: // import static com.example.generated.Sequences.*; System.out.println(ctx.fetchValue(S.nextval())); The sequence expression translates to a variety … Continue reading How to Fetch Sequence Values with jOOQ
Use MULTISET Predicates to Compare Data Sets
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we're using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET Predicates to Compare Data Sets
No More MultipleBagFetchException Thanks to Multiset Nested Collections
I've recently stumbled upon this interesting Stack Overflow question about Hibernate's popular MultipleBagFetchException. The question is super popular, and the answers are plenty. The various limitations are discussed throughout the question, it all boils down to a simple fact: Joins are the wrong tool to nest collections. Given a schema like the Sakila database: There … Continue reading No More MultipleBagFetchException Thanks to Multiset Nested Collections
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
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
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