Today, jOOQ 3.11 has been released with support for 4 new databases, implicit joins, diagnostics, and much more

New Databases Supported

At last, 4 new SQL dialects have been added to jOOQ! These are:

jOOQ Professional Edition

Aurora MySQL Edition

Aurora PostgreSQL Edition

Azure SQL Data Warehouse

jOOQ Enterprise Edition

Teradata

Implicit Joins

One of the really cool features in ORMs like Hibernate, Doctrine, and others, is

the capability of using a relationship graph notation to access another entity’s

columns through what is often called “implicit joins”.

Instead of explicitly joining a to-one relationship to access its columns:

SELECT author.first_name, author.last_name, book.title FROM book JOIN author ON book.author_id = author.id

We would like to be able to access those columns directly, using this notation:

SELECT book.author.first_name, book.author.last_name, book.title FROM book

The join is implied and should be added implicitly. jOOQ now allows for this to

happen when you use the code generator:

ctx.select(BOOK.author().FIRST_NAME, BOOK.author().LAST_NAME, BOOK.TITLE) .from(BOOK) .fetch();

When rendering this query, the implicit join graph will be calculated on the fly

and added behind the scenes to the BOOK table. This works for queries of

arbitrary complexity and on any level of nested SELECT.

More details in this blog post:

https://blog.jooq.org/2018/02/20/type-safe-implicit-join-through-path-navigation-in-jooq-3-11/

DiagnosticsListener SPI

A new DiagnosticsListener SPI has been added to jOOQ:

https://github.com/jOOQ/jOOQ/issues/5960

The purpose of this SPI is to sanitise your SQL language, JDBC and jOOQ API

usage. Listeners can listen to events such as:

duplicateStatements (similar SQL is executed, bind variables should be used)

repeatedStatements (identical SQL is executed, should be batched or rewritten)

tooManyColumnsFetched (not all projected columns were needed)

tooManyRowsFetched (not all fetched rows were needed)

The great thing about this SPI is that it can be exposed to clients through the

JDBC API, in case of which the diagnostics feature can reverse engineer your

JDBC or even JPA generated SQL. Ever wanted to detect N+1 queries from

Hibernate? Pass those Hibernate-generated queries through this SPI.

Want to find missing bind variables leading to cursor cache contention or SQLi?

Let jOOQ find similar SQL statements and report them. E.g.

SELECT name FROM person WHERE id = 1

SELECT name FROM person WHERE id = 2

Or also:

SELECT name FROM person WHERE id IN (?, ?)

SELECT name FROM person WHERE id IN (?, ?, ?)

Anonymous blocks

Many databases support anonymous blocks to run several statements in a single

block scope. For example, Oracle:

DECLARE l_var NUMBER(10); BEGIN l_var := 10; dbms_output.put_line(l_var); END;

jOOQ now supports the new org.jooq.Block API to allow for wrapping DDL and DML

statements in such a block. This is a first step towards a future jOOQ providing

support for:

Abstractions over procedural languages

CREATE PROCEDURE and CREATE FUNCTION statements

Trigger support

And much more

Parser

jOOQ’s parser support is an ongoing effort. This release has added support for

a lot of new SQL clauses and functions from various vendors and in various DDL

and DML statements.

The parser is now also exposed through a public website and API, where SQL can

be translated from one dialect to another:

https://www.jooq.org/translate

This website will help further drive jOOQ API development by helping to find

missing functionality that is used in real-world SQL.

Another way to access this API is through the new org.jooq.ParserCLI command

line tool. For example, run:

$ java -cp jooq-3.11.0.jar org.jooq.ParserCLI -f -t ORACLE -s "SELECT * FROM (VALUES(1),(2)) AS t(a)"

To get:

select * from ( ( select null a from dual where 1 = 0 ) union all ( select * from ( ( select 1 from dual ) union all ( select 2 from dual ) ) t ) ) t;

Formal Java 10 Support

jOOQ 3.11 is the first release that is formally integration tested with Java 10.

To use jOOQ with Java 10, use the Java 8 distribution which has not yet been

modularised, but contains Automatic-Module-Name specification to be forward

compatible with future, modularised jOOQ distributions.

Additionally, package names between jOOQ, jOOQ-meta, and jOOQ-codegen have been

cleaned up to prevent duplicate package names, and the JAXB dependency has been

added explicitly to the various artefacts.

Other great improvements

Finally, asterisks (SELECT * or SELECT t.*) are formally supported in the API.

Collations can now be specified on a variety of syntax elements

The org.jooq.Comment type has been added, and DDL statements for it

The DefaultBinding implementation has been rewritten for better peformance

Several performance improvements in jOOQ’s internals

Many more DDL statements are supported including GRANT and REVOKE

Support for the EXPLAIN statement

FETCH n PERCENT ROWS and TOP n PERCENT clauses are supported

Better org.jooq.Name and org.jooq.Named API for identifier handling

Support for PostgreSQL 10

Support for SQL Server 2017

Support for DB2 11

Upgraded MariaDB support for window functions, inv dist functions, WITH

jOOU dependency updated to 0.9.3

jOOR dependency updated to 0.9.8

Server output (e.g. DBMS_OUTPUT) can now be fetched automatically, by jOOQ

Code generation support for PL/SQL TABLE types

SQL Keywords Can Now Be Rendered In Pascal Style If You Must

Emulate PostgreSQL’s ON CONFLICT clause using MERGE

The complete list can be seen here:

https://www.jooq.org/notes/?version=3.11