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

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

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

[ad_1]

An extended standing characteristic request has seen little love from the jOOQ group, regardless of lots of people most likely 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 potential to nest information 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 may be solely actually one factor this might imply, proper? It seems 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)

Nevertheless it’s subtly completely different. The consequence seems 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")
...                                              |

If you happen to’re utilizing DBeaver to show the outcomes, you’ll see the same nesting construction:

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

SELECT DISTINCT 
  ROW(actor_id, first_name, last_name) AS actor, 
  ROW(category_id, identify) 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 information, although this time and not using a document kind. 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 at all times been obtainable, such ad-hoc nested document expressions projected from the SELECT clause have been potential in jOOQ since jOOQ 3.15. Identical 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
Consequence<Record2<ActorRecord, CategoryRecord>> result1 =
ctx.selectDistinct(ACTOR, CATEGORY)
   .from(ACTOR)
   .be part of(FILM_ACTOR).utilizing(FILM_ACTOR.ACTOR_ID)
   .be part of(FILM_CATEGORY).utilizing(FILM_CATEGORY.FILM_ID)
   .be part of(CATEGORY).utilizing(CATEGORY.CATEGORY_ID)
   .fetch();

// Projecting ad-hoc ROW expressions
Consequence<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 part of(FILM_ACTOR).utilizing(FILM_ACTOR.ACTOR_ID)
   .be part of(FILM_CATEGORY).utilizing(FILM_CATEGORY.FILM_ID)
   .be part of(CATEGORY).utilizing(CATEGORY.CATEGORY_ID)
   .fetch();

Identical to with jOOQ 3.15, you can too ad-hoc converters to transform the jOOQ-generated document to something extra helpful in your goal shoppers, e.g. a Java 16 document kind.

Combining this with implicit joins

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

Consequence<Record2<CustomerRecord, CountryRecord>> consequence =
ctx.choose(
       CUSTOMER,
       CUSTOMER.deal with().metropolis().nation()
   )
   .from(CUSTOMER)
   .fetch();

Word that once more, we don’t mission any particular person columns of the CUSTOMER or COUNTRY desk, we simply mission the complete desk, similar to in PostgreSQL, and all kind protected with getters and setters obtainable on the ensuing information.

Caveat

As at all times, 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 would possibly get a whole lot of knowledge that you simply may not want. There’s at all times 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 overseas keys in jOOQ, you’ll be able to nonetheless revenue from the implicit be part of syntax on views as properly.

[ad_2]

Supply hyperlink

LEAVE A REPLY

Please enter your comment!
Please enter your name here