Version 3.12.0 - August 29, 2019
In this release, we've focused on a lot of minor infrastructure tasks,
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
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
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
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,
support, formal Java 11+ support, a much better parser, and reactive stream
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
- Exit, Continue, Goto
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
that helps move data processing logic into the database server.
New Databases Supported
The jOOQ Professional Edition now supports the MemSQL dialect. MemSQL is
from MySQL, although our integration tests have shown that there are
differences, such that supporting MemSQL formally will add a lot of value
customers by providing much increased syntactic correctness.
The reactive programming model is gaining traction in some environments as
useful streaming APIs emerge, such as e.g. Reactor. These APIs have agreed
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
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
ADBA (by Oracle) or R2DBC (by Spring)
New data types
We've introduced native support for a few new data types, which are often
useful in specific situations. These include:
- JSON / JSONB: A native string wrapper for textual and binary JSON data.
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
deserialise JSON content as strings is sufficient. jOOQ now provides out
the box support for this approach for various SQL dialects.
- INSTANT: RDBMS do not agree on the meaning of the SQL standard TIMESTAMP
TIME ZONE. PostgreSQL, for example, interprets it as a unix timestamp,
like java.time.Instant. For an optimal PostgreSQL experience, this new
type will be much more useful than the standard JDBC
- 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
more performant, vendor specific queries. Supporting this type allows for
easily using this feature in arbitrary queries.
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
live JDBC connections to your database
- The https://www.jooq.org/translate website, which translates any kind of
between database dialects.
SQL dialect translation will evolve into an independent product in the
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
which will open up a variety of possible use cases, including better schema
Specific jOOQ 3.12 parser improvements include:
- Being able to access schema meta information (column types, constraints)
better emulate SQL features / translate SQL syntax between dialects
- A parse search path, similar to PostgreSQL's search_path, or other
current_schema, allowing support for unqualified object references.
- The DDL simulation from the DDLDatabase is now moved into the core
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
jOOQ 3.12 now fully supports Java 11 to help improve the experience around
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
including the jOOQ Open Source Edition, have a Java 8+ distribution that
supports any Java version starting from Java 8.
Dual licensing is at the core of our business, helping us to provide
value to our customers.
In the past, the main distinction between the different jOOQ editions was
number of database products each edition supported. In the future, we want
provide even more value to our customers with commercial subscriptions.
why, starting from jOOQ 3.12, we are now offering some new, advanced
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
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
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
the runtime libraries is reserved to the jOOQ Professional and Enterprise
Editions. The jOOQ Open Source Edition will ship with support for the
version of an RDBMS dialect, only. The code generator is not affected by
By offering more value to our paying customers, we believe that we can
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)
paying customers only.
- To continue supporting a rich set of features to Open Source Edition
H2 and SQLite integration
Over the past year, both H2 and SQLite have seen a lot of improvements,
have now supported in jOOQ as well. Specifically, H2 is moving at a very
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.
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
LIKE ANY predicate.
- The JAXB implementation dependency has been removed and replaced by our
simplified implementation for a better Java 9+ experience.
- The historic log4j (1.x) dependency has been removed. We're now logging
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 can now run with ErrorProne as well as with the checker
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
to using programmatic configuration. It is now possible to use SQL
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
- MySQL DDL character sets are now supported, just like collations.
- A new Table.where() API simplifies the construction of simple derived
This feature will be very useful in the future, for improved row level
- A nice BigQuery and H2 feature is the "* EXCEPT (...)" syntax, which
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
for vendor specific DateParts, like WEEK.
Features and Improvements
#714 - Add support for non-standard ORDER BY .. LIMIT clauses in UPDATE,
#1699 - Add support for the SQL standard UNIQUE predicate
#1725 - Add support for SQL standard SIMILAR TO predicate
#2026 - Add a new @Internal annotation to annotate API parts that are not
supposed to be used
#2059 - Add support for the MemSQL database for the jOOQ Professional
#2132 - Add support for non-standard date-time fields in the EXTRACT()
#2208 - Implement a MockFileDatabase
#2233 - Add Result<?> DSLContext.fetchFromXML() to allow for loading
results that were exported using Result.formatXML()
#2731 - Add some sections to the manual helping users migrate from other
#3606 - Emulate INSERT/UPDATE/DELETE (SELECT ...) by WITH v AS (SELECT ...)
INSERT/UPDATE/DELETE v on SQL Server
#3607 - Allow for emitting common table expression declarations to
#4371 - Let ResultQuery<R> extend Publisher<R>
#4473 - Add ResultQuery<R1>.coerce(Field<?>), coerce(Table<R>), that
coerce a new Record type on a ResultQuery
#4498 - Emulate INSERT .. RETURNING via SQL Server OUTPUT clause
#5110 - Add support for Derby's MERGE statement
#5576 - Add support for SQLite 3.15's row value expression support
#5601 - IN list padding setting should also apply to row IN predicates
#5640 - MockFileDatabase should support all jOOQ import formats, including
#5700 - Add support for Oracle's STATS_MODE() aggregate function
#5781 - Add support for ALTER TABLE .. RENAME COLUMN in MySQL 8
#5895 - Add support for TIME[STAMP] WITH TIME ZONE in HSQLDB
#5899 - jOOQ-checker should provide both checker-framework and ErrorProne
#5909 - Deprecate RenderNameStyle.QUOTED and replace feature by
#5939 - Add PRODUCT() aggregate and window function
#5966 - Add support for NATURAL FULL [ OUTER ] JOIN
#5969 - Add <includeInvisibleColumns/> to code generator configuration to
allow for hiding Oracle's and H2's INVISIBLE columns
#6234 - Add newline configuration to code generator
#6260 - Support loading multiple files in XMLDatabase
#6450 - Javadoc should reference JDK 11 Javadoc
#6475 - Add procedural language abstraction API
#6486 - Add a JDK 11 build
#6548 - Support multiple external configuration files through
#6583 - Work around MySQL's self-reference-in-DML-subquery restriction
#6709 - Support custom Hibernate properties in JPADatabase
#6778 - Support parsing PostgreSQL's dollar quoted string constants
#6944 - Add a way to specify what object type a <forcedType/> should match
#6971 - Allow Maven plugin to succeed in absence of valid DB connection if
schema is up to date
#7005 - Add support for MariaDB INTERSECT and EXCEPT
#7048 - Document the FILTER clause
#7143 - Support merging external <configurationFile/> into Maven
#7153 - Allow for configuring a locale to be used for upper/lower casing
inside of jOOQ
#7163 - Add Settings.parseWithMetaLookups
#7216 - Add RecordN<T1, ..., TN> fetchSingle(Field<T1>, ..., Field<TN>)
#7235 - DDLDatabase fails with ParserException on JSON field
#7268 - Add support for MySQL's UNIQUE [ index_name ] syntax
#7297 - Support deriving a WindowDefinition from another WindowDefinition
#7305 - Add support for DECLARE statements inside of statement blocks
#7306 - Add support for the SET command to assign values to variables
#7331 - Add reference to <forcedType/> configuration in DataType,
#7336 - Add new flags to Settings to govern parser behaviour
#7337 - Add Settings.parseDialect
#7361 - Improve formatting of nested joins
#7397 - Add a section to the manual about SELECT *
#7426 - Add support for Teradata QUALIFY
#7445 - Support parsing PostgreSQL-specific interval literals
#7470 - Add indexOf() methods to all TableLike types
#7485 - Document jOOQ's auto-conversion utility
#7490 - Upgrade jOOQ-meta-extensions dependency to Hibernate 5.4
#7545 - ForcedType configurations should have include / exclude expressions
#7570 - Add support for CREATE TYPE .. AS ENUM
#7587 - Use --release flag in JDK 9+ based builds
#7591 - Add new ResourceManagingScope which allows for Binding
implementations to register resources which are freed after execution
#7595 - Add support for Java 11
#7598 - Add search box to Javadocs
#7599 - Print row count in LoggerListener for ResultQuery
#7602 - Upgrade jOOR dependency's Java 9 functionality
#7609 - Add Setting to turn off ORDER BY clause in emulated OFFSET .. FETCH
#7612 - Add configuration to DDLDatabase to indicate whether unquoted names
are reported in lower case, upper case or as-is
#7619 - Don't ship old XSDs in binaries
#7628 - Add support for PostgreSQL 11
#7633 - ALTER TABLE .. ADD FOREIGN KEY .. REFERENCES references unqualified
#7646 - Support for window functions GROUPS mode
#7647 - Support window function EXCLUDE clause
#7678 - Mention more ways to inline parameters in manual
#7705 - Add support for PostgreSQL FOR NO KEY UPDATE and FOR KEY SHARE
#7712 - Add support for Catalogs in JDBCDatabase
#7719 - Add code generation flag
Regarding the commercial editions:
- The Java 11+ supporting distribution, "which includes more optimal API
usage, depending on new Java 9-11 APIs" - can you be a bit more specific?
Which newer JDK APIs are leveraged? Examples?
- Can you give examples for the procedural language API? How does that work?
Further (not related to the commercial editions):
- Native JSON/JSONB support: Can you show an example?
- You write "...immutable query object model, with all the secondary
benefits like caching of generated SQL..." Is this "immutable query object
model" in internal change? Is there a (new) user facing API? And: Will
generated SQL be cached internally now somehow (or was that the case
Thank you very much for this great piece of software!
We've just released jOOQ 3.12. With it there is experimental support for
running our DDL simulation in the core library though
DSLContext.meta(Source...). You can provide a set of DDL scripts, and we'll
run them against H2 (translated to H2 dialect), to produce the resulting
meta model, which you can use in your tooling, or in the parser.
Any early feedback would be greatly appreciated!
On Monday, July 29, 2019 at 12:51:53 PM UTC+2, marekgregor wrote:
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].