3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0 – Java, SQL and jOOQ.

DiagnosticsListener improvements

A lot of additional diagnostics have been added, including the automated detection of pattern replacements, helping you lint your SQL queries irrespective of whether you’re using jOOQ to write your SQL, or if you’re using it as a JDBC / R2DBC proxy for an existing application.

A lot of these diagnostics are available as ordinary pattern transformations, which we’ve started adding in jOOQ 3.17. Some new patterns include:

• 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 much more

See these sections for more details:

More SQL/JSON support

SQL/JSON is one of the most promising recent additions to the SQL language, and we’re always keen on improving jOOQ’s support for these features. In this release, we’ve added support for a variety of useful, vendor specific SQL/JSON extensions, including:

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

More information on new JSON function support can be found here

More QOM implementation

The Query Object Model (QOM) API, which was introduced in jOOQ 3.16, has been enhanced with more statement, function, expression support, allowing for more complete SQL transformation and traversal. This is specifically interesting for pattern replacements, diagnostics, and custom SQL transformations.

The QOM API is still in an experimental state. While we don’t expect any fundamental changes anymore, there can still be source incompatibilities between minor releases.

For more details about the model API, click here

Oracle associative array support

When using stored procedures in Oracle, users are likely going to make heavy use of Oracle PL/SQL package types. We’ve supported PL/SQL RECORD types and PL/SQL TABLE types for a while, both of which had limited ojdbc support in the past. Associative array support can still be a challenge with ojdbc, but with jOOQ and its code generator, most associative arrays can be bound and fetched very easily.

PostgreSQL Multi dimensional array types

An often requested feature from our PostgreSQL integration is multi dimensional array support. This version of jOOQ will support those types in code generation (where possible) and at runtime via multi dimensional Java arrays.

Kotlin specific improvements

jOOQ is also the best way to write SQL in kotlin. We’re always looking out for new convenience via the jOOQ-kotlin extension module, for example:

• ResultQuery Collectors
• JSON access
• More nullability support in generated code

For more details, see this section of the manual

R2DBC 1.0 support

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

The full release notes can be found here.