jOOQ 3.12 has been released with a new procedural language API, new data types, MemSQL support, formal Java 11+ support, a much better parser, and reactive stream API support

In this release, we’ve focused on a lot of minor infrastructure tasks, greatly

improving the overall quality of jOOQ. We’ve reworked some of our automated

integration tests, which has helped us fix a big number of not yet discovered

issues, including a much better coverage of our 26 supported RDBMS dialects.

We’re excited about these internal changes, as they will help us implement a lot

of features that have been requested by many for a long time, including an

immutable query object model, with all the secondary benefits like caching of

generated SQL, and much more powerful dynamic SQL construction and

transformation in the future.

Major new features include the new procedural language API shipped with our

commercial distributions, new data types including native JSON support, MemSQL

support, formal Java 11+ support, a much better parser, and reactive stream API

support.

Procedural languages

Following up on jOOQ 3.11’s support for anonymous blocks, the jOOQ 3.12

Professional and Enterprise Editions now include support for a variety of

procedural language features, including

Variable declarations

Variable assignments

Loops (WHILE, REPEAT, FOR, LOOP)

If Then Else

Labels

Exit, Continue, Goto

Execute

This feature set is part of our ongoing efforts to continue supporting more

advanced vendor specific functionality, including our planned definition and

translation of stored procedures, triggers, and other, ad-hoc procedural logic

that helps move data processing logic into the database server.

New Databases Supported

The jOOQ Professional Edition now supports the MemSQL dialect. MemSQL is derived

from MySQL, although our integration tests have shown that there are numerous

differences, such that supporting MemSQL formally will add a lot of value to our

customers by providing much increased syntactic correctness.

Reactive streams

The reactive programming model is gaining traction in some environments as new,

useful streaming APIs emerge, such as e.g. Reactor. These APIs have agreed to

work with a common SPI: reactive streams, and since JDK 9 the new

java.util.concurrent.Flow SPI. jOOQ 3.12 now implements these paradigms on an

API level, such that an integration with APIs like Reactor becomes much more

easy. The implementation still binds to JDBC, and is thus blocking. Future

versions of jOOQ will abstract over JDBC to allow for running queries against

ADBA (by Oracle) or R2DBC (by Spring)

New data types

We’ve introduced native support for a few new data types, which are often very

useful in specific situations. These include:

JSON / JSONB: A native string wrapper for textual and binary JSON data. While

users will still want to bind more specific JSON to maps and lists using

custom data type Bindings, in a lot of cases, being able to just serialise and

deserialise JSON content as strings is sufficient. jOOQ now provides out of

the box support for this approach for various SQL dialects.

users will still want to bind more specific JSON to maps and lists using custom data type Bindings, in a lot of cases, being able to just serialise and deserialise JSON content as strings is sufficient. jOOQ now provides out of the box support for this approach for various SQL dialects. INSTANT: RDBMS do not agree on the meaning of the SQL standard TIMESTAMP WITH

TIME ZONE. PostgreSQL, for example, interprets it as a unix timestamp, just

like java.time.Instant. For an optimal PostgreSQL experience, this new INSTANT

type will be much more useful than the standard JDBC java.time.OffsetDateTime

binding.

TIME ZONE. PostgreSQL, for example, interprets it as a unix timestamp, just like java.time.Instant. For an optimal PostgreSQL experience, this new INSTANT type will be much more useful than the standard JDBC java.time.OffsetDateTime binding. ROWID: Most RDBMS have a native ROWID / OID / CTID / physloc identity

value that physically identifies a row on the underlying storage system,

irrespective of any logical primary key. These ROWIDs can be leveraged to run

more performant, vendor specific queries. Supporting this type allows for

easily using this feature in arbitrary queries.

Parser

Our parser is seeing a lot of continued improvements over the releases as we

gather feedback from our users. Our main drivers for feedback are:

The DDLDatabase which allows for generating code from DDL scripts rather than

live JDBC connections to your database

live JDBC connections to your database The https://www.jooq.org/translate website, which translates any kind of SQL

between database dialects.

SQL dialect translation will evolve into an independent product in the future.

DDL parsing is already very powerful, and a lot of customers rely on it for

their production systems.

In the next versions, we will be able to simulate DDL on our own, without H2,

which will open up a variety of possible use cases, including better schema

management.

Specific jOOQ 3.12 parser improvements include:

Being able to access schema meta information (column types, constraints) to

better emulate SQL features / translate SQL syntax between dialects

better emulate SQL features / translate SQL syntax between dialects A parse search path, similar to PostgreSQL’s search_path, or other dialects’

current_schema, allowing support for unqualified object references.

current_schema, allowing support for unqualified object references. The DDL simulation from the DDLDatabase is now moved into the core library,

supporting it also out of the box as a DDL script based meta data source

supporting it also out of the box as a DDL script based meta data source A new special comment syntax that helps ignoring SQL fragments in the jOOQ

parser only, while executing it in your ordinary SQL execution.

parser only, while executing it in your ordinary SQL execution. A new interactive mode in the ParserCLI

Support for nested block comments

And much more

Formal Java 11 Support

While we have been supporting Java 11 for a while through our integration tests,

jOOQ 3.12 now fully supports Java 11 to help improve the experience around the

transitive JAXB dependency, which we now removed entirely out of jOOQ.

The commercial editions ship with a Java 11+ supporting distribution, which

includes more optimal API usage, depending on new Java 9-11 APIs. All editions,

including the jOOQ Open Source Edition, have a Java 8+ distribution that

supports any Java version starting from Java 8.

Commercial Editions

Dual licensing is at the core of our business, helping us to provide continued

value to our customers.

In the past, the main distinction between the different jOOQ editions was the

number of database products each edition supported. In the future, we want to

provide even more value to our customers with commercial subscriptions. This is

why, starting from jOOQ 3.12, we are now offering some new, advanced features

only in our commercial distributions. Such features include:

The procedural language API, which is available with the jOOQ Professional

and Enterprise Editions

and Enterprise Editions While the jOOQ 3.12 Open Source Edition supports Java 8+, the jOOQ 3.12

Professional Edition also ships with a Java 11+ distribution, leveraging some

newer JDK APIs, and the jOOQ 3.12 Enterprise Edition continues supporting

Java 6 and 7.

Professional Edition also ships with a Java 11+ distribution, leveraging some newer JDK APIs, and the jOOQ 3.12 Enterprise Edition continues supporting Java 6 and 7. Since Java 8 still sees very substantial market adoption, compared to Java 11,

we still support Java 8 in the jOOQ 3.12 Open Source Edition.

we still support Java 8 in the jOOQ 3.12 Open Source Edition. Starting from jOOQ 3.12, formal support for older RDBMS dialect versions in

the runtime libraries is reserved to the jOOQ Professional and Enterprise

Editions. The jOOQ Open Source Edition will ship with support for the latest

version of an RDBMS dialect, only. The code generator is not affected by this

change.

By offering more value to our paying customers, we believe that we can continue

our successful business model, which in turn allows us to continue the free

jOOQ Open Source Edition for free. Our strategy is:

To implement new, advanced, commercial only features.

To offer legacy support (legacy Java versions, legacy database versions) to

paying customers only.

paying customers only. To continue supporting a rich set of features to Open Source Edition users.

H2 and SQLite integration

Over the past year, both H2 and SQLite have seen a lot of improvements, which we

have now supported in jOOQ as well. Specifically, H2 is moving at a very fast

pace, and our traditional close cooperation got even better as we’re helping

the H2 team with our insights into the SQL standards, while the H2 team is

helping us with our own implementations.

Other improvements

The complete list of changes can be found on our website:

https://www.jooq.org/notes

A few improvements are worth summarising here explicitly

We’ve added support for a few new SQL predicates, such as the standard

UNIQUE and SIMILAR TO predicates, as well as the synthetic, but very useful

LIKE ANY predicate.

UNIQUE and SIMILAR TO predicates, as well as the synthetic, but very useful LIKE ANY predicate. The JAXB implementation dependency has been removed and replaced by our own

simplified implementation for a better Java 9+ experience.

simplified implementation for a better Java 9+ experience. The historic log4j (1.x) dependency has been removed. We’re now logging only

via the optional slf4j dependency (which supports log4j bridges), or

java.util.logging, if slf4j cannot be found on the classpath.

via the optional slf4j dependency (which supports log4j bridges), or java.util.logging, if slf4j cannot be found on the classpath. The shaded jOOR dependency has been upgraded to 0.9.12.

We’ve greatly improved our @Support annotation usage for better use with

jOOQ-checker.

jOOQ-checker. jOOQ-checker can now run with ErrorProne as well as with the checker framework

as the latter still does not support Java 9+.

as the latter still does not support Java 9+. We’ve added support for a lot of new DDL statements and clauses.

There is now a synthetic PRODUCT() aggregate and window function.

We added support for the very useful window functions GROUPS mode.

Formatting CSV, JSON, XML now supports nested formatting.

UPDATE / DELETE statements now support (and emulate) ORDER BY and LIMIT.

When constructing advanced code generation configuration, users had to resort

to using programmatic configuration. It is now possible to use SQL statements

to dynamically construct regular expression matching tables, columns, etc.

to using programmatic configuration. It is now possible to use SQL statements to dynamically construct regular expression matching tables, columns, etc. Configuration has a new UnwrapperProvider SPI.

MockFileDatabase can now handle regular expressions and update statements.

Settings can cleanly separate the configuration of name case and quotation.

MySQL DDL character sets are now supported, just like collations.

A new Table.where() API simplifies the construction of simple derived tables.

This feature will be very useful in the future, for improved row level

security support.

This feature will be very useful in the future, for improved row level security support. A nice BigQuery and H2 feature is the “* EXCEPT (…)” syntax, which allows

for removing columns from an asterisked expression. We now have

Asterisk.except() and QualifiedAsterisk.except().

for removing columns from an asterisked expression. We now have Asterisk.except() and QualifiedAsterisk.except(). A lot of improvements in date time arithmetic were added, including support

for vendor specific DateParts, like WEEK.

Full release notes here: https://www.jooq.org/notes