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 ENDto
CASE a WHEN b THEN 1 END
CASE WHEN x IS NULL THEN y ELSE x ENDto
CASE WHEN x = y THEN NULL ELSE x ENDto
(SELECT COUNT(*) FROM t) > 0to
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.