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 refined SQL transformation capabilities, together with:

• Shopper aspect computed columns for each learn and write operations
• Audit columns
• Sample matching SQL transformations
• Extra implicit JOIN capabilities

Shopper aspect computed columns

A floor breaking new core characteristic accessible in all business distributions is
the brand new consumer aspect computed columns characteristic, constructing on high of jOOQ 3.16’s
business help for readonly columns and server aspect 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 aspect, 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 era characteristic.

In contrast to their server aspect counterparts, these consumer aspect options can produce arbitrary expressions, together with:

• Implicit joins
• Scalar subqueries
• MULTISET subqueries
• Way more

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

Extra about this characteristic right here

Audit columns

A particular case of STORED consumer aspect 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 mushy deletion, extra meta knowledge, (bi)temporal versioning, however these columns are among the many hottest approaches, making this business solely comfort characteristic very helpful to lots of clients.

Extra about this characteristic right here

Java 17 baseline for the jOOQ Open Supply Version

Java 17 has been the most recent LTS, and it consists of lots of actually cool options, together with:

• sealed sorts (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 sorts, which will likely be adopted extra typically as soon as the QOM API is finalized.

To get broader person 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 knowledge kind help

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

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

To be able to revenue from these knowledge sorts, simply add the org.jooq:jooq-postgres-extensions module to your code era and runtime dependencies, and the categories are generated routinely.

Implicit JOIN enhancements

On this launch, we experimented with just a few 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 fascinating. The prototype, nonetheless, 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 characteristic additionally as an implicit to-many JOIN.

A leftover from the prototype is the truth that now you can extra simply undertaking expressions apart from traditional Area<T> in your SELECT clause, specifically:

• Desk<R> now extends SelectField<R>
• Situation now extends Area<Boolean>

This implies you possibly can write a question like this:

Outcome<Record3<CustomerRecord, AddressRecord, Boolean>> end result =
ctx.choose(

      // Mission a CustomerRecord straight
      CUSTOMER,

      // Mission an AddressRecord from an implicit JOIN
      CUSTOMER.handle(),

      // Mission 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 characteristic set to current jOOQ releases, providing extra compatibility between SQL dialects to business clients, equivalent to, for instance:

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

This launch ships with a brand new business solely characteristic that straight 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 way 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 characteristic, see right here

Notice that this characteristic can also be accessible without cost on-line at https://www.jooq.org/translate

Reactive and kotlin coroutine help

Loads of minor enhancements have been carried out. A couple of extra important ones
embrace:

• R2DBC 0.9.1.RELEASE is now supported
• A brand new reactive transaction API has been added, which gives 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 by way of the Writer SPI at the moment are
  bridged routinely to kotlin coroutines within the new
  org.jooq:jooq-kotlin-coroutines module utilizing the same old utilites
  org.jetbrains.kotlinx:kotlinx-coroutines-core and
  org.jetbrains.kotlinx:kotlinx-coroutines-reactor
• The org.jooq:jooq-kotlin extensions module now has extra
  extension capabilities 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 accident blocking on a question, when utilizing IntelliJ. As well as, we
  now annotate experimental and inner API with the ApiStatus
  annotation from the identical bundle.

Full launch notes right here