jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more

New dialects:

jOOQ 3.20 ships with 2 new experimental dialects:

• ClickHouse in all editions, including the jOOQ Open Source Edition
• Databricks in the jOOQ Enterprise Edition

ClickHouse is a fast-moving SQL dialect with a historic vendor-specific syntax that is gradually migrated to a more standards compliant alternative, which is why our support is still experimental. A lot of behaviours differ from what one would expect elsewhere, including NULL handling, which is very different from standard SQL. Future jOOQ versions will continue deepening this integration.

Databricks offers a very promising SQL dialect with a lot of functionality, which we’re going to cover in full with the next version jOOQ 3.21. An initial version of the dialect is shipped already in 3.20 as an experimental dialect. 3.20 will continue to receive bug fixes and Databricks improvements as we work with early adopting customers.

DuckDB improvements

We continue supporting various DuckDB SQL features, including:

• ARRAY, ROW, and STRUCT support
• MULTISET support
• JSON support
• Date time arithmetic
• Sequences
• More DDL support
• More DML support
• Spatial support
• And much more

In order to better integrate with Reactor, probably the most popular reactive streams API on the JVM, we’ve added a new jOOQ-reactor-extensions module, which offers an implementation of the new SubscriberProvider SPI, a new SPI in the Configuration that helps make jOOQ’s R2DBC specific internals Reactor Context aware.

The jOOQ-beans-extensions module now hosts our support for the @ConstructorProperties annotation in the DefaultRecordMapper, an annotation that is located in the JDK’s jdk.desktop module. This allows us to move the heavy module dependency out of jOOQ’s core library, which is useful for those applications that want to omit shipping the complete JDK and keep low footprints.

The jOOQ-jpa-extensions module now hosts our support for the various JPA annotations like @Column, @Table, also in the DefaultRecordMapper, effectively removing the optional jakarta.persistence dependency from the core library, as well as offering alternative implementations of annotation based mapping.

Support for Oracle type hierarchies

Oracle is the most sophisticated ORDBMS implementation, with a rich set of object-oriented PL/SQL language features. jOOQ 3.20 finally adds support for PL/SQL OBJECT type hierarchies both in our code generator as well as in the runtime library, making jOOQ an even strong match for your PL/SQL language usage

This is a commercial only feature.

Better spatial support

A lot of additional spatial functions have been added to jOOQ’s spatial support. These efforts also include many improvements to the DuckDB and Oracle spatial implementations.

For more information, refer to:

• https://www.jooq.org/doc/dev/manual/sql-building/column-expressions/spatial-functions/
• https://www.jooq.org/doc/dev/manual/sql-building/conditional-expressions/spatial-predicates/

This is a commercial only feature.

DECFLOAT support

A variety of dialects offer an additional decimal floating point data type, DECFLOAT, in addition to the binary floating point types REAL (32 bit), DOUBLE PRECISION (64 bit), and FLOAT (variable sized). Our new org.jooq.Decfloat type allows for capturing these types in our code generator and runtime.

Synonym support

A variety of dialects support the concept of a SYNONYM or ALIAS where an alternative name can be given to any object type.

jOOQ 3.20 supports these synonyms both in the code generator as well as the DDL API. Future jOOQ versions may continue to improve synonym support, e.g. by generating type aliases in Kotlin or Scala.

For more information, refer to:

• https://www.jooq.org/doc/dev/manual/code-generation/codegen-synonyms/

This is a commercial only feature.

Hidden columns

Just like a few dialects support hidden or invisible columns, this is now possible in jOOQ as well, at the client side. Hiding columns effectively removes them from:

• Asterisk expansions
• selectFrom() and similar calls
• Generated records, POJOs, and interfaces

At the same time, the columns are still available for explicit references. This feature can be useful for schema evolution purposes, where data of deprecated columns is kept around for historic purposes. It works well together with the column deprecation feature of the code generator:

• https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-comments/

For more information about hidden columns, refer to:

• https://www.jooq.org/doc/dev/manual/sql-building/column-expressions/hidden-columns/

This is a commercial only feature.

Kotlin 2 and Scala 3 support

Starting from jOOQ 3.20, we formally support both Kotlin 2 and Scala 3 and fully integration tests both the core libraries as well as the code generator and extension libraries on these language versions. For Scala support, please also consider our support matrix:

• https://www.jooq.org/download/support-matrix-scala

JDK baselines

The jOOQ Open Source Edition 3.20 increases its baseline to JDK 21. Support for older JDKs is available int he commercial distributions, see:

• https://www.jooq.org/download/support-matrix-jdk

Record dirty tracking

jOOQ has long supported record dirty tracking in its UpdatableRecord allowing for a simple way of performing CRUD. With jOOQ 3.20, it will be possible for users to override the default behaviour of the dirty flag from a “touched” semantics to a “modified” semantics, effectively sending only actual modifications to the database.

For more information, refer to:

• https://www.jooq.org/doc/dev/manual/sql-building/dsl-context/custom-settings/settings-dirty-tracking/

DML join improvements

jOOQ 3.20 now supports the useful DELETE .. USING and UPDATE .. FROM syntaxes on all RDBMS through a new set of SQL transformations that allow for these vendor specific JOIN syntaxes to be used in DML statements even in the absence of MERGE statement support.

At the same time, MERGE itself also received an upgrade, including:

• BY SOURCE and BY TARGET support
• Multiple WHEN NOT MATCHED AND .. clause support

For more information, refer to:

• https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/update-statement/update-from/
• https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/delete-statement/delete-using/
• https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/merge-statement/

Code generation improvements

A lot of problems related to the generation of interfaces, immutablePojos, UDTs, etc. have been addressed in this release, making generated code more sound for various configuration edge cases where these features are combined.

Manual search

We have (at last!) added in-page search functionality to our user manual, so users can better find information on our website.

More improvements

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