3.18.0 Launch with Assist for extra Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0 – Java, SQL and jOOQ.

DiagnosticsListener enhancements

Lots of further diagnostics have been added, together with the automated detection of sample replacements, serving to you lint your SQL queries regardless of whether or not you’re utilizing jOOQ to write down your SQL, or in case you’re utilizing it as a JDBC / R2DBC proxy for an present software.

Lots of these diagnostics can be found as strange sample transformations, which we’ve began including in jOOQ 3.17. Some new patterns embody:

CASE WHEN a = b THEN 1 END to CASE a WHEN b THEN 1 END
CASE WHEN x IS NULL THEN y ELSE x END to NVL(x, y)
CASE WHEN x = y THEN NULL ELSE x END to NULLIF(x, y)
(SELECT COUNT(*) FROM t) > 0 to EXISTS(SELECT 1 FROM t)
And far more

See these sections for extra particulars:

Extra SQL/JSON help

SQL/JSON is among the most promising current additions to the SQL language, and we’re at all times eager on enhancing jOOQ’s help for these options. On this launch, we’ve added help for a wide range of helpful, vendor particular SQL/JSON extensions, together with:

JSON_KEYS (from MySQL)
JSON_SET (from MySQL)
JSON_INSERT (from MySQL)
JSON_REPLACE (from MySQL)
JSON_REMOVE (from MySQL)
Accessors -> and ->> (from PostgreSQL)

Extra info on new JSON operate help will be discovered right here

Extra QOM implementation

The Question Object Mannequin (QOM) API, which was launched in jOOQ 3.16, has been enhanced with extra assertion, operate, expression help, permitting for extra full SQL transformation and traversal. That is particularly fascinating for sample replacements, diagnostics, and customized SQL transformations.

The QOM API continues to be in an experimental state. Whereas we don’t count on any basic modifications anymore, there can nonetheless be supply incompatibilities between minor releases.

For extra particulars in regards to the mannequin API, click on right here

Oracle associative array help

When utilizing saved procedures in Oracle, customers are probably going to make heavy use of Oracle PL/SQL bundle sorts. We’ve supported PL/SQL RECORD sorts and PL/SQL TABLE sorts for some time, each of which had restricted ojdbc help previously. Associative array help can nonetheless be a problem with ojdbc, however with jOOQ and its code generator, most associative arrays will be sure and fetched very simply.

PostgreSQL Multi dimensional array sorts

An usually requested function from our PostgreSQL integration is multi dimensional array help. This model of jOOQ will help these sorts in code technology (the place potential) and at runtime by way of multi dimensional Java arrays.

Kotlin particular enhancements

jOOQ can be one of the best ways to write down SQL in kotlin. We’re at all times looking for brand spanking new comfort by way of the jOOQ-kotlin extension module, for instance: