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:
RETURNINGclause 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
- 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.
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.
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)
- 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
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:
RANGE(including all the specialisations for
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:
- Transforming Oracle’s
ROWNUMinto equivalent window functions or
- 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:
x != a AND x != binto
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
- SQL auto cleanup, including in a
- Dialect migration, when upgrading database versions, or moving between dialects
- Patching specific SQL features
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
- 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:
- jOOQ’s reactive streams bindings via the
PublisherSPI are now
bridged automatically to kotlin coroutines in the new
org.jooq:jooq-kotlin-coroutinesmodule using the usual utilites
org.jooq:jooq-kotlinextensions module now has additional
extension functions for more
MULTISETand other nesting related
- The entire blocking execution API is now annotated with
org.jetbrains.annotations.Blockingto help reactive jOOQ users
avoid accidentally blocking on a query, when using IntelliJ. In addition, we
now annotate experimental and internal API with the
annotation from the same package.