3.16.0 Release with a new Public Query Object Model API, Spatial Support, YugabyteDB Support and Much More

This release tackles two long standing and complex feature requests that users
have asked us to offer for a long time: a public API for manipulating jOOQ’s
query object model (QOM), and spatial support.

New Query Object Model (QOM)

Every jOOQ query is modeled as an expression tree constructed via our intuitive
DSL. For some use-cases there exist historic model API versions (e.g.
SelectQuery), but those models aren’t readable or transformable. We’re now
starting to offer most of our expression tree model as a public API for users to
consume and manipulate. Every expression tree element has a corresponding type
in org.jooq.impl.QOM. Every type provides access to its components using "$"
prefixed method names, e.g.:

// Create an expression using the DSL API:
Field field = substring(BOOK.TITLE, 2, 4);

// Access the expression's internals using the model API
if (field instanceof QOM.Substring substring) {
    Field string = substring.$string();
    Field startingPosition = substring.$startingPosition();
    Field length = substring.$length();
}

The new API is experimental and might change in the next minor release.

Licensed power users will get auxiliary APIs to traverse and transform the
expression tree, e.g. traversal:

// Contains 7 query parts 
long count2 = BOOK.ID.eq(1).or(BOOK.ID.eq(2))
    .$traverse(Traversers.collecting(Collectors.counting());

Or replacement:

// Contains redundant operators
Condition c = not(not(BOOK.ID.eq(1)));
System.out.println(c.$replace(q ->
    q instanceof QOM.Not n1 && n1.$arg1() instanceof QOM.Not n2
        ? n2.$arg1()
        : q
));

The above prints the following, having removed the redundant NOT operators:

"BOOK"."ID" = 1

This new API is very powerful for even more sophisticated dynamic SQL use-cases,
such as:

• Optimising SQL expressions as the above NOT NOT example
• Row level security
• Soft deletion
• Shared schema multi tenancy
• Audit column support
• And much more (stay tuned for future blogs and out-of-the-box transformations)

For more info, see:

• https://www.jooq.org/doc/dev/manual/sql-building/model-api/model-api-design/
• https://www.jooq.org/doc/dev/manual/sql-building/model-api/model-api-traversal/
• https://www.jooq.org/doc/dev/manual/sql-building/model-api/model-api-replacement/

Spatial support

A long awaited feature that starts shipping to our commercially licensed
customers is spatial support. Many dialects support the ISO/IEC 13249-3:2016 SQL
standard extension, and finally, so do we.

jOOQ introduces new auxiliary data types for GEOMETRY and GEOGRAPHY data to use
as standardised bind variables, containing WKB or WKT data, as well as a
variety of out of the box functions and predicates.

Future versions will add more support for other functions and dialects.

New dialect and versions

Another new officially supported SQL dialect has been added to all jOOQ editions
including the jOOQ Open Source Edition: YugabyteDB. This was a sponsored
integration, thank you very much Yugabyte!

Other dialects have upgraded versions, meaning:

• We support this version in all editions now
• The jOOQ Open Source Edition now requires this version if applicable

The versions are:

• Firebird 4.0
• H2 2.0.202
• MariaDB 10.6
• PostgreSQL 14
• Oracle 21c

Computed and readonly columns including ROWID

A lot of dialects support computed columns (“generated” columns), and we now
support those as well in jOOQ. For most use-cases, this does not affect jOOQ
usage, but especially when writing CRUD code, the new readonly column feature
can be very helpful to avoid having to exclude computed columns manually from
the CRUD operation.

This also includes a new, improved ROWID support, where users can choose to work
with a synthetic ROWID column instead of the primary key, when performing CRUD
operations.

All of these features are available in the commercial jOOQ editions.

Jakarta EE

We’ve moved on from Java EE to Jakarta EE dependencies. This change is
currently backwards incompatible because:

• It greatly facilitates maintaining the related code
• It prevents tons of user problems resulting from having both dependencies
• We’re not actually integrating tightly with Java EE / Jakarta EE

The following Jakarta EE modules are affected:

• JAXB, which we’re using to load our configuration objects.
• Validation, whose annotations can be generated by the code generator.
• JPA, which is used by the DefaultRecordMapper and the JPADatabase

If this breaking change causes issues, please get in touch at
https://github.com/jOOQ/jOOQ/issues/9641

Various improvements

As with every minor release, a lot of smaller improvements have been
implemented. Some highlights include:

• PostgreSQL procedures are now supported in code generation and at runtime.
• SQLite JSON support has been added, which includes the MULTISET emulation!
• A lot of MULTISET / ROW improvements have been implemented
• R2DBC 0.9 has been released, and we’ve upgraded our dependency
• The Java 17 distribution now requires Java 17 instead of Java 16
• Pre jOOQ 3.6 deprecations have been removed

Full release notes here

from Java, SQL and jOOQ. https://ift.tt/3JLyMYS
via IFTTT