Jooq 3.19 has been released with support for duckdb, trino, and much more

New dialects

It’s been a more releases since we added support for new dialects, but finally some very interesting rdbms of increasing popularity have Joineed The joining the joining the joining the joining

• DuckDB (Experimental Support)
• Trino

These dialects are available in all joooq editions.

New dialect versions

In addition to these entryly new dielects, 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, include:

• 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 with all the way in the Samme Release Cadence Cadenc as Juoq.

Our new gradle plugin supports all of the code generation features in both an idiomatic grooovy 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 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 everyone forms, to all forms, to allow for a somple and Thorough Row Level Security Implementation.

For example, with a policy on the multi tenance 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 uds in a way for attribute paths to be made accessial to claient 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

This feature is available in all Jooq Editions.

Trigger meta data

The code generator can now revise engineer trigger meta data from most rdbms that support triggers. This meta data can be helpsful at Runtime, EG to Render Improved Returning Support in the absence of triggers, in dialects where triggers requires special emulations (EG SQLE SQLITE or SQLE SICLITE SAQLITE

This feature is available only in Commercial Jooq Editions.

Hierrachies

A new collector has been added to recursively collect a flat representation of hierchical data into an object hierchy. This plays very well with our multiset nested collection support.

For more details, see this blog post: https://blog.jooq.org/how- to–aa-list-flat-flat- elements-Anto-a-hairachy- 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 joooq enterprise edition, which will continue support 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.