Using jOOQ 3.14 Synthetic Foreign Keys to Write Implicit Joins on Views
jOOQ has supported one of JPQL's most cool features for a while now: implicit joins.
Join the DZone community and get the full member experience.Join For Free
jOOQ has supported one of JPQL's most cool features for a while now: implicit joins. Using jOOQ, you can navigate your to-one relationships in a type safe way, generating
LEFT JOIN operations implicitly without the effort of having to keep thinking about join predicates, and the correct join order. Consider this Sakila database query here, if SQL supported implicit joins natively:
It translates to this query in native SQL:
Note: Inner joins are supported starting from jOOQ 3.14, depending on whether the foreign key is mandatory / not null. The default behaviour is to produce
LEFT JOIN which are the correct way to implicitly join optional foreign keys.
Implicit joins aren't a silver bullet. Not every
JOIN graph can be completely transformed into implicit join usage, and not every implicit join usage is more readable than native SQL
JOIN graphs. But to have this option is great. Especially, when your keys are composite keys.
Classic Joins on Views
In classic relational design, surrogate keys are often avoided, and I think we should still avoid them in many cases. Even if you don't agree, you may occasionally work on a schema where there are few to no surrogate keys. One such example is the standard SQL
INFORMATION_SCHEMA which is implemented, for example, in H2, HSQLDB, MariaDB, MySQL, PostgreSQL, or SQL Server.
For example, when querying HSQLDB's
DOMAIN_CONSTRAINTS view to reverse engineer DOMAIN types. The jOOQ query for that used to be:
So, the query joined the many-to-many relationship between
DOMAINS - DOMAIN_CONSTRAINTS - CHECK_CONSTRAINTS to get all the information required for generating domain types.
These views are not updatable, nor do they have any constraint information associated with them, but what if we were able to define synthetic constraints? jOOQ has supported synthetic primary keys to help make views updatable.
Synthetic Foreign Keys
You can specify a configuration like this:
And already jOOQ's code generator will think that these views were tables that actually had constraints like the below:
More sophisticated configuration is possible, e.g. to assign names to constraints, to have composite constraints using field ordering differing from the ordering in the table, or foreign keys referencing unique keys rather than primary keys. For a full description of what's available, please refer to the manual.
With the above synthetic metadata available to the code generator, all the numerous goodies are now available on views as well, including:
Let's look at
Implicit joins are now also possible on these views, meaning:
- You'll never have to remember including all the key columns in join predicates anymore (bye bye accidental cartesian products)
- Your code will still be correct in case your composite key changes to something else!
So, this is more than just merely a convenience thing, it's also a correctness thing. Our query from before can now be written like this, in a much more concise way:
Notice how we're using the relationship table as the only table to put in the
FROM clause. This way, we can navigate in both directions of the to-one relationships from
DOMAIN_CONSTRAINTS -> DOMAINS and
DOMAIN_CONSTRAINTS -> CHECK_CONSTRAINTS. The resulting SQL query is equivalent to the previous one, but all the nastiness of joining by 3-column-composite-keys is gone. I personally find this much more readable.
So far, only to-one relationships can be navigated this way. JPQL also offers navigating to-many relationships with a few restrictions. This is a slippery slope. When offering to-many relationships, some use-cases are obvious, but the semantics of others is less so. For example, it is not a good idea to let the
SELECT clause produce more (or less) rows depending on the presence of a projected column. This is why jOOQ so far produced only
LEFT JOIN for implicit joins, because that would guarantee that an implicitly joined column does not reduce the number of rows because of an
INNER JOIN not producing any matches.
Nevertheless, there is a lot that we can still add in #7536, including:
- Implicit to-many joins in the
FROMclause, where they don't cause any trouble
- Implicit joins in DML
- Parser support to offer this functionality also in https://www.jooq.org/translate and to everyone working with jOOQ SQL through jOOQ's
And much more!
Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.