[ad_1]
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 extremely cool PostgreSQL characteristic. In PostgreSQL, it’s potential to nest information 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 may be solely actually one factor this might imply, proper? It appears to be like 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)
However it’s subtly completely different. The consequence appears to be like 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 (*), 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 information, although this time with no 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,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 document expressions projected from the SELECT clause have been potential 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
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();
Similar to with jOOQ 3.15, you can even 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 express be part of syntax very pleasing to write down on a regular basis? Why not write issues like this, as a substitute:
Consequence<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 complete desk, similar to in PostgreSQL, and all kind protected with getters and setters obtainable on the ensuing information.
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 plenty of knowledge that you simply may not want. There’s all the time a comfort / efficiency tradeoff to be made. Ideally, if you wish to make use of this method rather a lot, 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 effectively.
[ad_2]
