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

New Dialects

It’s been just a few releases since we’ve added assist for brand spanking new dialects, however lastly some very attention-grabbing RDBMS of accelerating recognition 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 solely 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 features
• UDTs
• Materialized views
• LATERAL
• New native DML clauses
• NULLS FIRST and NULLS LAST

Be 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 greatest 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 out there in all jOOQ editions.

Gradle plugin

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

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

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

This characteristic is out there in all jOOQ editions.

Business maven repository

A characteristic that a lot of our paying prospects 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 out there 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 degree 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();

Would possibly in reality run a SQL assertion like this, as an alternative:

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 knowledge from being written / learn.

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

This characteristic is out there solely in industrial jOOQ editions.

UDT paths

Along with including Consumer Outlined Sort (UDT) assist to CockroachDB and Informix, we’ve improved our code generator assist for UDTs in a method for attribute paths to be made accessible to consumer code in a kind secure method.

So, with sorts like these:

CREATE TYPE nation AS (
  iso_code TEXT
);

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

CREATE TYPE deal with AS (
  avenue TEXT,
  ...,
  nation COUNTRY
);

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

Now you can destructure the UDTs immediately 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 info right here: https://www.jooq.org/doc/3.19/handbook/sql-building/column-expressions/user-defined-type-attribute-paths/

This characteristic is out there in all jOOQ editions.

Set off meta knowledge

The code generator can now reverse engineer set off meta knowledge from most RDBMS that assist triggers. This meta knowledge 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 out there solely in industrial jOOQ editions.

Hierarchies

A brand new Collector has been added to recursively gather a flat illustration of hierarchical knowledge into an object hierarchy. This performs very effectively 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 out there 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. If you happen to require Java 8 assist, you’ll be able to improve to the jOOQ Enterprise Version, which is able to proceed supporting Java 8 for an additional few minor releases, or keep on jOOQ 3.18, which can even obtain bug fixes for an additional whereas.

This modification impacts solely industrial jOOQ editions.

For an entire listing different, minor enhancements, see the beneath change notes.

Extra enhancements

For details about the numerous different minor enhancements, bug fixes, and so forth., please consult with the launch notes.