jOOQ 3.19 gives many new and helpful path primarily based be a part of options

jOOQ 3.19 lastly delivers on a set of options that may tremendously simplify your queries additional, after jOOQ 3.11 launched implicit to-one joins:

What are these options?

Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) help “path joins” (they could have totally different names for this idea). A path be a part of is a be a part of derived from a path the place the question language permits for navigating international keys. E.g. in jOOQ, you may write:

ctx.choose(
       CUSTOMER.FIRST_NAME,
       CUSTOMER.LAST_NAME,
       CUSTOMER.deal with().metropolis().nation().NAME)
   .from(CUSTOMER)
   .fetch();

The generated question seems one thing like this:

SELECT
  buyer.first_name,
  buyer.last_name,
  nation.identify
FROM
  buyer
  JOIN deal with ON buyer.address_id = deal with.address_id
  JOIN metropolis ON deal with.city_id = metropolis.city_id 
  JOIN nation ON metropolis.country_id = nation.country_id

Relying in your tastes, the implicit be a part of syntax could also be way more readable than the express one. Along with that, it’s unattainable to ever write a flawed be a part of predicate this manner (flawed columns in contrast, or lacking columns in a composite key) as a result of the meta information is thought to jOOQ and generated accurately, each time.

Very idiomatic SQL

In truth these options are fairly idiomatic in SQL, normally. Think about a brand new model of the SQL customary that permits for declaring “labels” on international keys:

CREATE TABLE guide (
  ..
  author_id INT REFERENCES creator 
    PARENT PATH LABEL creator 
    CHILD PATH LABEL books
);

And now, you may reference these labels in queries:

SELECT guide.title, guide.creator.first_name
FROM guide

Or:

SELECT 
  creator.id, 
  creator.first_name, 
  creator.last_name,
  COUNT(*)
FROM creator
LEFT JOIN creator.books
GROUP BY creator.id

As a result of: why not? We are able to dream! In truth, ORDBMS extensions (as carried out by Oracle), carried out one thing related with the REF kind, nevertheless it’s by no means been adopted, regrettably.

However for now, let’s have a look at what new issues jOOQ is providing.

New: Express path joins

As talked about initially, one new factor in jOOQ 3.19 is help for express path joins. This was not often vital thus far, as a result of the implicit to-one be a part of semantics is clear, however typically, chances are you’ll need to make the be a part of path declaration express, or have management over the be a part of kind on a per-query foundation, e.g. when you want LEFT JOIN over INNER JOIN.

Observe: jOOQ already generates LEFT JOIN for nullable international keys.

You’ll be able to explicitly be a part of paths like this now:

ctx.choose(
       CUSTOMER.FIRST_NAME,
       CUSTOMER.LAST_NAME,
       CUSTOMER.deal with().metropolis().nation().NAME)
   .from(CUSTOMER)

   // All the path will probably be left joined:
   .leftJoin(CUSTOMER.deal with().metropolis().nation()
   .fetch();

Or much more explicitly, like this:

ctx.choose(
       CUSTOMER.FIRST_NAME,
       CUSTOMER.LAST_NAME,
       CUSTOMER.deal with().metropolis().nation().NAME)
   .from(CUSTOMER)
   .leftJoin(CUSTOMER.deal with())
   .leftJoin(CUSTOMER.deal with().metropolis())
   .leftJoin(CUSTOMER.deal with().metropolis().nation())
   .fetch();

Clearly, you may also assign every path to a neighborhood variable, and use aliases and all the opposite jOOQ options, as at all times.

Observe that the JOIN .. ON clause is now elective, as a result of jOOQ already generates it for you primarily based on the obtainable international key meta information. If you happen to require a further be a part of predicate on a path (which could be very not often vital, and now, it’s lastly doable), you are able to do so:

ctx.choose(
       CUSTOMER.FIRST_NAME,
       CUSTOMER.LAST_NAME,
       CUSTOMER.deal with().metropolis().nation().NAME)
   .from(CUSTOMER)
   .leftJoin(CUSTOMER.deal with().metropolis())
      // You might have your causes to show the nation provided that
      // town identify begins with A
      .on(CUSTOMER.deal with().metropolis().NAME.like("A%"))
   .leftJoin(CUSTOMER.deal with().metropolis().nation())
   .fetch();

With the intention to revenue from this new path primarily based be a part of, the code technology flag must be turned on (which it’s, by default).

The function additionally works with out the flag, however then, the ON clause will probably be obligatory for many be a part of sorts. Turning off the flag may be helpful if you wish to keep away from too many sorts being generated by jOOQ (one Path kind per desk).

New: to-many path joins

The primary purpose for introducing the above express path primarily based joins are the brand new to-many path joins. Implicit to-many path joins are unavailable by default (by way of an exception thrown), due to their bizarre semantics inside a question. For instance, when discovering all of the movies of an actor:

ctx.choose(
      ACTOR.FIRST_NAME,
      ACTOR.LAST_NAME,
      ACTOR.movie().TITLE)
   .from(ACTOR)
   .fetch();

It could be tempting to jot down queries this manner, however this could change one of many basic assumptions of SQL, specifically that rows may be generated solely within the FROM clause (or in GROUP BY, with GROUPING SETS), and so they’re filtered primarily within the WHERE, HAVING, QUALIFY clauses. See an summary of SQL clauses right here.

However within the above instance, a projection (i.e. an expression in SELECT) is able to producing rows by making a cartesian product! Simply by including the FILM.TITLE column, all of a sudden, an ACTOR.FIRST_NAME and ACTOR.LAST_NAME will probably be repeated, which can or is probably not what individuals anticipate.

It is a very un-SELECT-y factor to do, as if Stream.map() might generate or filter rows!

Even worse, what when you write this:

ctx.choose(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .the place(ACTOR.movie().TITLE.like("A%"))
   .fetch();

This seems as if we’re querying for actors who performed in movies beginning with the letter A, however actually, we’re once more making a cartesian product between ACTOR × FILM the place every actor is repeated for every matching movie. Since we’re now not projecting any FILM columns, this seems like a mistake! The outcome might appear to be this:

|first_name|last_name|
|----------|---------|
|PENELOPE  |GUINESS  |
|PENELOPE  |GUINESS  |
|PENELOPE  |GUINESS  |
|NICK      |WAHLBERG |
|NICK      |WAHLBERG |
|ED        |CHASE    |
|ED        |CHASE    |
|ED        |CHASE    |

And when you’re not cautious, you then may be tempted to take away the duplicates with DISTINCT, which simply makes issues worse.

So, to be able to make issues express, you must explicitly declare the paths within the FROM clause, e.g.:

ctx.choose(
      ACTOR.FIRST_NAME,
      ACTOR.LAST_NAME,
      ACTOR.movie().TITLE)
   .from(ACTOR)
   .leftJoin(ACTOR.movie())
   .fetch();

Now, the cartesian product is seen within the jOOQ question, and doesn’t shock you as a developer (or reviewer) of this code anymore. Plus, with to-many path joins, the INNER or OUTER semantics of the JOIN is extra vital than with to-one path joins, so that you’re pressured to select.

Overriding the default

Observe that when you disagree with the above default of disallowing such queries, you may inform jOOQ to permit implicit to-many path joins by specifying a brand new Settings.renderImplicitJoinType worth:

Settings settings = new Settings()
    .withRenderImplicitJoinType(RenderImplicitJoinType.LEFT_JOIN);

Many-to-many paths

You might have observed within the examples above that we skipped the connection desk when writing ACTOR.movie(). That is purely a code technology function, the place the code generator recognises relationship tables primarily based on a novel constraint on the 2 international keys:

CREATE TABLE film_actor (
  actor_id BIGINT REFERENCES actor,
  film_id BIGINT REFERENCES movie,

  PRIMARY KEY (actor_id, film_id)
)

Since you love normalisation (there’s a constraint on the international keys) and also you hate gradual queries (you didn’t use an pointless surrogate key), this clearly qualifies as a relationship desk to jOOQ.

Therefore, you may write ACTOR.movie().TITLE as a substitute of ACTOR.filmActor().movie().TITLE. If you happen to ever must entry auxiliary attributes on the connection desk, you may clearly nonetheless try this, as each paths can be found from the code generator.

New: implicit path correlation

Probably probably the most highly effective new function is the implicit path correlation help, which permits for correlating subqueries primarily based on paths that begin with a desk reference of the outer question. That is once more greatest defined by instance.

Earlier than, you needed to correlate subqueries explicitly, like this, e.g. to search out all actors that performed in movies whose title begins with "A":

ctx.choose(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .the place(exists(
       selectOne()
       .from(FILM_ACTOR)
       .the place(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID))
       .and(FILM_ACTOR.movie().TITLE.like("A%"))
   ))
   .fetch();

That is shortly very tedious to jot down, and unreadable. Now, with implicit path correlations, you may simply entry the FILM_ACTOR and FILM tables from the ACTOR desk within the correlated subquery!

ctx.choose(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .the place(exists(
       selectOne()
       .from(ACTOR.movie())
       .the place(ACTOR.movie().TITLE.like("A%"))
   ))
   .fetch();

And even:

ctx.choose(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .leftSemiJoin(ACTOR.movie())
   .on(ACTOR.movie().TITLE.like("A%"))
   .fetch();

That is notably helpful with MULTISET correlated subqueries as properly! Getting actors, and all their movies, and all their movie classes is a breeze, much more than earlier than:

ctx.choose(
       ACTOR.FIRST_NAME, 
       ACTOR.LAST_NAME,
       multiset(choose(ACTOR.movie().TITLE).from(ACTOR.movie())).as("movies"),
       multiset(
           selectDistinct(ACTOR.movie().class().NAME)
           .from(ACTOR.movie().class())
       ).as("classes")
   )
   .from(ACTOR)
   .fetch();

That is how easy it’s now to supply a question producing information like this:

[
{
"first_name":"PENELOPE",
"last_name":"GUINESS",
"films":[
{ "title":"ACADEMY DINOSAUR" },
{ "title":"ANACONDA CONFESSIONS" },
{ "title":"ANGELS LIFE" },
...
],
"classes":[
{ "name":"Family" },
{ "name":"Games" },
{ "name":"Animation" },
...
]
},
{
"first_name":"NICK",
"last_name":"WAHLBERG",
...
}
]

All the things continues to be kind secure, and you may proceed combining this with ad-hoc conversion to be able to map information to your DTOs very simply:

report Actor (
    String firstName, String lastName, 
    Checklist titles, 
    Checklist classes
) {}

Checklist actors =
ctx.choose(
       ACTOR.FIRST_NAME, 
       ACTOR.LAST_NAME,
       multiset(choose(ACTOR.movie().TITLE).from(ACTOR.movie())).as("movies")
           .convertFrom(r -> r.accumulate(Information.intoList())),
       multiset(
           selectDistinct(ACTOR.movie().class().NAME)
           .from(ACTOR.movie().class())
       ).as("classes")
           .convertFrom(r -> r.accumulate(Information.intoList()))
   )
   .from(ACTOR)
   .fetch(Information.mapping(Actor::new));

It’s actually exhausting to not love this!

Conclusion

Time to improve to jOOQ 3.19! Path primarily based implicit joins have been round for a few years, since jOOQ 3.11. However now, with these 3 new options, you’ll love them much more!

Get jOOQ 3.19 now!