Tuesday, July 23, 2024
HomeProgrammingProjecting Sort Secure Nested TableRecords with jOOQ 3.17 – Java, SQL and...

Projecting Sort Secure Nested TableRecords with jOOQ 3.17 – Java, SQL and jOOQ.


An extended standing characteristic request has seen little love from the jOOQ group, regardless of lots of people in all probability wanting it. It goes by the unimpressive title Let Desk<R> lengthen SelectField<R>: https://github.com/jOOQ/jOOQ/points/4727

What does the characteristic imply, particularly?

The superior PostgreSQL

Let’s take a look at a very cool PostgreSQL characteristic. In PostgreSQL, it’s doable to nest data in numerous methods, together with by merely referencing (unqualified) desk names within the SELECT clause. Utilizing the sakila database, that is legitimate PostgreSQL:

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

This lists all actors and the classes of movies they performed in. There’s solely actually one factor this might imply, proper? It appears as if it was syntax sugar for this, which works on all database merchandise:

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

But it surely’s subtly completely different. The consequence appears one thing like this, in psql:

                      actor                      |               class
-------------------------------------------------+---------------------------------------
...                                              |
 (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 activities,"2006-02-15 04:46:27")
 (2,NICK,WAHLBERG,"2006-02-15 04:34:33")         | (1,Motion,"2006-02-15 04:46:27")
 (2,NICK,WAHLBERG,"2006-02-15 04:34:33")         | (2,Animation,"2006-02-15 04:46:27")
...                                              |

When you’re utilizing DBeaver to show the outcomes, you’ll see an identical nesting construction:

What occurred right here is that PostgreSQL merely nested the 2 tables as nested data within the output. The illustration is a bit completely different from projecting the asterisks (*), nevertheless it’s logically the identical factor (with just a few delicate variations). Isn’t that cool? A few of you is likely to be used to utilizing the ROW constructor like this (the place the ROW key phrase is non-compulsory):

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

This additionally produces nested data, although this time with out a document sort. From psql:

           actor           |    class
---------------------------+-----------------
 ...                       |
 (1,PENELOPE,GUINESS)      | (12,Music)
 (1,PENELOPE,GUINESS)      | (13,New)
 (1,PENELOPE,GUINESS)      | (14,Sci-Fi)
 (1,PENELOPE,GUINESS)      | (15,Sports activities)
 (2,NICK,WAHLBERG)         | (1,Motion)
 (2,NICK,WAHLBERG)         | (2,Animation)
 (2,NICK,WAHLBERG)         | (3,Youngsters)
 (2,NICK,WAHLBERG)         | (4,Classics)

Or, from DBeaver:

Can this be utilized in jOOQ?

Whereas Oracle/PostgreSQL UDTs have all the time been out there, such ad-hoc nested document expressions projected from the SELECT clause have been doable in jOOQ since jOOQ 3.15. Similar to the superior MULTISET operator, they’re key to accessing extra highly effective nested assortment mappings.

However ranging from jOOQ 3.17, the desk expression model is now lastly additionally accessible. The earlier SQL queries from PostgreSQL would translate to this, in jOOQ:

// Projecting desk expressions
End result<Record2<ActorRecord, CategoryRecord>> result1 =
ctx.selectDistinct(ACTOR, CATEGORY)
   .from(ACTOR)
   .be a part of(FILM_ACTOR).utilizing(FILM_ACTOR.ACTOR_ID)
   .be a part of(FILM_CATEGORY).utilizing(FILM_CATEGORY.FILM_ID)
   .be a part of(CATEGORY).utilizing(CATEGORY.CATEGORY_ID)
   .fetch();

// Projecting ad-hoc ROW expressions
End result<Record2<
    Record3<Lengthy, String, String>, // actor
    Record2<Lengthy, String> // class
>> result2 =
ctx.selectDistinct(
       row(
           ACTOR.ACTOR_ID, 
           ACTOR.FIRST_NAME, 
           ACTOR.LAST_NAME
       ).as("actor"),
       row(CATEGORY.CATEGORY_ID, CATEGORY.NAME).as("class")
   )
   .from(ACTOR)
   .be a part of(FILM_ACTOR).utilizing(FILM_ACTOR.ACTOR_ID)
   .be a part of(FILM_CATEGORY).utilizing(FILM_CATEGORY.FILM_ID)
   .be a part of(CATEGORY).utilizing(CATEGORY.CATEGORY_ID)
   .fetch();

Similar to with jOOQ 3.15, it’s also possible to ad-hoc converters to transform the jOOQ-generated document to something extra helpful to your goal shoppers, e.g. a Java 16 document sort.

Combining this with implicit joins

A really highly effective characteristic in jOOQ are implicit joins, which have been added in jOOQ 3.11. Perhaps, you don’t discover the express be a part of syntax very pleasing to jot down on a regular basis? Why not write issues like this, as a substitute:

End result<Record2<CustomerRecord, CountryRecord>> consequence =
ctx.choose(
       CUSTOMER,
       CUSTOMER.handle().metropolis().nation()
   )
   .from(CUSTOMER)
   .fetch();

Notice that once more, we don’t mission any particular person columns of the CUSTOMER or COUNTRY desk, we simply mission your entire desk, identical to in PostgreSQL, and all sort secure with getters and setters out there on the ensuing data.

Caveat

As all the time, know what you’re doing, and why you’re doing it. Each in PostgreSQL and in jOOQ, projecting the CUSTOMER desk is usually simply sugar for projecting CUSTOMER.*, i.e. you may get lots of knowledge that you just may not want. There’s all the time a comfort / efficiency tradeoff to be made. Ideally, if you wish to make use of this strategy loads, create views in your database, and generate jOOQ code for these views. With artificial international keys in jOOQ, you may nonetheless revenue from the implicit be a part of syntax on views as nicely.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments