Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

A Completely Overhauled, Modularized jOOQ 3.11, Ready for Java 11

DZone's Guide to

A Completely Overhauled, Modularized jOOQ 3.11, Ready for Java 11

In JDK 11, CORBA and Java EE modules will be removed from the Java SE and the JDK. Java is shrinking! And we want jOOQ to be shrinking, as well.

· Java Zone ·
Free Resource

Verify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!

FOR THE RECORD, THE ORIGINAL POST WAS PUBLISHED ON APRIL 1. DZONE TOOK A FEW DAYS TO SYNDICATE IT...

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 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 codename jOOQ 3.11 For Workgroups.

How to Modularize?

As proven by the JDK folks, we need to create many modules — as many as possible — such that jOOQ users can reassemble 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 submodules, and it'll work out of the box.

One Module per SQL Feature

Then, we've calculated that we currently support roughly 1,337 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 in 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.

What About Logging?

It's important to be able to log executed SQL. Classically, jOOQ shipped a quite heavyweight 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 that 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 bytecode 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 one to two years from today, when we'll announce yet another cool jOOQ improvement. Stay tuned, same day next year.

Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue. Our APIs verify, standardize, and correct the Big 4 + more – name, email, phone and global addresses – to ensure accurate delivery, prevent blacklisting and identify risks in real-time.

Topics:
java ,jooq ,sql ,mysql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}