jOOQ 3.19 has been released with support for DuckDB, Trino, and much more

New Dialects

It’s been a few releases since we’ve added support for new dialects, but finally some very interesting RDBMS of increasing popularity have joined the jOOQ family including:

• DuckDB (experimental support)
• Trino

These dialects are available in all jOOQ editions.

New dialect versions

In addition to these entirely new dialects, big new CockroachDB and Oracle versions have shipped:

• CockroachDB 23
• Oracle 23c

We’ve added support for many new Oracle features, including:

• Domains
• UPDATE .. FROM
• IF [ NOT ] EXISTS
• Table value constructor
• SELECT without FROM

As well as CockroachDB features, including:

• Triggers
• Stored functions
• UDTs
• Materialized views
• LATERAL
• New native DML clauses
• NULLS FIRST and NULLS LAST

Join path improvements

Implicit to-one path joins have been with jOOQ since version 3.11. Now, we’ve greatly improved this very useful feature by adding support for:

This is best shown by example:

// Before 3.19:
ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .where(exists(
       selectOne()
       .from(FILM_ACTOR)
       .where(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID))
       .and(FILM_ACTOR.film().TITLE.like("A%"))
   ))
   .fetch();

// After 3.19:
ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .where(exists(
       selectOne()
       .from(ACTOR.film())
       .where(ACTOR.film().TITLE.like("A%"))
   ))
   .fetch();

This feature is available in all jOOQ editions.

Gradle plugin

One of the longest awaited features is an official jooq-codegen-gradle plugin, that offers a tight integration with gradle’s task system while being released in the same release cadence as jOOQ itself.

Our new gradle plugin supports all of the code generation features in both an idiomatic groovy or kotlin DSL

More information here: https://www.jooq.org/doc/3.19/manual/code-generation/codegen-gradle/

This feature is available in all jOOQ editions.

Commercial maven repository

A feature that many of our paying customers have wanted for a long time has finally been implemented: our commercial maven repository at https://repo.jooq.org, where all historic and new commercial only jOOQ artifacts as well as snapshot versions will be hosted, in addition to our ZIP file download website: https://www.jooq.org/download/versions

This feature is available only in commercial jOOQ editions.

Policies

Similar to PostgreSQL’s powerful POLICY feature, or Oracle’s Virtual Private Database, jOOQ 3.19 allows for declaring policies that act as automatic filters on some of your tables, to allow for a simple and thorough row level security implementation.

For example, with a policy on the multi tenancy capable CUSTOMER table, a query like this:

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

Might in fact run a SQL statement like this, instead:

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

Not just queries, but all DML statements are rewritten to disallow any inaccessible data from being written / read.

More information here: https://www.jooq.org/doc/3.19/manual/sql-building/queryparts/policies/

This feature is available only in commercial jOOQ editions.

UDT paths

In addition to adding User Defined Type (UDT) support to CockroachDB and Informix, we’ve improved our code generator support for UDTs in a way for attribute paths to be made accessible to client code in a type safe way.

So, with types like these:

CREATE TYPE country AS (
  iso_code TEXT
);

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

CREATE TYPE address AS (
  street TEXT,
  ...,
  country COUNTRY
);

CREATE TABLE customer (
  id INT PRIMARY KEY,
  name NAME,
  address ADDRESS
);

You can now destructure the UDTs directly in your SQL query like this:

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

More information here: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/user-defined-type-attribute-paths/

This feature is available in all jOOQ editions.

Trigger meta data

The code generator can now reverse engineer trigger meta data from most RDBMS that support triggers. This meta data can be helpful at runtime, e.g. to render improved RETURNING support in the absence of triggers, in dialects where triggers require special emulations (e.g. SQLite or SQL Server).

This feature is available only in commercial jOOQ editions.

Hierarchies

A new Collector has been added to recursively collect a flat representation of hierarchical data into an object hierarchy. This plays very well with our MULTISET nested collection support.

For more details, see this blog post: https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/

This feature is available in all jOOQ editions.

Java 8 support removed from jOOQ Express and Professional Editions

Like other leading platforms, we’re moving on to help with the adoption of newer JDK versions. Our Java 8 support will be discontinued for the jOOQ Express Edition and jOOQ Professional Edition. If you require Java 8 support, you can upgrade to the jOOQ Enterprise Edition, which will continue supporting Java 8 for another few minor releases, or stay on jOOQ 3.18, which will also receive bug fixes for another while.

This change affects only commercial jOOQ editions.

For a complete list other, minor improvements, see the below change notes.

More improvements

For information about the many other minor improvements, bug fixes, etc., please refer to the release notes.