Various Meanings of SQL’s PARTITION BY Syntax

For SQL beginners, there's a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The meaning is similar to that of GROUP BY, namely to group/partition data sets by some grouping/partitioning criteria. For example, when querying the … Continue reading Various Meanings of SQL’s PARTITION BY Syntax

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

Projecting Type Safe Nested TableRecords with jOOQ 3.17

A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table<R> extend SelectField<R>: https://github.com/jOOQ/jOOQ/issues/4727 What does the feature mean, specifically? The awesome PostgreSQL Let's have a look at a really cool PostgreSQL feature. In PostgreSQL, it is … Continue reading Projecting Type Safe Nested TableRecords with jOOQ 3.17

jOOQ 3.16 and Java EE vs Jakarta EE

A tidal wave is rippling through the Java ecosystem. It is the renaming of javax to jakarta package names. Now, while we've all been whining and complaining and shaking our heads due the clash between corporate legal and engineering interests, eventually it's time to move on and learn what this means specifically, for jOOQ. jOOQ … Continue reading jOOQ 3.16 and Java EE vs Jakarta EE

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

Approximating e With SQL

If you're running on PostgreSQL, you could try the following cool query: WITH RECURSIVE r (r, i) AS ( SELECT random(), i FROM generate_series(1, 1000000) AS t (i) ), s (ri, s, i) AS ( SELECT i, r, i FROM r UNION ALL SELECT s.ri, r.r + s.s, s.i + 1 FROM r JOIN s … Continue reading Approximating e With SQL

Traversing jOOQ Expression Trees with the new Traverser API

Starting from jOOQ 3.16, we're investing a lot into opening up our internal query object model (QOM) as a public API. This is mainly useful for people who use jOOQ's parser and wish to access the parsed expression tree, or to transform SQL, e.g. to implement row level security in jOOQ. But occasionally, even with … Continue reading Traversing jOOQ Expression Trees with the new Traverser API

Detect Accidental Blocking Calls when Using R2DBC

A while ago, jOOQ has added the org.jetbrains:annotations dependency to the jOOQ API, in order to annotate return types with nullability information. For example, the entire DSL is non-nullable: public interface SelectWhereStep<R extends Record> extends SelectConnectByStep<R> { @NotNull @CheckReturnValue @Support SelectConditionStep<R> where(Condition condition); // ... } It makes sense to give this guarantee especially to … Continue reading Detect Accidental Blocking Calls when Using R2DBC

A Rarely Seen, but Useful SQL Feature: CORRESPONDING

I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is CORRESPONDING, and it can be used with all set operations, including UNION, INTERSECT, and EXCEPT. Let's look at the sakila database. It has 3 tables with people in it: CREATE TABLE actor ( actor_id integer NOT … Continue reading A Rarely Seen, but Useful SQL Feature: CORRESPONDING

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