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

Posted on by lukaseder

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.

Full release notes here

Published by lukaseder

I made jOOQ

Leave a Reply