Site icon Java, SQL and jOOQ.

3.17.0 Release with Computed Columns, Audit Columns, Pattern Matching, Reactive Transactions and Kotlin Coroutine Support

lukaseder

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

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:

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

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:

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:

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:

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:

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:

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:

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:

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

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:

Full release notes here

Exit mobile version