Menu

Java, SQL and jOOQ.

Skip to content
  • Home
  • Java And Other Languages
  • jOOQ and jOOλ
  • jOOQ Tuesdays and Guest Posts
  • SQL Tricks And Tuning
  • Thoughts on Programming

Java, SQL and jOOQ.

Skip to content
  • Home
  • Java And Other Languages
  • jOOQ and jOOλ
  • jOOQ Tuesdays and Guest Posts
  • SQL Tricks And Tuning
  • Thoughts on Programming

New Logo

April 1, 2018April 1, 2018 fun, java, jooq-development 4 Comments check the date before you get angryjavaJigsawjOOQ 3.11jOOQ 3.11 For WorkgroupsModular jOOQModulesNew Logosql

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

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.

  • Share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on Google+ (Opens in new window)
  • More
  • Click to share on Reddit (Opens in new window)
  • Click to email (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Like this blog? Check out our product:

jOOQ: Get Back in Control of Your SQL

And don’t forget to book our SQL trainings and follow us on Twitter

Jump to:

  • Home
  • SQL Tricks and Tuning
  • Thoughts on Programming
  • jOOQ Tuesdays and Guest Posts
  • Java and Other Languages
  • jOOQ and jOOλ

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 7,419 other followers

Top Posts & Pages

  • The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()
  • 3 Reasons why You Shouldn't Replace Your for-loops by Stream.forEach()
  • How Functional Programming will (Finally) do Away With the GoF Patterns
  • A Beginner's Guide to the True Order of SQL Operations
  • 10 SQL Tricks That You Didn't Think Were Possible
  • 10 More Common Mistakes Java Developers Make when Writing SQL
  • How to Create a Range From 1 to 10 in SQL
  • How to Calculate Multiple Aggregate Functions in a Single Query
  • Java 8 Friday: 10 Subtle Mistakes When Using the Streams API
  • Is Your Eclipse Running a Bit Slow? Just Use This Simple Trick!

Recent Posts

  • The Performance Difference Between SQL Row-by-row Updating, Batch Updating, and Bulk Updating April 19, 2018
  • When Using Bind Variables is not Enough: Dynamic IN Lists April 13, 2018
  • Why SQL Bind Variables are Important for Performance April 12, 2018
  • Mocking JDBC Using a Set of SQL String / Result Pairs April 10, 2018
  • How to Compile a Class at Runtime with Java 8 and 9 April 3, 2018
  • A Completely Overhauled, Modularised jOOQ 3.11, Ready for Java 11 April 1, 2018
  • Correct Reflective Access to Interface Default Methods in Java 8, 9, 10 March 28, 2018
  • Top 10 SQL Dialect Emulations Implemented in jOOQ March 13, 2018
  • Map Reducing a Set of Values Into a Dynamic SQL UNION Query February 27, 2018
  • How to Order Versioned File Names Semantically in Java February 23, 2018
jOOQ: Get Back in Control of Your SQL

Archives

  • April 2018 (6)
  • March 2018 (2)
  • February 2018 (5)
  • January 2018 (1)
  • December 2017 (5)
  • November 2017 (4)
  • October 2017 (2)
  • September 2017 (3)
  • August 2017 (3)
  • July 2017 (7)
  • June 2017 (6)
  • May 2017 (7)
  • April 2017 (2)
  • March 2017 (9)
  • February 2017 (5)
  • January 2017 (6)
  • December 2016 (6)
  • November 2016 (4)
  • October 2016 (7)
  • September 2016 (3)
  • August 2016 (5)
  • July 2016 (8)
  • June 2016 (4)
  • May 2016 (3)
  • April 2016 (7)
  • March 2016 (10)
  • February 2016 (8)
  • January 2016 (8)
  • December 2015 (10)
  • November 2015 (2)
  • October 2015 (8)
  • September 2015 (5)
  • August 2015 (7)
  • July 2015 (3)
  • June 2015 (7)
  • May 2015 (9)
  • April 2015 (11)
  • March 2015 (11)
  • February 2015 (10)
  • January 2015 (9)
  • December 2014 (10)
  • November 2014 (8)
  • October 2014 (5)
  • September 2014 (13)
  • August 2014 (11)
  • July 2014 (10)
  • June 2014 (14)
  • May 2014 (15)
  • April 2014 (11)
  • March 2014 (15)
  • February 2014 (17)
  • January 2014 (14)
  • December 2013 (23)
  • November 2013 (24)
  • October 2013 (19)
  • September 2013 (15)
  • August 2013 (24)
  • July 2013 (17)
  • June 2013 (3)
  • May 2013 (5)
  • April 2013 (6)
  • March 2013 (8)
  • February 2013 (11)
  • January 2013 (3)
  • December 2012 (5)
  • November 2012 (2)
  • October 2012 (5)
  • September 2012 (7)
  • August 2012 (7)
  • July 2012 (7)
  • June 2012 (1)
  • May 2012 (6)
  • April 2012 (12)
  • March 2012 (3)
  • February 2012 (5)
  • January 2012 (12)
  • December 2011 (8)
  • November 2011 (15)
  • October 2011 (17)
  • September 2011 (12)
  • August 2011 (13)
  • July 2011 (4)
Blog at WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: