3.15.0 Release with Support for R2DBC, Nested ROW, ARRAY, and MULTISET types, 5 new SQL dialects, CREATE PROCEDURE, FUNCTION, and TRIGGER support and Much More

R2DBC

What a lot of users have been waiting for: jOOQ 3.15 is reactive, thanks to the new native R2DBC integration. Recent versions already implemented the reactive streams Publisher SPI, but now we’re not cheating anymore. We’re not longer blocking. Just wrap your R2DBC ConnectionFactory configured jOOQ query in a Flux (or any reactive streams API of your choice), and see what happens.

Flux.from(ctx.select(BOOK.TITLE).from(BOOK));

Both blocking (via JDBC) and non-blocking (via R2DBC) can work side-by-side, allowing users to quickly a query between the two execution models without anychanges to the query building logic.

Projecting ROW types, ARRAY of ROW Types, and MULTISETS

After having implemented standard SQL/XML and SQL/JSON support in jOOQ 3.14, another major milestone in taking SQL to the next level is now available as anexperimental feature: Nesting collections using the standard SQL MULTISET operator.

The operator is currently emulated using SQL/XML or SQL/JSON. The resulting documents are parsed again when fetching them from JDBC. Future versions will also provide native support (Informix, Oracle), and emulations using ARRAY (various dialects, including PostgreSQL).

Imagine this query against the Sakila database (https://www.jooq.org/sakila):

var result =
ctx.select(
      FILM.TITLE,
      multiset(
        select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
        .from(ACTOR)
        .join(FILM_ACTOR).using(ACTOR.ACTOR_ID)
        .where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
      ).as("actors"),
      multiset(
        select(CATEGORY.NAME)
        .from(CATEGORY)
        .join(FILM_CATEGORY).using(CATEGORY.CATEGORY_ID)
        .where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
      ).as("films")
   )
   .from(FILM)
   .orderBy(FILM.TITLE)
   .fetch();

You’re really going to love Java 10’s var keyword for these purposes. What’s the type of result? Exactly:

Result<Record3<
    String, 
    Result<Record2<String, String>>, 
    Result<Record1<String>>
>>

It contains:

+---------------------------+--------------------------------------------------+---------------+
|title                      |actors                                            |films          |
+---------------------------+--------------------------------------------------+---------------+
|ACADEMY DINOSAUR           |[(PENELOPE, GUINESS), (CHRISTIAN, GABLE), (LUCI...|[(Documentary)]|
|ACE GOLDFINGER             |[(BOB, FAWCETT), (MINNIE, ZELLWEGER), (SEAN, GU...|[(Horror)]     |
|ADAPTATION HOLES           |[(NICK, WAHLBERG), (BOB, FAWCETT), (CAMERON, ST...|[(Documentary)]|
 ...

Two collections were nested in a single query without producing any unwanted cartesian products and duplication of data. And stay tuned, we’ve added more goodies! See this article on how to map the above structural type to your nominal types (e.g. Java 16 records) in a type safe way, without reflection!

More info here:

• MULTISET value constructor
• MULTISET_AGG aggregate function
• Ad-hoc data type conversion

New Dialects

We’ve added support for 5 (!) new SQLDialect’s. That’s unprecedented for any previous minor release. The new dialects are:

• BIGQUERY
• EXASOL
• IGNITE
• JAVA
• SNOWFLAKE

Yes, there’s an experimental “JAVA” dialect. It’s mostly useful if you want to translate your native SQL queries to jOOQ using https://www.jooq.org/translate, and it cannot be executed. In the near future, we might add SCALA and KOTLIN as well, depending on demand.

BigQuery and Snowflake were long overdue by popular vote. The expedited EXASOL support has been sponsored by a customer, which is a great reminder that this is always an option. You need something more quickly? We can make it happen, even if the feature isn’t very popular on the roadmap.

Many other dialects have been brought up to date, including REDSHIFT, HANA, VERTICA, and two have been deprecated: INGRES and ORACLE10G, as these grow less and less popular.

Drop Java 6/7 support for Enterprise Edition, require Java 11 in OSS Edition

We’re cleaning up our support for old dependencies and features. Starting with jOOQ 3.12, we offered Java 6 and 7 support only to jOOQ Enterprise Edition customers. With jOOQ 3.15, this support has now been removed, and Java 8 is the new baseline for commercial editions, Java 11 for the jOOQ Open Source Edition, meaning the OSS Edition is now finally modular, and we get access to little things like the Flow API (see R2DBC) and @Deprecate(forRemoval, since).

Upgrading to Java 8 allows for exciting new improvements to our internals, as we can finally use default methods, lambdas, generalised target type inference, effectively final, diamond operators, try-with-resources, string switches, and what not. Improving our code base leads to dog fooding, and that again leads to new features for you. For example, we’ve put a lot of emphasis on ResultQuery.collect(), refactoring internals: https://blog.jooq.org/2021/05/17/use-resultquery-collect-to-implement-powerful-mappings/

There are new auxiliary types, like org.jooq.Rows and org.jooq.Records for more functional transformation convenience. More functions mean less loops, and also less ArrayList allocations.

At the same time, we’ve started building a Java 17 ready distribution for the commercial editions, which unlocks better record type support.

Refactoring of ResultQuery to work with DML

With all the above goodies related to Java 8, and a more functional usage of jOOQ, we’ve also finally refactored our DML statement type hierarchy (INSERT,UPDATE, DELETE), to let their respective RETURNING clauses return an actual ResultQuery. That means you can now stream(), collect(), fetchMap() and subscribe() (via R2DBC) to your DML statements, and even put them in the WITH clause (in PostgreSQL).

Massive improvements to the parser / translator use case

jOOQ’s secondary value proposition is to use its parser and translator, instead of the DSL API, which is also available for free on our website: https://www.jooq.org/translate

With increasing demand for this product, we’ve greatly improved the experience:

• The ParsingConnection no longer experimental
• Batch is now possible
• We’ve added a cache for input/output SQL string pairs to heavily speed up the integration
• We’re now delaying bind variable type inference to use actual PreparedStatement information. This produces more accurate results, especially when data types are not known to the parser.
• A new ParseListener SPI allows for hooking into the parser and extend it with custom syntax support for column, table, and predicate expressions.

CREATE PROCEDURE, FUNCTION, TRIGGER and more procedural instructions

Over the recent releases, we’ve started working on procedural language extensions for the commercial distributions. In addition to creating anonymous blocks, we now also support all lifecycle management DDL for procedures, functions, and triggers, which can contain procedural language logic.

This is great news if you’re supporting multiple RDBMS and want to move some more data processing logic to the server side in a vendor agnostic way.

Explicit JDBC driver dependencies to avoid reflection

To get AOP ready, we’re slowly removing internal reflection usage, meaning we’re experimenting with an explicit JDBC driver build-time dependency. This currently affects:

• Oracle
• PostgreSQL
• SQL Server

Only drivers available from Maven Central have been added as dependency so far.

Full release notes here.

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