3.17.0 Launch with Computed Columns, Audit Columns, Sample Matching, Reactive Transactions and Kotlin Coroutine Help – Java, SQL and jOOQ.

This launch contiues the work from earlier releases round extra subtle SQL transformation capabilities, together with:

Consumer facet computed columns for each learn and write operations
Audit columns
Sample matching SQL transformations
Extra implicit JOIN capabilities

Consumer facet computed columns

A floor breaking new core function out there in all business distributions is
the brand new consumer facet computed columns function, constructing on high of jOOQ 3.16’s
business help for readonly columns and server facet computed columns.

Not all RDBMS help computed columns (e.g. utilizing the usual SQL syntax
GENERATED ALWAYS AS), and in the event that they do, they won’t help them in each STORED (computed on write) and VIRTUAL (computed on learn) variants. jOOQ can now emulate each options on the consumer facet, by reworking your SQL queries:

STORED impacts INSERT, UPDATE, DELETE, and MERGE
VIRTUAL impacts SELECT and the RETURNING clause of DML statements. To make use of those, mix them with the brand new artificial column technology function.

Not like their server facet counterparts, these consumer facet options can produce arbitrary expressions, together with:

Implicit joins
Scalar subqueries
MULTISET subqueries
Far more

Consider this as “views” written in jOOQ, on a per-column foundation. An expecially helpful function mixture is to mix these computed columns with the brand new visibility modifier that enables for retaining computed columns (or the underlying base columns) personal and thus invisible to consumer code.

Extra about this function right here

Audit columns

A particular case of STORED consumer facet computed columns are audit columns, whose most elementary implementation comes within the type of:

CREATED_AT
CREATED_BY
MODIFIED_AT
MODIFIED_BY

Different approaches to auditing exist, together with comfortable deletion, further meta information, (bi)temporal versioning, however these columns are among the many hottest approaches, making this business solely comfort function very helpful to plenty of prospects.

Extra about this function right here

Java 17 baseline for the jOOQ Open Supply Version

Java 17 has been the newest LTS, and it contains plenty of actually cool options, together with:

sealed varieties (important for sample matching)
data
instanceof sample matching
textual content blocks
swap expressions

jOOQ 3.16’s experimental new Question Object Mannequin (QOM) API experiments with sealed varieties, which might be adopted extra usually as soon as the QOM API is finalized.

To get broader consumer suggestions on these enhancements, in addition to to embrace Java’s new LTS replace cadence, we’ve determined to make Java 17 the baseline for the jOOQ 3.17 Open Supply Version, persevering with our Java 8 and 11 help within the business jOOQ distributions.

The next older jOOQ releases will proceed to obtain upgrades for some time:

jOOQ 3.14: The final launch with Java 8 help within the jOOQ Open Supply
Version and Java 6 help within the jOOQ Enterprise Version
jOOQ 3.15 and three.16: The final releases with Java 11 help within the jOOQ Open
Supply Version.

PostgreSQL information sort help

The jooq-postgres-extensions module, which contained help for the HSTORE sort, now has much more help for PostgreSQL particular information varieties, together with array sorts of every of:

CIDR
CITEXT
LTREE
HSTORE
INET
RANGE (together with all of the specialisations for INT4, INT8, and so forth.)

With the intention to revenue from these information varieties, simply add the org.jooq:jooq-postgres-extensions module to your code technology and runtime dependencies, and the categories are generated routinely.

Implicit JOIN enhancements

On this launch, we experimented with a number of new implicit JOIN options, together with help for implicit JOIN in DML statements. The present implementation produces correlated subqueries the place JOIN isn’t supported in DML statements.

We’ve additionally experimented with making a “comfort syntax” for different generally used correlated subqueries, equivalent to EXISTS(...) subqueries or MULTISET(...) subqueries. The experiment has been very attention-grabbing. The prototype, nevertheless, was rejected. See the discussions right here:

Future jOOQ variations will implement the specified comfort within the type of extra implicit JOIN performance, providing the function additionally as an implicit to-many JOIN.

A leftover from the prototype is the truth that now you can extra simply venture expressions aside from basic Subject in your SELECT clause, particularly:

Desk now extends SelectField
Situation now extends Subject

This implies you’ll be able to write a question like this:

Consequence> consequence =
ctx.choose(

      // Challenge a CustomerRecord immediately
      CUSTOMER,

      // Challenge an AddressRecord from an implicit JOIN
      CUSTOMER.tackle(),

      // Challenge a boolean expression, as an alternative of wrapping it with subject()
      exists(
        selectOne()
        .from(PAYMENT)
        .the place(PAYMENT.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
      )
   .from(CUSTOMER)
   .fetch();

Sample matching SQL Transformations

SQL transformations have been a strategic function set to current jOOQ releases, providing further compatibility between SQL dialects to business prospects, equivalent to, for instance:

Reworking Oracle’s ROWNUM into equal window features or LIMIT clauses.
Turning desk lists together with Oracle’s (+) operator into ANSI JOIN syntax.

This launch ships with a brand new business solely function that immediately transforms the brand new Question Object Mannequin (QOM)’s expression tree previous to rendering. It does so by making use of sample matching to the expression tree. Some assorted examples embrace:

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 far more. The first use-cases for this performance are:

SQL linting, e.g. as a part of an ExecuteListener
SQL auto cleanup, together with in a ParsingConnection
Dialect migration, when upgrading database variations, or transferring between dialects
Patching particular SQL options

For extra details about the function, see right here

Observe that this function can be out there free of charge on-line at https://www.jooq.org/translate

Reactive and kotlin coroutine help

A variety of minor enhancements have been applied. A number of extra important ones
embrace:

R2DBC 0.9.1.RELEASE is now supported
A brand new reactive transaction API has been added, which provides the identical nested
transaction semantics as the prevailing blocking transaction API, see additionally:
https://weblog.jooq.org/nested-transactions-in-jooq/
jOOQ’s reactive streams bindings through the Writer SPI are actually
bridged routinely to kotlin coroutines within the new
org.jooq:jooq-kotlin-coroutines module utilizing the standard utilites
org.jetbrains.kotlinx:kotlinx-coroutines-core and
org.jetbrains.kotlinx:kotlinx-coroutines-reactor
The org.jooq:jooq-kotlin extensions module now has further
extension features for extra MULTISET and different nesting associated
comfort.
The complete blocking execution API is now annotated with
org.jetbrains.annotations.Blocking to assist reactive jOOQ customers
keep away from by chance blocking on a question, when utilizing IntelliJ. As well as, we
now annotate experimental and inner API with the ApiStatus
annotation from the identical package deal.