An extended standing function request has seen little love from the jOOQ neighborhood, regardless of lots of people in all probability wanting it. It goes by the unimpressive title Let Desk<R> prolong SelectField<R>: https://github.com/jOOQ/jOOQ/points/4727
What does the function imply, particularly?
The superior PostgreSQL
Let’s take a look at a very cool PostgreSQL function. In PostgreSQL, it’s attainable to nest data in varied 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 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)
But it surely’s subtly completely different. The end result 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") ... |
For those who’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 a couple of refined 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 data, although this time with no report 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,Kids) (2,NICK,WAHLBERG) | (4,Classics)
Or, from DBeaver:
Can this be utilized in jOOQ?
Whereas Oracle/PostgreSQL UDTs have all the time been accessible, such ad-hoc nested report expressions projected from the SELECT clause have been attainable 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
Outcome<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
Outcome<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 too can ad-hoc converters to transform the jOOQ-generated report to something extra helpful in your goal customers, e.g. a Java 16 report sort.
Combining this with implicit joins
A really highly effective function in jOOQ are implicit joins, which have been added in jOOQ 3.11. Possibly, you don’t discover the express be part of syntax very pleasing to write down on a regular basis? Why not write issues like this, as an alternative:
Outcome<Record2<CustomerRecord, CountryRecord>> end result =
ctx.choose(
CUSTOMER,
CUSTOMER.deal with().metropolis().nation()
)
.from(CUSTOMER)
.fetch();
Observe that once more, we don’t mission any particular person columns of the CUSTOMER or COUNTRY desk, we simply mission all the desk, identical to in PostgreSQL, and all sort secure with getters and setters accessible 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 would possibly get loads of information that you just won’t want. There’s all the time a comfort / efficiency tradeoff to be made. Ideally, if you wish to make use of this method so much, create views in your database, and generate jOOQ code for these views. With artificial overseas keys in jOOQ, you possibly can nonetheless revenue from the implicit be part of syntax on views as properly.
