Using Testcontainers to Generate jOOQ Code

Database first is at the core of jOOQ’s design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think “data have mass”

This not only translates to moving logic closer to the data (see our previous posts about the cost of JDBC round trips or generating vendor agnostic procedural logic), but also avoiding moving data around between migrations (e.g. of RDBMS products).

Compared to how “heavy” data is, applications and UIs come and go. Speaking of go, maybe you’ll replace all of your Java code tomorrow for some go code. But you will keep the database if it isn’t trivial.

With this in mind, jOOQ assumes you have a pre-existing schema, which you mange with Flyway or Liquibase, and then you use jOOQ to reverse engineer your updated schema using the code generator.

The old days

In the old days, setting up an Oracle instance was very heavy, and also hard. I remember working at a company where we had shared development and test instances. The schema was always in flux. We couldn’t assume a stable dev version.

As such, pointing the jOOQ code generator towards a live database used to be a bit of a challenge, which is why jOOQ offers alternative, connection-free code generation modes, including:

  • The JPADatabase, if you have a pre-existing JPA entity based meta model.
  • The XMLDatabase, if you have some form of XML version of your schema, which you can XSL transform to jOOQ’s format
  • The DDLDatabase, which can interpret your DDL scripts, e.g. the ones you pass to Flyway, or the ones produced by pg_dump.
  • The LiquibaseDatabase, which simulates a Liquibase database migration and uses the simulated database output as a source for meta information of the code generator

But all of the above have the same limitation. You can’t really use many vendor-specific features, such as advanced stored procedures, data types, etc.

A modern approach using testcontainers

Ideally, unless you’re supporting several RDBMS products (most people don’t), you should work only with your production database product, say PostgreSQL.

Thanks to testcontainers.org, it’s very easy to programmatically, or configuratively, start up a PostgreSQL instance of any version in a Docker container. If you have a SQL script that contains your database, you can supply it to the testcontainers JDBC URL, e.g. like this:

jdbc:tc:postgresql:13:///sakila?TC_TMPFS=/testtmpfs:rw&TC_INITSCRIPT=file:${basedir}/src/main/resources/postgres-sakila-schema.sql

For more information, see their docs about JDBC support. Now, add the testcontainers dependency on your project classpath, e.g.

<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>postgresql</artifactId>
</dependency>

And use the ContainerDatabaseDriver instead of the actual PostgreSQL driver for your code generation configuration in jOOQ, e.g. when using Maven:

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>

  <executions>
    <execution>
      <id>java-generator</id>
      <phase>generate-sources</phase>
      <goals>
        <goal>generate</goal>
      </goals>

      <configuration>
        <jdbc>
          <driver>
            org.testcontainers.jdbc.ContainerDatabaseDriver
          </driver>
          <url>${db.url}</url>
          <user>${db.username}</user>
          <password>${db.password}</password>
        </jdbc>
        <generator>
          <database>
            <inputSchema>public</inputSchema>
          </database>
          <target>
            <packageName>org.jooq.example.tc.db</packageName>
            <directory>src/main/java</directory>
          </target>
        </generator>
      </configuration>
    </execution>
  </executions>

  <dependencies>

    <!-- Junit seems a transitive dependency of testcontainers? -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.13.1</version>
    </dependency>
  </dependencies>
</plugin>

As simple as that! Check out the jOOQ-testcontainers-example for a runnable example that uses testcontainers for code generation using the above approach.

Adding database change management

A real world example would be using again Flyway or Liquibase, etc. to apply a complete database migration to your PostgreSQL instance inside of testcontainers, prior to generating code and/or running your integration tests.

This just slightly complicates things, but doesn’t produce any impossible problems. Instead of creating throwaway containers with a single TC_INITSCRIPT, you will now have to make sure the following steps are executed consecutively in your build somehow:

  1. A testcontainers instance of your database is started
  2. A Flyway or Liquibase migration is run inside of that database
  3. The jOOQ code generator reverse engineers that database
  4. Optionally, your integration tests also reuse the database of that container

Of course you should integration test your code! But for the sake of this discussion, that might be an optional step, as you may have different preferences on how to run those tests, e.g. more globally than just for this module. But in our example, let’s include the tests.

You can find the full example using testcontainers/flyway/jOOQ here.

Start the testcontainers instance

Unfortunately, testcontainers doesn’t ship any Maven / Gradle plugins yet to invoke container lifecycle management during a build. I’ve created a feature request for that here, which you should upvote: https://github.com/testcontainers/testcontainers-java/issues/4397.

But we can easily help ourselves by using the ever powerful Maven escape hatch that is the groovy-maven-plugin (the ideas are the same for gradle):

<plugin>
  <groupId>org.codehaus.gmaven</groupId>
  <artifactId>groovy-maven-plugin</artifactId>
  <version>2.1.1</version>
  <executions>
    <execution>
      <!-- Start the container in any phase before the actual code
           generation is required, i.e. at the latest in
           generate-sources -->
      <phase>generate-sources</phase>
      <goals>
        <goal>execute</goal>
      </goals>
      <configuration>
        <source>
          db = new org.testcontainers.containers.PostgreSQLContainer(
                  "postgres:latest")
            .withUsername("${db.username}")
            .withDatabaseName("postgres")
            .withPassword("${db.password}");
            
          db.start();

          // After you've started the container, collect its generated
          // JDBC URL (which contains a random port)
          project.properties.setProperty('db.url', db.getJdbcUrl());
        </source>
      </configuration>
    </execution>
  </executions>
  
  <dependencies>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>postgresql</artifactId>
      <version>1.15.2</version>
    </dependency>
  </dependencies>
</plugin>

So, that starts a container and keeps it running until the build terminates. I won’t show a graceful shutdown, because it’s not needed for the example, but you could implement that as well, of course.

Now, migrate your database

The above database is empty. Now to run the migration, in the example using Flyway, but it will be the same thing with Liquibase.

<plugin>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-maven-plugin</artifactId>
  <version>7.14.0</version>
  <executions>
    <execution>

      <!-- We run the migration in the same phase, before jOOQ's
           code generation -->
      <phase>generate-sources</phase>
      <goals>
        <goal>migrate</goal>
      </goals>
      <configuration>

        <!-- This URL has been set by groovy, above -->
        <url>${db.url}</url>
        <user>${db.username}</user>
        <password>${db.password}</password>
        <locations>
          <location>
            filesystem:src/main/resources/db/migration
          </location>
        </locations>
      </configuration>
    </execution>
  </executions>
</plugin>

Add all the additional complexity of your migration in this configuration if you like. jOOQ wouldn’t know anything about it.

Now, generate the code

Again, nothing special here:

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>

  <executions>
    <execution>
      <id>java-generator</id>

      <!-- Same phase as above, but the previous plugins have already
           executed, so we're generating the db post migration -->
      <phase>generate-sources</phase>
      <goals>
        <goal>generate</goal>
      </goals>

      <configuration>
        <jdbc>

          <!-- Again, this URL has been set by groovy, above -->
          <url>${db.url}</url>
          <user>${db.username}</user>
          <password>${db.password}</password>
        </jdbc>
        <generator>
          <database>
            <inputSchema>public</inputSchema>
          </database>
          <target>
            <packageName>org.jooq.example.db</packageName>
          </target>
        </generator>
      </configuration>
    </execution>
  </executions>
</plugin>

And finally, optionally, integration test

If you want to re-use the above container with migrated database also in your integration tests, you could just pass along the generated JDBC URL to the maven-surefire-plugin as follows:

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-surefire-plugin</artifactId>
  <configuration>
    <systemPropertyVariables>

      <!-- Again, this URL has been set by groovy, above -->
      <db.url>${db.url}</db.url>
      <db.username>${db.username}</db.username>
      <db.password>${db.password}</db.password>
    </systemPropertyVariables>
  </configuration>
</plugin>

There are many ways to achieve the same thing, this is one of them that works decently out of the box. You can check out a full example from github here, and play around with it:

https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-testcontainers-flyway-example

More about testcontainers

To learn more about testcontainers, see our interview with Richard North here.

Using jOOQ to write vendor agnostic SQL with JPA’s native query or @Formula

If your legacy JPA application is using occasional native queries or Hibernate @Formula or Spring Data @Query annotation with vendor specific native SQL embedded in it, you can use jOOQ’s parsing connection and parsing data source to translate between dialects, without having to go all in on your jOOQ adoption – though I think it’s inevitable once you see what jOOQ can do for you.

Now, let’s design a table like this:

CREATE TABLE author (
  id INT NOT NULL,
  first_name TEXT,
  last_name TEXT NOT NULL,

  CONSTRAINT pk_author PRIMARY KEY (id)
);

Now, you may want to write a native query on this table using JPA’s EntityManager.createNativeQuery(), mapping that to entities. You could be using jOOQ’s DSL API for this, but let’s say you’re not ready for a migration to jOOQ yet, or you want to use actual SQL as provided by your DBA, not jOOQ’s DSL.

So, in MariaDB, you might be writing something like this:

List<Author> result =
em.createNativeQuery("""
    select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
    from t_author as a
    order by a.id
    """, Author.class)
  .getResultList();

Where your entity is defined like this:

@Entity
@Table(name = "author")
public class Author {
    @Id
    public int id;

    @Column(name = "first_name")
    public String firstName;

    @Column(name = "last_name")
    public String lastName;

    // Constructors, getters, setters, equals, hashCode, etc
}

The above runs just fine and produces all the authors in MariaDB, which implemented support for Oracle’s NVL() function. But what about Oracle itself? The query fails on Oracle with:

ORA-00933: SQL command not properly ended

This is because in Oracle, you can’t use the AS keyword to alias tables, only to alias columns. Sure, you can remove that, but what about NVL()? You want this to work on MySQL and SQL Server, as well, but they complain:

MySQL

SQL Error [1305] [42000]: FUNCTION test.nvl does not exist

SQL Server

SQL Error [195] [S0010]: ‘nvl’ is not a recognized built-in function name.

Now, you have these options:

  • Use jOOQ to generate the SQL string for you, using the DSL
  • Use JPQL instead of a native query (but then heavily rewrite it, because JPQL is much less powerful than SQL)
  • Try your luck writing actual vendor agnostic SQL, manually
  • Or…

jOOQ’s parsing connection

You could use jOOQ’s parsing connection, which acts as a proxy to your actual connection, intercepting every SQL statement on the JDBC level in order to translate it to the target dialect.

This is as simple as wrapping your existing JDBC Connection or DataSource as follows:

DataSource originalDataSource = ...
DataSource parsingDataSource = DSL
    .using(originalDataSource, dialect)
    .parsingDataSource();

That’s it! I mean, you could pass some additional configuration Settings after the dialect, but that’s about as easy as it gets. The new DataSource can now run your SQL query on all the aforementioned dialects, e.g. you might see this in your DEBUG logs:

On MySQL:

-- org.hibernate.SQL
   select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name 
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translating from:
   select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translation cache miss: 
   select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translating to: 
   select a.id, ifnull(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id

On SQL Server:

-- org.hibernate.SQL
   select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name 
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translating from:
   select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection Translation cache miss: 
   select a.id, nvl(a.first_name, 'N/A') as first_name, a.last_name
   from t_author as a 
   order by a.id
-- org.jooq.impl.ParsingConnection] Translating to: 
   select a.id, coalesce(a.first_name, 'N/A') first_name, a.last_name 
   from author a 
   order by a.id

Hibernate was tricked by jOOQ! The NVL function was translated to MySQL’s IFNULL or SQL Server COALESCE, and the AS keyword was removed from the SQL Server query. These are just simple examples, your actual SQL may be much more complex. Play around with the feature set online, here.

Also, the Settings.cacheParsingConnectionLRUCacheSize flag, which defaults to 8192, makes sure the same query doesn’t get re-translated all the time so you won’t spend too much time in jOOQ’s parser.

@Formula too, not just native queries

A quick win in Hibernate when you want to project additional values, akin to SQL’s own computed columns, which are available in many SQL dialects, is the @Formula annotation, which can be added to any entity, like so. Assuming this additional column:

ALTER TABLE author ADD year_of_birth INT;

We might have the following amended entity:

@Entity
@Table(name = "author")
public class Author {
    @Id
    public int id;

    @Column(name = "first_name")
    public String firstName;

    @Column(name = "last_name")
    public String lastName;

    @Column(name = "year_of_birth")
    public Integer yearOfBirth;

    @Formula("year_of_birth between 1981 and 1996")
    public Boolean millenial;

    // Constructors, getters, setters, equals, hashCode, etc
}

But unfortunately, there are still so many RDBMS that don’t actually support boolean types, and the @Formula annotation is purely static, and doesn’t allow for vendor-specific overrides. Are we going to rewrite that SQL query manually to make sure a SQL-92, vendor agnostic native SQL fragment is present that works on all dialects?

Or will we just plug in jOOQ’s parsing connection again? Let’s try the latter with:

Author author = em.find(Author.class, 1);

The MySQL log contains:

-- org.hibernate.SQL
   select 
     jpaauthorw0_.id as id1_4_0_,
     jpaauthorw0_.first_name as first_na2_4_0_, 
     jpaauthorw0_.last_name as last_nam3_4_0_, 
     jpaauthorw0_.year_of_birth between 1981 and 1996 as formula1_0_ 
   from author jpaauthorw0_ 
   where jpaauthorw0_.id=?
-- org.jooq.impl.ParsingConnection Translating from: [...]
-- org.jooq.impl.ParsingConnection Translation cache miss: [...]
-- org.jooq.impl.ParsingConnection Translating to: 
   select 
     jpaauthorw0_.id as id1_4_0_, 
     jpaauthorw0_.first_name as first_na2_4_0_, 
     jpaauthorw0_.last_name as last_nam3_4_0_, 
     jpaauthorw0_.year_of_birth between 1981 and 1996 as formula1_0_ 
   from author as jpaauthorw0_ 
   where jpaauthorw0_.id = ?

As you can see, jOOQ re-added the AS keyword to alias the on MySQL for good measure, because we like to be explicit about aliasing, and because that’s the default for Settings.renderOptionalAsKeywordForTableAliases

Whereas the SQL Server log contains:

-- org.hibernate.SQL 
   select 
     jpaauthorw0_.id as id1_4_0_, 
     jpaauthorw0_.first_name as first_na2_4_0_, 
     jpaauthorw0_.last_name as last_nam3_4_0_, 
     jpaauthorw0_.year_of_birth between 1981 and 1996 as formula1_0_ 
   from author jpaauthorw0_ 
   where jpaauthorw0_.id=?
-- org.jooq.impl.ParsingConnection Translating from: [...]
-- org.jooq.impl.ParsingConnection Translation cache miss: [...]
-- org.jooq.impl.ParsingConnection Translating to: 
   select
     jpaauthorw0_.id id1_4_0_, 
     jpaauthorw0_.first_name first_na2_4_0_, 
     jpaauthorw0_.last_name last_nam3_4_0_, 
     case 
       when jpaauthorw0_.year_of_birth between 1981 and 1996 
         then 1 
       when not (jpaauthorw0_.year_of_birth between 1981 and 1996) 
         then 0 
     end formula1_0_ 
   from author jpaauthorw0_ 
   where jpaauthorw0_.id = ?

A NULL-safe BOOLEAN type emulation (because if YEAR_OF_BIRTH is NULL (i.e. UNKNOWN), then MILLENIAL must be NULL, too (i.e. UNKNOWN))

Spring Data @Query annotation

Another case where native SQL can appear in JPA integrations is Spring Data JPA with its @Query annotation, especially when used with @Query(nativeQuery = true). Just like Hibernate’s @Formula, this annotation is compile-time static, with no way to override the value of the native query at runtime, short of maybe subtyping repositories per dialect.

But why go through all that hassle. It’s always the same thing. Just patch the DataSource with jOOQ’s parsing connection or parsing data source, and you’re set.

Conclusion

Even if you’re not using jOOQ’s DSL API, you can profit from jOOQ in many ways in your existing JDBC, R2DBC, JPA, MyBatis, etc. based applications by hooking in the jOOQ parsing connection and translating your vendor specific input dialect to any number of configurable output dialects.

If jOOQ’s parser can’t handle a feature, chances are, you can work around this limitation using the ParseListener SPI, e.g. when you want to support a hypothetical LOGICAL_XOR predicate (which is supported natively in MySQL):

Query query = configuration
    .derive(ParseListener.onParseCondition(ctx -> {
        if (ctx.parseFunctionNameIf("LOGICAL_XOR")) {
            ctx.parse('(');
            Condition c1 = ctx.parseCondition();
            ctx.parse(',');
            Condition c2 = ctx.parseCondition();
            ctx.parse(')');

            return CustomCondition.of(c -> {
                switch (c.family()) {
                    case MARIADB:
                    case MYSQL:
                        c.visit(condition("{0} xor {1}", c1, c2));
                        break;
                    default:
                        c.visit(c1.andNot(c2).or(c2.andNot(c1)));
                        break;
            });
        }

        // Let the parser take over if we don't know the token
        return null;
    })
    .dsl()
    .parser()
    .parseQuery(
        "select * from t where logical_xor(t.a = 1, t.b = 2)"
    );
  
System.out.println(DSL.using(SQLDialect.MYSQL).render(query));
System.out.println(DSL.using(SQLDialect.ORACLE).render(query));

The above program will print:

-- MYSQL:
select * 
from t
where (t.a = 1 xor t.b = 2);

-- ORACLE:
select * 
from t 
where (t.a = 1 and not (t.b = 2)) or (t.b = 2 and not (t.a = 1));

So, profit from using jOOQ to migrate your application’s vendor specific SQL off one RDBMS onto another, or to support multiple RDBMS products in a single application, with or without using jOOQ’s DSL!

Side note: Query transformation

This isn’t the topic of this blog post, but once you have jOOQ parse your every SQL statement, you can also use jOOQ to transform this SQL and tamper with the expression tree, for example by implementing client side row level security. The possibilities are endless!

Vendor Agnostic, Dynamic Procedural Logic with jOOQ

One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code.

SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, but also statically as we’ve blogged before.

Sometimes, however, an imperative 3GL is better suited for a given task. That’s where stored procedures shine, or more specifically, procedural languages of RDBMS.

Among the ones that jOOQ supports, at least these ones support procedures:

  • BigQuery
  • Db2
  • Exasol
  • Firebird
  • HANA
  • HSQLDB
  • Informix
  • MariaDB
  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server
  • Vertica

Others may do, as well, but jOOQ isn’t supporting their dialects yet.

Many have implemented their own procedural languages, some according to the ISO/IEC 9075-4 Persistent stored modules (SQL/PSM) standard, others have their own.

jOOQ support for procedural logic

Since jOOQ 3.12, our commercial distributions have supported anonymous blocks and the procedural statements they contain, such as the IF statement, LOOP statements, etc. Starting with jOOQ 3.15, we also support 3 types of statements to manage storing procedural logic in the catalog:

Using these statements via jOOQ may not be your every day use-case. You may prefer managing that logic via the native syntax, which is still more powerful than what jOOQ 3.15 supports (especially when you’re using Oracle’s PL/SQL), in case of which you’ll u se jOOQ purely to call your procedure from Java in the usual type safe manner.

But maybe, you have one of these use-cases?

  • You’re a product vendor, and you profit from procedural logic being vendor agnostic in order to support multiple of your clients’ RDBMS
  • Your procedural logic is dynamic, just like your SQL logic (and what other than jOOQ to use for that?)
  • You don’t have the necessary privileges to create procedures, functions, or triggers in your schema

In all of those cases, jOOQ is here to help you.

How does it work?

The first building block is the anonymous block, which isn’t supported by all of the above dialects, regrettably. jOOQ can emulate it on MySQL as discussed here, but not currently in other dialects.

Here’s a simple, empty anonymous block:

-- Db2
BEGIN
END

-- Firebird
EXECUTE BLOCK AS
BEGIN
END

-- MariaDB
BEGIN NOT ATOMIC
END;

-- Oracle
BEGIN
  NULL;
END;

-- PostgreSQL
DO $$
BEGIN
  NULL;
END;
$$

It doesn’t really do much, but you can try executing it as follows, with jOOQ:

ctx.begin().execute();

Now, let’s do something more interesting, such as:

// Assuming the usual static imports:
import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

// Then write
Variable<Integer> i = variable(unquotedName("i"), INTEGER);
Table<?> t = table(unquotedName("t"));
Field<Integer> col = field(unquotedName("col"), INTEGER);

ctx.begin(
    declare(i).set(1),

    while_(i.le(10)).loop(
        insertInto(t).columns(c).values(i),
        i.set(i.plus(1))
    )
).execute();

The above block executes:

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END

-- FIREBIRD
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
BEGIN NOT ATOMIC
  DECLARE i int;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Oracle
DECLARE
  i number(10);
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;

-- PostgreSQL
DO $$
DECLARE
  i int;
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;
$$

-- SQL Server
BEGIN
  DECLARE @i int;
  SET @i = 1;
  WHILE @i <= 10 BEGIN
    INSERT INTO t (c)
    VALUES (@i);
    SET @i = (@i + 1);
  END;
END;

Easy as pie. Perhaps you prefer a FOR loop, instead? Try this:

ctx.begin(
    for_(i).in(1, 10).loop(
        insertInto(t).columns(c).values(i)
    )
).execute();

It produces the necessary emulations, if required, because regrettably, not all dialects support FOR:

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END

-- Firebird
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
BEGIN NOT ATOMIC
  FOR i IN 1 .. 10 DO
    INSERT INTO t (c)
    VALUES (i);
  END FOR;
END;

-- Oracle
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;

-- PostgreSQL
DO $$
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;
$$

-- SQL Server
BEGIN
  DECLARE @i int;
  BEGIN
    SET @i = 1;
    WHILE @i <= 10 BEGIN
      INSERT INTO t (c)
      VALUES (@i);
      SET @i = (@i + 1);
    END;
  END;
END;

SQL vs procedures

Of course, this particular SQL statement would be better implemented using a single bulk insertion statement, purely with SQL, not with procedural logic

ctx.insertInto(t, c)
   .select(selectFrom(generateSeries(1, 10)))
   .execute();

Which translates to:

-- Db2
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH
    generate_series(generate_series) AS (
      SELECT 1
      FROM SYSIBM.DUAL
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) generate_series;

-- Firebird
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH RECURSIVE
    generate_series(generate_series) AS (
      SELECT 1
      FROM RDB$DATABASE
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) generate_series;

-- MariaDB
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH RECURSIVE
    generate_series(generate_series) AS (
      SELECT 1
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) AS generate_series;

-- Oracle
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  SELECT (level + (1 - 1)) generate_series
  FROM DUAL
  CONNECT BY level <= ((10 + 1) - 1)
) generate_series;

-- PostgreSQL
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM generate_series(1, 10);

-- SQL Server
WITH
  generate_series(generate_series) AS (
    SELECT 1
    UNION ALL
    SELECT (generate_series + 1)
    FROM generate_series
    WHERE generate_series < 10
  )
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  SELECT generate_series
  FROM generate_series
) generate_series

… but you get the point.

Storing the procedural logic

If you have the necessary privileges, and your procedural logic isn’t super dynamic, you may choose to store your logic in a procedure or function directly in your database. In some databases, this means a compiler will be able to eagerly translate the logic to something very efficient (e.g. machine code), instead of interpreting the logic on the fly.

Take the above WHILE loop, for example. You may want to store that as a procedure P:

Name p = unquotedName("p");

ctx.createProcedure(p)
   .modifiesSQLData()
   .as(
        declare(i).set(1),

        while_(i.le(10)).loop(
            insertInto(t).columns(c).values(i),
            i.set(i.plus(1))
        )
   )
   .execute();

This produces the following statements:

-- Db2
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Firebird
CREATE PROCEDURE p()
AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
  DECLARE i int;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Oracle
CREATE PROCEDURE p
AS
  i number(10);
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;

-- PostgreSQL
CREATE PROCEDURE p()
LANGUAGE plpgsql
AS
$$
DECLARE
  i int;
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;
$$

-- SQL Server
CREATE PROCEDURE p
AS
BEGIN
  DECLARE @i int;
  SET @i = 1;
  WHILE @i <= 10 BEGIN
    INSERT INTO t (c)
    VALUES (@i);
    SET @i = (@i + 1);
  END;
END;

And now, what better way to call this procedure than, again, an anonymous block?

ctx.begin(call(unquotedName("p"))).execute();

Producing:

-- Db2
BEGIN
  CALL p();
END

-- Firebird
EXECUTE BLOCK AS
BEGIN
  EXECUTE PROCEDURE p;
END

-- MariaDB
BEGIN NOT ATOMIC
  CALL p();
END;

-- Oracle
BEGIN
  p();
END;

-- PostgreSQL
DO $$
BEGIN
  CALL p();
END;
$$

-- SQL Server
BEGIN
  EXEC p ;
END;

If you’re using jOOQ in Flyway or Liquibase to generate procedures during your database migrations, you can obviously generate jOOQ procedure stubs to call in a more type safe manner, instead of the above dynamic procedure call.

Parsing procedural logic

This jOOQ feature is not really exceptional. You can play around with our parser / translator here: https://www.jooq.org/translate. It can definitely help you translate your (simpler) stored procedures between dialects, such as PL/SQL, T-SQL, PL/pgSQL, etc.

Conclusion

As a rule of thumb, if you can do it with SQL (the 4GL), do it with SQL alone. But sometimes, you can’t. A 3GL is a better choice for an algorithm. When using jOOQ, you’ll naturally think of using Java to implement that 3GL algorithm. But wait, you could move the logic to the server for (drastically) increased performance!

Thanks to jOOQ, you can generate procedural logic that is:

  • Dynamic
  • Vendor agnostic
  • Anonymous or stored

Just like you’re used to, from jOOQ, for SQL

MySQL’s allowMultiQueries flag with JDBC and jOOQ

MySQL’s JDBC connector has a security feature called allowMultiQueries, which defaults to false. When turned off, it prevents using a useful, but potentially dangerous feature in MySQL via JDBC:

try (Statement s = connection.createStatement()) {
    try {
        s.execute("create table t (i int);");

        // This doesn't work, by default:
        s.executeUpdate("""
            insert into t values (1);
            insert into t values (2);
        """);
    }
    finally {
        s.execute("drop table t");
    }
}

By default, the above produces a syntax error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into t values (2)' at line 2
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1333)
	at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2106)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1243)
	at org.jooq.testscripts.JDBC.main(JDBC.java:34)

We can’t chain statements like that, unless we turn on allowMultiQueries=true in the JDBC connection URL:

jdbc:mysql://localhost/test?allowMultiQueries=true

And now, suddenly, the statement batch completes normally, and two records are inserted into the table.

Why this feature?

The security feature helps prevent some SQL injection vulnerabilities. It is now much harder to append additional statements in case you have the bad idea of concatenating values to your string, such as:

// Terrible idea:
s.executeUpdate("insert into t values (" + value + ")");

Because, what if value contained the string "1); drop table t;"? It would be syntactically correct, so it would execute “as expected.” That wouldn’t be very nice.

Don’t get a false sense of security now. Turning off this capability will not prevent all SQL injection vulnerabilities. Just make this particular one harder. There are still various ways this particular lack of using a bind variable can lead to an attacker reading your data, e.g. through a time-based attack.

The risk of SQL injection needs to be taken seriously. The best thing is to always write static SQL with bind variables (e.g. PreparedStatement, stored procedures, or jOOQ), or a SQL builder like jOOQ for dynamic SQL.

Using allowMultiQueries in jOOQ

When using jOOQ, the above situation is very unlikely to happen. The default usage of jOOQ is using:

  • The code generator to generate database meta data
  • The DSL to generate SQL

Only in rare cases would you use plain SQL templating to work around a specific lack of functionality in jOOQ, and in that case, the templating language will help you avoid concatenating strings and running into SQL injection vulnerabilities.

If you’re of the careful sort, you can add an annotation processor to your build that prevents using the plain SQL API in jOOQ (any usage won’t compile by default, unless you explicitly opt in).

So, the MySQL flag isn’t really useful for your jOOQ usage. In fact, it’s even a problem, because jOOQ internally relies on generating statement batches as the above. Here are some features that don’t work correctly when you turn off allowMultiQueries=false (most of these also apply to MariaDB, btw):

GROUP_CONCAT

Whenever you use GROUP_CONCAT in jOOQ on MySQL, jOOQ assumes you haven’t already changed MySQL’s default value for @@group_concat_max_length. The default value is extremely low, namely 1024. And not only does that value prevent the string aggregation of larger data sets, it just fails silently, which produces wrong values!

When emulating JSON_ARRAYAGG() in MySQL using GROUP_CONCAT, there’s usually a detectable syntax error in the resulting JSON array, but this isn’t the case when you just want to produce some string values, e.g. a comma separated list. (See this previous blog why we don’t use the native JSON_ARRAYAGG() support yet).

So, what jOOQ does every time you use GROUP_CONCAT explicitly (or jOOQ uses it internally for some emulation), jOOQ will prepend and append the following statements:

-- These are prepended
SET @t = @@group_concat_max_len;
SET @@group_concat_max_len = 4294967295;

-- Actual statement here:
SELECT group_concat(A SEPARATOR ',') FROM T;

-- These are appended
SET @@group_concat_max_len = @t;

If you already fixed the system or session variable yourself, you can turn off this feature by changing the Settings.renderGroupConcatMaxLenSessionVariable flag.

CREATE OR REPLACE FUNCTION

Many SQL dialects have a CREATE OR REPLACE syntax for stored procedures, functions, triggers, and other stored objects that do not contain data. It’s very useful syntax sugar for writing this, instead:

-- Much simpler
CREATE OR REPLACE FUNCTION f ...

-- Than this
DROP FUNCTION IF EXISTS f;
CREATE FUNCTION f ...

But again, if you turn off allowMultiQueries=false, then this emulation in jOOQ won’t work and you get a syntax error again. There’s nothing jOOQ can do here for you. You’d have to manually run the two statements, instead of using the convenience syntax.

FOR UPDATE WAIT n

Many dialects have a FOR UPDATE WAIT n syntax that allows for specifying a WAIT timeout for pessimistic locks, e.g.

SELECT *
FROM t
FOR UPDATE WAIT n;

MySQL 8.0.26 doesn’t support this feature yet, but since jOOQ 3.15 and #11543, we’re emulating the above syntax using this:

SET @t = @@innodb_lock_wait_timeout;
SET @@innodb_lock_wait_timeout = 2;
SELECT *
FROM t
FOR UPDATE;
SET @@innodb_lock_wait_timeout = @t;

Another thing that wouldn’t work if you had allowMultiQueries=false

Anonymous blocks

Many procedural languages support anonymous blocks of procedural code, i.e. procedural code that is not stored in a procedure. It makes perfect sense. After all, we don’t have to store all of our SQL in views either, so why do we have to store our PL/SQL, T-SQL, PL/pgSQL, etc. in a stored procedure? This can be very useful especially when you want to generate these blocks dynamically, using jOOQ to run some logic on the server rather than the client, reducing round trips.

In Oracle, you can write:

BEGIN
  INSERT INTO t VALUES (1);

  IF TRUE THEN
    INSERT INTO t VALUES (2);
  END IF;
END;

jOOQ has started supporting such anonymous blocks since 3.12. Look at the manual page about the IF statement. You can write:

// Assuming the usual static imports:
import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType;

// Then write:
Variable<Integer> i = var("i", INTEGER);

ctx.begin(
  declare(i).set(1),

  if_(i.eq(0)).then(
    insertInto(A).columns(A.COL).values(1)
  ).elsif(i.eq(1)).then(
    insertInto(B).columns(B.COL).values(2)
  ).else_(
    insertInto(C).columns(C.COL).values(3)
  )
).execute();

This translates to and executes the correct procedural anonymous blocks in those dialects that support them, but MySQL 8.0.26 unfortunately doesn’t, yet, so what do we do? We generate an “anonymous” procedure, call it, and drop it again:

CREATE PROCEDURE block_1629705082441_2328258() 
BEGIN
DECLARE i INT; 
  SET i = 1; 

  IF i = 0 THEN
    INSERT INTO a (col) VALUES (1);
  ELSEIF i = 1 THEN
    INSERT INTO b (col) VALUES (2);
  ELSE
    INSERT INTO c (col) VALUES (3);
  END IF; 
END; 
CALL block_1629705082441_2328258(); 
DROP PROCEDURE block_1629705082441_2328258;

I mean, why not. But again, this relies on allowMultiQueries=true, otherwise, the JDBC driver will reject this statement.

For more info about the procedural language API in jOOQ, please refer to: https://blog.jooq.org/vendor-agnostic-dynamic-procedural-logic-with-jooq/

Conclusion

MySQL’s JDBC driver has a nice security feature that is intended to prevent some cases of SQL injection, especially when users use the JDBC connection for manual SQL execution. There’s always that one poor soul on the team that doens’t know about SQL injection yet, and thus gets it wrong, opening pandora’s box. For those usages, allowMultiQueries=false is a reasonable default.

When using jOOQ as jOOQ was intended to be used, SQL injection is much less likely. This excludes plain SQL templating usages, in case of which this article doesn’t apply, though. On the other hand, jOOQ internally relies on allowMultiQueries=true to enable a few emulations that require multiple statements to be executed in a single round trip.

A future jOOQ version will allow for configuring the execution model for multi queries, such that the above can be executed as multiple round trips. See #9645 for more details.

Until then, if you want to get the most out of jOOQ with MySQL, be sure to turn on allowMultiQueries=true on your jOOQ connection, possibly keeping it turned off elsewhere.

10 Things You Didn’t Know About jOOQ

jOOQ has been around for a while – since around 2009 as a publicly available library, and since 2013 as a commercially licensed product.

A lot of things have happened in 12 years. Here are 10 things that you maybe didn’t know about jOOQ.

1. eq, ne, gt, ge, lt, le are inspired by XSLT

What’s harder than naming a local variable?

Naming public API! The original jOOQ had methods like these to construct predicates:

But words such as greaterOrEqual() and others are kinda “heavy” in the middle of what is supposed to be a very readable, SQL style DSL, so a short version needed to be added. But what to name the short version? There are different opinions.

Since I love XSLT almost as much as I love SQL, it was natural to choose eq, ne, lt, le, gt, ge for these operators, just like in XSLT. Well, XPath, actually, to be precise. These are also available as HTML entities.

Fun fact, starting with jOOQ, we support also parsing these abbreviations in the SQL parser to support alternative Teradata syntax, see https://github.com/jOOQ/jOOQ/issues/11844 (yes, the almighty Teradata!)

-- Valid in Teradata, I mean, why not?
SELECT *
FROM t
WHERE id EQ 1

Other comparable libraries use eq, ne, lt, loe, gt, goe. But I could never sleep at night knowing the inconsistent length of names of these very similar operators.

2. API naming regrets

Some names, I wish I hadn’t chosen. The three most prominent ones are:

  • Field (it’s really more of a column or column expression. Field sounds so MS Excel-ish)
  • Condition (The SQL standard calls it predicate)
  • Record (the name is sound, but it now conflicts with java.lang.Record, which is a pain. If only I had called it Row)

Alas, there’s no way these terms are ever going to be changed without breaking every jOOQ application out there for no good reason. Such is life of an API developer.

3. Overloads in jOOQ’s API are really untagged union types

Oh, if only Java were more like TypeScript. They have these beautiful first-class untagged union types, which we poor Java folks only know from exception catch blocks, where they don’t exist as a first-class language feature, but just as syntactic sugar:

type F<T> = String | Name | Field<T> | Select<? extends Record1<T>>

If “just” we had those in Java… (along with the above type aliases). Then all the troubles of maintaining a vast API like jOOQ would be gone, namely the ever present set of overloaded methods.

Just look at the overloads for DSL::substring. Just look:

And that isn’t covering all possible permutations, by far. A pragmatic decision was made that it is not too likely for the first argument to be a string bind variable. We hardly ever support Name unless the argument is really about a column reference, not an arbitary column expression, and the scalar subquery case (Select<? extends Record1<T>>) well, that’s just convenience.

If users still need the bind variable, they can wrap it with DSL.val("value"). If Java did have untagged union types, however, the API would look more like this:

static Field<String> substring(
    F<String> string,
    F<? extends Number> startingPosition,
    F<? extends Number> length = null
) { ... }

Where F is the above union type. This would add a lot of convenience to a DSL like jOOQ because now, every permutation of argument types is supported. Alas, here we are, hand rolling the union type emulation through heavily overloaded methods, hoping IDE auto-completion doesn’t fail or become too slow (hello IntelliJ / Kotlin) πŸ˜…

4. Some SQL things are incredibly hard to abstract

Even after all those 12 years, there are still hard to solve bugs when trying to translate some SQL feature combinations to all 30 currently supported RDBMS.

I mean, try this on the almighty PostgreSQL, which isn’t even impressed by my lame attempts of creating not-everyday-SQL:

with t (a) as (
  with u (b) as (
    values (1)
  )
  select c
  from (
    with v (c) as (select b from u)
    select c from v
    union (
      select c from v
      union all
      select c from v
      order by c
    )
    order by c
  ) v
)
select a from t

It’s just an obfuscated way to write

values (1)

A few things here:

  • We’re nesting CTE in CTE declarations
  • We’re nesting CTE in derived tables
  • We’re nesting unions in derived tables
  • We’re nesting unions in union subqueries
  • We’re ordering derived tables
  • We’re ordering union subqueries

I mean, why not? But what about other dialects? Try converting this to alternative dialects on https://www.jooq.org/translate/, if you dare. Each one of those bullets (or several at a time), don’t work on some dialect. And not all of our translations work yet, for so many reasons.

These aren’t the most important bugs. They’re usually edge cases (e.g. the ORDER BY clauses are meaningless), but even then you want as much SQL to work on all of your dialects as possible, so we’re being kept busy, that’s for sure.

5. The pronunciation

Now it’s official (it always has been)

It’s pronounced dΚ’uːk (as in juke)

jOOQ is a recursive acronym that stands for jOOQ Object Oriented Querying. The “Object Oriented” stands for the API design, not how you’re supposed to use it. You’re supposed to use it in a functional programming style, duh. The jOOQ expression tree is following a composite pattern, if you will, and the SQL generation is implemented using a visitor pattern style approach, everything is encapsulated, etc. Just like you shouldn’t be tempted to say ess queue ell, you shouldn’t be tempted to say jay o o queue. It’s just dΚ’uːk for sequel. Hah!

6. RDBMS Bugs

jOOQ has helped discover a ton of RDBMS bugs maybe even more than the awesome https://github.com/sqlancer/sqlancer. When jOOQ integrates with a new dialect (e.g. EXASOL, recently), we discover a ton of bugs. See a list here: https://github.com/jOOQ/jOOQ/issues/1985, or for the recently supported Apache Ignite: https://github.com/jOOQ/jOOQ/issues/10551.

That’s because our integration tests are vast and cover all sorts of weird combinations of syntax that hardly anyone ever worries about such as the previous item 4. I always document each bug I find, either on the RDBMS issue tracker, if it’s public, or on Stack Overflow.

So, if you’re an RDBMS vendor and want us to test your SQL implementation, let us know! We’re for hire.

7. Mutability was a mistake

One of the biggest API design mistakes in jOOQ was mutability of the DSL, which can now hardly be removed. It’s even harder to change behaviour incompatibly than API. When API changes incompatibly, there are compilation errors. They’re a pain, but at least there aren’t any surprises.

Changing behaviour is a big no-go for a library. Here’s what I’m talking about:

SelectWhereStep<?> s =
ctx.select(T.A, T.B)
   .from(T);

// Dynamic SQL how you shouldn't do it:
if (something)
    s.where(T.C.eq(1));

if (somethingElse)
    s.where(T.D.eq(2));

Result<?> result = s.fetch();

Yes, the DSL API is mutable, which is why the above works. It shouldn’t work, and you shouldn’t use this, but here we are. Our own little sun.misc.Unsafe disaster. It’s too late. Everyone is using it already.

Not all DSL elements are mutable, for example, expressions are not:

Condition c = noCondition();

// Has no effect
if (something)
    c.and(T.C.eq(1));

if (somethingElse)
    c.and(T.D.eq(2));

The above doesn’t work. You’ll notice soon enough, and fix it accordingly:

Condition c = noCondition();

if (something)
    c = c.and(T.C.eq(1));

if (somethingElse)
    c = c.and(T.D.eq(2));

So, there’s still mutation, but only of your local variable, not any jOOQ objects. That’s how the entire DSL ought to work. Or even better, you could use a functional programming style to implement dynamic SQL.

In any case, it will be very hard to change this behaviour without breaking everything, in very subtle ways, because you can’t easily detect mutable API usage. In jOOQ 3.15, we’ve started annotating the DSL API with a @CheckReturnValue annotation, which is getting picked up by some tools and IDEs, see e.g. https://youtrack.jetbrains.com/issue/IDEA-265263.

Luckily, this annotation will also cause a warning when you use the DSL API in a mutable fashion, because you’re supposed to consume the return value of that where(T.C.eq(1)) call. Perhaps there is a way to change this, after all, though chances are slim. Probably not worth the damage caused.

Yeah, such is the fate of every “lightweight library”, once it reaches maturity. It’s almost impossible to change its fundamental flaws anymore

8. Source and behavioural compatibility is very important in jOOQ

Behavioural incompatibilities

Behavioural incompatibilities are an absolute no-go in almost all libraries / products.

Source incompatibilities

Source incompatibilities are sometimes inevitable, at least in major releases, if there is a very compelling reason. Since we haven’t released a major release in almost a decade, we treat our minor releases as major.

An example of such an incompatibility in jOOQ was when we removed the convenience overloads that accepted Condition|Field<Boolean>|Boolean. The three things are the same in jOOQ:

  • Condition is a SQL predicate (see item 2)
  • Field<Boolean> is a Condition wrapped as Field<Boolean>, which is quite nice in dialects with native support for the BOOLEAN type
  • Boolean is just a boolean bind variable, wrapped in DSL.val(boolean)

So, what’s wrong? The problem was the Boolean overload. It was intended for these kinds of usages:

// Turn off the entire query or subquery, dynamically
boolean featureFlag = ...;
.where(featureFlag)

So, rarely useful, only in edge cases. Again, what’s the problem? The problem was that users accidentally wrote this. All. The. Time.

.where(USER.NAME.equals(userName))

Can you spot the bug? It would not have happened if they had written USER.NAME.eq(userName), i.e. if they had used the XSLT style abbreviations.

Yes, they wrote equals() (as in Object::equals), not equal() (as in Field::equal). Simply typo. Still compiled. Chose the wrong overload. And looked almost correct. These would have been correct.

.where(USER.NAME.equal(userName))
.where(USER.NAME.eq(userName))

So, we deprecated, and then removed the overload, such that there is now a compilation error when using Object::equals. Seems a reasonable case for breaking source code, because that API’s usage was almost exclusively accidental.

How to test these things?

In order to make sure we don’t break behavioural or source compatibility, we have a ton (and I mean a ton) of tests. Behavioural compatibility is checked via unit and integration tests, making sure complex, and weird usages of jOOQ API continue to produce the same results on all of our supported RDBMS.

Source compatibility is checked via a ton of hand-written jOOQ API usage, which may be a bit more verbose than your average usage. For example, we don’t use var much in our own tests, though we heavily recommend you use it in your client code!

var result = ctx.select(T.A, multiset(..).as("fancy stuff")).fetch();

Instead, even in the most fancy statements using the new MULTISET operator, which can be quite heavy on the structural typing abuse of jOOQ, we always assign everything to explicitly typed variables that may look as threatening as this:

Result<Record4<
    String,                   // FILM.TITLE
    Result<Record2<
        String,               // ACTOR.FIRST_NAME
        String                // ACTOR.LAST_NAME
    >>,                       // "actors"
    Result<Record1<String>>,  // CATEGORY.NAME
    Result<Record4<
        String,               // CUSTOMER.FIRST_NAME
        String,               // CUSTOMER.LAST_NAME
        Result<Record2<
            LocalDateTime,    // PAYMENT.PAYMENT_DATE
            BigDecimal        // PAYMENT.AMOUNT
        >>, 
        BigDecimal            // "total"
    >>                        // "customers"
>> result = 
dsl.select(...)

Even if that type is not actually consumed or used in any way, e.g. when calling result.formatXML() on it. More tests = better. Any source incompatibility would immediately cause our tests to stop compiling, so we can be confident not to run into any surprises.

There are always weird things, nonetheless. It’s hard to achieve perfection. An example is this issue where rawtype compatibility was overlooked. I wonder if anyone actually makes sure their generics can be used safely and compatibly with raw types? Seems very hard in jOOQ’s case…

9. Binary compatibility is almost impossible to provide in jOOQ

But binary incompatibilities? In a DSL like jOOQ’s they’re probably completely impossible. An example is when we introduced support for the Teradata QUALIFY clause in jOOQ 3.12. Here’s a blog post explaining how our DSL works. Before supporting QUALIFY, the jOOQ WINDOW clause, supported via the SelectWindowStep had several window() method overloads like this:

// "extends SelectOrderByStep", because is window() methods are
// obviously optional. It's a hardly used feature
interface SelectWindowStep<R extends Record> 
extends SelectOrderByStep<R> {
    SelectOrderByStep<R> window(WindowDefinition... w);
}

Example usage:

select(T.A, count().over(w))
.from(T)
.window(w)
.orderBy(T.A)
.fetch();

Now, the QUALIFY clause comes after WINDOW (Teradata doesn’t support WINDOW, but if they did, they’d have to prepend it to QUALIFY, because WINDOW declares named window definitions, and QUALIFY consumes them, though you never know with SQL)

// "extends SelectQualifyStep" is a compatible change
interface SelectWindowStep<R extends Record> 
extends SelectQualifyStep<R> {

    // But these changes are not
    SelectQualifyStep<R> window(WindowDefinition... w);
}

While the JVM allows overloading by return type (and that feature is used to implement generics and covariant overloads since Java 5), the Java language does not allow this. There’s no way a new version of this API can maintain the old byte code, at least not in Java. Kotlin supports such things, i.e. the ability to emit (synthetic?) methods in byte code that cannot be called from source code directly, purely for backwards compatibility reasons.

So, if you’re upgrading from jOOQ 3.11 to 3.12, and you were using the WINDOW clause, tough luck. You have to recompile your client code, otherwise your friend NoSuchMethodError will have a word at runtime only (because at compile time, everything still compiles).

I guess that binary compatibility is not such a huge problem anymore in most cases. We run CI/CD jobs all the time, and recompile everything all the time for various reasons. It’s unlikely that you keep your own internal libraries binary compatible for other teams to consume. Nevertheless, it would be nice if it weren’t a problem. Ultimately, pragmatism dictates that we can’t offer this service in jOOQ (such as the JDK does), and try really hard not to break binary compatibility at least in patch releases, but minor releases do not have any such guarantee.

10. The Logo

The current logo has been designed when jOOQ went commercial

jOOQ is now jOOQβ„’

Fun fact, I designed both of these logos myself. My wife never liked the old one. It looked like some bloody samurai to her with all bloody eyes πŸ˜…

The new one went through several iterations. All weird and colourful. When finally, a friend who is working in design told me a few very simple tricks:

  • Black and white can be applied to all media (screens, print, etc.) very easily, compared to coloured logos.
  • It can even be inverted to white and black.
  • Squares are easier to manage in icons, headers, thumbnails, etc. than rectangles.

I took this a step further and made the entire logo quadratic, including the font, such that it doesn’t even require an SVG format to scale. It’s essentially a 20×20 bitmap. Can’t go more low budget yet effective than this! 😁

Formatting ASCII Charts With jOOQ

A very little known feature in jOOQ is the Formattable.formatChart() capability, which allows for formatting any jOOQ result as an ASCII chart. This can be useful for quick plotting of results in your console application.

Assuming you have a result set of this form (which is what you’re getting when you call result.format() or just result.toString())

+---+---+---+---+
|c  | v1| v2| v3|
+---+---+---+---+
|a  |  1|  2|  3|
|b  |  2|  1|  1|
|c  |  4|  0|  1|
+---+---+---+---+

This result can be produced by any query, or you can construct it without executing a query like this:

Field<String>  c  = field("c", VARCHAR);
Field<Integer> v1 = field("v1", INTEGER);
Field<Integer> v2 = field("v2", INTEGER);
Field<Integer> v3 = field("v3", INTEGER);

Result<Record4<String, Integer, Integer, Integer>> result = 
    create.newResult(c, v1, v2, v3); 
                      
result.add(create.newRecord(c, v1, v2, v3).values("a", 1, 2, 3));
result.add(create.newRecord(c, v1, v2, v3).values("b", 2, 1, 1));
result.add(create.newRecord(c, v1, v2, v3).values("c", 4, 0, 1));

Then, calling result.formatChart() will produce a nice ASCII chart like this, by default:

4.00|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.86|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.73|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.59|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.45|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.32|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.18|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.05|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.91|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.77|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.64|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.50|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.36|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.23|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.09|                                                  β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.95|                         β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.82|                         β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.68|                         β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.55|                         β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.41|                         β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.27|                         β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.14|                         β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.00|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
----+---------------------------------------------------------------------------
    |            a                        b                        c            

It includes the first column as the category label on the x-axis, and the second column as the value to be plotted on the y-axis. You can tweak all sorts of configuration, including height and width:

result.formatChart(new ChartFormat().dimensions(6, 20));

To get this much smaller chart:

4.00|          β–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.00|          β–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.00|     β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.00|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
----+---------------
    |  a    b    c  

To include the values of the other value columns in a stacked chart (which is the default) write:

result.formatChart(new ChartFormat()
    .dimensions(40, 8)
    .values(1, 2, 3)
);

Producing:

6.00|β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’                       
5.00|β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’            β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’
4.00|β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–’β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
3.00|β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
2.00|β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–“β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
1.00|β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ
----+-----------------------------------
    |     a           b          c      

If those default ASCII characters from the sweet old 90s MS-DOS and BBS times don’t align well with your font (e.g. as on this blog), you can switch them like this:

result.formatChart(new ChartFormat()
    .dimensions(40, 8)
    .values(1, 2, 3)
    .shades('@', 'o', '.')
);

And now you’re getting:

6.00|............                       
5.00|............            ...........
4.00|........................@@@@@@@@@@@
3.00|oooooooooooooooooooooooo@@@@@@@@@@@
2.00|oooooooooooo@@@@@@@@@@@@@@@@@@@@@@@
1.00|@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
----+-----------------------------------
    |     a           b          c      

Prefer displaying 100% charts? No problem

result.formatChart(new ChartFormat()
    .dimensions(40, 8)
    .values(1, 2, 3)
    .shades('@', 'o', '.')
    .display(Display.HUNDRED_PERCENT_STACKED)
);

And now, the result is:

100.00%|................................
 80.00%|......................@@@@@@@@@@
 60.00%|...........ooooooooooo@@@@@@@@@@
 40.00%|ooooooooooo@@@@@@@@@@@@@@@@@@@@@
 20.00%|ooooooooooo@@@@@@@@@@@@@@@@@@@@@
  0.00%|@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-------+--------------------------------
       |     a          b         c     

Who needs MS Excel? No one, if you have jOOQ!