That is how SQL ought to have been used all alongside.
They known as it The Third Manifesto, ORDBMS, or different issues. Regrettably, it by no means actually took off. As a result of most distributors didn’t undertake it. And people who did, didn’t agree on syntax.
However that is about to alter. Because of the now ubiquitous SQL/JSON assist (which jOOQ 3.14 has already lined), we are able to now emulate essentially the most highly effective ORDBMS characteristic that you’ll want to use all over the place: Nested collections!
How We Used to do Issues: With Joins
We’re going to be utilizing the Sakila database for this instance. It’s a DVD rental retailer with issues like ACTOR, FILM, CATEGORY (of movies) and different good relational issues. Let’s write a question for this requirement
Get me all of the movies with their actors and their classes
Classically, we’d go forward and use jOOQ to put in writing:
ctx.choose(
FILM.TITLE,
ACTOR.FIRST_NAME,
ACTOR.LAST_NAME,
CATEGORY.NAME
)
.from(ACTOR)
.be part of(FILM_ACTOR)
.on(ACTOR.ACTOR_ID.eq(FILM_ACTOR.ACTOR_ID))
.be part of(FILM)
.on(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
.be part of(FILM_CATEGORY)
.on(FILM.FILM_ID.eq(FILM_CATEGORY.FILM_ID))
.be part of(CATEGORY)
.on(FILM_CATEGORY.CATEGORY_ID.eq(CATEGORY.CATEGORY_ID))
.orderBy(1, 2, 3, 4)
.fetch();
And the consequence? Not so good. A denormalised, flat desk containing tons of repetition:
+----------------+----------+---------+-----------+ |title |first_name|last_name|identify | +----------------+----------+---------+-----------+ |ACADEMY DINOSAUR|CHRISTIAN |GABLE |Documentary| |ACADEMY DINOSAUR|JOHNNY |CAGE |Documentary| |ACADEMY DINOSAUR|LUCILLE |TRACY |Documentary| |ACADEMY DINOSAUR|MARY |KEITEL |Documentary| |ACADEMY DINOSAUR|MENA |TEMPLE |Documentary| |ACADEMY DINOSAUR|OPRAH |KILMER |Documentary| |ACADEMY DINOSAUR|PENELOPE |GUINESS |Documentary| |ACADEMY DINOSAUR|ROCK |DUKAKIS |Documentary| |ACADEMY DINOSAUR|SANDRA |PECK |Documentary| |ACADEMY DINOSAUR|WARREN |NOLTE |Documentary| |ACE GOLDFINGER |BOB |FAWCETT |Horror | |ACE GOLDFINGER |CHRIS |DEPP |Horror | |ACE GOLDFINGER |MINNIE |ZELLWEGER|Horror | ...
If we don’t eat this consequence unmodified as it’s (e.g. when displaying tabular knowledge to a person), we’d then go and de-duplicate issues, shoehorning them again once more into some nested knowledge constructions (e.g. for consumption by some JSON primarily based UI), and spending hours making an attempt to untangle the cartesian merchandise between the two nested collections FILM -> ACTOR and FILM -> CATEGORY (as a result of ACTOR and CATEGORY now created a cartesian product, which we didn’t need!)
Within the worst case, we don’t even discover! This instance database solely has 1 class per movie, however it’s designed to assist a number of classes.
jOOQ may also help with that deduplication, however simply have a look at the variety of questions for jOOQ many to many on Stack Overflow! You’ll most likely nonetheless have to put in writing at the very least 2 queries to separate the nested collections.
ENTER the Stage: Multiset
The usual SQL <multiset worth constructor> operator permits for gathering the info from a correlated subquery right into a nested knowledge construction, a MULTISET. Every thing in SQL is a MULTISET, so the operator isn’t too stunning. However the nesting is the place it shines. The earlier question can now be re-written in jOOQ as follows:
var consequence =
dsl.choose(
FILM.TITLE,
multiset(
choose(
FILM_ACTOR.actor().FIRST_NAME,
FILM_ACTOR.actor().LAST_NAME)
.from(FILM_ACTOR)
.the place(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
).as("actors"),
multiset(
choose(FILM_CATEGORY.class().NAME)
.from(FILM_CATEGORY)
.the place(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
).as("classes")
)
.from(FILM)
.orderBy(FILM.TITLE)
.fetch();
Notice, it’s not related to this job, however I’m utilizing jOOQ’s kind protected implicit to-one be part of characteristic, which additional helps taming the joins, syntactically. A matter of style.
The way to learn this question? Straightforward:
- Get all of the movies
- For every
FILM, get all of the actors as a nested assortment - For every
FILM, get all of the classes as a nested assortment
You’re going to love Java 10’s var key phrase much more after this 🙂 As a result of what kind is consequence? It’s of this kind:
End result<Record3<
String, // FILM.TITLE
End result<Record2<String, String>>, // ACTOR.FIRST_NAME, ACTOR.LAST_NAME
End result<Record1<String>> // CATEGORY.NAME
>>
That’s fairly one thing. Not too complicated when you consider it. There’s a consequence with 3 columns
TITLE- A primary nested consequence that has 2 string columns:
ACTOR.FIRST_NAMEandACTOR.LAST_NAME - A second nested consequence that has 1 string column:
CATEGORY.NAME
Utilizing var or different kind inference mechanisms, you don’t need to denote this kind. And even higher (keep tuned): We’ll type-safely map the structural kind to our nominal DTO kind hierarchy, with just some extra traces of code. I’ll clarify that later.
What Does the End result Look Like?
Calling toString() on the above End result kind yields one thing like this:
+---------------------------+--------------------------------------------------+---------------+ |title |actors |classes | +---------------------------+--------------------------------------------------+---------------+ |ACADEMY DINOSAUR |[(PENELOPE, GUINESS), (CHRISTIAN, GABLE), (LUCI...|[(Documentary)]| |ACE GOLDFINGER |[(BOB, FAWCETT), (MINNIE, ZELLWEGER), (SEAN, GU...|[(Horror)] | |ADAPTATION HOLES |[(NICK, WAHLBERG), (BOB, FAWCETT), (CAMERON, ST...|[(Documentary)]| |AFFAIR PREJUDICE |[(JODIE, DEGENERES), (SCARLETT, DAMON), (KENNET...|[(Horror)] | |AFRICAN EGG |[(GARY, PHOENIX), (DUSTIN, TAUTOU), (MATTHEW, L...|[(Family)] | |AGENT TRUMAN |[(KIRSTEN, PALTROW), (SANDRA, KILMER), (JAYNE, ...|[(Foreign)] | ...
Discover how we’re again to getting every FILM.TITLE entry solely as soon as (no duplication), and nested in every row are the subquery outcomes. There’s no denormalisation taking place!
When calling consequence.formatJSON() with the suitable formatting choices, we’ll get this illustration:
[
{
"title": "ACADEMY DINOSAUR",
"actors": [
{
"first_name": "PENELOPE",
"last_name": "GUINESS"
},
{
"first_name": "CHRISTIAN",
"last_name": "GABLE"
},
{
"first_name": "LUCILLE",
"last_name": "TRACY"
},
{
"first_name": "SANDRA",
"last_name": "PECK"
},
...
],
"classes": [
{ "name": "Documentary" }
]
},
{
"title": "ACE GOLDFINGER",
"actors": [
{
"first_name": "BOB",
"last_name": "FAWCETT"
},
...
Calling result.formatXML() would produce this:
<result>
<record>
<title>ACADEMY DINOSAUR</title>
<actors>
<result>
<record>
<first_name>PENELOPE</first_name>
<last_name>GUINESS</last_name>
</record>
<record>
<first_name>CHRISTIAN</first_name>
<last_name>GABLE</last_name>
</record>
<record>
<first_name>LUCILLE</first_name>
<last_name>TRACY</last_name>
</record>
<record>
<first_name>SANDRA</first_name>
<last_name>PECK</last_name>
</record>
...
</result>
</actors>
<categories>
<result>
<record>
<name>Documentary</name>
</record>
</result>
</categories>
</record>
<record>
<title>ACE GOLDFINGER</title>
<actors>
<result>
<record>
<first_name>BOB</first_name>
<last_name>FAWCETT</last_name>
</record>
...
You get the idea!
What’s the Generated SQL?
Just turn on jOOQ’s DEBUG logging and observe a query like this one (in PostgreSQL):
select
film.title,
(
select coalesce(
jsonb_agg(jsonb_build_object(
'first_name', t.first_name,
'last_name', t.last_name
)),
jsonb_build_array()
)
from (
select
alias_78509018.first_name,
alias_78509018.last_name
from (
film_actor
join actor as alias_78509018
on film_actor.actor_id = alias_78509018.actor_id
)
where film_actor.film_id = film.film_id
) as t
) as actors,
(
select coalesce(
jsonb_agg(jsonb_build_object('name', t.name)),
jsonb_build_array()
)
from (
select alias_130639425.name
from (
film_category
join category as alias_130639425
on film_category.category_id = alias_130639425.category_id
)
where film_category.film_id = film.film_id
) as t
) as categories
from film
order by film.title
The Db2, MySQL, Oracle, SQL Server versions would look similar. Just try it on your Sakila database installation. It runs fast, too.
Mapping the Results to DTOs
Now, I promised to get rid of that lengthy structural type with all the generics. Check this out!
We used to call them POJOs (Plain Old Java Objects). Then DTOs (Data Transfer Objects). Now records. Yes, let’s try some Java 16 records here. (Note, records aren’t required for these examples. Any POJOs with appropriate constructors would do).
Wouldn’t it be nice, if result was of this type
record Actor(String firstName, String lastName) {}
record Film(
String title,
List<Actor> actors,
List<String> categories
) {}
List<Film> result = ...
Structural typing is essential to jOOQ and its type safe query system, but in your code, you probably don’t want to have those merge-conflict lookalike generics all the time, and even var won’t help you if your data needs to be returned from a method.
So, let’s transform our jOOQ query to one that produces List<Film>, step by step. We’re starting with the original query, untouched:
Result<Record3<
String,
Result<Record2<String, String>>,
Result<Record1<String>>
>> result =
dsl.select(
FILM.TITLE,
multiset(
select(
FILM_ACTOR.actor().FIRST_NAME,
FILM_ACTOR.actor().LAST_NAME)
.from(FILM_ACTOR)
.where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
).as("actors"),
multiset(
select(FILM_CATEGORY.category().NAME)
.from(FILM_CATEGORY)
.where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
).as("categories")
)
.from(FILM)
.orderBy(FILM.TITLE)
.fetch();
Now, we’re going to map the Actor in the first MULTISET expression. This can be done as follows, using the new Field.convertFrom() convenience method, which allows to turn a Field<T> into any read-only Field<U> for ad-hoc usage. A simple example would be this:
record Title(String title) {}
// Use this field in any query
Field<Title> title = FILM.TITLE.convertFrom(Title::new);
It’s just an easy, new way to attach a read-only Converter to a Field for single usage, instead of doing that with the code generator.
Applied to the original query:
Result<Record3<
String,
List<Actor>, // A bit nicer already
Result<Record1<String>>
>> result =
dsl.select(
FILM.TITLE,
multiset(
select(
FILM_ACTOR.actor().FIRST_NAME,
FILM_ACTOR.actor().LAST_NAME)
.from(FILM_ACTOR)
.where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
// Magic here: vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
multiset(
select(FILM_CATEGORY.category().NAME)
.from(FILM_CATEGORY)
.where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
).as("categories")
)
.from(FILM)
.orderBy(FILM.TITLE)
.fetch();
What are we doing here?
- The method
convertFrom()takes a lambdaResult<Record2<String, String>> -> Actor. - The
Resulttype is the usual jOOQResult, which has aResult.map(RecordMapper<R, E>)method. - The
mapping()method is the newRecords.mapping(), which isn’t doing much, just turning a constructor reference of typeFunction2<String, String, Actor>into aRecordMapper, which can then be used to turn aResult<Record2<String, String>>into aList<Actor>.
And it type checks! Try it yourself. If you add a column to the multiset, you’ll get a compilation error. If you add/remove an attribute from the Actor record, you’ll get a compilation error. No reflection here, just declarative mapping of jOOQ results/records to custom List<UserType>. If you prefer the “old” reflection approach using jOOQ’s ubiquitous into() methods, you can still do that, too:
Result<Record3<
String,
List<Actor>, // A bit nicer already
Result<Record1<String>>
>> result =
dsl.select(
FILM.TITLE,
multiset(
select(
FILM_ACTOR.actor().FIRST_NAME,
FILM_ACTOR.actor().LAST_NAME)
.from(FILM_ACTOR)
.where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
// Magic here: vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
).as("actors").convertFrom(r -> r.into(Actor.class))),
multiset(
select(FILM_CATEGORY.category().NAME)
.from(FILM_CATEGORY)
.where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
).as("categories")
)
.from(FILM)
.orderBy(FILM.TITLE)
.fetch();
The result still type checks, but the conversion from Result<Record2<String, String>> to List<Actor> no longer does, it uses reflection.
Let’s continue. Let’s remove the clumsy category Result<Record1<String>>. We could’ve added another record, but in this case, a List<String> will suffice.
Result<Record3<
String,
List<Actor>,
List<String> // Begone, jOOQ structural type!
>> result =
dsl.select(
FILM.TITLE,
multiset(
select(
FILM_ACTOR.actor().FIRST_NAME,
FILM_ACTOR.actor().LAST_NAME)
.from(FILM_ACTOR)
.where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
multiset(
select(FILM_CATEGORY.category().NAME)
.from(FILM_CATEGORY)
.where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
// Magic. vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
).as("categories").convertFrom(r -> r.map(Record1::value1))
)
.from(FILM)
.orderBy(FILM.TITLE)
.fetch();
And finally, the outer-most Result<Record3<...>> to List<Film> conversion
List<Film> result =
dsl.select(
FILM.TITLE,
multiset(
select(
FILM_ACTOR.actor().FIRST_NAME,
FILM_ACTOR.actor().LAST_NAME)
.from(FILM_ACTOR)
.where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
).as("actors").convertFrom(r -> r.map(mapping(Actor::new))),
multiset(
select(FILM_CATEGORY.category().NAME)
.from(FILM_CATEGORY)
.where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
).as("categories").convertFrom(r -> r.map(Record1::value1))
)
.from(FILM)
.orderBy(FILM.TITLE)
// vvvvvvvvvvvvvvvvvv grande finale
.fetch(mapping(Film::new));
This time, we don’t need the Field.convertFrom() method. Just using the Records.mapping() auxiliary will be sufficient.
A More Complex Example
The previous example showed nesting of two independent collections, which is quite hard with classic JOIN based SQL or ORMs. How about a much more complex example, where we nest things 2 levels, one of which being an aggregation, even? The requirement is:
Give me all the films, the actors that played in the film, the categories that categorise the film, the customers that have rented the film, and all the payments per customer for that film
I won’t even show a JOIN based approach. Let’s dive directly into MULTISET and the also new, synthetic MULTISET_AGG aggregate function. Here’s how to do this with jOOQ. Now, check out that beautiful result type:
Result<Record4<
String, // FILM.TITLE
Result<Record2<
String, // ACTOR.FIRST_NAME
String // ACTOR.LAST_NAME
>>, // "actors"
Result<Record1<String>>, // CATEGORY.NAME
Result<Record4<
String, // CUSTOMER.FIRST_NAME
String, // CUSTOMER.LAST_NAME
Result<Record2<
LocalDateTime, // PAYMENT.PAYMENT_DATE
BigDecimal // PAYMENT.AMOUNT
>>,
BigDecimal // "total"
>> // "customers"
>> result =
dsl.select(
// Get the films
FILM.TITLE,
// ... and all actors that played in the film
multiset(
select(
FILM_ACTOR.actor().FIRST_NAME,
FILM_ACTOR.actor().LAST_NAME
)
.from(FILM_ACTOR)
.where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
).as("actors"),
// ... and all categories that categorise the film
multiset(
select(FILM_CATEGORY.category().NAME)
.from(FILM_CATEGORY)
.where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
).as("categories"),
// ... and all customers who rented the film, as well
// as their payments
multiset(
select(
PAYMENT.rental().customer().FIRST_NAME,
PAYMENT.rental().customer().LAST_NAME,
multisetAgg(
PAYMENT.PAYMENT_DATE,
PAYMENT.AMOUNT
).as("payments"),
sum(PAYMENT.AMOUNT).as("total"))
.from(PAYMENT)
.where(PAYMENT
.rental().inventory().FILM_ID.eq(FILM.FILM_ID))
.groupBy(
PAYMENT.rental().customer().CUSTOMER_ID,
PAYMENT.rental().customer().FIRST_NAME,
PAYMENT.rental().customer().LAST_NAME)
).as("customers")
)
.from(FILM)
.where(FILM.TITLE.like("A%"))
.orderBy(FILM.TITLE)
.limit(5)
.fetch();
You’ll be using var, of course, rather than denoting this insane type but I wanted to denote the type explicitly for the sake of the example.
Note again how implicit joins were really helpful here since we wanted to aggregate all the payments per customer, we can just select from PAYMENT and group by the payment’s PAYMENT.rental().customer(), as well as correlate the subquery by PAYMENT.rental().inventory().FILM_ID without any extra effort.
The executed SQL looks like this, where you can see the generated implicit joins (run it on your PostgreSQL Sakila database!):
select
film.title,
(
select coalesce(
jsonb_agg(jsonb_build_object(
'first_name', t.first_name,
'last_name', t.last_name
)),
jsonb_build_array()
)
from (
select alias_78509018.first_name, alias_78509018.last_name
from (
film_actor
join actor as alias_78509018
on film_actor.actor_id = alias_78509018.actor_id
)
where film_actor.film_id = film.film_id
) as t
) as actors,
(
select coalesce(
jsonb_agg(jsonb_build_object('name', t.name)),
jsonb_build_array()
)
from (
select alias_130639425.name
from (
film_category
join category as alias_130639425
on film_category.category_id =
alias_130639425.category_id
)
where film_category.film_id = film.film_id
) as t
) as categories,
(
select coalesce(
jsonb_agg(jsonb_build_object(
'first_name', t.first_name,
'last_name', t.last_name,
'payments', t.payments,
'total', t.total
)),
jsonb_build_array()
)
from (
select
alias_63965917.first_name,
alias_63965917.last_name,
jsonb_agg(jsonb_build_object(
'payment_date', payment.payment_date,
'amount', payment.amount
)) as payments,
sum(payment.amount) as total
from (
payment
join (
rental as alias_102068213
join customer as alias_63965917
on alias_102068213.customer_id =
alias_63965917.customer_id
join inventory as alias_116526225
on alias_102068213.inventory_id =
alias_116526225.inventory_id
)
on payment.rental_id = alias_102068213.rental_id
)
where alias_116526225.film_id = film.film_id
group by
alias_63965917.customer_id,
alias_63965917.first_name,
alias_63965917.last_name
) as t
) as customers
from film
where film.title like 'A%'
order by film.title
fetch next 5 rows only
The result, in JSON, now looks something like this:
[
{
"title": "ACADEMY DINOSAUR",
"actors": [
{
"first_name": "PENELOPE",
"last_name": "GUINESS"
},
{
"first_name": "CHRISTIAN",
"last_name": "GABLE"
},
{
"first_name": "LUCILLE",
"last_name": "TRACY"
},
...
],
"classes": [{ "name": "Documentary" }],
"clients": [
{
"first_name": "SUSAN",
"last_name": "WILSON",
"payments": [
{
"payment_date": "2005-07-31T22:08:29",
"amount": 0.99
}
],
"whole": 0.99
},
{
"first_name": "REBECCA",
"last_name": "SCOTT",
"funds": [
{
"payment_date": "2005-08-18T18:36:16",
"amount": 0.99
}
],
"whole": 0.99
},
...
That’s it. Nesting collections in arbitrary methods is totally easy and intuitive. No N+1, no deduplication. Simply declare the ends in precisely the shape you require in your shopper.
There isn’t a different approach to pull off this complexity with such ease, than letting your RDBMS do the heavy lifting of planning and operating such a question, and letting jOOQ do the mapping.
Conclusion
We had this type of performance all alongside. We simply by no means used it, or not sufficient. Why? As a result of shopper APIs didn’t make it accessible sufficient. As a result of RDBMS didn’t agree on syntax sufficient.
That’s now over. jOOQ makes use of customary SQL MULTISET syntax in its DSL API, enhances it with the artificial MULTISET_AGG mixture operate, the way in which all RDBMS ought to have applied it (go Informix, Oracle). We will wait for one more 40 years for the opposite RDBMS to implement this, or we simply use jOOQ right this moment.
And, I can not stress this sufficient:
- That is all kind protected
- There isn’t a reflection
- The nesting is completed within the database utilizing SQL (through SQL/XML or SQL/JSON for now)
- … So, execution planners can optimise your total question
- … No further columns or further queries or different further work is carried out within the database
This works on all dialects which have both SQL/XML or SQL/JSON assist (or each), together with the foremost fashionable dialects:
- MySQL
- Oracle
- PostgreSQL
- SQL Server
And it’s provided beneath the standard license phrases of jOOQ. So, blissful nesting of collections.
Addendum: Utilizing SQL/XML or SQL/JSON instantly
It’s possible you’ll be tempted to make use of this all over the place. And also you rightfully achieve this. However watch out for this, in case your SQL shopper is consuming XML or JSON instantly, there’s no want to make use of MULTISET. Use jOOQ’s native SQL/XML or SQL/JSON assist that was launched in jOOQ 3.14. That method, you gained’t convert from JSON to jOOQ outcomes to JSON, however stream the JSON (or XML) to your frontend instantly.
