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

Loads of further diagnostics have been added, together with the automated detection of sample replacements, serving to you lint your SQL queries no matter whether or not you’re utilizing jOOQ to put in writing your SQL, or in the event you’re utilizing it as a JDBC / R2DBC proxy for an current software.

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

• 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 bettering jOOQ’s help for these options. On this launch, we’ve added help for quite a lot 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 data on new JSON operate help might 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 adjustments anymore, there can nonetheless be supply incompatibilities between minor releases.

For extra particulars concerning 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 package deal sorts. We’ve supported PL/SQL RECORD sorts and PL/SQL TABLE sorts for some time, each of which had restricted ojdbc help up to now. Associative array help can nonetheless be a problem with ojdbc, however with jOOQ and its code generator, most associative arrays might be sure and fetched very simply.

PostgreSQL Multi dimensional array sorts

An typically requested characteristic from our PostgreSQL integration is multi dimensional array help. This model of jOOQ will help these sorts in code era (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 put in writing SQL in kotlin. We’re at all times searching for brand new comfort by way of the jOOQ-kotlin extension module, for instance:

• ResultQuery Collectors
• JSON entry
• Extra nullability help in generated code

For extra particulars, see this part of the guide

R2DBC 1.0 help

This jOOQ model upgrades its R2DBC dependency to 1.0.0.RELEASE.

The complete launch notes might be discovered right here.