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:
SELECT cu.first_name, cu.last_name FROM customer AS cu WHERE cu.address.city.country.country = 'Switzerland'
It translates to this query in native SQL:
SELECT cu.first_name, cu.last_name FROM customer AS cu JOIN address AS ad ON cu.address_id = ad.address_id JOIN city AS ci ON a.city_id = ci.city_id JOIN country AS co ON ci.country_id = co.country_id WHERE co.country = 'Switzerland'
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:
Domains d = DOMAINS.as("d");
DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");
CheckConstraints cc = CHECK_CONSTRAINTS.as("cc");
for (Record record : create()
.select(
d.DOMAIN_SCHEMA,
d.DOMAIN_NAME,
d.DATA_TYPE,
d.CHARACTER_MAXIMUM_LENGTH,
d.NUMERIC_PRECISION,
d.NUMERIC_SCALE,
d.DOMAIN_DEFAULT,
cc.CHECK_CLAUSE)
.from(d)
.join(dc)
.on(row(d.DOMAIN_CATALOG, d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
.eq(dc.DOMAIN_CATALOG, dc.DOMAIN_SCHEMA, dc.DOMAIN_NAME))
.join(cc)
.on(row(dc.CONSTRAINT_CATALOG,
dc.CONSTRAINT_SCHEMA,
dc.CONSTRAINT_NAME)
.eq(cc.CONSTRAINT_CATALOG,
cc.CONSTRAINT_SCHEMA,
cc.CONSTRAINT_NAME))
.where(d.DOMAIN_SCHEMA.in(getInputSchemata()))
.orderBy(d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
) { ... }
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
Starting with jOOQ 3.14, we’ve reworked the way synthetic keys work, and the commercial editions will support synthetic foreign keys as well.
You can specify a configuration like this:
<configuration>
<generator>
<database>
<syntheticObjects>
<primaryKeys>
<primaryKey>
<tables>
CHECK_CONSTRAINTS|CONSTRAINTS|TABLE_CONSTRAINTS
</tables>
<fields>
<field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
</fields>
</primaryKey>
<primaryKey>
<tables>DOMAINS</tables>
<fields>
<field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
</fields>
</primaryKey>
</primaryKeys>
<foreignKeys>
<foreignKey>
<tables>DOMAIN_CONSTRAINTS</tables>
<fields>
<field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
</fields>
<referencedTable>CHECK_CONSTRAINTS</referencedTable>
</foreignKey>
<foreignKey>
<tables>DOMAIN_CONSTRAINTS</tables>
<fields>
<field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
</fields>
<referencedTable>DOMAINS</referencedTable>
</foreignKey>
</foreignKeys>
</syntheticObjects>
</database>
</generator>
</configuration>
And already jOOQ’s code generator will think that these views were tables that actually had constraints like the below:
ALTER TABLE CHECK_CONSTRAINTS
ADD PRIMARY KEY (
CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
);
ALTER TABLE DOMAINS
ADD PRIMARY KEY (
DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
);
ALTER TABLE DOMAIN_CONSTRAINTS
ADD FOREIGN KEY (
CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
)
REFERENCES CHECK_CONSTRAINTS;
ALTER TABLE DOMAIN_CONSTRAINTS
ADD FOREIGN KEY (
DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
)
REFERENCES DOMAINS;
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 meta data available to the code generator, all the numerous goodies are now available on views as well, including:
- The
synthetic JOIN .. ON KEYsyntax - Implicit joins
- Navigational methods on generated TableRecords
- Embedded keys (another exciting jOOQ 3.14 feature)
Let’s look at
Implicit joins
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:
DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");
for (Record record : create()
.select(
dc.domains().DOMAIN_SCHEMA,
dc.domains().DOMAIN_NAME,
dc.domains().DATA_TYPE,
dc.domains().CHARACTER_MAXIMUM_LENGTH,
dc.domains().NUMERIC_PRECISION,
dc.domains().NUMERIC_SCALE,
dc.domains().DOMAIN_DEFAULT,
dc.checkConstraints().CHECK_CLAUSE)
.from(dc)
.where(dc.domains().DOMAIN_SCHEMA.in(getInputSchemata()))
.orderBy(dc.domains().DOMAIN_SCHEMA, dc.domains().DOMAIN_NAME)
) { ... }
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.
Future work
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
ParsingConnection
And much more!
from Java, SQL and jOOQ. https://ift.tt/3jWihvY
via IFTTT
No comments:
Post a Comment