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. - 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. - 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 - 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 - A parse search path, similar to PostgreSQL’s search_path, or other dialects’
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 - A new special comment syntax that helps ignoring SQL fragments in the jOOQ
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 - 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. - 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. - 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. - 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. - The JAXB implementation dependency has been removed and replaced by our own
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. - 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 can now run with ErrorProne as well as with the checker framework
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. - 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. - 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(). - 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