Projecting Type Safe Nested TableRecords with jOOQ 3.17 – Java, SQL and jOOQ.

testv October 3, 2024

0 0 3 minutes read

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 possible to nest records in various ways, including by simply referencing (unqualified) table names in the SELECT clause. Using the sakila database, this is valid PostgreSQL:

SELECT DISTINCT actor, category
FROM actor
JOIN film_actor USING (actor_id)
JOIN film_category USING (film_id)
JOIN category USING (category_id)

This lists all actors and the categories of films they played in. There is only really one thing this could mean, right? It looks as though it was syntax sugar for this, which works on all database products:

SELECT DISTINCT actor.*, category.*
FROM actor
JOIN film_actor USING (actor_id)
JOIN film_category USING (film_id)
JOIN category USING (category_id)

But it’s subtly different. The result looks something like this, in psql:

                      actor                      |               category
-------------------------------------------------+---------------------------------------
...                                              |
 (1,PENELOPE,GUINESS,"2006-02-15 04:34:33")      | (12,Music,"2006-02-15 04:46:27")
 (1,PENELOPE,GUINESS,"2006-02-15 04:34:33")      | (13,New,"2006-02-15 04:46:27")
 (1,PENELOPE,GUINESS,"2006-02-15 04:34:33")      | (14,Sci-Fi,"2006-02-15 04:46:27")
 (1,PENELOPE,GUINESS,"2006-02-15 04:34:33")      | (15,Sports,"2006-02-15 04:46:27")
 (2,NICK,WAHLBERG,"2006-02-15 04:34:33")         | (1,Action,"2006-02-15 04:46:27")
 (2,NICK,WAHLBERG,"2006-02-15 04:34:33")         | (2,Animation,"2006-02-15 04:46:27")
...                                              |

If you’re using DBeaver to display the results, you’ll see a similar nesting structure:

What happened here is that PostgreSQL simply nested the two tables as nested records in the output. The representation is a bit different from projecting the asterisks (*), but it’s logically the same thing (with a few subtle differences). Isn’t that cool? Some of you might be used to using the ROW constructor like this (where the ROW keyword is optional):

SELECT DISTINCT 
  ROW(actor_id, first_name, last_name) AS actor, 
  ROW(category_id, name) AS category
FROM actor
JOIN film_actor USING (actor_id)
JOIN film_category USING (film_id)
JOIN category USING (category_id)

This also produces nested records, though this time without a record type. From psql:

           actor           |    category
---------------------------+-----------------
 ...                       |
 (1,PENELOPE,GUINESS)      | (12,Music)
 (1,PENELOPE,GUINESS)      | (13,New)
 (1,PENELOPE,GUINESS)      | (14,Sci-Fi)
 (1,PENELOPE,GUINESS)      | (15,Sports)
 (2,NICK,WAHLBERG)         | (1,Action)
 (2,NICK,WAHLBERG)         | (2,Animation)
 (2,NICK,WAHLBERG)         | (3,Children)
 (2,NICK,WAHLBERG)         | (4,Classics)

Or, from DBeaver:

Can this be used in jOOQ?

While Oracle/PostgreSQL UDTs have always been available, such ad-hoc nested record expressions projected from the SELECT clause have been possible in jOOQ since jOOQ 3.15. Just like the awesome MULTISET operator, they’re key to accessing more powerful nested collection mappings.

But starting from jOOQ 3.17, the table expression version is now finally also accessible. The previous SQL queries from PostgreSQL would translate to this, in jOOQ:

// Projecting table expressions
Result<Record2<ActorRecord, CategoryRecord>> result1 =
ctx.selectDistinct(ACTOR, CATEGORY)
   .from(ACTOR)
   .join(FILM_ACTOR).using(FILM_ACTOR.ACTOR_ID)
   .join(FILM_CATEGORY).using(FILM_CATEGORY.FILM_ID)
   .join(CATEGORY).using(CATEGORY.CATEGORY_ID)
   .fetch();

// Projecting ad-hoc ROW expressions
Result<Record2<
    Record3<Long, String, String>, // actor
    Record2<Long, String> // category
>> result2 =
ctx.selectDistinct(
       row(
           ACTOR.ACTOR_ID, 
           ACTOR.FIRST_NAME, 
           ACTOR.LAST_NAME
       ).as("actor"),
       row(CATEGORY.CATEGORY_ID, CATEGORY.NAME).as("category")
   )
   .from(ACTOR)
   .join(FILM_ACTOR).using(FILM_ACTOR.ACTOR_ID)
   .join(FILM_CATEGORY).using(FILM_CATEGORY.FILM_ID)
   .join(CATEGORY).using(CATEGORY.CATEGORY_ID)
   .fetch();

Just like with jOOQ 3.15, you can also ad-hoc converters to convert the jOOQ-generated record to anything more useful for your target consumers, e.g. a Java 16 record type.

Combining this with implicit joins

A very powerful feature in jOOQ are implicit joins, which have been added in jOOQ 3.11. Maybe, you don’t find the explicit join syntax very pleasing to write all the time? Why not write things like this, instead:

Result<Record2<CustomerRecord, CountryRecord>> result =
ctx.select(
       CUSTOMER,
       CUSTOMER.address().city().country()
   )
   .from(CUSTOMER)
   .fetch();

Note that again, we don’t project any individual columns of the CUSTOMER or COUNTRY table, we just project the entire table, just like in PostgreSQL, and all type safe with getters and setters available on the resulting records.

Caveat

As always, know what you’re doing, and why you’re doing it. Both in PostgreSQL and in jOOQ, projecting the CUSTOMER table is mostly just sugar for projecting CUSTOMER.*, i.e. you might get a lot of data that you might not need. There’s always a convenience / performance tradeoff to be made. Ideally, if you want to make use of this approach a lot, create views in your database, and generate jOOQ code for those views. With synthetic foreign keys in jOOQ, you can still profit from the implicit join syntax on views as well.