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

DiagnosticsListener enhancements

Lots of extra 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 jot down your SQL, or if you happen to’re utilizing it as a JDBC / R2DBC proxy for an present utility.

Lots of these diagnostics can be found as unusual 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 rather more

See these sections for extra particulars:

Extra SQL/JSON assist

SQL/JSON is likely one of the most promising current additions to the SQL language, and we’re all the time eager on enhancing jOOQ’s assist for these options. On this launch, we’ve added assist 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 data on new JSON perform assist 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, perform, expression assist, permitting for extra full SQL transformation and traversal. That is particularly attention-grabbing for sample replacements, diagnostics, and customized SQL transformations.

The QOM API remains to be in an experimental state. Whereas we don’t count on any elementary 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 assist

When utilizing saved procedures in Oracle, customers are possible going to make heavy use of Oracle PL/SQL package deal varieties. We’ve supported PL/SQL RECORD varieties and PL/SQL TABLE varieties for some time, each of which had restricted ojdbc assist up to now. Associative array assist can nonetheless be a problem with ojdbc, however with jOOQ and its code generator, most associative arrays will be certain and fetched very simply.

PostgreSQL Multi dimensional array varieties

An usually requested function from our PostgreSQL integration is multi dimensional array assist. This model of jOOQ will assist these varieties in code era (the place potential) and at runtime through multi dimensional Java arrays.

Kotlin particular enhancements

jOOQ can be one of the simplest ways to jot down SQL in kotlin. We’re all the time searching for brand spanking new comfort through the jOOQ-kotlin extension module, for instance:

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

For extra particulars, see this part of the handbook

R2DBC 1.0 assist

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

The total launch notes will be discovered right here.