New Logo – Java, SQL and jOOQ.

If you’ve been following the fast paced JDK 9+ projects, you may have noticed an exciting, first big change that has been made possible thanks to Java 9’s Jigsaw feature. In JDK 11, JEP 320 will ship, or rather: it will no longer ship, as JEP 320 means that both CORBA and Java EE modules (mostly JAXB) will be removed from the Java SE and the JDK. That’s great! So great that Simon Ritter from Azul Systems has written up a blog post:

The Incredible Shrinking Java Platform

We want jOOQ to be shrinking as well! 11 is the number and the perfect occasion as we at Data Geekery are soon going to ship jOOQ 3.11, project code name jOOQ 3.11 For Workgroups.

How to modularise?

As proven by the JDK folks, we need to create many many modules, as many as possible, such that jOOQ users can re-assemble their individual parts in any possible way, potentially shipping a jOOQ as small as a Hello World class:

public class jOOQ {

  // No different from running the query in an actual database
  public static String selectHelloWorldFromDual() {
    return "Hello World";
  }
}

So, let’s split the functionality step by step.

One module per SQL dialect

We’ve thought of splitting jOOQ first in 21 modules, because we support 21 RBDMS as of version jOOQ 3.11. This will be very easy to extend in the future, when we’ll add MongoDB, Cassandra and Hibernate modules. Just copy paste the existing modules and sub-modules, and it’ll work out of the box.

One module per SQL feature

Then, we’ve calculated that we currently support roughly 1337 vendor-specific functions, things like SUBSTRING() or CONCAT() or even SINH(). Aggregate functions like COUNT() and ARRAY_AGG(), and also window functions like ROW_NUMBER().

It’s really cool to compare how these functionalities work on each database. For this, we’ve recently created the SQL translator. If you want to translate your jOOQ code from MySQL to Oracle, just search replace s/mysql/oracle in your module-info.java and you’re done.

Now, imagine a jOOQ user who – let’s say – wants to run SUBSTRING() and CONCAT() queries only on MySQL and Oracle. What will they do? Exactly, pull in only four modules:

module com.example {
    requires org.jooq.oracle.substring;
    requires org.jooq.oracle.concat;
    requires org.jooq.mysql.substring;
    requires org.jooq.mysql.concat;
}

The beauty of this approach is that we can now easily remove the LPAD (left pad) module in the future, as is common practice in modern module systems.

What about statements?

Of course, without the SELECT statement, it’s not possible to actually fetch SUBSTRING() or CONCAT() on either database. But as we all know, SELECT != SELECT. Not all databases have the same support for the various SELECT clauses. For instance, Oracle has CONNECT BY, MySQL does not. The answer: Modules!

This is why each clause has its own module and jOOQ will just run through the module path to see what’s available when it generates your query.

In MySQL, luckily, the FROM clause is optional, i.e. we can run queries like this:

SELECT substring(concat('a', 'b', 'c'), 2)

In Oracle, we need the FROM clause (and don’t forget the dual table), so we have to run this instead:

SELECT substr('a' || 'b' || 'c', 2) FROM dual

The result is the same. So, we’re down to:

module com.example {
    requires org.jooq.oracle.substring;
    requires org.jooq.oracle.concat;
    requires org.jooq.oracle.select;
    requires org.jooq.oracle.select.from;
    requires org.jooq.oracle.tables.dual;
    requires org.jooq.mysql.substring;
    requires org.jooq.mysql.concat;
    requires org.jooq.mysql.select;
}

That’s really cool and convenient!

What about execution?

Excellent question! So far, we’ve only discussed what it takes to use the SQL API. In order to execute such a query, we’ll simply load the relevant modules for:

Rendering the vendor-specific SQL string
Executing the vendor-specific query

As we’re forward looking, we’re already experimenting with the new ADBA API (“JDBC Next”) that is going to be shipping with some future release. The goal is asynchronous database interactions. For the sake of the example, let’s assume we’ll be running the Oracle query asynchronously (via ADBA), and the MySQL query synchronously (via classic JDBC). And since we’re still undecided whether to use ADBA’s built-in CompletionStage support or Spring’s Mono/Flux API, or just classic RxJava, we’ll just pull inn all three ADBA submodules

So, we’ll load these few additional modules:

// The .sql submodule generates SQL strings, e.g. from substring
// The .exec submodule allows for executing statements
module com.example {
    requires org.jooq.oracle.substring;
    requires org.jooq.oracle.substring.sql;
    requires org.jooq.oracle.concat;
    requires org.jooq.oracle.concat.sql;
    requires org.jooq.oracle.select;
    requires org.jooq.oracle.select.sql;
    requires org.jooq.oracle.select.exec;
    requires org.jooq.oracle.select.exec.adba;
    requires org.jooq.oracle.select.exec.adba.completionstage;
    requires org.jooq.oracle.select.exec.adba.monoFlux;
    requires org.jooq.oracle.select.exec.adba.rxjava;
    requires org.jooq.oracle.select.from;
    requires org.jooq.oracle.select.from.sql;
    requires org.jooq.oracle.tables.dual;
    requires org.jooq.oracle.tables.dual.sql;
    requires org.jooq.mysql.substring;
    requires org.jooq.mysql.substring.sql;
    requires org.jooq.mysql.concat;
    requires org.jooq.mysql.concat.sql;
    requires org.jooq.mysql.select;
    requires org.jooq.mysql.select.sql;
    requires org.jooq.mysql.select.exec;
    requires org.jooq.mysql.select.exec.jdbc;
}

Of course, it would totally be possible to load both JDBC and ADBA execution submodules for a given dialect.

How about logging?

It’s important to be able to log executed SQL. Classically, jOOQ shipped a quite heavy weight Execution Logger, which you couldn’t get rid off (you could only turn it off through configuration).

In the next, modular jOOQ version, you can specify on a per-module basis, whether you want to have the generated SQL in your log files. This is really cool! Let’s say, we want to log SUBSTRING() usage, but not really CONCAT() usage. Simply load the relevant submodules of the .sql submodule and of course, don’t forget to actually log things on execution, otherwise, the modules will just sit there, idly:

module com.example {
    requires org.jooq.oracle.substring;
    requires org.jooq.oracle.substring.sql;
    requires org.jooq.oracle.substring.sql.debug;
    requires org.jooq.oracle.concat;
    requires org.jooq.oracle.concat.sql; // No debug here
    requires org.jooq.oracle.select;
    requires org.jooq.oracle.select.sql;
    requires org.jooq.oracle.select.sql.debug;
    requires org.jooq.oracle.select.exec;
    requires org.jooq.oracle.select.exec.debug;
    requires org.jooq.oracle.select.exec.adba;
    requires org.jooq.oracle.select.exec.adba.completionstage;
    requires org.jooq.oracle.select.exec.adba.monoFlux;
    requires org.jooq.oracle.select.exec.adba.rxjava;
    requires org.jooq.oracle.select.from;
    requires org.jooq.oracle.select.from.sql;
    requires org.jooq.oracle.select.from.sql.debug;
    requires org.jooq.oracle.tables.dual;
    requires org.jooq.oracle.tables.dual.sql;
    requires org.jooq.oracle.tables.dual.sql.debug;
    requires org.jooq.mysql.substring;
    requires org.jooq.mysql.substring.sql;
    requires org.jooq.mysql.substring.sql.debug;
    requires org.jooq.mysql.concat;
    requires org.jooq.mysql.concat.sql; // No debug here
    requires org.jooq.mysql.select;
    requires org.jooq.mysql.select.sql;
    requires org.jooq.mysql.select.sql.debug;
    requires org.jooq.mysql.select.exec;
    requires org.jooq.mysql.select.exec.debug;
    requires org.jooq.mysql.select.exec.jdbc;
}

The above requires list models a quite convenient set of dependencies needed to run a single query on two databases. Just add a few more dependencies as you go and add more queries to your application.

Where to go next?

By now, you should have seen where we’re heading with a modularised jOOQ. The examples are far from exhaustive, of course. We’ll have modules for:

Rendering names and identifiers in UPPER_CASE, lower_case, PascalCase.
Rendering names and identifiers in “double quotes”, `backticks`, [brackets], or unquoted.
Rendering SQL keywords in UPPER CASE (REMEMBER: STERN AND CONFIDENT SQL WILL HELP THE DATABASE SENSE THE URGENCY AND RUN THE SQL FASTER), lower case for the more timid ones, and Pascal Case for the connoisseurs.
The jOOQ code generator will generate a module per schema / table and for each table, a submodule per column. This way, individual columns can be ignored if you don’t need them.
The same is true for packages / procedures / arguments. Especially, defaulted arguments that you never need can thus be excluded by simply not requiring the module. Very convenient.

And many many more modules. We truly believe that a more modular jOOQ will be extremely helpful for those of you keen on saving those extra kilobytes in byte code that you need to ship due to the jOOQ dependency.

Excited! Excellent. We’re looking forward to what features project Valhalla and project Amber will ship on the JDK in 1-2 years from today, when we’ll announce yet another cool jOOQ improvement. Stay tuned, same day next year.