jOOQ 3.19 has been launched with help for DuckDB, Trino, and far more

New Dialects

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

DuckDB (experimental help)
Trino

These dialects can be found in all jOOQ editions.

New dialect variations

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

CockroachDB 23
Oracle 23c

We’ve added help 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 part of path enhancements

Implicit to-one path joins have been with jOOQ since model 3.11. Now, we’ve drastically improved this very helpful function by including help 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 function 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 decent 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 info right here: https://www.jooq.org/doc/3.19/handbook/code-generation/codegen-gradle/

This function is out there in all jOOQ editions.

Business maven repository

A function that lots 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 function is out there solely in industrial jOOQ editions.

Insurance policies

Just like PostgreSQL’s highly effective POLICY function, or Oracle’s Digital Non-public 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 actual fact 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 knowledge from being written / learn.

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

This function is out there solely in industrial jOOQ editions.

UDT paths

Along with including Consumer Outlined Kind (UDT) help to CockroachDB and Informix, we’ve improved our code generator help for UDTs in a manner for attribute paths to be made accessible to consumer code in a sort protected manner.

So, with sorts like these:

CREATE TYPE nation AS (
  iso_code TEXT
);

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

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

CREATE TABLE buyer (
  id INT PRIMARY KEY,
  title NAME,
  tackle 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 function 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 help triggers. This meta knowledge will be useful at runtime, e.g. to render improved RETURNING help within the absence of triggers, in dialects the place triggers require particular emulations (e.g. SQLite or SQL Server).

This function is out there solely in industrial jOOQ editions.

Hierarchies

A brand new Collector has been added to recursively accumulate a flat illustration of hierarchical knowledge into an object hierarchy. This performs very effectively with our MULTISET nested assortment help.

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

This function is out there in all jOOQ editions.

Java 8 help faraway from jOOQ Categorical and Skilled Editions

Like different main platforms, we’re transferring on to assist with the adoption of newer JDK variations. Our Java 8 help can be discontinued for the jOOQ Categorical Version and jOOQ Skilled Version. If you happen to require Java 8 help, 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 may even obtain bug fixes for an additional whereas.

This variation impacts solely industrial jOOQ editions.

For an entire checklist different, minor enhancements, see the under change notes.