jOOQ 3.19 has been launched with assist for DuckDB, Trino, and way more

New Dialects

It’s been a number of releases since we’ve added assist for brand spanking new dialects, however lastly some very fascinating RDBMS of accelerating reputation have joined the jOOQ household together with:

• DuckDB (experimental assist)
• Trino

These dialects can be found in all jOOQ editions.

New dialect variations

Along with these fully new dialects, massive new CockroachDB and Oracle variations have shipped:

• CockroachDB 23
• Oracle 23c

We’ve added assist for a lot of new Oracle options, together with:

• Domains
• UPDATE .. FROM
• IF [ NOT ] EXISTS
• Desk worth constructor
• SELECT with out FROM

In addition to CockroachDB options, together with:

• Triggers
• Saved capabilities
• UDTs
• Materialized views
• LATERAL
• New native DML clauses
• NULLS FIRST and NULLS LAST

Be a part of path enhancements

Implicit to-one path joins have been with jOOQ since model 3.11. Now, we’ve significantly improved this very helpful characteristic by including assist for:

That is finest proven by instance:

// Earlier than 3.19:
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();

// After 3.19:
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();

This characteristic is offered in all jOOQ editions.

Gradle plugin

One of many longest awaited options is an official jooq-codegen-gradle plugin, that provides a good integration with gradle’s process system whereas being launched in the identical launch cadence as jOOQ itself.

Our new gradle plugin helps all the code technology options in each an idiomatic groovy or kotlin DSL

Extra data right here: https://www.jooq.org/doc/3.19/guide/code-generation/codegen-gradle/

This characteristic is offered in all jOOQ editions.

Business maven repository

A characteristic that lots of our paying clients have needed for a very long time has lastly been carried out: our industrial maven repository at https://repo.jooq.org, the place all historic and new industrial solely jOOQ artifacts in addition to snapshot variations can be hosted, along with our ZIP file obtain web site: https://www.jooq.org/obtain/variations

This characteristic is offered solely in industrial jOOQ editions.

Insurance policies

Much like PostgreSQL’s highly effective POLICY characteristic, or Oracle’s Digital Personal Database, jOOQ 3.19 permits for declaring insurance policies that act as automated filters on a few of your tables, to permit for a easy and thorough row stage safety implementation.

For instance, with a coverage on the multi tenancy succesful CUSTOMER desk, a question like this:

ctx.choose(CUSTOMER.ID, CUSTOMER.NAME)
   .from(CUSTOMER)
   .fetch();

May in truth run a SQL assertion like this, as a substitute:

SELECT CUSTOMER.ID, CUSTOMER.NAME
FROM CUSTOMER
WHERE CUSTOMER.TENANT_ID = 42

Not simply queries, however all DML statements are rewritten to disallow any inaccessible information from being written / learn.

Extra data right here: https://www.jooq.org/doc/3.19/guide/sql-building/queryparts/insurance policies/

This characteristic is offered solely in industrial jOOQ editions.

UDT paths

Along with including Person Outlined Sort (UDT) assist to CockroachDB and Informix, we’ve improved our code generator assist for UDTs in a means for attribute paths to be made accessible to shopper code in a sort protected means.

So, with sorts like these:

CREATE TYPE nation AS (
  iso_code TEXT
);

CREATE TYPE title AS (
  first_name TEXT,
  last_name TEXT
);

CREATE TYPE handle AS (
  road TEXT,
  ...,
  nation COUNTRY
);

CREATE TABLE buyer (
  id INT PRIMARY KEY,
  title NAME,
  handle ADDRESS
);

Now you can destructure the UDTs straight in your SQL question like this:

ctx.choose(
        CUSTOMER.NAME.FIRST_NAME,
        CUSTOMER.NAME.LAST_NAME,
        CUSTOMER.ADDRESS.COUNTRY.ISO_CODE)
   .from(CUSTOMER)
   .fetchOne();

Extra data right here: https://www.jooq.org/doc/3.19/guide/sql-building/column-expressions/user-defined-type-attribute-paths/

This characteristic is offered in all jOOQ editions.

Set off meta information

The code generator can now reverse engineer set off meta information from most RDBMS that assist triggers. This meta information could be useful at runtime, e.g. to render improved RETURNING assist within the absence of triggers, in dialects the place triggers require particular emulations (e.g. SQLite or SQL Server).

This characteristic is offered solely in industrial jOOQ editions.

Hierarchies

A brand new Collector has been added to recursively gather a flat illustration of hierarchical information into an object hierarchy. This performs very nicely with our MULTISET nested assortment assist.

For extra particulars, see this weblog put up: https://weblog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/

This characteristic is offered in all jOOQ editions.

Java 8 assist faraway from jOOQ Specific and Skilled Editions

Like different main platforms, we’re transferring on to assist with the adoption of newer JDK variations. Our Java 8 assist can be discontinued for the jOOQ Specific Version and jOOQ Skilled Version. Should you require Java 8 assist, you possibly can improve to the jOOQ Enterprise Version, which can proceed supporting Java 8 for one more few minor releases, or keep on jOOQ 3.18, which will even obtain bug fixes for one more whereas.

This alteration impacts solely industrial jOOQ editions.

For a whole checklist different, minor enhancements, see the beneath change notes.

Extra enhancements

For details about the numerous different minor enhancements, bug fixes, and many others., please discuss with the launch notes.