Hi, I'm Lukas. I was invited to talk about the business behind jOOQ on Foojay.io and as a short introduction to jOOQ, I'd like to highlight 5 great reasons to use it.

Before discussing those reasons:

What is jOOQ

jOOQ is an internal domain-specific language (DSL) modelling the SQL language as an API directly in Java.

This provides compile-time type safety to your query and enables a lot of other interesting features that I'll show later.

In short, if your SQL query looks like this:

SELECT author.first_name, author.last_name
FROM author
WHERE author.last_name = 'Shakespeare'
ORDER BY author.first_name, author.last_name
LIMIT 10

...then your equivalent jOOQ query looks like this:

ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .where(AUTHOR.LAST_NAME.eq("Shakespeare"))
   .orderBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .limit(10)
   .fetch();

So, there's (almost always) a 1:1 equivalence between SQL syntax and the jOOQ API, irrespective of whether you're coding in Java, Kotlin, or Scala.

Great, so why should I use it?

It has always been easy to write SQL in Java with JDBC.

Things have gotten even simpler since Java 15's introduction of text blocks, so why use an internal DSL instead of the external one, that is, SQL?

Here are 5 great reasons to use jOOQ rather than native SQL:

1. Type safety

The DSL itself is type-safe, meaning you can't write illegal SQL as easily as with String-based SQL. But using jOOQ's code generator, you have access to your entire database catalog directly as Java objects. This extends to:

• IDE auto-completion (even for dynamic SQL, where the IDE can't help).
• Compile-time checking (where the IDE also can't help).
• Documentation of the schema.

In the above intro, we've seen objects like AUTHOR or AUTHOR.LAST_NAME. These are generated classes, members, methods, etc., which the Java compiler knows and can type-check.

For example, the AUTHOR.LAST_NAME member is of type Field<String> meaning that the resulting record will be aware of its contents being a String. Not just that, but the predicate from the query is type-safe as well!

// This compiles:
AUTHOR.LAST_NAME.eq("Shakespeare")

// This does not:
AUTHOR.LAST_NAME.eq(1)

SQL is a type-safe language itself. When you write a view or procedure in your database, the database's compiler will type-check those objects, as well. But this luxury goes away as soon as Java developers write their SQL in String form using JDBC or other libraries that work with SQL strings.

And not just SQL queries are affected. jOOQ's code generator also generates stubs for your stored procedures. If you have a procedure like this:

CREATE OR REPLACE PROCEDURE my_proc (
  i1 NUMBER,
  io1 IN OUT NUMBER,
  o1 OUT NUMBER,
  o2 OUT NUMBER,
  io2 IN OUT NUMBER,
  i2 NUMBER
) IS
BEGIN
  o1 := io1;
  io1 := i1;

  o2 := io2;
  io2 := i2;
END my_proc;

You can call it conveniently like this:

MyProc result = Routines.myProc(configuration, 1, 2, 5, 6);

System.out.println("io1 = " + result.getIo1());
System.out.println("o1 = " + result.getO1());
System.out.println("o2 = " + result.getO2());
System.out.println("io2 = " + result.getIo2());

Using code generation and the type-safe API, it feels as if the database is a part of your Java application, which helps speed up development, just as much as it helps prevent errors as your Java code stops compiling as soon as you modify objects in your database.

Note you don't have to use the DSL API to get access to many of the remaining benefits. For example, jOOQ also has a parser that can work as a JDBC proxy, e.g. to lint or translate SQL between dialects, or otherwise transform your SQL.

Also, there's always the plain SQL templating escape hatch, if you prefer native SQL for some queries or some parts of a query.

2. Mapping

A big (and very boring) part of working with SQL is mapping the result set to some representation in application code, be it Java, JSON, XML, etc. jOOQ helps with those things again in a type-safe way. Let's say you're using Java records to represent your data:

record Book (int id, String title) {}
record Name (String firstName, String lastName) {}
record Author (int id, Name name, List<Book> books) {}

With jOOQ, you can easily map any level of nested data structures to your Java representation directly in the query, in a type-safe way, like so:

List<Author> authors =
ctx.select(
     AUTHOR.ID,
     row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new),
     multiset(
       select(BOOK.ID, BOOK.TITLE)
       .from(BOOK)
       .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
     ).convertFrom(r -> r.map(mapping(Book::new)))
   )
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch(mapping(Author::new))

That's it! The above example uses a few features:

• Nested records using the row() operator
• Nested collections using the multiset() operator
• Ad-hoc converters to turn jOOQ Result<Record3> types into your own types in a type safe way.

Behind the scenes, in most dialects, some SQL/JSON or SQL/XML query is run in order to be able to nest the records and collections directly in SQL, thus avoiding:

• Costly N+1 problems.
• Complicated mapping algorithms to deduplicate JOIN results again.

Neither the JSON serialisation format, nor the nesting imposes any significant performance penalty, as can be seen in this article.

Plus, both the ROW() and MULTISET() operators are standard SQL operators, so this is really an idiomatic way to work with SQL. Read more about MULTISET here.

3. Vendor agnosticity

If you have to support multiple RDBMS, then an abstraction like jOOQ is almost inevitable. You can open up any page of the jOOQ manual to see how something as mundane as the POSITION() function gets translated by jOOQ.

When you write position("hello", "e") in jOOQ, then you're getting:

-- ASE, SQLDATAWAREHOUSE, SQLSERVER
charindex('e', 'hello')

-- AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, EXASOL, FIREBIRD, H2, HSQLDB,
-- MARIADB, MEMSQL, MYSQL, POSTGRES, SNOWFLAKE, TERADATA, TRINO, VERTICA,
-- YUGABYTEDB
position('e' IN 'hello')

-- BIGQUERY, ORACLE, SQLITE
instr('hello', 'e')

-- DB2, DERBY
locate('e', 'hello')

-- HANA, SYBASE
locate('hello', 'e')

This goes much further when more complex SQL features are involved, such as the LIMIT or FETCH clause, which may have to be emulated using filters on ROW_NUMBER(), RANK(), DENSE_RANK() or PERCENT_RANK() window functions, depending on the specific clause usage. Or, if you want to use SQL/JSON, which is an ISO/IEC TR 19075:6 standard, yet hardly anyone implements the standard. But with jOOQ, you get access to the standard syntax (via Java APIs), and jOOQ handles the translation to the relevant SQL dialect.

Even if you're not supporting multiple RDBMS, this approach will save you having to look up every single function's specifics, plus it works around all the little caveats of the dialect. You wouldn't believe how many there are! (Some examples about dialects not implementing the standard can be found in the above article).

Note that you don't have to use the jOOQ API to get access to vendor agnosticity. If you prefer working with SQL strings, you can use jOOQ as a JDBC proxy, for example.

4. Dynamic SQL

Probably the biggest benefit that you get out of the box with jOOQ is that all of your SQL queries are automatically dynamic SQL queries, even if they don't look like it. The previous query example looks static:

ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .where(AUTHOR.LAST_NAME.eq("Shakespeare"))
   .orderBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .limit(10)
   .fetch();

But you're really building an expression tree behind the scenes that can be constructed dynamically. For example, what if the WHERE clause should be dynamic?

You can extract it into a local variable, and conditionally append to it:

Condition condition = AUTHOR.LAST_NAME.eq("Shakespeare");

if (something)
    condition = condition.and(somethingElse);

ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .where(condition)
   .orderBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .limit(10)
   .fetch();

jOOQ doesn't know or care how you constructed your query. But it will still type-check and prevent SQL injection, etc. (The value "Shakespeare" is automatically turned into a bind value.)

You can transform your SQL also more globally, e.g. using the VisitListener SPI, or using model API transformations. This can be useful to implement more sophisticated use-cases, like row level security or multitenancy on all of your SQL queries. (For the latter, there's an out of the box schema mapping feature for simple cases).

Finally, with the expression tree of your query being available programmatically, you can also implement diagnostics, or use jOOQ's built-in diagnostics, e.g. to detect N+1 problems.

Note: Again, thanks to jOOQ's parser, you don't have to use the jOOQ API to get access to all of this. You can parse any SQL query (e.g. generated by Hibernate) and translate/transform/lint/diagnose it, even if that's not really the primary use-case for jOOQ.

5. Everything SQL

Don't take my word on this one, but listen to existing jOOQ users who have told me many times that thanks to jOOQ, they've started appreciating SQL again. Obviously, this is a subjective take, but jOOQ:

• makes SQL "first class," meaning that it encourages people to think like a SQL developer (e.g. set based thinking rather than row-by-row processing);
• makes SQL idiomatic, meaning that it feels like something Java can do; and
• helps users discover SQL features they might not have been aware of, such as LATERAL, WITH ORDINALITY, XMLTABLE, just to name a few (do check out the manual! We keep adding more support for more awesome standard or vendor specific SQL features).

jOOQ's philosophy stems from the fact that, historically, SQL databases tend to have a long and steady lifetime, whereas user-facing software changes much more frequently. Just think of the last 25 years of Java development.

User-facing logic has moved from server (CGI-BIN, J2EE, etc.), to client (JavaScript SPAs, Mobile apps, etc.), back to server (Vaadin, etc.), etc. But during all of these times, SQL databases have gone almost unchallenged, as this ranking suggests. This is also due to the fact that the relational model is very sound and normalisation helps design databases for the long run.

So, given the assumption that a database is likely to outlive any user-facing application accessing it, jOOQ embraces a "database first" philosophy, hence the focus on code generation.

Since your schema is "eternal," your application code should have a derived version of it (remember the stored procedure stubs!).

If that's how you think as well, then you'll find jOOQ very productive. If that's not how you think, then you can still use jOOQ for dynamic SQL, SQL transformations, and many other things—jOOQ won't judge.

Conclusion

jOOQ makes SQL a "first-class" language in the JVM ecosystem by embedding it into Java, Kotlin, and Scala in an idiomatic way, increasing developer productivity in various ways.

jOOQ is dual licensed software (Apache 2.0 and commercial).

Download it here, or start playing around with the demo, to see for yourself.