![3.17.0 Launch with Computed Columns, Audit Columns, Sample Matching, Reactive Transactions and Kotlin Coroutine Help – Java, SQL and jOOQ. 3.17.0 Launch with Computed Columns, Audit Columns, Sample Matching, Reactive Transactions and Kotlin Coroutine Help – Java, SQL and jOOQ.](https://techtoguide.com/wp-content/uploads/2023/10/A-simple-example-configuration-to-generate-package-private-jOOQ-code.png)
[ad_1]
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 industrial distributions is
the brand new consumer aspect computed columns characteristic, constructing on high of jOOQ 3.16’s
industrial assist for readonly columns and server aspect computed columns.
Not all RDBMS assist computed columns (e.g. utilizing the usual SQL syntaxGENERATED 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 consumer aspect, by remodeling your SQL queries:
STORED
impactsINSERT
,UPDATE
,DELETE
, andMERGE
VIRTUAL
impactsSELECT
and theRETURNING
clause of DML statements. To make use of those, mix them with the brand new artificial column era characteristic.
Not like 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 conserving computed columns (or the underlying base columns) non-public 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 delicate deletion, further meta information, (bi)temporal versioning, however these columns are among the many hottest approaches, making this industrial solely comfort characteristic very helpful to a whole lot of prospects.
Extra about this characteristic right here
Java 17 baseline for the jOOQ Open Supply Version
Java 17 has been the newest LTS, and it consists of a whole lot of actually cool options, together with:
- sealed varieties (important for sample matching)
- information
- instanceof sample matching
- textual content blocks
- swap expressions
jOOQ 3.16’s experimental new Question Object Mannequin (QOM) API experiments with sealed varieties, which can be adopted extra usually 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 assist within the industrial 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
Supply Version.
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:
CIDR
CITEXT
LTREE
HSTORE
INET
RANGE
(together with all of the specialisations forINT4
,INT8
, and so on.)
As a way to revenue from these information varieties, simply add the org.jooq:jooq-postgres-extensions
module to your code era and runtime dependencies, and the kinds are generated routinely.
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, comparable 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 characteristic 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 traditional Subject<T>
in your SELECT
clause, specifically:
Desk<R>
now extendsSelectField<R>
Situation
now extendsSubject<Boolean>
This implies you’ll be able to write a question like this:
Outcome<Record3<CustomerRecord, AddressRecord, Boolean>> outcome =
ctx.choose(
// Venture a CustomerRecord straight
CUSTOMER,
// Venture an AddressRecord from an implicit JOIN
CUSTOMER.tackle(),
// Venture a boolean expression, as an alternative of wrapping it with area()
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 further compatibility between SQL dialects to industrial prospects, comparable to, for instance:
- Reworking Oracle’s
ROWNUM
into equal window capabilities orLIMIT
clauses. - Turning desk lists together with Oracle’s
(+)
operator into ANSI JOIN syntax.
This launch ships with a brand new industrial 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 embody:
LTRIM(RTRIM(x))
intoTRIM(x)
x != a AND x != b
intox NOT IN (a, b)
x IN (a, b, c) AND x IN (b, c, d)
intox IN (b, c)
NOT (NOT (x = 1))
intox = 1
NOT (x = 1)
intox != 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
Observe that this characteristic can be out there free of charge on-line at https://www.jooq.org/translate
Reactive and kotlin coroutine assist
Lots of minor enhancements have been applied. Just a few extra important ones
embody:
- 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 present blocking transaction API, see additionally:
https://weblog.jooq.org/nested-transactions-in-jooq/ - jOOQ’s reactive streams bindings by way of the
Writer
SPI are actually
bridged routinely to kotlin coroutines within the neworg.jooq:jooq-kotlin-coroutines
module utilizing the standard utilitesorg.jetbrains.kotlinx:kotlinx-coroutines-core
andorg.jetbrains.kotlinx:kotlinx-coroutines-reactor
- The
org.jooq:jooq-kotlin
extensions module now has further
extension capabilities for extraMULTISET
and different nesting associated
comfort. - All the blocking execution API is now annotated with
org.jetbrains.annotations.Blocking
to assist reactive jOOQ customers
keep away from unintentionally blocking on a question, when utilizing IntelliJ. As well as, we
now annotate experimental and inner API with theApiStatus
annotation from the identical bundle.
[ad_2]