JAVA

3.17.0 Release with Computed Columns, Audit Columns, Pattern Matching, Reactive Transactions and Kotlin Coroutine Support – Java, SQL and jOOQ.

testv September 29, 2024

0 0 4 minutes read

This release contiues the work from previous releases around more sophisticated SQL transformation capabilities, including:

Client side computed columns for both read and write operations
Audit columns
Pattern matching SQL transformations
More implicit JOIN capabilities

Client side computed columns

A ground breaking new core feature available in all commercial distributions is
the new client side computed columns feature, building on top of jOOQ 3.16’s
commercial support for readonly columns and server side computed columns.

Not all RDBMS support computed columns (e.g. using the standard SQL syntax
GENERATED ALWAYS AS), and if they do, they might not support them in both STORED (computed on write) and VIRTUAL (computed on read) variants. jOOQ can now emulate both features at the client side, by transforming your SQL queries:

STORED affects INSERT, UPDATE, DELETE, and MERGE
VIRTUAL affects SELECT and the RETURNING clause of DML statements. To make use of these, combine them with the new synthetic column generation feature.

Unlike their server side counterparts, these client side features can produce arbitrary expressions, including:

Implicit joins
Scalar subqueries
MULTISET subqueries
Much more

Think of this as “views” written in jOOQ, on a per-column basis. An expecially useful feature combination is to combine these computed columns with the new visibility modifier that allows for keeping computed columns (or the underlying base columns) private and thus invisible to user code.

More about this feature here

Audit columns

A special case of STORED client side computed columns are audit columns, whose most basic implementation comes in the form of:

CREATED_AT
CREATED_BY
MODIFIED_AT
MODIFIED_BY

Other approaches to auditing exist, including soft deletion, additional meta data, (bi)temporal versioning, but these columns are among the most popular approaches, making this commercial only convenience feature very useful to a lot of customers.

More about this feature here

Java 17 baseline for the jOOQ Open Source Edition

Java 17 has been the latest LTS, and it includes a lot of really cool features, including:

sealed types (essential for pattern matching)
records
instanceof pattern matching
text blocks
switch expressions

jOOQ 3.16’s experimental new Query Object Model (QOM) API experiments with sealed types, which will be adopted more generally once the QOM API is finalized.

To get broader user feedback on these improvements, as well as to embrace Java’s new LTS update cadence, we’ve decided to make Java 17 the baseline for the jOOQ 3.17 Open Source Edition, continuing our Java 8 and 11 support in the commercial jOOQ distributions.

The following older jOOQ releases will continue to receive upgrades for a while:

jOOQ 3.14: The last release with Java 8 support in the jOOQ Open Source
Edition and Java 6 support in the jOOQ Enterprise Edition
jOOQ 3.15 and 3.16: The last releases with Java 11 support in the jOOQ Open
Source Edition.

PostgreSQL data type support

The jooq-postgres-extensions module, which contained support for the HSTORE type, now has a lot more support for PostgreSQL specific data types, including array types of each of:

CIDR
CITEXT
LTREE
HSTORE
INET
RANGE (including all the specialisations for INT4, INT8, etc.)

In order to profit from these data types, just add the org.jooq:jooq-postgres-extensions module to your code generation and runtime dependencies, and the types are generated automatically.

Implicit JOIN improvements

In this release, we experimented with a few new implicit JOIN features, including support for implicit JOIN in DML statements. The current implementation produces correlated subqueries where JOIN isn’t supported in DML statements.

We’ve also experimented with creating a “convenience syntax” for other commonly used correlated subqueries, such as EXISTS(...) subqueries or MULTISET(...) subqueries. The experiment has been very interesting. The prototype, however, was rejected. See the discussions here:

Future jOOQ versions will implement the desired convenience in the form of more implicit JOIN functionality, offering the feature also as an implicit to-many JOIN.

A leftover from the prototype is the fact that you can now more easily project expressions other than classic Field<T> in your SELECT clause, namely:

Table<R> now extends SelectField<R>
Condition now extends Field<Boolean>

This means you can write a query like this:

Result<Record3<CustomerRecord, AddressRecord, Boolean>> result =
ctx.select(

      // Project a CustomerRecord directly
      CUSTOMER,

      // Project an AddressRecord from an implicit JOIN
      CUSTOMER.address(),

      // Project a boolean expression, instead of wrapping it with field()
      exists(
        selectOne()
        .from(PAYMENT)
        .where(PAYMENT.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
      )
   .from(CUSTOMER)
   .fetch();

Pattern matching SQL Transformations

SQL transformations have been a strategic feature set to recent jOOQ releases, offering additional compatibility between SQL dialects to commercial customers, such as, for example:

Transforming Oracle’s ROWNUM into equivalent window functions or LIMIT clauses.
Turning table lists including Oracle’s (+) operator into ANSI JOIN syntax.

This release ships with a new commercial only feature that directly transforms the new Query Object Model (QOM)’s expression tree prior to rendering. It does so by applying pattern matching to the expression tree. Some assorted examples include:

LTRIM(RTRIM(x)) into TRIM(x)
x != a AND x != b into x NOT IN (a, b)
x IN (a, b, c) AND x IN (b, c, d) into x IN (b, c)
NOT (NOT (x = 1)) into x = 1
NOT (x = 1) into x != 1

And much more. The primary use-cases for this functionality are:

SQL linting, e.g. as part of an ExecuteListener
SQL auto cleanup, including in a ParsingConnection
Dialect migration, when upgrading database versions, or moving between dialects
Patching specific SQL features

For more information about the feature, see here

Note that this feature is also available for free online at https://www.jooq.org/translate

Reactive and kotlin coroutine support

A lot of minor improvements have been implemented. A few more significant ones
include:

R2DBC 0.9.1.RELEASE is now supported
A new reactive transaction API has been added, which offers the same nested
transaction semantics as the existing blocking transaction API, see also:
https://blog.jooq.org/nested-transactions-in-jooq/
jOOQ’s reactive streams bindings via the Publisher SPI are now
bridged automatically to kotlin coroutines in the new
org.jooq:jooq-kotlin-coroutines module using the usual utilites
org.jetbrains.kotlinx:kotlinx-coroutines-core and
org.jetbrains.kotlinx:kotlinx-coroutines-reactor
The org.jooq:jooq-kotlin extensions module now has additional
extension functions for more MULTISET and other nesting related
convenience.
The entire blocking execution API is now annotated with
org.jetbrains.annotations.Blocking to help reactive jOOQ users
avoid accidentally blocking on a query, when using IntelliJ. In addition, we
now annotate experimental and internal API with the ApiStatus
annotation from the same package.