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

jOOQ 3.19 lastly delivers on a set of options that can 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 possibly can write:

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

The generated question seems to be one thing like this:

SELECT
  buyer.first_name,
  buyer.last_name,
  nation.title
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 rather more readable than the specific one. Along with that, it’s inconceivable to ever write a mistaken be a part of predicate this fashion (mistaken 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, typically. Think about a brand new model of the SQL commonplace 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 could possibly 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 comparable with the REF kind, nevertheless it’s by no means been adopted, regrettably.

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

New: Specific path joins

As talked about initially, one new factor in jOOQ 3.19 is help for express path joins. This was not often essential to this point, as a result of the implicit to-one be a part of semantics is clear, however generally, it’s possible 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 choose LEFT JOIN over INNER JOIN.

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

You may 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)

   // The whole path shall 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 can too assign every path to an area variable, and use aliases and all the opposite jOOQ options, as all the time.

Observe that the JOIN .. ON clause is now optionally available, as a result of jOOQ already generates it for you primarily based on the out there international key meta information. In case you require an extra be a part of predicate on a path (which may be very not often essential, 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'll have your causes to show the nation provided that
      // town title begins with A
      .on(CUSTOMER.deal with().metropolis().NAME.like("A%"))
   .leftJoin(CUSTOMER.deal with().metropolis().nation())
   .fetch();

To be able to revenue from this new path primarily based be a part of, the <implicitJoinPathTableSubtypes/> code era flag must be turned on (which it’s, by default).

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

New: to-many path joins

The principle 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 (through 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 might be tempting to put in writing queries this fashion, however this could change one of many elementary assumptions of SQL, specifically that rows might 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 outline 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 shall be repeated, which can or might not be what individuals count on.

It is a very un-SELECT-y factor to do, as if Stream.map() may 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 to be as if we’re querying for actors who performed in movies beginning with the letter A, however in truth, we’re once more making a cartesian product between ACTOR × FILM the place every actor is repeated for every matching movie. Since we’re not projecting any FILM columns, this seems to be like a mistake! The outcome could seem like 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, then you definately could be tempted to take away the duplicates with DISTINCT, which simply makes issues worse.

So, as a way to make issues express, it’s a must to 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 necessary than with to-one path joins, so that you’re compelled to select.

Overriding the default

Observe that when you disagree with the above default of disallowing such queries, you possibly can 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’ll have seen within the examples above that we skipped the connection desk when writing ACTOR.movie(). That is purely a code era 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 possibly can write ACTOR.movie().TITLE as a substitute of ACTOR.filmActor().movie().TITLE. In case you ever have to entry auxiliary attributes on the connection desk, you possibly can clearly nonetheless do this, as each paths can be found from the code generator.

New: implicit path correlation

Presumably 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 rapidly very tedious to put in writing, and unreadable. Now, with implicit path correlations, you possibly can 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 significantly helpful with MULTISET correlated subqueries as nicely! 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",
...
}
]

Every little thing continues to be kind protected, and you’ll proceed combining this with ad-hoc conversion as a way to map information to your DTOs very simply:

document Actor (
    String firstName, String lastName, 
    Listing<String> titles, 
    Listing<String> classes
) {}

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

It’s actually laborious 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!