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 extremely cool PostgreSQL function. 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 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 totally 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") ... |
Should 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 totally different from projecting the asterisks (*), however it’s logically the identical factor (with a number of refined variations). Isn’t that cool? A few of you could be used to utilizing the ROW constructor like this (the place the ROW key phrase is optionally available):
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 out a 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 obtainable, such ad-hoc nested report 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
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();
Similar to with jOOQ 3.15, you can too ad-hoc converters to transform the jOOQ-generated report to something extra helpful in your goal shoppers, 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 specific be part of syntax very pleasing to write down on a regular basis? Why not write issues like this, as a substitute:
Outcome<Record2<CustomerRecord, CountryRecord>> end result =
ctx.choose(
CUSTOMER,
CUSTOMER.handle().metropolis().nation()
)
.from(CUSTOMER)
.fetch();
Notice that once more, we don’t challenge any particular person columns of the CUSTOMER or COUNTRY desk, we simply challenge the whole desk, similar to in PostgreSQL, and all sort protected with getters and setters obtainable 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 quite a lot 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 so much, create views in your database, and generate jOOQ code for these views. With artificial international keys in jOOQ, you’ll be able to nonetheless revenue from the implicit be part of syntax on views as nicely.
