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 out there in all business distributions is
the brand new shopper aspect computed columns characteristic, constructing on prime of jOOQ 3.16’s
business assist for readonly columns and server aspect computed columns.
Not all RDBMS assist computed columns (e.g. utilizing the usual SQL syntax
GENERATED ALWAYS AS), and in the event that they do, they won’t assist them in each
STORED (computed on write) and
VIRTUAL (computed on learn) variants. jOOQ can now emulate each options on the shopper aspect, by reworking your SQL queries:
RETURNINGclause of DML statements. To make use of those, mix them with the brand new artificial column technology characteristic.
Not like their server aspect counterparts, these shopper aspect options can produce arbitrary expressions, together with:
- Implicit joins
- Scalar subqueries
- Rather 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 enables for preserving computed columns (or the underlying base columns) non-public and thus invisible to consumer code.
A particular case of
STORED shopper aspect computed columns are audit columns, whose most elementary implementation comes within the type of:
Different approaches to auditing exist, together with delicate deletion, extra meta information, (bi)temporal versioning, however these columns are among the many hottest approaches, making this business solely comfort characteristic very helpful to quite a lot of prospects.
Java 17 baseline for the jOOQ Open Supply Version
Java 17 has been the newest LTS, and it contains quite a lot of actually cool options, together with:
- sealed varieties (important for sample matching)
- instanceof sample matching
- textual content blocks
- change expressions
jOOQ 3.16’s experimental new Question Object Mannequin (QOM) API experiments with sealed varieties, which shall be adopted extra typically 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 assist 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 assist within the jOOQ Open Supply
Version and Java 6 assist within the jOOQ Enterprise Version
- jOOQ 3.15 and three.16: The final releases with Java 11 assist within the jOOQ Open
PostgreSQL information kind assist
The jooq-postgres-extensions module, which contained assist for the
HSTORE kind, now has much more assist for PostgreSQL particular information varieties, together with array kinds of every of:
RANGE(together with all of the specialisations for
INT8, and many others.)
In an effort to revenue from these information varieties, simply add the
org.jooq:jooq-postgres-extensions module to your code technology and runtime dependencies, and the kinds are generated mechanically.
Implicit JOIN enhancements
On this launch, we experimented with a number of new implicit JOIN options, together with assist 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, resembling
EXISTS(...) subqueries or
MULTISET(...) subqueries. The experiment has been very fascinating. 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 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 basic
Subject<T> in your
SELECT clause, specifically:
This implies you possibly can write a question like this:
End result<Record3<CustomerRecord, AddressRecord, Boolean>> outcome = ctx.choose( // Mission a CustomerRecord instantly 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 prospects, resembling, for instance:
- Reworking Oracle’s
ROWNUMinto equal window features or
- Turning desk lists together with Oracle’s
(+)operator into ANSI JOIN syntax.
This launch ships with a brand new business solely characteristic that instantly 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 embody:
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 way more. The first use-cases for this performance are:
- SQL linting, e.g. as a part of an
- SQL auto cleanup, together with in a
- Dialect migration, when upgrading database variations, or shifting between dialects
- Patching particular SQL options
Notice that this characteristic can also be out there without cost on-line at https://www.jooq.org/translate
Reactive and kotlin coroutine assist
A number of minor enhancements have been carried out. A number of extra vital ones
- R2DBC 0.9.1.RELEASE is now supported
- A brand new reactive transaction API has been added, which presents the identical nested
transaction semantics as the prevailing blocking transaction API, see additionally:
- jOOQ’s reactive streams bindings through the
WriterSPI at the moment are
bridged mechanically to kotlin coroutines within the new
org.jooq:jooq-kotlin-coroutinesmodule utilizing the same old utilites
org.jooq:jooq-kotlinextensions module now has extra
extension features for extra
MULTISETand different nesting associated
- The whole blocking execution API is now annotated with
org.jetbrains.annotations.Blockingto assist reactive jOOQ customers
keep away from by accident blocking on a question, when utilizing IntelliJ. As well as, we
now annotate experimental and inside API with the
annotation from the identical package deal.