jOOQ 3.11 Released With 4 New Databases, Implicit Joins, Diagnostics, and Much More

Today, jOOQ 3.11 has been released with support for 4 new databases, implicit joins, diagnostics, and much more

New Databases Supported

At last, 4 new SQL dialects have been added to jOOQ! These are:

jOOQ Professional Edition

  • Aurora MySQL Edition
  • Aurora PostgreSQL Edition
  • Azure SQL Data Warehouse

jOOQ Enterprise Edition

  • Teradata

Implicit Joins

One of the really cool features in ORMs like Hibernate, Doctrine, and others, is
the capability of using a relationship graph notation to access another entity’s
columns through what is often called “implicit joins”.

Instead of explicitly joining a to-one relationship to access its columns:

SELECT author.first_name, author.last_name, book.title
FROM book
JOIN author ON book.author_id = author.id

We would like to be able to access those columns directly, using this notation:

SELECT book.author.first_name, book.author.last_name, book.title
FROM book

The join is implied and should be added implicitly. jOOQ now allows for this to
happen when you use the code generator:

ctx.select(BOOK.author().FIRST_NAME, BOOK.author().LAST_NAME, BOOK.TITLE)
   .from(BOOK)
   .fetch();

When rendering this query, the implicit join graph will be calculated on the fly
and added behind the scenes to the BOOK table. This works for queries of
arbitrary complexity and on any level of nested SELECT.

More details in this blog post:
https://blog.jooq.org/2018/02/20/type-safe-implicit-join-through-path-navigation-in-jooq-3-11/

DiagnosticsListener SPI

A new DiagnosticsListener SPI has been added to jOOQ:
https://github.com/jOOQ/jOOQ/issues/5960

The purpose of this SPI is to sanitise your SQL language, JDBC and jOOQ API
usage. Listeners can listen to events such as:

  • duplicateStatements (similar SQL is executed, bind variables should be used)
  • repeatedStatements (identical SQL is executed, should be batched or rewritten)
  • tooManyColumnsFetched (not all projected columns were needed)
  • tooManyRowsFetched (not all fetched rows were needed)

The great thing about this SPI is that it can be exposed to clients through the
JDBC API, in case of which the diagnostics feature can reverse engineer your
JDBC or even JPA generated SQL. Ever wanted to detect N+1 queries from
Hibernate? Pass those Hibernate-generated queries through this SPI.

Want to find missing bind variables leading to cursor cache contention or SQLi?
Let jOOQ find similar SQL statements and report them. E.g.

  • SELECT name FROM person WHERE id = 1
  • SELECT name FROM person WHERE id = 2

Or also:

  • SELECT name FROM person WHERE id IN (?, ?)
  • SELECT name FROM person WHERE id IN (?, ?, ?)

Anonymous blocks

Many databases support anonymous blocks to run several statements in a single
block scope. For example, Oracle:

DECLARE
  l_var NUMBER(10);
BEGIN
  l_var := 10;
  dbms_output.put_line(l_var);
END;

jOOQ now supports the new org.jooq.Block API to allow for wrapping DDL and DML
statements in such a block. This is a first step towards a future jOOQ providing
support for:

  • Abstractions over procedural languages
  • CREATE PROCEDURE and CREATE FUNCTION statements
  • Trigger support
  • And much more

Parser

jOOQ’s parser support is an ongoing effort. This release has added support for
a lot of new SQL clauses and functions from various vendors and in various DDL
and DML statements.

The parser is now also exposed through a public website and API, where SQL can
be translated from one dialect to another:
https://www.jooq.org/translate

This website will help further drive jOOQ API development by helping to find
missing functionality that is used in real-world SQL.

Another way to access this API is through the new org.jooq.ParserCLI command
line tool. For example, run:

$ java -cp jooq-3.11.0.jar org.jooq.ParserCLI -f -t ORACLE -s "SELECT * FROM (VALUES(1),(2)) AS t(a)"

To get:

select *
from (
  (
    select null a
    from dual
    where 1 = 0
  )
  union all (
    select *
    from (
      (
        select 1
        from dual
      )
      union all (
        select 2
        from dual
      )
    ) t
  )
) t;

Formal Java 10 Support

jOOQ 3.11 is the first release that is formally integration tested with Java 10.
To use jOOQ with Java 10, use the Java 8 distribution which has not yet been
modularised, but contains Automatic-Module-Name specification to be forward
compatible with future, modularised jOOQ distributions.

Additionally, package names between jOOQ, jOOQ-meta, and jOOQ-codegen have been
cleaned up to prevent duplicate package names, and the JAXB dependency has been
added explicitly to the various artefacts.

Other great improvements

  • Finally, asterisks (SELECT * or SELECT t.*) are formally supported in the API.
  • Collations can now be specified on a variety of syntax elements
  • The org.jooq.Comment type has been added, and DDL statements for it
  • The DefaultBinding implementation has been rewritten for better peformance
  • Several performance improvements in jOOQ’s internals
  • Many more DDL statements are supported including GRANT and REVOKE
  • Support for the EXPLAIN statement
  • FETCH n PERCENT ROWS and TOP n PERCENT clauses are supported
  • Better org.jooq.Name and org.jooq.Named API for identifier handling
  • Support for PostgreSQL 10
  • Support for SQL Server 2017
  • Support for DB2 11
  • Upgraded MariaDB support for window functions, inv dist functions, WITH
  • jOOU dependency updated to 0.9.3
  • jOOR dependency updated to 0.9.8
  • Server output (e.g. DBMS_OUTPUT) can now be fetched automatically, by jOOQ
  • Code generation support for PL/SQL TABLE types
  • SQL Keywords Can Now Be Rendered In Pascal Style If You Must
  • Emulate PostgreSQL’s ON CONFLICT clause using MERGE

The complete list can be seen here:
https://www.jooq.org/notes/?version=3.11

Truth First, or Why You Should Mostly Implement Database First Designs

In this much overdue article, I will explain why I think that in almost all cases, you should implement a “database first” design in your application’s data models, rather than a “Java first” design (or whatever your client language is), the latter approach leading to a long road of pain and suffering, once your project grows.

This article is inspired by a recent Stack Overflow question.

Interesting reddit discussions on /r/java and /r/programming.

Code generation

To my surprise, a small group of first time jOOQ users seem to be appalled by the fact that jOOQ heavily relies on source code generation. No one keeps you from using jOOQ the way you want and you don’t have to use code generation, but the default way to use jOOQ according to the manual is to start with a (legacy) database schema, reverse engineer that using jOOQ’s code generator to get a bunch of classes representing your tables, and then to write type safe queries against those tables:

for (Record2<String, String> record : DSL.using(configuration)
//   ^^^^^^^^^^^^^^^^^^^^^^^ Type information derived from the 
//   generated code referenced from the below SELECT clause

       .select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
//           vvvvv ^^^^^^^^^^^^  ^^^^^^^^^^^^^^^ Generated names
       .from(ACTOR)
       .orderBy(1, 2)) {
    // ...
}

The code is generated either manually outside of the build, or automatically with every build. For instance, such a re-generation could follow immediately after a Flyway database migration, which can also be run either manually or automatically.

Source code generation

There are different philosophies, advantages, and disadvantages regarding these manual/automatic approaches, which I don’t want to discuss in this article. But essentially, the point of generated code is that it provides a Java representation of something that we take for granted (a “truth”) either within or outside of our system. In a way, compilers do the same thing when they generate byte code, machine code, or some other type of source code from the original sources – we get a representation of our “truth” in a different language, for whatever reason.

There are many such code generators out there. For instance, XJC can generate Java code from XSD or WSDL files. The principle is always the same:

  • There is some truth (internal or external), like a specification, data model, etc.
  • We need a local representation of that truth in our programming language

And it almost always makes sense to generate that latter, to avoid redundancy.

Type providers and annotation processing

Noteworthy: Another, more modern approach to jOOQ’s particular code generation use-case would be Type Providers, as implemented by F#, in case of which the code is generated by the compiler while compiling. It never really exists in its source form. A similar (but less sophisticated) tool in Java are annotation processors, e.g. Lombok.

In a way, this does the same thing except:

  • You don’t see the generated code (perhaps that’s less appalling to some?)
  • You must ensure the types can be provided, i.e. the “truth” must always be available. Easy in the case of Lombok, which annotates the “truth”. A bit more difficult with database models, which rely on an always available live connection.

What’s the problem with code generation?

Apart from the tricky question whether to trigger code generation manually or automatically, some people seem to think that code must not be generated at all. The reason I hear the most is the idea that it is difficult to set up in a build pipeline. And yes, that is true. There is extra infrastructure overhead. Especially if you’re new to a certain product (like jOOQ, or JAXB, or Hibernate, etc.), setting up an environment takes time you would rather spend learning the API itself and getting value out of it.

If the overhead of learning how the code generator works is too high, then indeed, the API failed to make the code generator easy to use (and later on, to customise). That should be a high priority for any such API. But that’s the only argument against code generation. Other than that, it makes absolutely no sense at all to hand-write the local representation of the internal or external truth.

Many people argue that they don’t have time for that stuff. They need to ship their MVPs. They can finalise their build pipelines later. I say:

“But Hibernate / JPA makes coding Java first easy”

Yes that’s true. And it’s both a bliss and a curse for Hibernate and its users. In Hibernate, you can just write a couple of entities, such as:

@Entity
class Book {
  @Id
  int id;
  String title;
}

And you’re almost set. Let Hibernate generate the boring “details” of how to define this entity in your SQL dialect’s DDL:

CREATE TABLE book (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title VARCHAR(50),

  CONSTRAINT pk_book PRIMARY KEY (id)
);

CREATE INDEX i_book_title ON book (title);

… and start running the application. That’s really cool to get started quickly and to try out things.

But, huh, wait. I cheated.

  • Will Hibernate really apply that named primary key definition?
  • Will it create the index on TITLE, which I know we’ll need?
  • Will it add an identity specification?

Probably not. While you’re developing your greenfield project, it is convenient to always throw away your entire database and re-generate it from scratch, once you’ve added the additional annotations. So, the Book entity would eventually look like this:

@Entity
@Table(name = "book", indexes = {
  @Index(name = "i_book_title", columnList = "title")
})
class Book {
  @Id
  @GeneratedValue(strategy = IDENTITY)
  int id;
  String title;
}

Cool. Re-generate. Again, this makes it really easy to get started.

But you’ll pay the price later on

At some point, you go to production. And that’s when this model no longer works. Because

Once you go live, you can no longer throw away your database, as your database has become legacy.

From now on, you have to write DDL migration scripts, e.g. using Flyway. And then, what happens to your entities? You can either adapt them manually (so you double the work), or have Hibernate re-generate them for you (how big are your chances of the generation matching your expectations?) You can only lose.

Because once you go to production, you need hotfixes. And those have to go live fast. And since you didn’t prepare for pipelining your migrations to production smoothly, you’ll patch things wildly. And then you run out of time to do it right™. And you’ll blame Hibernate, because it’s always someone else’s fault…

Instead, you could have done things entirely differently from the beginning. Like using those round wheels.

Go Database First

The real “truth” of your database schema, and the “sovereignty” over it, resides with your database. The database is the only place where the schema is defined, and all clients have a copy of the database schema, not vice versa. The data is in your database, not in your client, so it makes perfect sense to enforce the schema and its integrity in the database, right where the data is.

This is old wisdom, nothing new. Primary and unique keys are good. Foreign keys are good. Check constraints are good. Assertions (when they’re finally implemented) are good.

And that’s not where it ends. For instance, if you’re using Oracle, you may want to specify:

  • In what tablespace your table resides
  • What PCTFREE value it has
  • What the cache size of your sequence (behind the identity) is

Maybe, all of this doesn’t matter in small systems, but you don’t have to go “big data” before you can profit from vendor-specific storage optimisations as the above. None of the ORMs I’ve ever seen (including jOOQ) will allow you to use the full set of DDL options that you may want to use on your database. ORMs offer some tools to help you write DDL.

But ultimately, a well-designed schema is hand written in DDL. All generated DDL is only an approximation of that.

What about the client model?

As mentioned before, you will need a copy of your database schema in your client, a client representation. Needless to say that this client representation needs to be in-sync with the real model. How to best do that? By using a code generator.

All databases expose their meta information through SQL. Here’s how to get all tables from your database in various SQL dialects:

-- H2, HSQLDB, MySQL, PostgreSQL, SQL Server
SELECT table_schema, table_name
FROM information_schema.tables

-- DB2
SELECT tabschema, tabname
FROM syscat.tables

-- Oracle
SELECT owner, table_name
FROM all_tables

-- SQLite
SELECT name
FROM sqlite_master

-- Teradata
SELECT databasename, tablename
FROM dbc.tables

These queries (or similar ones, e.g. depending on whether views, materialised views, table valued functions should also be considered) are also run by JDBC’s DatabaseMetaData.getTables() call, or by the jOOQ-meta module.

From the result of such queries, it’s relatively easy to generate any client representation of your database model, regardless what your client technology is.

  • If you’re using JDBC or Spring, you can create a bunch of String constants
  • If you’re using JPA, you can generate the entities themselves
  • If you’re using jOOQ, you can generate the jOOQ meta model

Depending on the amount of features your client API offers (e.g. jOOQ or JPA), the generated meta model can be really rich and complete. Consider, for instance, jOOQ 3.11’s implicit join feature, which relies on generated meta information about the foreign key relationships between your tables.

Now, any database increment will automatically lead to updated client code. For instance, imagine:

ALTER TABLE book RENAME COLUMN title TO book_title;

Would you really want to do this work twice? No way. Just commit the DDL, run it through your build pipeline, and have an updated entity:

@Entity
@Table(name = "book", indexes = {

  // Would you have thought of this?
  @Index(name = "i_book_title", columnList = "book_title")
})
class Book {
  @Id
  @GeneratedValue(strategy = IDENTITY)
  int id;

  @Column("book_title")
  String bookTitle;
}

Or an updated jOOQ class. Plus: Your client code might no longer compile, which can be a good thing! Most DDL changes are also semantic changes, not just syntactic ones. So, it’s great to be able to see in compiled client source code, what code is (or may be) affected by your database increment.

A single truth

Regardless what technology you’re using, there’s always one model that contains the single truth for a subsystem – or at least, we should aim for this goal and avoid the enterprisey mess where “truth” is everywhere and nowhere. It just makes everything much simpler. If you exchange XML files with some other system, you’re going to use XSD. Like jOOQ’s INFORMATION_SCHEMA meta model in XML form:
https://www.jooq.org/xsd/jooq-meta-3.10.0.xsd

  • XSD is well understood
  • XSD specifies XML content very well, and allows for validation in all client languages
  • XSD can be versioned easily, and evolved backwards compatibly
  • XSD can be translated to Java code using XJC

The last bullet is important. When communicating with an external system through XML messages, we want to be sure our messages are valid. That’s really really easy to do with JAXB, XJC, and XSD. It would be outright nuts to think that a Java-first approach where we design our messages as Java objects could somehow be reasonably mapped to XML for someone else to consume. That generated XML would be of very poor quality, undocumented, and hard to evolve. If there’s an SLA on such an interface, we’d be screwed.

Frankly, that’s what happens to JSON APIs all the time, but that’s another story, another rant…

Databases: Same thing

When you’re using databases, it’s the same thing. The database owns its data and it should be the master of the schema. All modifications to the schema should be implemented using DDL directly, to update the single truth.

Once that truth is updated, all clients need to update their copies of the model as well. Some clients may be written in Java, using either (or both of) jOOQ and Hibernate, or JDBC. Other clients may be written in Perl (good luck to them). Even other clients may be written in C#. It doesn’t matter. The main model is in the database. ORM-generated models are of poor quality, not well documented, and hard to evolve.

So, don’t do it. And, don’t do it from the very beginning. Instead, go database first. Build a deployment pipeline that can be automated. Include code generators to copy your database model back into the clients. And stop worrying about code generation. It’s a good thing. You’ll be productive. All it takes is a bit of initial effort to set it up, and you’ll get years of improved productivity for the rest of your project.

Thank me later.

Clarification

Just to be sure: This article in no way asserts that your database model should be imposed on your entire system (e.g. your domain, your business logic, etc. etc.). The claim I made here is that client code interacting with the database should act upon the database model, and not have its own first class model of the database instead. This logic typically resides in the data access layer of your client.

In 2-tier architectures, which still have their place sometimes, that may be the only model of your system. In most systems, however, I consider the data access layer a “subsystem” that encapsulates the database model. So, there.

Exceptions

There are always exceptions, and I promised that the database first and code generation approach may not always be the right choice. These exceptions are (probably not exhaustive):

  • When the schema is unknown and must be discovered. E.g. you’re a tool vendor helping users navigate any schema. Duh… No code generation. But still database first.
  • When the schema needs to be generated on the fly for some task. This sounds a lot like a more or less sophisticated version of the entity attribute value pattern, i.e. you don’t really have a well-defined schema. In that case, it’s often not even sure if an RDBMS will be the right choice.

The nature of exceptions is that they’re exceptional. In the majority of RDBMS usage, the schema is known in advance, placed inside the RDBMS as the single source of “truth”, and clients will have derived copies from it – ideally generated using a code generator.

Mocking JDBC Using a Set of SQL String / Result Pairs

In a previous blog post, I’ve shown how the programmatic MockDataProvider can be used to mock the entire JDBC API through a single functional interface:

// context contains the SQL string and bind variables, etc.
MockDataProvider provider = context -> {

    // This defines the update counts, result sets, etc.
    // depending on the context above.
    return new MockResult[] { ... }
};

Writing the provider manually can be tedious in some cases, especially when a few static SQL strings need to be mocked and constant result sets would be OK. In that case, the MockFileDatabase is a convenient implementation that is based on a text file (or SQL string), which contains a set of SQL string / result pairs of the form:

  • SQL string
  • Result set
  • Update count

Assuming this is the content of the mocking.txt file:

select first_name, last_name from actor;
> first_name last_name
> ---------- ---------
> GINA       DEGENERES
> WALTER     TORN     
> MARY       KEITEL   
@ rows: 3

select first_name, last_name, count(*)
from actor
join film_actor using (actor_id)
group by actor_id, first_name, last_name
order by count(*) desc;
> first_name last_name count
> ---------- --------- -----
> GINA       DEGENERES 42
> WALTER     TORN      41
> MARY       KEITEL    40
@ rows: 3

We can then easily load that file into a class and run queries against it:

import static java.lang.System.out;
import java.sql.*;
import org.jooq.tools.jdbc.*;

public class Mocking {
    public static void main(String[] args) throws Exception {
        MockDataProvider db = new MockFileDatabase(
            Mocking.class.getResourceAsStream("/mocking.txt");

        try (Connection c = new MockConnection(db));
            Statement s = c.createStatement()) {

            out.println("Actors:");
            out.println("-------");
            try (ResultSet rs = s.executeQuery(
                "select first_name, last_name from actor")) {
                while (rs.next())
                    out.println(rs.getString(1) 
                        + " " + rs.getString(2));
            }

            out.println();
            out.println("Actors and their films:");
            out.println("-----------------------");
            try (ResultSet rs = s.executeQuery(
                "select first_name, last_name, count(*)\n" +
                "from actor\n" +
                "join film_actor using (actor_id)\n" +
                "group by actor_id, first_name, last_name\n" +
                "order by count(*) desc")) {
                while (rs.next())
                    out.println(rs.getString(1) 
                        + " " + rs.getString(2) 
                        + " (" + rs.getInt(3) + ")");
            }
        }
    }
}

The above will print:

Actors:
-------
GINA DEGENERES
WALTER TORN
MARY KEITEL

Actors and their films:
-----------------------
GINA DEGENERES (42)
WALTER TORN (41)
MARY KEITEL (40)

Notice how we’re not really connecting to any database at all, but simply running queries against our mock database file, which contains a hard-coded set of SQL string / result pairs. While this obviously shouldn’t be used to implement / mock a full-fledged database, it is certainly very useful to intercept only a few queries and return hard-coded results to any JDBC based caller – regardless if they’re using jOOQ, Hibernate, or vanilla JDBC as in the above API.

Top 10 SQL Dialect Emulations Implemented in jOOQ

The SQL standard is a nice thing. But it’s a standard. And as such, while it may provide good guidelines on how to implement some SQL features, most dialects deviate from it in one way or another (sometimes drastically, cheers, MySQL).

But that doesn’t have to be a bad thing. Innovation is not driven by standards, but by individual vendors’ attempts to tackle a problem from a different perspective. And then, sometimes, the innovation becomes the standard. One example for this is Oracle’s very very interesting MATCH_RECOGNIZE feature, on which Markus Winand did an excellent presentation.

Other functionality is not standardised, such as Oracle/SQL Server’s PIVOT and UNPIVOT.

In many cases, vendor-specific functionality can be translated to equivalent standard SQL, or to other vendor-specific SQL. That’s one of jOOQ’s core features: The “standardisation” of currently 21 SQL dialects into a single Java API. Since jOOQ 3.9, the Java API can also be hidden behind a parser, which makes visualising the differences much simpler. If you want to play around with the following examples, do visit https://www.jooq.org/translate to see our online SQL translator in action!

Here are Top 10 SQL Dialect Emulations Implemented in jOOQ:

1. Empty FROM clause

The SQL standard doesn’t allow this, but many databases do. You have to specify a FROM clause in at least these databases

  • Access
  • CUBRID
  • DB2
  • Derby
  • Firebird
  • HANA
  • HSQLDB
  • Informix
  • Ingres
  • MariaDB
  • MySQL (not always)
  • Oracle
  • Sybase SQL Anywhere

These ones don’t really need a FROM clause:

  • H2
  • PostgreSQL
  • Redshift
  • SQL Server
  • SQLite
  • Sybase ASE
  • Vertica

An example of such a query could be the following query that retrieves the server timestamp:

SELECT current_timestamp

In Oracle, you’d have to add the DUAL table:

SELECT current_timestamp FROM dual

There are other possibilities to emulate this in other databases. If you want to see how jOOQ does it, again, either visit our online SQL translator, or run this little code snippet yourself, locally (be sure to report any issues you may find, greatly appreciated!):

import org.jooq.Query;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

public class Translate {
    public static void main(String[] args) {
        Query query = DSL.using(SQLDialect.DEFAULT)
            .parser()
            .parseQuery("SELECT current_timestamp");

        for (SQLDialect family : SQLDialect.families()) {
            System.out.println(String.format(
                "%1$-13s: %2$s", family, 
                DSL.using(family).render(query)
            ));
    }
}

So, running the above snippet will yield (and observe, free current_timestamp translation:

ACCESS    : select now() from (select count(*) dual from MSysResources) as dual
ASE       : select current_bigdatetime()
CUBRID    : select current_timestamp() from "db_root"
DB2       : select current_timestamp from "SYSIBM"."DUAL"
DERBY     : select current_timestamp from "SYSIBM"."SYSDUMMY1"
FIREBIRD  : select current_timestamp from "RDB$DATABASE"
H2        : select current_timestamp()
HANA      : select current_timestamp from "SYS"."DUMMY"
HSQLDB    : select current_timestamp from (
              select 1 as dual from information_schema.system_users limit 1
            ) as dual
INFORMIX  : select current from (
              select 1 as dual from systables where tabid = 1
            ) as dual
INGRES    : select current_timestamp from (select 1 as dual) as dual
MARIADB   : select current_timestamp() from dual
MYSQL     : select current_timestamp() from dual
ORACLE    : select current_timestamp from dual
POSTGRES  : select current_timestamp
REDSHIFT  : select current_timestamp
SQLITE    : select current_timestamp
SQLSERVER : select current_timestamp
SYBASE    : select current timestamp from [SYS].[DUMMY]
VERTICA   : select current_timestamp

See also the jOOQ manual’s section about the dual table.

2. LIMIT .. OFFSET

First off, don’t use OFFSET.

Since you didn’t listen and you’re still using OFFSET, let’s discuss how to emulate it (and the much more useful LIMIT in many database dialects.

The SQL:2016 standard syntax is:

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
  FETCH { FIRST | NEXT } [ <fetch first quantity> ]
    { ROW | ROWS } { ONLY | WITH TIES }

<fetch first quantity> ::=
    <fetch first row count>
  | <fetch first percentage>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

So, there are a few interesting features:

  • The OFFSET (which is the least interesting)
  • The number of rows to FETCH
  • Whether tied rows should be fetched, too (TIES). This will be covered in the next section
  • Whether the number of rows is really a PERCENTage

Oracle currently is the only database (I’m aware of) that does it all and with standard syntax.

FETCH without OFFSET

For instance, when querying the Sakila database, we can get the TOP 3 longest films:

SELECT film_id, title, length
FROM film 
ORDER BY length DESC 
FETCH NEXT 3 ROWS ONLY

Yielding:

FILM_ID  TITLE           LENGTH
-------------------------------
212      DARN FORRESTER  185
182      CONTROL ANTHEM  185
141      CHICAGO NORTH   185

(In the next section we’ll look at the WITH TIES clause to find the other films of length 185)

But what do these queries look like in other databases? Here’s the translation of the ROWS ONLY query, according to jOOQ:

ACCESS    : select top 3 film_id, title, length from film order by length desc
ASE       : select top 3 film_id, title, length from film order by length desc
CUBRID    : select film_id, title, length from film 
              order by length desc limit 0, 3
DB2       : select film_id, title, length from film 
              order by length desc fetch first 3 rows only
DERBY     : select film_id, title, length from film 
              order by length desc offset 0 rows fetch next 3 rows only
FIREBIRD  : select film_id, title, length from film 
              order by length desc rows (0 + 1) to (0 + 3)
H2        : select film_id, title, length from film order by length desc limit 3
HANA      : select film_id, title, length from film order by length desc limit 3
HSQLDB    : select film_id, title, length from film order by length desc limit 3
INFORMIX  : select first 3 film_id, title, length from film order by length desc
INGRES    : select film_id, title, length from film 
              order by length desc offset 0 fetch first 3 rows only
MARIADB   : select film_id, title, length from film order by length desc limit 3
MYSQL     : select film_id, title, length from film order by length desc limit 3
ORACLE    : select film_id, title, length from film 
              order by length desc fetch next 3 rows only
POSTGRES  : select film_id, title, length from film order by length desc limit 3
REDSHIFT  : select film_id, title, length from film order by length desc limit 3
SQLITE    : select film_id, title, length from film order by length desc limit 3
SQLSERVER : select top 3 film_id, title, length from film order by length desc
SYBASE    : select top 3 film_id, title, length from film order by length desc
VERTICA   : select film_id, title, length from film order by length desc limit 3

So, there are essentially 3 families:

  • The standard family using FETCH, including DB2 (doesn’t support OFFSET), Derby, Ingres (although missing a keyword), Oracle
  • The MySQL family using LIMIT, including CUBRID, H2, HANA, HSQLDB, MariaDB, MySQL, PostgreSQL, Redshift, SQLite, Vertica
  • The T-SQL family using TOP, inculding Access, ASE, SQL Server, Sybase

There are also exotic syntaxes implemented by Firebird and Informix.

FETCH with OFFSET

You’ll find tons of blog posts on the web on how to emulate OFFSET .. LIMIT, including jOOQ’s manual. Things do get a bit more tricky when adding an offset, as can be seen here:

CUBRID    : select film_id, title, length from film
              order by length desc limit 3, 3
DB2       : select "v0" film_id, "v1" title, "v2" length from (
              select 
                film_id "v0", title "v1", length "v2", 
                row_number() over (order by length desc) "rn" 
              from film order by "v2" desc
            ) "x" 
            where "rn" > 3 and "rn" <= (3 + 3) 
            order by "rn"
DERBY     : select film_id, title, length from film 
              order by length desc offset 3 rows fetch next 3 rows only
FIREBIRD  : select film_id, title, length from film 
              order by length desc rows (3 + 1) to (3 + 3)
H2        : select film_id, title, length from film 
              order by length desc limit 3 offset 3
HANA      : select film_id, title, length from film 
              order by length desc limit 3 offset 3
HSQLDB    : select film_id, title, length from film 
              order by length desc limit 3 offset 3
INFORMIX  : select skip 3 first 3 film_id, title, length from film 
              order by length desc
INGRES    : select film_id, title, length from film 
              order by length desc offset 3 fetch first 3 rows only
MARIADB   : select film_id, title, length from film 
              order by length desc limit 3 offset 3
MYSQL     : select film_id, title, length from film 
              order by length desc limit 3 offset 3
ORACLE    : select film_id, title, length from film 
              order by length desc offset 3 rows fetch next 3 rows only
POSTGRES  : select film_id, title, length from film 
              order by length desc limit 3 offset 3
REDSHIFT  : select film_id, title, length from film 
              order by length desc limit 3 offset 3
SQLITE    : select film_id, title, length from film 
              order by length desc limit 3 offset 3
SQLSERVER : select film_id, title, length from film 
              order by length desc offset 3 rows fetch next 3 rows only
SYBASE    : select top 3 start at 4 film_id, title, length from film 
              order by length desc
VERTICA   : select film_id, title, length from film 
              order by length desc limit 3 offset 3

Interesting to note:

  • MS Access, and Sybase ASE do not support offsets at all (maybe a good thing).
  • The more recent versions of SQL Server support the SQL standard OFFSET .. FETCH clause (although OFFSET, unfortunately, is mandatory), which is great. Older versions can emulate OFFSET just like DB2 below
  • Sybase SQL Anywhere enhanced the T-SQL TOP syntax to something intuitive: TOP .. START AT. Why not?
  • DB2 doesn’t support the syntax, and we have to emulate it using ROW_NUMBER() window functions:
    select "v0" film_id, "v1" title, "v2" length from (
      select 
        film_id "v0", title "v1", length "v2", 
        row_number() over (order by length desc) "rn" 
      from film order by "v2" desc
    ) "x" 
    where "rn" > 3 and "rn" &lt;= (3 + 3) 
    order by "rn"
    

    Notice how, over the years, we’ve learned to do it right and prevent all sorts of side-effects from wrong emulations:

    • In the nested query, all columns have to be renamed to some enumerated column names to prevent problems from possibly duplicate column names in the user SQL query. It is perfectly OK for top-level SELECT statements to have duplicate / ambiguous column names, but not for subqueries
    • The top level SELECT statement should not project the auxiliary ROW_NUMBER() value. While this might not be causing trouble in ordinary queries, it is certainly causing trouble in subqueries. Imagine emulating something like:
      WHERE x IN (
        SELECT id
        FROM table
        OFFSET 1 ROW
        FETCH NEXT ROW ONLY
      )
      

      In this case, we must be very careful that the subquery continues to project only exactly one column.

3. WITH TIES

The previous approach to getting TOP 3 films is dangerous, because the ranking is rather random. There are other films of length 185, and they didn’t make it into the TOP 3. We could add another ordering criteria to make the ordering deterministic (e.g. FILM_ID), or we can use WITH TIES to get 3 films and all the tied films, too.

The query is now:

SELECT film_id, title, length
FROM film 
ORDER BY length DESC 
FETCH NEXT 3 ROWS WITH TIES

And we’re getting:

FILM_ID  TITLE               LENGTH
-----------------------------------
212      DARN FORRESTER	     185
872      SWEET BROTHERHOOD   185
817      SOLDIERS EVOLUTION  185
991      WORST BANGER        185
690      POND SEATTLE        185
609      MUSCLE BRIGHT       185
349      GANGS PRIDE         185
426      HOME PITY           185
182      CONTROL ANTHEM      185
141      CHICAGO NORTH       185

There are no more films of length 185 than the above. For more information about doing TOP N SQL queries, see this blog post.

For the sake of simplicity, let’s remove again the OFFSET clause (because it behaves inconsistently when combined with WITH TIES, at least in Oracle). Let’s look at WITH TIES only. jOOQ currently doesn’t emulate this clause for all dialects as it is hard to get right without window functions.

DB2       : select "v0" film_id, "v1" title, "v2" length from (
              select 
                film_id "v0", title "v1", length "v2", 
                rank() over (order by length desc) "rn"
              from film
            ) "x" 
            where "rn" > 0 and "rn" <= (0 + 3) 
            order by "rn"
HANA      : ... see DB2
MYSQL     : ... see DB2
ORACLE    : select film_id, title, length from film 
              order by length desc fetch next 3 rows with ties
POSTGRES  : ... see DB2
REDSHIFT  : ... see DB2
SQLSERVER : select top 3 with ties film_id, title, length from film 
              order by length desc
SYBASE    : ... see DB2

There are 3 ways to implement WITH TIES:

  • Oracle implements the SQL standard
  • SQL Server has a vendor-specific TOP N WITH TIES clause
  • All the others can emulate this feature using window functions

4. Nested set operations

Granted, this isn’t something you might run into every day, but when you need it, it’s really a PITA if your database doesn’t support it. Nested set operations. There are three set operations in SQL and relational algebra:

  • UNION
  • INTERSECT
  • EXCEPT (or MINUS, in Oracle)

All of the above come in two flavours:

  • OP or OP DISTINCT (standard syntax that isn’t implemented in any database)
  • OP ALL (most databases support this only for UNION)

Where ALL turns the set operation into a multiset operation, allowing duplicate results. ALL is fully supported (including on INTERSECT and EXCEPT) in:

  • CUBRID
  • DB2
  • Derby
  • HSQLDB
  • PostgreSQL

Now, the query. What if you want to find all actor names and all customer names, but you don’t want e.g. ADAM GRANT: In PostgreSQL, you could write:

SELECT first_name, last_name
FROM actor
UNION
SELECT first_name, last_name
FROM customer
EXCEPT
SELECT 'ADAM', 'GRANT'
ORDER BY 1, 2

In this case, we can simply hope that all these operators are left-associative, which means we’ll add customers to actors, and then remove ADAM GRANT. In fact, according to the standard, this is the case. But perhaps, not all databases implement things this way, and as soon as you mix in INTERSECT, things change, as INTERSECT has higher operator precedence.

Want to be sure? Put parentheses around the expressions, e.g.

(
  SELECT first_name, last_name
  FROM actor
  UNION
  SELECT first_name, last_name
  FROM customer
)
EXCEPT
SELECT 'ADAM', 'GRANT'
ORDER BY 1, 2

Still valid in PostgreSQL (and if you add FROM dual, and replace EXCEPT by MINUS, then also in Oracle), but won’t work e.g. in MySQL. How can we get this to work in all the databases?

Here’s how:

ASE       : ... like MySQL
CUBRID    : ... like PostgreSQL (plus, add the dual table)
DB2       : ... like PostgreSQL (plus, add the dual table)
DERBY     : select first_name, last_name from (
              select first_name, last_name from (
                select first_name, last_name from actor
              ) x 
              union 
              select first_name, last_name from (
                select first_name, last_name from customer
              ) x
            ) x 
            except 
            select "ADAM", "GRANT" from (
              select 'ADAM', 'GRANT' from "SYSIBM"."SYSDUMMY1"
            )
            x order by 1, 2
H2        : ... like PostgreSQL
HANA      : ... like PostgreSQL (plus, add the dual table)
HSQLDB    : ... like PostgreSQL (plus, add the dual table)
INFORMIX  : ... like PostgreSQL (plus, add the dual table)
INGRES    : ... like PostgreSQL (plus, add the dual table)
MARIADB   : ... like MySQL
MYSQL     : select * from (
              select * from (
                select first_name, last_name from actor
              ) x 
              union 
              select * from (
                select first_name, last_name from customer
              ) x
            ) x
            except 
            select * from (
              select 'ADAM', 'GRANT' from dual
            ) 
            x order by 1, 2
ORACLE    : ... like PostgreSQL (add dual and replace EXCEPT by MINUS)
POSTGRES  : (
              (select first_name, last_name from actor) 
                union 
              (select first_name, last_name from customer)
            ) 
            except (select 'ADAM', 'GRANT') 
            order by 1, 2
REDSHIFT  : 
SQLITE    : ... like MySQL
SQLSERVER : ... like PostgreSQL
SYBASE    : ... like PostgreSQL (plus, add the dual table)
VERTICA   : ... like PostgreSQL

Some observations:

  • Access doesn’t support EXCEPT
  • Firebird has a bit of trouble with these operators – I simply haven’t figured out how to work around them yet. Probably due to some parser bugs
  • PostgreSQL (and many others) get it right
  • MySQL (and a few others) get it wrong, and we have to wrap the various set operation subqueries in derived tables, when suddenly things work well again. This really seems to be just a parser problem, not actually missing functionality. But it’s really a pain if you have to rewrite your SQL manually to the MySQL version
  • Derby is like MySQL, but in addition to lacking parser support for standard SQL nested set operations, it also suffers from these nasty bugs: https://issues.apache.org/jira/browse/DERBY-6983 and https://issues.apache.org/jira/browse/DERBY-6984. Luckily, you have jOOQ to work around all these hassles for you!

5. Derived column lists

A really cool standard feature is called “derived column lists”. It allows for renaming a table AND its columns in one go, for instance in PostgreSQL:

SELECT a, b
FROM (
  SELECT first_name, last_name
  FROM actor
) t(a, b) -- Interesting feature here
WHERE a LIKE 'Z%'

Yielding

A     B
----------
ZERO  CAGE

The utility of this functionality is most important when:

  • You generate SQL dynamically, and perhaps you’re not entirely sure what your derived table’s column names are – just rename them and be sure again
  • You call a table-valued function, i.e. a function that returns a table, and again, you’re not really sure what it’s columns are
  • You simply don’t like the column names of a table. This might not be the most important use-case, as with the above syntax, you have to rename ALL (except in PostgreSQL) the columns, in the right order, and we don’t like to depend on such ordering

Again, not all databases support this feature. So, what to do if they don’t? Use this one weird trick with a UNION ALL subquery to emulate it!

ACCESS    : ... like PostgreSQL
ASE       : ... like PostgreSQL
CUBRID    : ... like PostgreSQL
DB2       : ... like PostgreSQL
DERBY     : ... like PostgreSQL
FIREBIRD  : ... like PostgreSQL
H2        : select a, b from (
              (select null a, null b where 1 = 0) 
               union all 
              (select first_name, last_name from actor)
            ) t 
            where a like 'Z%'
HANA      : ... like H2 (plus, add dual table)
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like PostgreSQL
INGRES    : ... like H2 (plus, add dual table)
MARIADB   : ... like H2 (plus, add dual table)
MYSQL     : ... like H2 (plus, add dual table)
ORACLE    : ... like H2 (plus, add dual table)
POSTGRES  : select a, b from (
              select first_name, last_name from actor
            ) as t(a, b) 
            where a like 'Z%'
REDSHIFT  : ... like PostgreSQL
SQLITE    : ... like H2
SQLSERVER : ... like PostgreSQL
SYBASE    : ... like PostgreSQL
VERTICA   : ... like PostgreSQL

Not a lot of magic here. Either the database supports the feature, or it doesn’t. If it’s not supported, then the derived table whose columns should be aliased must be prefixed by a zero-row-returning UNION ALL subquery, which defines the column names. Because if you use set operations, then the first subquery defines the column names. Cool, eh?

select a, b from (

  -- Dummy subquery defining the column names
  (select null a, null b where 1 = 0) 
   union all 

  -- Actually interesting subqeury
  (select first_name, last_name from actor)
) t 
where a like 'Z%'

Please, forgive me… Actually, it was all Bill Karwin’s idea.

6. VALUES clause

Did you know that VALUES() is a clause that can be used outside of INSERT statements? Yes. In PostgreSQL, you can just write:

VALUES ('Hello', 'World'), ('Cool', 'eh?')

And you’re getting the following result:

column1  column2
----------------
Hello    World  
Cool     eh?    

Of course, we should never rely on such generated column names, thus again, derived column lists. In PostgreSQL, this is only possible when actually using a derived table in this context:

SELECT *
FROM (
  VALUES ('Hello', 'World'), ('Cool', 'eh?') 
) AS t(a, b)

Do all the databases support this clause? Of course not! But at least, it can be emulated in all databases:

ACCESS    : ... like Oracle
ASE       : ... like PostgreSQL
CUBRID    : ... like PostgreSQL
DB2       : ... like PostgreSQL
DERBY     : ... like PostgreSQL
FIREBIRD  : ... like Sybase SQL Anywhere
H2        : select "v"."c1", "v"."c2" from (
              (select null "c1", null "c2" where 1 = 0) 
               union all 
              (select * from (
                 values ('Hello', 'World'), ('Cool', 'eh?')
               ) "v")
            ) "v"
HANA      : ... like Oracle
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like Sybase SQL Anywhere
INGRES    : ... like Oracle
MARIADB   : ... like Oracle
MYSQL     : ... like Oracle
ORACLE    : select "v"."c1", "v"."c2" from (
              (select null "c1", null "c2" from dual where 1 = 0) 
               union all 
              (select * from (
                (select 'Hello', 'World' from dual)
                 union all 
                (select 'Cool', 'eh?' from dual)
              ) "v")
            ) "v"
POSTGRES  : select "v"."c1", "v"."c2" from (
              values ('Hello', 'World'), ('Cool', 'eh?')
            ) as "v"("c1", "c2")
REDSHIFT  : ... like PostgreSQL
SQLITE    : ... like H2
SQLSERVER : ... like PostgreSQL
SYBASE    : select [v].[c1], [v].[c2] from (
              (select 'Hello', 'World' from [SYS].[DUMMY]) 
               union all 
              (select 'Cool', 'eh?' from [SYS].[DUMMY])
            ) [v]([c1], [c2])
VERTICA   : ... like PostgreSQL

There are 4 flavours of how this is supported:

  • PostgreSQL and others: Support both VALUES and derived column lists
  • H2 and others: Support only VALUES, not derived column lists
  • Sybase SQL Anywhere and others: Do not support VALUES, but derived column lists
  • Oracle and others: Support neither feature

Clearly, this is only syntactic sugar for other, more verbose SQL, but it’s really cool when you don’t actually need any real table. In fact the whole optional FROM clause discussion from the beginning of this article is unnecessary, once you have VALUES(), which would be the standard way to “avoid” the FROM clause.

7. Predicates using Row Value Expressions

Once you’ve started using these, you will not want to miss them. Row value expressions. They’re essentially just tuple expressions, like:

SELECT *
FROM customer
WHERE (first_name, last_name)
    = ('MARY', 'SMITH')

Or, according to the standard and to PostgreSQL, also:

SELECT *
FROM customer
WHERE ROW (first_name, last_name)
    = ROW ('MARY', 'SMITH')

The functionality doesn’t seem very useful when using equality predicates, but it is much more interesting when using IN predicates:

-- Any customer named the same way as an actor?
SELECT *
FROM customer
WHERE (first_name, last_name) IN (
  SELECT first_name, last_name
  FROM actor
)

Or, when doing keyset pagination, through non-equality predicates:

SELECT *
FROM customer
WHERE (first_name, last_name) 
    > ('JENNIFER', 'DAVIS')

Again, not all databases support these. And those that do, have various levels of support. PostgreSQL is again the only database that goes “all in” on all the predicates, including funky things like the DISTINCT predicate:

SELECT *
FROM customer
WHERE (first_name, last_name) 
  IS DISTINCT FROM ('JENNIFER', 'DAVIS')

But luckily, again, these things can be emulated. Let’s look at all 3 of the above examples, and save ourselves the DISTINCT predicate for the next list item:

Equality on row value expressions

This is trivial. Either it’s supported, or it isn’t. Or you’re Oracle, and require a special syntax, to prevent ORA-00920 invalid relational operator (I would really love to hear that story. Must be funky):

ACCESS    : ... like SQL Server
ASE       : ... like SQL Server
CUBRID    : ... like PostgreSQL
DB2       : ... like PostgreSQL
DERBY     : ... like SQL Server
FIREBIRD  : ... like SQL Server
H2        : ... like PostgreSQL
HANA      : ... like SQL Server
HSQLDB    : ... like PostgreSQL
INFORMIX  : select * from customer 
            where row (first_name, last_name) = row ('MARY', 'SMITH')
INGRES    : ... like SQL Server
MARIADB   : ... like PostgreSQL
MYSQL     : ... like PostgreSQL
ORACLE    : select * from customer 
            where (first_name, last_name) = (('MARY', 'SMITH'))
POSTGRES  : select * from customer 
            where (first_name, last_name) = ('MARY', 'SMITH')
REDSHIFT  : ... like PostgreSQL
SQLITE    : ... like SQL Server
SQLSERVER : select * from customer 
            where (first_name = 'MARY' and last_name = 'SMITH')
SYBASE    : ... like SQL Server
VERTICA   : ... like PostgreSQL

Note that Informix requires the ROW() constructor, which should be optional. And again, Oracle is… Oracle :-)

IN predicate

Emulating this is much more tricky if it is not supported. Remember that IN and EXISTS predicates can be equivalent, so there’s always a way to transform them into each other.

ACCESS    : ... like SQLite
ASE       : ... like SQL Server
CUBRID    : ... like SQL Server
DB2       : ... like SQL Server
DERBY     : ... like SQL Server
FIREBIRD  : ... like SQL Server
H2        : select * from customer where (first_name, last_name) in (
              select (first_name, last_name) from actor
            )
HANA      : ... like SQLite
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like SQL Server
INGRES    : ... like SQLite
MARIADB   : ... like PostgreSQL
MYSQL     : ... like PostgreSQL
ORACLE    : select * from customer where (first_name, last_name) in ((
              select first_name, last_name from actor
            ))
POSTGRES  : select * from customer where (first_name, last_name) in (
              select first_name, last_name from actor
            )
REDSHIFT  : ... like PostgreSQL
SQLITE    : select * from customer where exists (
              select x.c1, x.c2 from (
                (select null c1, null c2 where 1 = 0) 
                 union all 
                (select first_name, last_name from actor)
              ) x 
              where (first_name = x.c1 and last_name = x.c2)
            )
SQLSERVER : select * from customer where exists (
              select x.c1, x.c2 
              from (select first_name, last_name from actor) x(c1, c2) 
              where (first_name = x.c1 and last_name = x.c2)
            )
SYBASE    : ... like SQL Server
VERTICA   : ... like SQL Server

Observations:

  • At this point, it’s worth mentioning that these things work “by accident” in H2. H2 unfortunately decided to use the (a, b, …, n) syntax for arrays, which are similar to tuples, but not the same thing. You can see in the H2 syntax that we have to wrap the two columns of the subquery in parentheses as well for the IN predicate to work as expected.
  • The transformation to an EXISTS() predicate requires the derived column list feature again. This is why some emulations are even more verbose than others.

Non-equality predicate

This predicate can be expanded to its standard definition again, easily, if it is not natively supported:

ACCESS    : ... like Oracle
ASE       : ... like PostgreSQL
CUBRID    : ... like Oracle
DB2       : ... like PostgreSQL
DERBY     : ... like Oracle
FIREBIRD  : ... like Oracle
H2        : ... like PostgreSQL
HANA      : ... like Oracle
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like Oracle
INGRES    : ... like Oracle
MARIADB   : ... like PostgreSQL
MYSQL     : ... like PostgreSQL
ORACLE    : select * from customer where (
              first_name >= 'JENNIFER' and (
                first_name > 'JENNIFER' or (
                  first_name = 'JENNIFER' and last_name > 'DAVIS'
                )
              )
            )
POSTGRES  : select * from customer 
              where (first_name, last_name) > ('JENNIFER', 'DAVIS')
REDSHIFT  : ... like Oracle
SQLITE    : ... like Oracle
SQLSERVER : ... like Oracle
SYBASE    : ... like Oracle
VERTICA   : ... like PostgreSQL

Observation:

  • Strictly speaking, it is not necessary to have one of the two predicates redundant in the emulation, but unfortunately, many databases have trouble when the top boolean operator of a boolean expression is OR rather than AND

8. The DISTINCT predicate

In the previous section, we’ve briefly mentioned the DISTINCT predicate, a useful predicate that helps handling NULL values as we’s mostly expect.

A quick summary in PostgreSQL:

WITH t(v) AS (
  VALUES (1),(2),(null)
)
SELECT v1, v2, v1 IS DISTINCT FROM v2
FROM t t1(v1), t t2(v2)

This yields:

v1  v2  d
-----------------
1   1   false    
1   2   true     
1       true     
2   1   true     
2   2   false    
2       true     
    1   true     
    2   true     
        false    

Conveniently, this never returns NULL when comparing anything with NULL, so simply spoken NULL IS NOT DISTINCT FROM NULL is TRUE. Quite some syntax, but hey, it’s SQL.

Regrettably, only few databases support the standard syntax, and MySQL and SQLite have a much more concise, non-standard operator. Let’s emulate the query from the previous section in our databases:

SELECT *
FROM customer
WHERE (first_name, last_name) 
  IS DISTINCT FROM ('JENNIFER', 'DAVIS')

Observe, there’s a really cool way to emulate this operation using INTERSECT, because interestingly, set operations also treat two NULL values as “the same”, i.e. non-DISTINCT. We have:

ACCESS    : ... like SQL Server (plus, add the dual table)
ASE       : ... like SQL Server (plus, add the dual table)
CUBRID    : ... like SQL Server (plus, add the dual table)
DB2       : ... like SQL Server (plus, add the dual table)
DERBY     : ... like SQL Server (plus, add the dual table)
FIREBIRD  : ... like PostgreSQL
H2        : ... like PostgreSQL
HANA      : ... like SQL Server (plus, add the dual table)
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like SQL Server (plus, add the dual table)
INGRES    : ... like SQL Server (plus, add the dual table)
MARIADB   : ... like MySQL
MYSQL     : select * from customer where (not((first_name, last_name) 
                                         <=> ('JENNIFER', 'DAVIS')))
ORACLE    : ... like SQL Server (plus, add the dual table)
POSTGRES  : select * from customer where (first_name, last_name) 
                        is distinct from ('JENNIFER', 'DAVIS')
REDSHIFT  : ... like PostgreSQL
SQLITE    : select * from customer where ((first_name, last_name) 
                                   is not ('JENNIFER', 'DAVIS'))
SQLSERVER : select * from customer where not exists (
              (select first_name, last_name) 
               intersect 
              (select 'JENNIFER', 'DAVIS')
            )
SYBASE    : ... like SQL Server (plus, add the dual table)
VERTICA   : ... like SQL Server

Want to try it yourself? The original PostgreSQL truth-table producing query can be transformed to this one:

WITH t(v) AS (
  VALUES (1),(2),(null)
)
SELECT v1, v2, NOT EXISTS (
  SELECT v1 INTERSECT SELECT v2
)
FROM t t1(v1), t t2(v2)

It produces the same truth table. Cool, eh?

9. DDL statements

This is one of the main reasons why we’re doing all of this. We want to allow for SQL text based migration scripts (e.g. run with Flyway) to be translatable to any kind of SQL dialect. Because DDL is really the most boring part of SQL to keep vendor-agnostic.

Just two short examples:

Copying a table structure into a new table

A quick and dirty way to copy a table structure is this:

CREATE TABLE x AS 
SELECT 1 AS one
WITH NO DATA

Looks cool, hm? Unfortunately, there is some trouble with the syntax as you will see in the emulations:

DB2       : create table x as (select 1 one from "SYSIBM"."DUAL") 
            with no data
H2        : ... like Oracle
MARIADB   : ... like Oracle
MYSQL     : ... like Oracle
ORACLE    : create table x as select 1 one from dual where 1 = 0
POSTGRES  : create table x as select 1 as one with no data
SQLSERVER : select 1 one into x where 1 = 0

I’ve left out a couple of dialects, as this hasn’t been integration tested everywhere yet, being work in progress. There are 4 flavours:

  • PostgreSQL: Actual support for the WITH [ NO ] DATA clause
  • DB2: Actual support for the WITH [ NO ] DATA clause (but funky requirement to wrap the source query in parentheses
  • Oracle: No support for the clause (easy to emulate with dummy predicate), but at least support for CTAS (CREATE TABLE AS SELECT)
  • SQL Server: Vendor specific alternative to CTAS

The inverse is equally fun to emulate, let’s actually add the data:

CREATE TABLE x AS 
SELECT 1 AS one
WITH DATA

And we’re getting:

DB2       : begin 
              execute immediate '
                create table x as (select 1 one from "SYSIBM"."DUAL") 
                with no data 
              '; 
              execute immediate '
                insert into x select 1 one from "SYSIBM"."DUAL" 
              '; 
            end
ORACLE    : create table x as select 1 one from dual
POSTGRES  : create table x as select 1 as one with data
SQLSERVER : select 1 one into x

Let’s focus on the interesting bits only.

  • Oracle, PostgreSQL, SQL Server as before
  • DB2 actually cannot copy the data with the table structure. Huh!

As can be seen above, in cases like this, we might need to split a single DDL statement in a statement batch or anonymous block containing several statements. This is work in progress as not all databases support anonymous blocks or statement batches.

There are many other interesting DDL emulations, and a lot of it is not yet implemented. Just play around with them on https://www.jooq.org/translate and send us your feature requests / ideas to https://github.com/jOOQ/jOOQ/issues/new

10. Built-in Functions

Last but not least, there are a ton of built-in functions, such as the boring LPAD() function. (Left pad is known for various things). Migrating these between databases is really really tedious. We’re here to help! Let’s emulate:

SELECT lpad('abc', ' ', 5)

Translation:

ACCESS    : replace(space(' ' - len('abc')), ' ', 5) & 'abc'
ASE       : (replicate(5, (' ' - char_length('abc'))) || 'abc')
CUBRID    : lpad('abc', ' ', 5)
DB2       : lpad('abc', ' ', 5)
DERBY     : lpad('abc', ' ', 5)
FIREBIRD  : lpad('abc', ' ', 5)
H2        : lpad('abc', ' ', 5)
HANA      : lpad('abc', ' ', 5)
HSQLDB    : lpad('abc', ' ', 5)
INFORMIX  : lpad('abc', ' ', 5)
INGRES    : lpad('abc', ' ', 5)
MARIADB   : lpad('abc', ' ', 5)
MYSQL     : lpad('abc', ' ', 5)
ORACLE    : lpad('abc', ' ', 5)
POSTGRES  : lpad('abc', ' ', 5)
REDSHIFT  : lpad('abc', ' ', 5)
SQLITE    : substr(replace(replace(substr(quote(zeroblob(((' ' - length('abc') - 1 + length("5")) / length("5") + 1) / 2)), 3), '''', ''), '0', "5"), 1, (' ' - length('abc'))) || 'abc'
SQLSERVER : (replicate(5, (' ' - len('abc'))) + 'abc')
SYBASE    : (repeat(5, (' ' - length('abc'))) || 'abc')
VERTICA   : lpad('abc', ' ', 5)

Forgive me again for the SQLite version. It was a suggestion made by an unknown user on Stack Overflow, the place where I tend to nerd-snipe people into solving such problems for me for free.

Conclusion

jOOQ standardises SQL into a type safe, embedded internal DSL in Java. With jOOQ 3.9+, we’ve added a parser (which is also publicly available on https://www.jooq.org/translate), which removes the need to express everything in the jOOQ API. Just parse your random SQL string and translate it to some other SQL dialect. This list could easily be extended to 50 items and more, but it is much more fun to play around with our website and try this on your own.

Please, if you do, do report any issue, feature request that you’d like to see at: https://github.com/jOOQ/jOOQ/issues/new to help us make this new tool even more valuable for you. In the near future, we’re going to more closely integrate this parser with other tools, such as Flyway, as we think there’s a lot of value in vendor-agnostic, standardised SQL.

Map Reducing a Set of Values Into a Dynamic SQL UNION Query

Sounds fancy, right? But it’s a really nice and reasonable approach to doing dynamic SQL with jOOQ.

This blog post is inspired by a Stack Overflow question, where a user wanted to turn a set of values into a dynamic UNION query like this:

SELECT T.COL1
FROM T
WHERE T.COL2 = 'V1'
UNION
SELECT T.COL1
FROM T
WHERE T.COL2 = 'V2'
...
UNION
SELECT T.COL1
FROM T
WHERE T.COL2 = 'VN'

Note, both the Stack Overflow user and I are well aware of the possibility of using IN predicates :-), let’s just assume for the sake of argument, that the UNION query indeed outperforms the IN predicate in the user’s particular MySQL version and database. If this cannot be accepted, just imagine a more complex use case.

The solution in Java is really very simple:

import static org.jooq.impl.DSL.*;
import java.util.*;
import org.jooq.*;

public class Unions {
    public static void main(String[] args) {
        List<String> list = Arrays.asList("V1", "V2", "V3", "V4");

        System.out.println(
            list.stream()
                .map(Unions::query)
                .reduce(Select::union));
    }

    // Dynamically construct a query from an input string
    private static Select<Record1<String>> query(String s) {
        return select(T.COL1).from(T).where(T.COL2.eq(s));
    }
}

The output is:

Optional[(
  select T.COL1
  from T
  where T.COL2 = 'V1'
)
union (
  select T.COL1
  from T
  where T.COL2 = 'V2'
)
union (
  select T.COL1
  from T
  where T.COL2 = 'V3'
)
union (
  select T.COL1
  from T
  where T.COL2 = 'V4'
)]

If you’re using JDK 9+ (which has Optional.stream()), you can further proceed to running the query fluently as follows:

List<String> list = Arrays.asList("V1", "V2", "V3", "V4");

try (Stream<Record1<String>> stream = list.stream()
    .map(Unions::query)
    .reduce(Select::union))
    .stream() // Optional.stream()!
    .flatMap(Select::fetchStream)) {
    ...
}

This way, if the list is empty, reduce will return an empty optional. Streaming that empty optional will result in not fetching any results from the database.

Type Safe Implicit JOIN Through Path Navigation in jOOQ 3.11

One of the biggest contributors to SQL syntax verbosity is the need to explicitly JOIN every table that somehow contributes to the query, even if that contribution is “trivial”. When looking at the Sakila database, an example could be seen easily when fetching customer data:

SELECT 
  cu.first_name,
  cu.last_name,
  co.country
FROM customer AS cu
JOIN address USING (address_id)
JOIN city USING (city_id)
JOIN country AS co USING (country_id)  

That single access to the country information cost us 3 additional lines of SQL code as well as the cognitive overhead of mentally navigating through the to-one relationships in order to get the joins right.

This can be equally tedious when writing the SQL as well as when reading it! There is separation of concerns (projection vs joins) where there shouldn’t be in this particular case. We’re just projecting the country, not doing anything with it, let alone care about the individual table / primary key / foreign key names. Imagine if we had composite keys in the path from customer to country…

Implicit JOIN from SELECT clause

Wouldn’t it be much better (in this case) to be able to write:

SELECT 
  cu.first_name,
  cu.last_name,
  cu.address.city.country.country
FROM customer AS cu

Because after all, that’s really the same thing. We’re fetching only customers, and we load some additional content from its parent table(s). Since we’re navigating to-one relationships only (as opposed to navigating to-many relationships), we don’t really need actual JOIN semantics, a JOIN being a filtered cartesian product.

Implicit JOIN from WHERE clause

The same is true when fetching customers from a particular country. Why write:

SELECT 
  cu.first_name,
  cu.last_name
FROM customer AS cu
JOIN address USING (address_id)
JOIN city USING (city_id)
JOIN country AS co USING (country_id)
WHERE co.country = 'Switzerland'

When this would be a lot more natural:

SELECT 
  cu.first_name,
  cu.last_name
FROM customer AS cu
WHERE cu.address.city.country.country = 'Switzerland'

Implicit JOIN from multiple clauses

Another example would be when grouping by country to find out how many customers per country we have. Standard SQL, explicit JOIN version:

SELECT 
  co.country,
  COUNT(*),
  COUNT(DISTINCT city.city)
FROM customer AS cu
JOIN address USING (address_id)
JOIN city USING (city_id)
JOIN country AS co USING (country_id)  
GROUP BY co.country
ORDER BY co.country

Again, the many JOINs could be seen as noise, when the implicit version may seem much leaner:

SELECT 
  cu.address.city.country.country,
  COUNT(*),
  COUNT(DISTINCT cu.address.city.city)
FROM customer AS cu
GROUP BY cu.address.city.country.country
ORDER BY cu.address.city.country.country

Even if the same expression is repeated 3x (and we could easily alias it, of course), the output query would still do only that single JOIN graph that we’ve seen before. In fact, there are two different paths:

  • cu.address.city.*
  • cu.address.city.country.*

Internally, we should recognise that the paths are part of the same tree traversal, so the JOIN graph produced by cu.address.city.* can be re-used for cu.address.city.country.*

In fact, we could actually add one (semi-)explicit JOIN to avoid the repetition:

SELECT 
  ci.country.country,
  COUNT(*),
  COUNT(DISTINCT ci.city)
FROM customer AS cu
IMPLICIT JOIN cu.address.city AS ci
GROUP BY ci.country.country
ORDER BY ci.country.country

Implicit JOIN from correlated subqueries

A more sophisticated case would be an implicit join in a correlated subquery, which should really affect the outer query rather than the subquery. Consider finding all customers and the number of customers from the same country:

SELECT 
  cu.first_name,
  cu.last_name, 
  (
    SELECT COUNT(*)
    FROM customer AS cu2
    JOIN address USING (address_id)
    JOIN city AS ci2 USING (city_id)
    WHERE ci2.country_id = ci.country_id
  ) AS customers_from_same_country
FROM customer AS cu
JOIN address USING (address_id)
JOIN city AS ci USING (city_id)

Now clearly, the JOINs start getting into the way of readability (and writeability as well). There’s a slight risk of getting semantics wrong because of all the aliasing going on. A much leaner solution is:

SELECT 
  cu.first_name,
  cu.last_name, 
  (
    SELECT COUNT(*)
    FROM customer AS cu2
    WHERE cu2.address.city.country_id =
          cu.address.city.country_id
  ) AS customers_from_same_country
FROM customer AS cu

Now, of course, many of you cringed and were ready to point out that a correlated subquery isn’t the best solution in this case, and you’re absolutely correct. Use window functions, instead!

Implicit JOIN from window functions

Still, you can profit from implicit JOIN again. Plain SQL version:

SELECT 
  cu.first_name,
  cu.last_name, 
  COUNT(*) OVER (PARTITION BY ci.country_id)
    AS customers_from_same_country
FROM customer AS cu
JOIN address USING (address_id)
JOIN city AS ci USING (city_id)

Implicit JOIN version:

SELECT 
  cu.first_name,
  cu.last_name, 
  COUNT(*) OVER (PARTITION BY cu.address.city.country_id)
    AS customers_from_same_country
FROM customer AS cu

It doesn’t matter where the implicit JOIN appears, i.e. where the path-based parent table access appears. The translation from implicit JOIN syntax to explicit JOIN will always append a JOIN or several JOINs to the left-most child table in the JOIN path, wherever that table is declared. This is a simple matter of scope resolution.

Drawbacks

Technically, there are no drawbacks of the implicit JOIN syntax for to-one relationships compared to the explicit JOIN syntax. But of course, as always with syntax sugar, there’s a slight risk of a developer not fully aware of how things work behind the scenes choosing a less optimal (but visually more elegant) solution over a more performant one.

This could be the case when modelling ANTI JOINs as implicit JOINs with a IS NULL predicate. In some databases, that might still be better, but in most databases, using NOT EXISTS() should be preferred when ANTI JOIN semantics is implemented.

Implicit JOIN for to-many relationship

Having a syntax for navigating to-many relationships is desireable as well, although the implications on semantics are vastly different. While implicit JOINs on to-one relationships have no unexpected effects on the semantics of the query, implicit JOINs on to-many relationships implicitly change the cardinalities of queries they’re contained in. For example:

SELECT
  a.first_name,
  a.last_name,
  a.film.title
FROM actor AS a

When navigating from the ACTOR to the FILM table (via the FILM_ACTOR relationship table), we’re going to duplicate the actor results. It is rather unexpected to have an expression in the SELECT clause to modify the cardinalities of a query, and thus, probably not a good idea. Specifically, there are many cases of implicit JOINs on to-many relationships where the semantics is unclear, ambiguous, or even wrong, because of this change of cardinalities.

For the sake of simplicity, this discussion is out of scope for this article, and for the upcoming jOOQ feature:

jOOQ support for implicit JOIN

Some ORMs like Hibernate, Doctrine, and others have implemented this feature in the past in their own respective query languages, such as HQL, DQL. jOOQ 3.11 follows suit and offers this feature as well through its type safe SQL query API (see https://github.com/jOOQ/jOOQ/issues/1502)

This will be done for the entirety of the SQL language, not just a limited subset, such as HQL or DQL.

All of the above queries can be written in jOOQ as such:

Customer cu = CUSTOMER.as("cu");

ctx.select(
      cu.FIRST_NAME,
      cu.LAST_NAME,
      cu.address().city().country().COUNTRY)
   .from(cu)
   .fetch();

ctx.select(
      cu.FIRST_NAME,
      cu.LAST_NAME)
   .from(cu)
   .where(cu.address().city().country().COUNTRY.eq("Switzerland"))
   .fetch();

ctx.select(cu.address().city().country().COUNTRY, count())
   .from(cu)
   .groupBy(cu.address().city().country().COUNTRY)
   .orderBy(cu.address().city().country().COUNTRY)
   .fetch();

Customer cu2 = CUSTOMER.as("cu2");

ctx.select(
      cu.FIRST_NAME,
      cu.LAST_NAME,
      field(selectCount()
          .from(cu2)
          .where(cu2.address().city().COUNTRY_ID.eq(
                 cu.address().city().COUNTRY_ID))
      ).as("customers_from_same_country"))
   .from(cu)
   .fetch();

ctx.select(
      cu.FIRST_NAME,
      cu.LAST_NAME,
      count().over(partitionBy(cu.address().city().COUNTRY_ID))
        .as("customers_from_same_country"))
   .from(cu)
   .fetch();

The navigation is completely type safe thanks to jOOQ’s code generator which generates navigational methods from child table to parent table in the presence of foreign keys. By default, the method name matches the parent table name (single foreign key between child and parent) or the foreign key constraint name (multiple foreign keys between child and parent), but as always, this can be overridden easily using generator strategies.

The feature is really extremely powerful. For a much more complex example, see:

Bringing implicit JOIN to actual SQL

A nice jOOQ feature that hasn’t been advertised too often yet is the new jOOQ parser, whose main purpose (so far) is to offer support for the DDLDatabase, a tool that reverse engineers your DDL scripts to generate jOOQ code. The parser will have many other uses in the future, though, including its capability of being exposed behind a JDBC proxy API, which can parse any JDBC based application’s SQL and re-generate it using different settings (e.g. a different dialect).

Of course, the parser (if supplied with schema meta information, see https://github.com/jOOQ/jOOQ/issues/5296) will be able to resolve such path expressions and transform the input SQL string using implicit JOINs to the equivalent output SQL string with natural SQL joins.

This topic is still under research. More information will follow as the scope of this functionality will become more clear.

Availability in jOOQ

jOOQ 3.11 is due for late Q3 2018 / early Q4 2018. You can already play around with this feature by checking out jOOQ from GitHub:
https://github.com/jOOQ/jOOQ

Your feedback is very welcome!

Top 5 Hidden jOOQ Features

jOOQ’s main value proposition is obvious: Type safe embedded SQL in Java.

People who actively look for such a SQL builder will inevitably stumble upon jOOQ and love it, of course. But a lot of people don’t really need a SQL builder – yet, jOOQ can still be immensely helpful in other situations, through its lesser known features.

Here’s a list of top 5 “hidden” jOOQ features.

1. Working with JDBC ResultSet

Even if you’re otherwise not using jOOQ but JDBC (or Spring JdbcTemplate, etc.) directly, one of the things that’s most annoying is working with ResultSet. A JDBC ResultSet models a database cursor, which is essentially a pointer to a collection on the server, which can be positioned anywhere, e.g. to the 50th record via ResultSet.absolute(50) (remember to start counting at 1).

The JDBC ResultSet is optimised for lazy data processing. This means that we don’t have to materialise the entire data set produced by the server in the client. This is a great feature for large (and even large-ish) data sets, but in many cases, it’s a pain. When we know we’re fetching only 10 rows and we know that we’re going to need them in memory anyway, a List<Record> type would be much more convenient.

jOOQ’s org.jooq.Result is such a List, and fortunately, you can easily import any JDBC ResultSet easily as follows by using DSLContext.fetch(ResultSet):

try (ResultSet rs = stmt.executeQuery()) {
    Result<Record> result = DSL.using(connection).fetch(rs);
    System.out.println(result);
}

With that in mind, you can now access all the nice jOOQ utilities, such as formatting a result, e.g. as TEXT (see The second feature for more details):

+---+---------+-----------+
| ID|AUTHOR_ID|TITLE      |
+---+---------+-----------+
|  1|        1|1984       |
|  2|        1|Animal Farm|
+---+---------+-----------+

Of course, the inverse is always possible as well. Need a JDBC ResultSet from a jOOQ Result? Call Result.intoResultSet() and you can inject dummy results to any application that operates on JDBC ResultSet:

DSLContext ctx = DSL.using(connection);

// Get ready for Java 10 with var!
var result = ctx.newResult(FIRST_NAME, LAST_NAME);
result.add(ctx.newRecord(FIRST_NAME, LAST_NAME)
              .values("John", "Doe"));

// Pretend this is a real ResultSet
try (ResultSet rs = result.intoResultSet()) {
  while (rs.next())
    System.out.println(rs.getString(1) + " " + rs.getString(2));
}

2. Exporting a Result as XML, CSV, JSON, HTML, TEXT, ASCII Chart

As we’ve seen in the previous section, jOOQ Result types have nice formatting features. Instead of just text, you can also format as XML, CSV, JSON, HTML, and again TEXT

The format can usually be adapted to your needs.

For instance, this text format is possible as well:

ID AUTHOR_ID TITLE      
------------------------
 1         1 1984       
 2         1 Animal Farm

When formatting as CSV, you’ll get:

ID,AUTHOR_ID,TITLE
1,1,1984
2,1,Animal Farm

When formatting as JSON, you might get:

[{"ID":1,"AUTHOR_ID":1,"TITLE":"1984"},
 {"ID":2,"AUTHOR_ID":1,"TITLE":"Animal Farm"}]

Or, depending on your specified formatting options, perhaps you’ll prefer the more compact array of array style?

[[1,1,"1984"],[2,1,"Animal Farm"]]

Or XML, again with various common formatting styles, among which:

<result>
  <record>
    <ID>1</ID>
    <AUTHOR_ID>1</AUTHOR_ID>
    <TITLE>1984</TITLE>
  </record>
  <record>
    <ID>2</ID>
    <AUTHOR_ID>1</AUTHOR_ID>
    <TITLE>Animal Farm</TITLE>
  </record>
</result>

HTML seems kind of obvious. You’ll get:

ID AUTHOR_ID TITLE
1 1 1984
2 1 Animal Farm

Or, in code:

<table>
<tr><th>ID</th><th>AUTHOR_ID</th><th>TITLE</th></tr>
<tr><td>1</td><td>1</td><td>1984</td></tr>
<tr><td>2</td><td>1</td><td>Animal Farm</td></tr>
</table>

As a bonus, you could even export the Result as an ASCII chart:

These features are obvious additions to ordinary jOOQ queries, but as I’ve shown in Section 1, you can get free exports from JDBC results as well!

3. Importing these text formats again

After the previous section’s export capabilities, it’s natural to think about how to import such data again back into a more usable format. For instance, when you write integration tests, you might expect a database query to return a result like this:

ID AUTHOR_ID TITLE      
-- --------- -----------
 1         1 1984       
 2         1 Animal Farm

Simply import the above textual representation of your result set into an actual jOOQ Result using Result.fetchFromTXT(String) and you can continue operating on a jOOQ Result (or as illustrated in Section 1, with a JDBC ResultSet!).

Most of the other export formats (except charts, of course) can be imported as well.

Now, don’t you wish for a second that Java has multi-line strings (in case of which this would be very nice looking):

Result<?> result = ctx.fetchFromTXT(
    "ID AUTHOR_ID TITLE      \n" +
    "-- --------- -----------\n" +
    " 1         1 1984       \n" +
    " 2         1 Animal Farm\n"
);
ResultSet rs = result.intoResultSet();

These types can now be injected anywhere where a service or DAO produces a jOOQ Result or a JDBC ResultSet. The most obvious application for this is mocking. The second most obvious application is testing. You can easily test that a service produces an expected result of the above form.

Let’s talk about mocking:

4. Mocking JDBC

Sometimes, mocking is cool. With the above tools, it’s only natural for jOOQ to provide a full-fledged, JDBC-based mocking SPI. I’ve written about this feature before and again here.

Essentially, you can implement a single FunctionalInterface called MockDataProvider. The simplest way to create one is by using the Mock.of() methods, e.g.:

MockDataProvider provider = Mock.of(ctx.fetchFromTXT(
    "ID AUTHOR_ID TITLE      \n" +
    "-- --------- -----------\n" +
    " 1         1 1984       \n" +
    " 2         1 Animal Farm\n"
));

What this provider does is it simply ignores all the input (queries, bind variables, etc.) and always returns the same simple result set. You can now plug this provider into a MockConnection and use it like any ordinary JDBC connection:

try (Connection c = new MockConnection(provider);
     PreparedStatement s = c.prepareStatement("SELECT foo");
     ResultSet rs = s.executeQuery()) {

    while (rs.next()) {
        System.out.println("ID        : " + rs.getInt(1));
        System.out.println("First name: " + rs.getString(2));
        System.out.println("Last name : " + rs.getString(3));
    }
}

The output being (completely ignoring the SELECT foo statement):

ID        : 1
First name: 1
Last name : 1984
ID        : 2
First name: 1
Last name : Animal Farm

This client code doesn’t even use jOOQ (although it could)! Meaning, you can use jOOQ as a JDBC mocking framework on any JDBC-based application, including a Hibernate based one.

Of course, you don’t always want to return the exact same result. This is why a MockDataProvider offers you an argument with all the query information in it:

try (Connection c = new MockConnection(ctx -> {
    if (ctx.sql().toLowerCase().startsWith("select")) {
        // ...
    }
})) {
    // Do stuff with this connection
}

You can almost implement an entire JDBC driver with a single lambda expression. Read more here. Cool, eh?

Side note: Don’t get me wrong: I don’t think you should mock your entire database layer just because you can. My thoughts are available in this tweet storm:

Speaking of “synthetic JDBC connections”

5. Parsing Connections

jOOQ 3.9 introduced a SQL parser, whose main use case so far is to parse and reverse engineer DDL scripts for the code generator.

Another feature that has not been talked about often yet (because still a bit experimental) is the parsing connection, available through DSLContext.parsingConnection(). Again, this is a JDBC Connection implementation that wraps a physical JDBC connection but runs all SQL queries through the jOOQ parser before generating them again.

What’s the point?

Let’s assume for a moment that we’re using SQL Server, which supports the following SQL standard syntax:

SELECT * FROM (VALUES (1), (2), (3)) t(a)

The result is:

 a
---
 1
 2
 3

Now, let’s assume we are planning to migrate our application to Oracle and we have the following JDBC code that doesn’t work on Oracle, because Oracle doesn’t support the above syntax:

try (Connection c = DriverManager.getConnection("...");
     Statement s = c.createStatement();
     ResultSet rs = s.executeQuery(
         "SELECT * FROM (VALUES (1), (2), (3)) t(a)")) {

    while (rs.next())
        System.out.println(rs.getInt(1));
}

Now, we have three options (hint #1 sucks, #2 and #3 are cool):

  1. Tediously migrate all such manually written JDBC based SQL to Oracle syntax and hope we don’t have to migrate back again
  2. Upgrade our JDBC based application to use jOOQ instead (that’s the best option, of course, but it also takes some time)
  3. Simply use the jOOQ parsing connection as shown below, and a lot of code will work right out of the box! (and then, of course, gradually migrate to jOOQ, see option #2)
try (DSLContext ctx = DSL.using("...");
     Connection c = ctx.parsingConnection(); // Magic here
     Statement s = c.createStatement();
     ResultSet rs = s.executeQuery(
         "SELECT * FROM (VALUES (1), (2), (3)) t(a)")) {

    while (rs.next())
        System.out.println(rs.getInt(1));
}

We haven’t touched any of our JDBC based client logic. We’ve only introduced a proxy JDBC connection that runs every statement through the jOOQ parser prior to re-generating the statement on the wrapped, physical JDBC connection.

What’s really executed on Oracle is this emulation here:

select t.a from (
  (select null a from dual where 1 = 0) union all 
  (select * from (
    (select 1 from dual) union all 
    (select 2 from dual) union all 
    (select 3 from dual)
  ) t)
) t

Looks funky, eh? The rationale for this emulation is described here.

Every SQL feature that jOOQ can represent with its API and that it can emulate between databases will be supported! This includes far more trivial things, like parsing this query:

SELECT substring('abcdefg', 2, 4)

… and running this one on Oracle instead:

select substr('abcdefg', 2, 4) from dual

You’re all thinking

Want to learn more about jOOQ?

There are many more such nice little things in the jOOQ API, which help make you super productive. Some examples include: