This section explains all about the jOOQ syntax used for building
queries through the query DSL and the query model API. It explains
the central factories, the supported SQL statements and various other syntax elements

This section will get you through the specifics of what can be done
with jOOQ at runtime, in order to execute queries, perform CRUD
operations, import and export data, and hook into the jOOQ execution
lifecycle for debugging

jOOQ's reason for being - compared to JPA

Java and SQL have come a long way. SQL is an "old", yet established and well-understood technology. Java is a legacy too, although its platform JVM allows for many new and contemporary languages built on top of it. Yet, after all these years, libraries dealing with the interface between SQL and Java have come and gone, leaving JPA to be a standard that is accepted only with doubts, short of any surviving options.

So far, there had been only few database abstraction frameworks or libraries, that truly respected SQL as a first class citizen among languages. Most frameworks, including the industry standards JPA, EJB, Hibernate, JDO, Criteria Query, and many others try to hide SQL itself, minimising its scope to things called JPQL, HQL, JDOQL and various other inferior query languages

jOOQ has come to fill this gap.

jOOQ's reason for being - compared to LINQ

Other platforms incorporate ideas such as LINQ (with LINQ-to-SQL), or Scala's SLICK, or also Java's QueryDSL to better integrate querying as a concept into their respective language. By querying, they understand querying of arbitrary targets, such as SQL, XML, Collections and other heterogeneous data stores. jOOQ claims that this is going the wrong way too.

In more advanced querying use-cases (more than simple CRUD and the occasional JOIN), people will want to profit from the expressivity of SQL. Due to the relational nature of SQL, this is quite different from what object-oriented and partially functional languages such as C#, Scala, or Java can offer.

It is very hard to formally express and validate joins and the ad-hoc table expression types they create. It gets even harder when you want support for more advanced table expressions, such as pivot tables, unnested cursors, or just arbitrary projections from derived tables. With a very strong object-oriented typing model, these features will probably stay out of scope.

In essence, the decision of creating an API that looks like SQL or one that looks like C#, Scala, Java is a definite decision in favour of one or the other platform. While it will be easier to evolve SLICK in similar ways as LINQ (or QueryDSL in the Java world), SQL feature scope that clearly communicates its underlying intent will be very hard to add, later on (e.g. how would you model Oracle's partitioned outer join syntax? How would you model ANSI/ISO SQL:1999 grouping sets? How can you support scalar subquery caching? etc...).

jOOQ has come to fill this gap.

jOOQ's reason for being - compared to SQL / JDBC

So why not just use SQL?

SQL can be written as plain text and passed through the JDBC API. Over the years, people have become wary of this approach for many reasons:

No typesafety

No syntax safety

No bind value index safety

Verbose SQL String concatenation

Boring bind value indexing techniques

Verbose resource and exception handling in JDBC

A very "stateful", not very object-oriented JDBC API, which is hard to use

For these many reasons, other frameworks have tried to abstract JDBC away in the past in one way or another. Unfortunately, many have completely abstracted SQL away as well

jOOQ has come to fill this gap.

jOOQ is different

SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than... SQL!

This section lists the various licenses that apply to different versions of jOOQ. Prior to version 3.2, jOOQ was shipped for free under the terms of the Apache Software License 2.0. With jOOQ 3.2, jOOQ became dual-licensed: Apache Software License 2.0 (for use with Open Source databases) and commercial (for use with commercial databases).

This manual itself (as well as the www.jooq.org public website) is licensed to you under the terms of the CC BY-SA 4.0 license.

License for jOOQ 3.2 and later

This work is dual-licensed
- under the Apache Software License 2.0 (the "ASL")
- under the jOOQ License and Maintenance Agreement (the "jOOQ License")
=============================================================================
You may choose which license applies to you:
- If you're using this work with Open Source databases, you may choose
either ASL or jOOQ License.
- If you're using this work with at least one commercial database, you must
choose jOOQ License
For more information, please visit http://www.jooq.org/licenses
Apache Software License 2.0:
-----------------------------------------------------------------------------
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
jOOQ License and Maintenance Agreement:
-----------------------------------------------------------------------------
Data Geekery grants the Customer the non-exclusive, timely limited and
non-transferable license to install and use the Software under the terms of
the jOOQ License and Maintenance Agreement.
This library is distributed with a LIMITED WARRANTY. See the jOOQ License
and Maintenance Agreement for more details: http://www.jooq.org/licensing

Historic license for jOOQ 1.x, 2.x, 3.0, 3.1

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

Trademarks owned by database vendors with no affiliation to Data Geekery™ GmbH

Access® is a registered trademark of Microsoft® Inc.

Adaptive Server® Enterprise is a registered trademark of Sybase®, Inc.

CUBRID™ is a trademark of NHN® Corp.

DB2® is a registered trademark of IBM® Corp.

Derby is a trademark of the Apache™ Software Foundation

H2 is a trademark of the H2 Group

HANA is a trademark of SAP SE

HSQLDB is a trademark of The hsql Development Group

Ingres is a trademark of Actian™ Corp.

MariaDB is a trademark of Monty Program Ab

MySQL® is a registered trademark of Oracle® Corp.

Firebird® is a registered trademark of Firebird Foundation Inc.

Oracle® database is a registered trademark of Oracle® Corp.

PostgreSQL® is a registered trademark of The PostgreSQL Global Development Group

Postgres Plus® is a registered trademark of EnterpriseDB® software

SQL Anywhere® is a registered trademark of Sybase®, Inc.

SQL Server® is a registered trademark of Microsoft® Inc.

SQLite is a trademark of Hipp, Wyrick & Company, Inc.

Other trademarks by vendors with no affiliation to Data Geekery™ GmbH

Java® is a registered trademark by Oracle® Corp. and/or its affiliates

Scala is a trademark of EPFL

Other trademark remarks

Other names may be trademarks of their respective owners.

Throughout the manual, the above trademarks are referenced without a formal ® (R) or ™ (TM) symbol. It is believed that referencing third-party trademarks in this manual or on the jOOQ website constitutes "fair use". Please contact us if you think that your trademark(s) are not properly attributed.

Contributions

The following are authors and contributors of jOOQ or parts of jOOQ in alphabetical order:

These chapters contain a quick overview of how to get started with this manual and with jOOQ. While the subsequent chapters contain a lot of reference information, this chapter here just wraps up the essentials.

This section helps you correctly interpret this manual in the context of jOOQ.

Code blocks

The following are code blocks:

-- A SQL code block
SELECT 1 FROM DUAL

// A Java code block
for (int i = 0; i < 10; i++);

<!-- An XML code block -->
<hello what="world"></hello>

# A config file code block
org.jooq.property=value

These are useful to provide examples in code. Often, with jOOQ, it is even more useful to compare SQL code with its corresponding Java/jOOQ code. When this is done, the blocks are aligned side-by-side, with SQL usually being on the left, and an equivalent jOOQ DSL query in Java usually being on the right:

-- In SQL:
SELECT 1 FROM DUAL

// Using jOOQ:
create.selectOne().fetch()

Code block contents

The contents of code blocks follow conventions, too. If nothing else is mentioned next to any given code block, then the following can be assumed:

-- SQL assumptions
------------------
-- If nothing else is specified, assume that the Oracle syntax is used
SELECT 1 FROM DUAL

// Java assumptions
// ----------------
// Whenever you see "standalone functions", assume they were static imported from org.jooq.impl.DSL
// "DSL" is the entry point of the static query DSL
exists(); max(); min(); val(); inline(); // correspond to DSL.exists(); DSL.max(); DSL.min(); etc...
// Whenever you see BOOK/Book, AUTHOR/Author and similar entities, assume they were (static) imported from the generated schema
BOOK.TITLE, AUTHOR.LAST_NAME // correspond to com.example.generated.Tables.BOOK.TITLE, com.example.generated.Tables.BOOK.TITLE
FK_BOOK_AUTHOR // corresponds to com.example.generated.Keys.FK_BOOK_AUTHOR
// Whenever you see "create" being used in Java code, assume that this is an instance of org.jooq.DSLContext.
// The reason why it is called "create" is the fact, that a jOOQ QueryPart is being created from the DSL object.
// "create" is thus the entry point of the non-static query DSL
DSLContext create = DSL.using(connection, SQLDialect.ORACLE);

Your naming may differ, of course. For instance, you could name the "create" instance "db", instead.

Execution

When you're coding PL/SQL, T-SQL or some other procedural SQL language, SQL statements are always executed immediately at the semi-colon. This is not the case in jOOQ, because as an internal DSL, jOOQ can never be sure that your statement is complete until you call fetch() or execute(). The manual tries to apply fetch() and execute() as thoroughly as possible. If not, it is implied:

SELECT 1 FROM DUAL
UPDATE t SET v = 1

create.selectOne().fetch();
create.update(T).set(T.V, 1).execute();

Degree (arity)

jOOQ records (and many other API elements) have a degree N between 1 and 22. The variable degree of an API element is denoted as [N], e.g. Row[N] or Record[N]. The term "degree" is preferred over arity, as "degree" is the term used in the SQL standard, whereas "arity" is used more often in mathematics and relational theory.

Settings

jOOQ allows to override runtime behaviour using org.jooq.conf.Settings. If nothing is specified, the default runtime settings are assumed.

jOOQ has originally been created as a library for complete abstraction of JDBC and all database interaction. Various best practices that are frequently encountered in pre-existing software products are applied to this library. This includes:

This is the most simple of all use cases, allowing for construction of valid SQL for any database. In this use case, you will not use jOOQ's code generator and probably not even jOOQ's query execution facilities. Instead, you'll use jOOQ's query DSL API to wrap strings, literals and other user-defined objects into an object-oriented, type-safe AST modelling your SQL statements. An example is given here:

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.
// For simplicity reasons, we're using the API to construct case-insensitive object references, here.
String sql = create.select(field("BOOK.TITLE"), field("AUTHOR.FIRST_NAME"), field("AUTHOR.LAST_NAME"))
.from(table("BOOK"))
.join(table("AUTHOR"))
.on(field("BOOK.AUTHOR_ID").eq(field("AUTHOR.ID")))
.where(field("BOOK.PUBLISHED_IN").eq(1948))
.getSQL();

The SQL string built with the jOOQ query DSL can then be executed using JDBC directly, using Spring's JdbcTemplate, using Apache DbUtils and many other tools (note that since jOOQ uses PreparedStatement by default, this will generate a bind variable for "1948". Read more about bind variables here).

If you wish to use jOOQ only as a SQL builder, the following sections of the manual will be of interest to you:

SQL building: This section contains a lot of information about creating SQL statements using the jOOQ API

In addition to using jOOQ as a standalone SQL builder, you can also use jOOQ's code generation features in order to compile your SQL statements using a Java compiler against an actual database schema. This adds a lot of power and expressiveness to just simply constructing SQL using the query DSL and custom strings and literals, as you can be sure that all database artefacts actually exist in the database, and that their type is correct. An example is given here:

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.
String sql = create.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.from(BOOK)
.join(AUTHOR)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.where(BOOK.PUBLISHED_IN.eq(1948))
.getSQL();

The SQL string that you can generate as such can then be executed using JDBC directly, using Spring's JdbcTemplate, using Apache DbUtils and many other tools.

If you wish to use jOOQ only as a SQL builder with code generation, the following sections of the manual will be of interest to you:

SQL building: This section contains a lot of information about creating SQL statements using the jOOQ API

Instead of any tool mentioned in the previous chapters, you can also use jOOQ directly to execute your jOOQ-generated SQL statements. This will add a lot of convenience on top of the previously discussed API for typesafe SQL construction, when you can re-use the information from generated classes to fetch records and custom data types. An example is given here:

This is probably the most complete use-case for jOOQ: Use all of jOOQ's features. Apart from jOOQ's fluent API for query construction, jOOQ can also help you execute everyday CRUD operations. An example is given here:

jOOQ's Execute Listeners: jOOQ allows you to hook your custom execute listeners into jOOQ's SQL statement execution lifecycle in order to centrally coordinate any arbitrary operation performed on SQL being executed. Use this for logging, identity generation, SQL tracing, performance measurements, etc.

Logging: jOOQ has a standard DEBUG logger built-in, for logging and tracing all your executed SQL statements and fetched result sets

Stored Procedures: jOOQ supports stored procedures and functions of your favourite database. All routines and user-defined types are generated and can be included in jOOQ's SQL building API as function references.

Batch execution: Batch execution is important when executing a big load of SQL statements. jOOQ simplifies these operations compared to JDBC

Exporting and Importing: jOOQ ships with an API to easily export/import data in various formats

If you're a power user of your favourite, feature-rich database, jOOQ will help you access all of your database's vendor-specific features, such as OLAP features, stored procedures, user-defined types, vendor-specific SQL, functions, etc. Examples are given throughout this manual.

Note that only the jOOQ Open Source Edition is available from Maven Central. If you're using the jOOQ Professional Edition or the jOOQ Enterprise Edition, you will have to manually install jOOQ in your local Nexus, or in your local Maven cache. For more information, please refer to the licensing pages.

In this step, we're going to use jOOQ's command line tools to generate classes that map to the Author table we just created. More detailed information about how to set up the jOOQ code generator can be found here:jOOQ manual pages about setting up the code generator

The easiest way to generate a schema is to copy the jOOQ jar files (there should be 3) and the MySQL Connector jar file to a temporary directory. Then, create a library.xml that looks like this:

Replace the username with whatever user has the appropriate privileges to query the database meta data. You'll also want to look at the other values and replace as necessary. Here are the two interesting properties:

generator.target.package - set this to the parent package you want to create for the generated classes. The setting of test.generated will cause the test.generated.Author and test.generated.AuthorRecord to be created

generator.target.directory - the directory to output to.

Once you have the JAR files and library.xml in your temp directory, type this on a Windows machine:

Note: jOOQ will try loading the library.xml from your classpath. This is also why there is a trailing period (.) on the classpath. If the file cannot be found on the classpath, jOOQ will look on the file system from the current working directory.

Replace the filenames with your actual filenames. In this example, jOOQ 3.6.4 is being used. If everything has worked, you should see this in your console output:

First get an instance of DSLContext so we can write a simple SELECT query. We pass an instance of the MySQL connection to DSL. Note that the DSLContext doesn't close the connection. We'll have to do that ourselves.

We then use jOOQ's query DSL to return an instance of Result. We'll be using this result in the next step.

Note that only the jOOQ Open Source Edition is available from Maven Central. If you're using the jOOQ Professional Edition or the jOOQ Enterprise Edition, you will have to manually install jOOQ in your local Nexus, or in your local Maven cache. For more information, please refer to the licensing pages.

Create a minimal Spring configuration file

The above dependencies are configured together using a Spring Beans configuration:

Run queries using declarative transactions

Spring-TX has very powerful means to handle transactions declaratively, using the @Transactional annotation. The BookService that we had defined in the previous Spring configuration can be seen here:

public interface BookService {
/**
* Create a new book.
* <p>
* The implementation of this method has a bug, which causes this method to
* fail and roll back the transaction.
*/
@Transactional
void create(int id, int authorId, String title);
}

When performing database migrations, we at Data Geekery recommend using jOOQ with Flyway - Database Migrations Made Easy. In this chapter, we're going to look into a simple way to get started with the two frameworks.

Philosophy

There are a variety of ways how jOOQ and Flyway could interact with each other in various development setups. In this tutorial we're going to show just one variant of such framework team play - a variant that we find particularly compelling for most use cases.

The general philosophy behind the following approach can be summarised as this:

1. Database increment

2. Database migration

3. Code re-generation

4. Development

The four steps above can be repeated time and again, every time you need to modify something in your database. More concretely, let's consider:

1. Database increment - You need a new column in your database, so you write the necessary DDL in a Flyway script

2. Database migration - This Flyway script is now part of your deliverable, which you can share with all developers who can migrate their databases with it, the next time they check out your change

The above Flyway Maven plugin configuration will read and execute all database migration scripts from src/main/resources/db/migration prior to compiling Java source code. While the official Flyway documentation suggests that migrations be done in the compile phase, the jOOQ code generator relies on such migrations having been done prior to code generation.

This configuration will now read the FLYWAY_TEST schema and reverse-engineer it into the target/generated-sources/jooq-h2 directory, and within that, into the org.jooq.example.flyway.db.h2 package.

1. Database increments

Now, when we start developing our database. For that, we'll create database increment scripts, which we put into the src/main/resources/db/migration directory, as previously configured for the Flyway plugin. We'll add these files:

4. Development

Note that all of the previous steps are executed automatically, every time someone adds new migration scripts to the Maven module. For instance, a team member might have committed a new migration script, you check it out, rebuild and get the latest jOOQ-generated sources for your own development or integration-test database.

Now, that these steps are done, you can proceed writing your database queries. Imagine the following test case

Conclusion

This tutorial shows very easily how you can build a rock-solid development process using Flyway and jOOQ to prevent SQL-related errors very early in your development lifecycle - immediately at compile time, rather than in production!

In some use-cases, having a lean, single-tier server-side architecture is desirable. Typically, such architectures expose a RESTful API implementing client code and the UI using something like AngularJS.

In Java, the standard API for RESTful applications is JAX-RS, which is part of JEE 7, along with a standard JSON implementation. But you can use JAX-RS also outside of a JEE container. The following example shows how to set up a simple license server using these technologies:

Java 8 has introduced a great set of enhancements, among which lambda expressions and the new java.util.stream.Stream. These new constructs align very well with jOOQ's fluent API as can be seen in the following examples:

jOOQ and lambda expressions

jOOQ's RecordMapper API is fully Java-8-ready, which basically means that it is a SAM (Single Abstract Method) type, which can be instanciated using a lambda expression. Consider this example:

The above example shows how jOOQ's Result.map() method can receive a lambda expression that implements RecordMapper to map from jOOQ Records to your custom types.

jOOQ and the Streams API

jOOQ's Result type extends java.util.List, which opens up access to a variety of new Java features in Java 8. The following example shows how easy it is to transform a jOOQ Result containing INFORMATION_SCHEMA meta data to produce DDL statements:

One of the major improvements of Java 8 is the introduction of JavaFX into the JavaSE. With jOOQ and Java 8 Streams and lambdas, it is now very easy and idiomatic to transform SQL results into JavaFX XYChart.Series or other, related objects:

Creating a bar chart from a jOOQ Result

As we've seen in the previous section about jOOQ and Java 8, jOOQ integrates seamlessly with Java 8's Streams API. The fluent style can be maintained throughout the data transformation chain.

In this example, we're going to use Open Data from the world bank to show a comparison of countries GDP and debts:

The above example uses basic SQL-92 syntax where the countries are ordered using aggregate information from a nested SELECT, which is supported in all databases. If you're using a database that supports window functions, e.g. PostgreSQL or any commercial database, you could have also written a simpler query like this:00

Note that while Groovy supports some means of operator overloading, we think that these means should be avoided in a jOOQ integration. For instance, a + b in Groovy maps to a formal a.plus(b) method invocation, and jOOQ provides the required synonyms in its API to help you write such expressions. Nonetheless, Groovy only offers little typesafety, and as such, operator overloading can lead to many runtime issues.

Another caveat of Groovy operator overloading is the fact that operators such as == or >= map to a.equals(b), a.compareTo(b) == 0, a.compareTo(b) >= 0 respectively. This behaviour does not make sense in a fluent API such as jOOQ.

Mapping the jOOQ API onto these alternative query languages would be a very poor fit and a leaky abstraction. We believe in the power and expressivity of the SQL standard and its various dialects. Databases that extend this standard too much, or implement it not thoroughly enough are often not suitable targets for jOOQ. It would be better to build a new, dedicated API for just that one particular query language.

jOOQ is about SQL, and about SQL alone. Read more about our visions in the manual's preface.

Dependencies are a big hassle in modern software. Many libraries depend on other, non-JDK library parts that come in different, incompatible versions, potentially causing trouble in your runtime environment. jOOQ has no external dependencies on any third-party libraries.

In order to build jOOQ (Open Source Edition) yourself, please download the sources from https://github.com/jOOQ/jOOQ and use Maven to build jOOQ, preferably in Eclipse. jOOQ requires Java 6+ to compile and run.

jOOQ follows the rules of semantic versioning according to http://semver.org quite strictly. Those rules impose a versioning scheme [X].[Y].[Z] that can be summarised as follows:

If a patch release includes bugfixes, performance improvements and API-irrelevant new features, [Z] is incremented by one.

If a minor release includes backwards-compatible, API-relevant new features, [Y] is incremented by one and [Z] is reset to zero.

If a major release includes backwards-incompatible, API-relevant new features, [X] is incremented by one and [Y], [Z] are reset to zero.

jOOQ's understanding of backwards-compatibility

Backwards-compatibility is important to jOOQ. You've chosen jOOQ as a strategic SQL engine and you don't want your SQL to break. That is why there is at most one major release per year, which changes only those parts of jOOQ's API and functionality, which were agreed upon on the user group. During the year, only minor releases are shipped, adding new features in a backwards-compatible way

However, there are some elements of API evolution that would be considered backwards-incompatible in other APIs, but not in jOOQ. As discussed later on in the section about jOOQ's query DSL API, much of jOOQ's API is indeed an internal domain-specific language implemented mostly using Java interfaces. Adding language elements to these interfaces means any of these actions:

Adding methods to the interface

Overloading methods for convenience

Changing the type hierarchy of interfaces

It becomes obvious that it would be impossible to add new language elements (e.g. new SQL functions, new SELECT clauses) to the API without breaking any client code that actually implements those interfaces. Hence, the following rule should be observed:

jOOQ's DSL interfaces should not be implemented by client code! Extend only those extension points that are explicitly documented as "extendable" (e.g. custom QueryParts)

jOOQ-codegen and jOOQ-meta

While a reasonable amount of care is spent to maintain these two modules under the rules of semantic versioning, it may well be that minor releases introduce backwards-incompatible changes. This will be announced in the respective release notes and should be the exception.

SQL is a declarative language that is hard to integrate into procedural, object-oriented, functional or any other type of programming languages. jOOQ's philosophy is to give SQL the credit it deserves and integrate SQL itself as an "internal domain specific language" directly into Java.

With this philosophy in mind, SQL building is the main feature of jOOQ. All other features (such as SQL execution and code generation) are mere convenience built on top of jOOQ's SQL building capabilities.

This section explains all about the various syntax elements involved with jOOQ's SQL building capabilities. For a complete overview of all syntax elements, please refer to the manual's sections about SQL to DSL mapping rules as well as jOOQ's BNF notation

The static query DSL API

With jOOQ 2.0, static factory methods have been introduced in order to make client code look more like SQL. Ideally, when working with jOOQ, you will simply static import all methods from the DSL class:

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

Note, that when working with Eclipse, you could also add the DSL to your favourites. This will allow to access functions even more fluently:

concat(trim(FIRST_NAME), trim(LAST_NAME));
// ... which is in fact the same as:
DSL.concat(DSL.trim(FIRST_NAME), DSL.trim(LAST_NAME));

There are a couple of subclasses for the general query DSL. Each SQL dialect has its own dialect-specific DSL. For instance, if you're only using the MySQL dialect, you can choose to reference the MySQLDSL instead of the standard DSL:

The advantage of referencing a dialect-specific DSL lies in the fact that you have access to more proprietary RDMBS functionality. This may include:

MySQL's encryption functions

PL/SQL constructs, pgplsql, or any other dialect's ROUTINE-language (maybe in the future)

DSLContext references a org.jooq.Configuration, an object that configures jOOQ's behaviour when executing queries (see SQL execution for more details). Unlike the static DSL, the DSLContext allow for creating SQL statements that are already "configured" and ready for execution.

java.sql.Connection : An optional JDBC Connection that will be re-used for the whole lifecycle of your Configuration (see Connection vs. DataSource for more details). For simplicity, this is the use-case referenced from this manual, most of the time.

java.sql.DataSource : An optional JDBC DataSource that will be re-used for the whole lifecycle of your Configuration. If you prefer using DataSources over Connections, jOOQ will internally fetch new Connections from your DataSource, conveniently closing them again after query execution. This is particularly useful in J2EE or Spring contexts (see Connection vs. DataSource for more details)

org.jooq.ConnectionProvider : A custom abstraction that is used by jOOQ to "acquire" and "release" connections. jOOQ will internally "acquire" new Connections from your ConnectionProvider, conveniently "releasing" them again after query execution. (see Connection vs. DataSource for more details)

Wrapping a Configuration object, a DSLContext can construct statements, for later execution. An example is given here:

// The DSLContext is "configured" with a Connection and a SQLDialect
DSLContext create = DSL.using(connection, dialect);
// This select statement contains an internal reference to the DSLContext's Configuration:
Select<?> select = create.selectOne();
// Using the internally referenced Configuration, the select statement can now be executed:
Result<?> result = select.fetch();

Note that you do not need to keep a reference to a DSLContext. You may as well inline your local variable, and fluently execute a SQL statement as such:

While jOOQ tries to represent the SQL standard as much as possible, many features are vendor-specific to a given database and to its "SQL dialect". jOOQ models this using the org.jooq.SQLDialect enum type.

Some parts of the jOOQ API are officially supported only by a given subset of the supported SQL dialects. For instance, the Oracle CONNECT BY clause, which is supported by the Oracle and CUBRID databases, is annotated with a org.jooq.Support annotation, as such:

jOOQ API methods which are not annotated with the org.jooq.Support annotation, or which are annotated with the Support annotation, but without any SQL dialects can be safely used in all SQL dialects. An example for this is the SELECT statement factory method:

jOOQ's SQL clause emulation capabilities

The aforementioned Support annotation does not only designate, which databases natively support a feature. It also indicates that a feature is emulated by jOOQ for some databases lacking this feature. An example of this is the DISTINCT predicate, a predicate syntax defined by SQL:1999 and implemented only by H2, HSQLDB, and Postgres:

A IS DISTINCT FROM B

Nevertheless, the IS DISTINCT FROM predicate is supported by jOOQ in all dialects, as its semantics can be expressed with an equivalent CASE expression. For more details, see the manual's section about the DISTINCT predicate.

jOOQ and the Oracle SQL dialect

Oracle SQL is much more expressive than many other SQL dialects. It features many unique keywords, clauses and functions that are out of scope for the SQL standard. Some examples for this are

In jOOQ 3.1, the notion of a SQLDialect.family() was introduced, in order to group several similar SQL dialects into a common family. An example for this is SQL Server, which is supported by jOOQ in various versions:

SQL Server: The "version-less" SQL Server version. This always maps to the latest supported version of SQL Server

In the above list, SQLSERVER is both a dialect and a family of three dialects. This distinction is used internally by jOOQ to distinguish whether to use the OFFSET .. FETCH clause (SQL Server 2012), or whether to emulate it using ROW_NUMBER() OVER() (SQL Server 2008).

Interact with JDBC Connections

While you can use jOOQ for SQL building only, you can also run queries against a JDBC java.sql.Connection. Internally, jOOQ creates java.sql.Statement or java.sql.PreparedStatement objects from such a Connection, in order to execute statements. The normal operation mode is to provide a Configuration with a JDBC Connection, whose lifecycle you will control yourself. This means that jOOQ will not actively close connections, rollback or commit transactions.

Note, in this case, jOOQ will internally use a org.jooq.impl.DefaultConnectionProvider, which you can reference directly if you prefer that. The DefaultConnectionProvider exposes various transaction-control methods, such as commit(), rollback(), etc.

Interact with JDBC DataSources

If you're in a J2EE or Spring context, however, you may wish to use a javax.sql.DataSource instead. Connections obtained from such a DataSource will be closed after query execution by jOOQ. The semantics of such a close operation should be the returning of the connection into a connection pool, not the actual closing of the underlying connection. Typically, this makes sense in an environment using distributed JTA transactions. An example of using DataSources with jOOQ can be seen in the tutorial section about using jOOQ with Spring.

Inject custom behaviour

If your specific environment works differently from any of the above approaches, you can inject your own custom implementation of a ConnectionProvider into jOOQ. This is the API contract you have to fulfil:

The jOOQ Configuration allows for some optional configuration elements to be used by advanced users. The org.jooq.conf.Settings class is a JAXB-annotated type, that can be provided to a Configuration in several ways:

In the DSLContext constructor (DSL.using()). This will override default settings below

Example

For example, if you want to indicate to jOOQ, that it should inline all bind variables, and execute static java.sql.Statement instead of binding its variables to java.sql.PreparedStatement, you can do so by creating the following DSLContext:

More details

By default, jOOQ fully qualifies all objects with their catalog and schema names, if such qualification is made available by the code generator. For instance, the following SQL statement containing full qualification may be produced by jOOQ code with seemingly no qualification:

-- Full qualification on columns and tables
SELECT schema.table.column
FROM schema.table

By turning off the rendering of full qualification as can be seen above, it will be possible to use code generated from one schema on an entirely different schema of the same structure, e.g. for multitenancy purposes.

Mapping your DEV schema to a productive environment

You may wish to design your database in a way that you have several instances of your schema. This is useful when you want to cleanly separate data belonging to several customers / organisation units / branches / users and put each of those entities' data in a separate database or schema.

In our AUTHOR example this would mean that you provide a book reference database to several companies, such as My Book World and Books R Us. In that case, you'll probably have a schema setup like this:

DEV: Your development schema. This will be the schema that you base code generation upon, with jOOQ

MY_BOOK_WORLD: The schema instance for My Book World

BOOKS_R_US: The schema instance for Books R Us

Mapping DEV to MY_BOOK_WORLD with jOOQ

When a user from My Book World logs in, you want them to access the MY_BOOK_WORLD schema using classes generated from DEV. This can be achieved with the org.jooq.conf.RenderMapping class, that you can equip your Configuration's settings with. Take the following example:

The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement:

SELECT *
FROM MY_BOOK_WORLD.AUTHOR

DSL.using(connection, dialect, settings)
.selectFrom(DEV.AUTHOR)

This works because AUTHOR was generated from the DEV schema, which is mapped to the MY_BOOK_WORLD schema by the above settings.

Mapping of tables

Not only schemata can be mapped, but also tables. If you are not the owner of the database your application connects to, you might need to install your schema with some sort of prefix to every table. In our examples, this might mean that you will have to map DEV.AUTHOR to something MY_BOOK_WORLD.MY_APP__AUTHOR, where MY_APP__ is a prefix applied to all of your tables. This can be achieved by creating the following mapping:

The query executed with a Configuration equipped with the above mapping will in fact produce this SQL statement:

SELECT * FROM DEV.MY_APP__AUTHOR

Table mapping and schema mapping can be applied independently, by specifying several MappedSchema entries in the above configuration. jOOQ will process them in order of appearance and map at first match. Note that you can always omit a MappedSchema's output value, in case of which, only the table mapping is applied. If you omit a MappedSchema's input value, the table mapping is applied to all schemata!

Quoting has the following effect on identifiers in most (but not all) databases:

It allows for using reserved names as object names, e.g. a table called "FROM" is usually possible only when quoted.

It allows for using special characters in object names, e.g. a column called "FIRST NAME" can be achieved only with quoting.

It turns what are mostly case-insensitive identifiers into case-sensitive ones, e.g. "name" and "NAME" are different identifiers, whereas name and NAME are not. Please consider your database manual to learn what the proper default case and default case sensitivity is.

The renderNameStyle setting allows for overriding the name of all identifiers in jOOQ to a consistent style. Possible options are:

QUOTED (the default): This will generate all names in their proper case with quotes around them.

AS_IS: This will generate all names in their proper case without quotes.

Bind values or bind parameters come in different flavours in different SQL databases. JDBC standardises on their syntax by allowing only ? (question mark) characters as placeholders for bind variables. Thus, jOOQ, by default, generates ? placeholders for JDBC consumptions.

Users who wish to use jOOQ with a different backend than JDBC can specify that all jOOQ bind values, including indexed parameters and named parameters generate alternative strings, other than ?. These are the current options:

By default, all records fetched through jOOQ are "attached" to the configuration that created them. This allows for features like updatable records as can be seen here:

AuthorRecord author =
DSL.using(configuration) // This configuration will be attached to any record produced by the below query.
.selectFrom(AUTHOR)
.where(AUTHOR.ID.eq(1))
.fetchOne();
author.setLastName("Smith");
author.store(); // This store call operates on the "attached" configuration.

In some cases (e.g. when serialising records), it may be desirable not to attach the Configuration that created a record to the record. This can be achieved with the attachRecords setting:

In most database design guidelines, primary key values are expected to never change - an assumption that is essential to a normalised database.

As always, there are exceptions to these rules, and users may wish to allow for updatable primary key values in the updatable records feature (note: any value can always be updated through ordinary update statements). An example:

AuthorRecord author =
DSL.using(configuration) // This configuration will be attached to any record produced by the below query.
.selectFrom(AUTHOR)
.where(AUTHOR.ID.eq(1))
.fetchOne();
author.setId(2);
author.store(); // The behaviour of this store call is governed by the updatablePrimaryKeys flag

The above store call depends on the value of the updatablePrimaryKeys flag:

false (the default): Since immutability of primary keys is assumed, the store call will create a new record (a copy) with the new primary key value.

true: Since mutablity of primary keys is allowed, the store call will change the primary key value from 1 to 2.

Some databases (mainly MySQL and MariaDB) unfortunately chose to go an alternative, non-SQL-standard route when escaping string literals. Here's an example of how to escape a string containing apostrophes in different dialects:

SELECT 'I''m sure this is OK' AS val -- Standard SQL escaping of apostrophe by doubling it.
SELECT 'I\'m certain this causes trouble' AS val -- Vendor-specific escaping of apostrophe by using a backslash.

As most databases don't support backslash escaping (and MySQL also allows for turning it off!), jOOQ by default also doesn't support it when inlining bind variables. However, this can lead to SQL injection vulnerabilities and syntax errors when not dealing with it carefully!

This feature is turned on by default and for historic reasons for MySQL and MariaDB.

DEFAULT (the - surprise! - default): Turns the feature ON for MySQL and MariaDB and OFF for all other dialects

This setting is useful mostly for the Oracle database, which implements a feature called scalar subquery caching, which is a good tool to avoid the expensive PL/SQL-to-SQL context switch when predicates make use of stored function calls.

With this setting in place, all stored function calls embedded in SQL statements will be wrapped in a scalar subquery:

jOOQ currently supports 5 types of SQL statements. All of these statements are constructed from a DSLContext instance with an optional JDBC Connection or DataSource. If supplied with a Connection or DataSource, they can be executed. Depending on the query type, executed queries can return results.

jOOQ ships with its own DSL (or Domain Specific Language) that emulates SQL in Java. This means, that you can write SQL statements almost as if Java natively supported it, just like .NET's C# does with LINQ to SQL.

Here is an example to illustrate what that means:

-- Select all books by authors born after 1920,
-- named "Paulo" from a catalogue:
SELECT *
FROM author a
JOIN book b ON a.id = b.author_id
WHERE a.year_of_birth > 1920
AND a.first_name = 'Paulo'
ORDER BY b.title

We'll see how the aliasing works later in the section about aliased tables

jOOQ as an internal domain specific language in Java (a.k.a. the DSL API)

Many other frameworks have similar APIs with similar feature sets. Yet, what makes jOOQ special is its informal BNF notation modelling a unified SQL dialect suitable for many vendor-specific dialects, and implementing that BNF notation as a hierarchy of interfaces in Java. This concept is extremely powerful, when using jOOQ in modern IDEs with syntax completion. Not only can you code much faster, your SQL code will be compile-checked to a certain extent. An example of a DSL query equivalent to the previous one is given here:

History of SQL building and incremental query building (a.k.a. the model API)

Historically, jOOQ started out as an object-oriented SQL builder library like any other. This meant that all queries and their syntactic components were modeled as so-called QueryParts, which delegate SQL rendering and variable binding to child components. This part of the API will be referred to as the model API (or non-DSL API), which is still maintained and used internally by jOOQ for incremental query building. An example of incremental query building is given here:

This query is equivalent to the one shown before using the DSL syntax. In fact, internally, the DSL API constructs precisely this SelectQuery object. Note, that you can always access the SelectQuery object to switch between DSL and model APIs:

Mutability

Note, that for historic reasons, the DSL API mixes mutable and immutable behaviour with respect to the internal representation of the QueryPart being constructed. While creating conditional expressions, column expressions (such as functions) assumes immutable behaviour, creating SQL statements does not. In other words, the following can be said:

Recursive common table expressions

The various SQL dialects do not agree on the use of RECURSIVE when writing recursive common table expressions. When using jOOQ, always use the DSLContext.withRecursive() or DSL.withRecursive() methods, and jOOQ will render the RECURSIVE keyword, if needed.

When you don't just perform CRUD (i.e. SELECT * FROM your_table WHERE ID = ?), you're usually generating new record types using custom projections. With jOOQ, this is as intuitive, as if using SQL directly. A more or less complete example of the "standard" SQL syntax, plus some extensions, is provided by a query like this:

SELECT from a complex table expression

-- get all authors' first and last names, and the number
-- of books they've written in German, if they have written
-- more than five books in German in the last three years
-- (from 2011), and sort those authors by last names
-- limiting results to the second and third row, locking
-- the rows for a subsequent update... whew!
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
WHERE BOOK.LANGUAGE = 'DE'
AND BOOK.PUBLISHED > '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
LIMIT 2
OFFSET 1
FOR UPDATE

Details about the various clauses of this query will be provided in subsequent sections.

SELECT from single tables

A very similar, but limited API is available, if you want to select from single tables in order to retrieve TableRecords or even UpdatableRecords. The decision, which type of select to create is already made at the very first step, when you create the SELECT statement with the DSL or DSLContext types:

As you can see, there is no way to further restrict/project the selected fields. This just selects all known TableFields in the supplied Table, and it also binds <R extends Record> to your Table's associated Record. An example of such a Query would then be:

In most parts of this manual, it is assumed that you do not use the "reduced" SELECT API. For more information about the simple SELECT API, see the manual's section about fetching strongly or weakly typed records.

See more details about functions and expressions in the manual's section about Column expressions

The SELECT DISTINCT clause

The DISTINCT keyword can be included in the method name, constructing a SELECT clause

SELECT DISTINCT BOOK.TITLE

Select<?> select1 = create.selectDistinct(BOOK.TITLE).fetch();

SELECT *

jOOQ does not explicitly support the asterisk operator in projections. However, you can omit the projection as in these examples:

// Explicitly selects all columns available from BOOK
create.select().from(BOOK).fetch();
// Explicitly selects all columns available from BOOK and AUTHOR
create.select().from(BOOK, AUTHOR).fetch();
create.select().from(BOOK).crossJoin(AUTHOR).fetch();
// Renders a SELECT * statement, as columns are unknown to jOOQ
create.select().from(table(name("BOOK"))).fetch();

Typesafe projections with degree up to 22

Since jOOQ 3.0, records and row value expressions up to degree 22 are now generically typesafe. This is reflected by an overloaded SELECT (and SELECT DISTINCT) API in both DSL and DSLContext. An extract from the DSL type:

JOIN ON KEY, convenience provided by jOOQ

Surprisingly, the SQL standard does not allow to formally JOIN on well-known foreign key relationship information. Naturally, when you join BOOK to AUTHOR, you will want to do that based on the BOOK.AUTHOR_ID foreign key to AUTHOR.ID primary key relation. Not being able to do this in SQL leads to a lot of repetitive code, re-writing the same JOIN predicate again and again - especially, when your foreign keys contain more than one column. With jOOQ, when you use code generation, you can use foreign key constraint information in JOIN expressions as such:

SELECT *
FROM AUTHOR
JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID

create.select()
.from(AUTHOR)
.join(BOOK).onKey()
.fetch();

In case of ambiguity, you can also supply field references for your foreign keys, or the generated foreign key reference to the onKey() method.

Note that formal support for the Sybase JOIN ON KEY syntax is on the roadmap.

The JOIN USING syntax

Most often, you will provide jOOQ with JOIN conditions in the JOIN .. ON clause. SQL supports a different means of specifying how two tables are to be joined. This is the JOIN .. USING clause. Instead of a condition, you supply a set of fields whose names are common to both tables to the left and right of a JOIN operation. This can be useful when your database schema has a high degree of relational normalisation. An example:

-- Assuming that both tables contain AUTHOR_ID columns
SELECT *
FROM AUTHOR
JOIN BOOK USING (AUTHOR_ID)

In schemas with high degrees of normalisation, you may also choose to use NATURAL JOIN, which takes no JOIN arguments as it joins using all fields that are common to the table expressions to the left and to the right of the JOIN operator. An example:

Oracle's partitioned OUTER JOIN

Oracle SQL ships with a special syntax available for OUTER JOIN clauses. According to the Oracle documentation about partitioned outer joins this can be used to fill gaps for simplified analytical calculations. jOOQ only supports putting the PARTITION BY clause to the right of the OUTER JOIN clause. The following example will create at least one record per AUTHOR and per existing value in BOOK.PUBLISHED_IN, regardless if an AUTHOR has actually published a book in that year.

SELECT *
FROM AUTHOR
LEFT OUTER JOIN BOOK
PARTITION BY (PUBLISHED_IN)
ON BOOK.AUTHOR_ID = AUTHOR.ID

T-SQL's CROSS APPLY and OUTER APPLY

T-SQL has long known what the SQL standard calls lateral derived tables, lateral joins using the APPLY keyword. To every row resulting from the table expression on the left, we apply the table expression on the right. This is extremely useful for table-valued functions, which are also supported by jOOQ. Some examples:

The WHERE clause can be used for JOIN or filter predicates, in order to restrict the data returned by the table expressions supplied to the previously specified from clause and join clause. Here is an example:

The above syntax is convenience provided by jOOQ, allowing you to connect the org.jooq.Condition supplied in the WHERE clause with another condition using an AND operator. You can of course also create a more complex condition and supply that to the WHERE clause directly (observe the different placing of parentheses). The results will be the same:

The Oracle database knows a very succinct syntax for creating hierarchical queries: the CONNECT BY clause, which is fully supported by jOOQ, including all related functions and pseudo-columns. A more or less formal definition of this clause is given here:

Some of the supported functions and pseudo-columns are these (available from the DSL):

LEVEL

CONNECT_BY_IS_CYCLE

CONNECT_BY_IS_LEAF

CONNECT_BY_ROOT

SYS_CONNECT_BY_PATH

PRIOR

Note that this syntax is also supported in the CUBRID database and might be emulated in other dialects supporting common table expressions in the future.

ORDER SIBLINGS

The Oracle database allows for specifying a SIBLINGS keyword in the ORDER BY clause. Instead of ordering the overall result, this will only order siblings among each other, keeping the hierarchy intact. An example is given here:

SELECT DIRECTORY.NAME
FROM DIRECTORY
CONNECT BY
PRIOR DIRECTORY.ID = DIRECTORY.PARENT_ID
START WITH DIRECTORY.PARENT_ID IS NULL
ORDER SIBLINGS BY 1

GROUP BY can be used to create unique groups of data, to form aggregations, to remove duplicates and for other reasons. It will transform your previously defined set of table expressions, and return only one record per unique group as specified in this clause. For instance, you can group books by BOOK.AUTHOR_ID:

Note, as defined in the SQL standard, when grouping, you may no longer project any columns that are not a formal part of the GROUP BY clause, or aggregate functions.

MySQL's deviation from the SQL standard

MySQL has a peculiar way of not adhering to this standard behaviour. This is documented in the MySQL manual. In short, with MySQL, you can also project any other field that is not part of the GROUP BY clause. The projected values will just be arbitrary values from within the group. You cannot rely on any ordering. For example:

According to the SQL standard, you may omit the GROUP BY clause and still issue a HAVING clause. This will implicitly GROUP BY (). jOOQ also supports this syntax. The following example selects one record, only if there are at least 4 books in the books table:

Even if only PostgreSQL and Sybase SQL Anywhere natively support this great feature, jOOQ can emulate it by expanding any org.jooq.WindowDefinition and org.jooq.WindowSpecification types that you pass to the window() method - if the database supports window functions at all.

Ordering by field index

The SQL standard allows for specifying integer literals (literals, not bind values!) to reference column indexes from the projection (SELECT clause). This may be useful if you do not want to repeat a lengthy expression, by which you want to order - although most databases also allow for referencing aliased column references in the ORDER BY clause. An example of this is given here:

If your database doesn't support this syntax, jOOQ emulates it using a CASE expression as follows

SELECT
AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
ORDER BY LAST_NAME ASC,
CASE WHEN FIRST_NAME IS NULL
THEN 1 ELSE 0 END ASC,
FIRST_NAME ASC

Ordering using CASE expressions

Using CASE expressions in SQL ORDER BY clauses is a common pattern, if you want to introduce some sort indirection / sort mapping into your queries. As with SQL, you can add any type of column expression into your ORDER BY clause. For instance, if you have two favourite books that you always want to appear on top, you could write:

SELECT *
FROM BOOK
ORDER BY CASE TITLE
WHEN '1984' THEN 0
WHEN 'Animal Farm' THEN 1
ELSE 2 END ASC

Of course, you can combine this feature with the previously discussed NULLS FIRST / NULLS LAST feature. So, if in fact these two books are the ones you like least, you can put all NULLS FIRST (all the other books):

jOOQ's understanding of SELECT .. ORDER BY

The SQL standard defines that a "query expression" can be ordered, and that query expressions can contain UNION, INTERSECT and EXCEPT clauses, whose subqueries cannot be ordered. While this is defined as such in the SQL standard, many databases allowing for the LIMIT clause in one way or another, do not adhere to this part of the SQL standard. Hence, jOOQ allows for ordering all SELECT statements, regardless whether they are constructed as a part of a UNION or not. Corner-cases are handled internally by jOOQ, by introducing synthetic subselects to adhere to the correct syntax, where this is needed.

Oracle's ORDER SIBLINGS BY clause

While being extremely useful for every application that does pagination, or just to limit result sets to reasonable sizes, this clause is not yet part of any SQL standard (up until SQL:2008). Hence, there exist a variety of possible implementations in various SQL dialects, concerning this limit clause. jOOQ chose to implement the LIMIT .. OFFSET clause as understood and supported by MySQL, H2, HSQLDB, Postgres, and SQLite. Here is an example of how to apply limits with jOOQ:

create.select().from(BOOK).limit(1).offset(2).fetch();

This will limit the result to 1 books starting with the 2nd book (starting at offset 0!). limit() is supported in all dialects, offset() in all but Sybase ASE, which has no reasonable means to emulate it. This is how jOOQ trivially emulates the above query in various SQL dialects with native OFFSET pagination support:

SQL Server's ORDER BY, TOP and subqueries

As can be seen in the above example, writing correct SQL can be quite tricky, depending on the SQL dialect. For instance, with SQL Server, you cannot have an ORDER BY clause in a subquery, unless you also have a TOP clause. This is illustrated by the fact that jOOQ renders a TOP 100 PERCENT clause for you. The same applies to the fact that ROW_NUMBER() OVER() needs an ORDER BY windowing clause, even if you don't provide one to the jOOQ query. By default, jOOQ adds ordering by the first column of your projection.

The previous chapter talked about OFFSET pagination using LIMIT .. OFFSET, or OFFSET .. FETCH or some other vendor-specific variant of the same. This can lead to significant performance issues when reaching a high page number, as all unneeded records need to be skipped by the database.

A much faster and more stable way to perform pagination is the so-called keyset pagination method also called seek method. jOOQ supports a synthetic seek() clause, that can be used to perform keyset pagination. Imagine we have these data:

Now, if we want to display page 6 to the user, instead of going to page 6 by using a record OFFSET, we could just fetch the record strictly after the last record on page 5, which yields the values (533, 2). This is how you would do it with SQL or with jOOQ:

For inter-process synchronisation and other reasons, you may choose to use the SELECT .. FOR UPDATE clause to indicate to the database, that a set of cells or records should be locked by a given transaction for subsequent updates. With jOOQ, this can be achieved as such:

FOR UPDATE in CUBRID and SQL Server

The SQL standard specifies a FOR UPDATE clause to be applicable for cursors. Most databases interpret this as being applicable for all SELECT statements. An exception to this rule are the CUBRID and SQL Server databases, that do not allow for any FOR UPDATE clause in a regular SQL SELECT statement. jOOQ emulates the FOR UPDATE behaviour, by locking record by record with JDBC. JDBC allows for specifying the flags TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE for any statement, and then using ResultSet.updateXXX() methods to produce a cell-lock / row-lock. Here's a simplified example in JDBC:

The main drawback of this approach is the fact that the database has to maintain a scrollable cursor, whose records are locked one by one. This can cause a major risk of deadlocks or race conditions if the JDBC driver can recover from the unsuccessful locking, if two Java threads execute the following statements:

Optimistic locking in jOOQ

SQL allows to perform set operations as understood in standard set theory on result sets. These operations include unions, intersections, subtractions. For two subselects to be combinable by such a set operator, each subselect must return a table expression of the same degree and type.

UNION and UNION ALL

These operators combine two results into one. While UNION removes all duplicate records resulting from this combination, UNION ALL leaves subselect results as they are. Typically, you should prefer UNION ALL over UNION, if you don't really need to remove duplicates. The following example shows how to use such a UNION operation in jOOQ.

SELECT * FROM BOOK WHERE ID = 3
UNION ALL
SELECT * FROM BOOK WHERE ID = 5

INTERSECT [ ALL ] and EXCEPT [ ALL ]

INTERSECT is the operation that produces only those values that are returned by both subselects. EXCEPT is the operation that returns only those values that are returned exclusively in the first subselect. Both operators will remove duplicates from their results. The SQL standard allows to specify the ALL keyword for both of these operators as well, but this is hardly supported in any database. jOOQ does not support INTERSECT ALL, EXEPT ALL operations either.

jOOQ's set operators and how they're different from standard SQL

As previously mentioned in the manual's section about the ORDER BY clause, jOOQ has slightly changed the semantics of these set operators. While in SQL, a subselect may not contain any ORDER BY clause or LIMIT clause (unless you wrap the subselect into a nested SELECT), jOOQ allows you to do so. In order to select both the youngest and the oldest author from the database, you can issue the following statement with jOOQ (rendered to the MySQL dialect):

(SELECT * FROM AUTHOR
ORDER BY DATE_OF_BIRTH ASC LIMIT 1)
UNION
(SELECT * FROM AUTHOR
ORDER BY DATE_OF_BIRTH DESC LIMIT 1)
ORDER BY 1

Projection typesafety for degrees between 1 and 22

Two subselects that are combined by a set operator are required to be of the same degree and, in most databases, also of the same type. jOOQ 3.0's introduction of Typesafe Record[N] types helps compile-checking these constraints:

If you are closely coupling your application to an Oracle (or CUBRID) database, you might need to be able to pass hints of the form /*+HINT*/ with your SQL statements to the Oracle database. For example:

SELECT /*+ALL_ROWS*/ FIRST_NAME, LAST_NAME
FROM AUTHOR

This can be done in jOOQ using the .hint() clause in your SELECT statement:

Note that you can pass any string in the .hint() clause. If you use that clause, the passed string will always be put in between the SELECT [DISTINCT] keywords and the actual projection list. This can be useful in other databases too, such as MySQL, for instance:

SQL has a lexical and a logical order of SELECT clauses. The lexical order of SELECT clauses is inspired by the English language. As SQL statements are commands for the database, it is natural to express a statement in an imperative tense, such as "SELECT this and that!".

Logical SELECT clause order

The logical order of SELECT clauses, however, does not correspond to the syntax. In fact, the logical order is this:

As can be seen, databases have to logically reorder a SQL statement in order to determine the best execution plan.

Alternative syntaxes: LINQ, SLICK

Some "higher-level" abstractions, such as C#'s LINQ or Scala's SLICK try to inverse the lexical order of SELECT clauses to what appears to be closer to the logical order. The obvious advantage of moving the SELECT clause to the end is the fact that the projection type, which is the record type returned by the SELECT statement can be re-used more easily in the target environment of the internal domain specific language.

While this looks like a good idea at first, it only complicates translation to more advanced SQL statements while impairing readability for those users that are used to writing SQL. jOOQ is designed to look just like SQL. This is specifically true for SLICK, which not only changed the SELECT clause order, but also heavily "integrated" SQL clauses with the Scala language.

For these reasons, the jOOQ DSL API is modelled in SQL's lexical order.

INSERT .. VALUES with a single row

Records can either be supplied using a VALUES() constructor, or a SELECT statement. jOOQ supports both types of INSERT statements. An example of an INSERT statement using a VALUES() constructor is given here:

A lesser-known syntactic feature of SQL is the INSERT .. DEFAULT VALUES statement, where a single record is inserted, containing only DEFAULT values for every row. It is written as such:

INSERT INTO AUTHOR
DEFAULT VALUES;

create.insertInto(AUTHOR)
.defaultValues()
.execute();

This can make a lot of sense in situations where you want to "reserve" a row in the database for an subsequent UPDATE statement within the same transaction. Or if you just want to send an event containing trigger-generated default values, such as IDs or timestamps.

The DEFAULT VALUES clause is not supported in all databases, but jOOQ can emulate it using the equivalent statement:

MySQL (and some other RDBMS) allow for using a non-SQL-standard, UPDATE-like syntax for INSERT statements. This is also supported in jOOQ (and emulated for all databases), should you prefer that syntax. The above INSERT statement can also be expressed as follows:

The synthetic ON DUPLICATE KEY UPDATE clause

The MySQL database supports a very convenient way to INSERT or UPDATE a record. This is a non-standard extension to the SQL syntax, which is supported by jOOQ and emulated in other RDBMS, where this is possible (i.e. if they support the SQL standard MERGE statement). Here is an example how to use the ON DUPLICATE KEY UPDATE clause:

// Add a new author called "Koontz" with ID 3.
// If that ID is already present, update the author's name
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
.values(3, "Koontz")
.onDuplicateKeyUpdate()
.set(AUTHOR.LAST_NAME, "Koontz")
.execute();

The synthetic ON DUPLICATE KEY IGNORE clause

The MySQL database also supports an INSERT IGNORE INTO clause. This is supported by jOOQ using the more convenient SQL syntax variant of ON DUPLICATE KEY IGNORE, which can be equally emulated in other databases using a MERGE statement:

The Postgres database has native support for an INSERT .. RETURNING clause. This is a very powerful concept that is emulated for all other dialects using JDBC's getGeneratedKeys() method. Take this example:

// Add another author, with a generated ID
Record<?> record =
create.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values("Charlotte", "Roche")
.returning(AUTHOR.ID)
.fetchOne();
System.out.println(record.getValue(AUTHOR.ID));
// For some RDBMS, this also works when inserting several values
// The following should return a 2x2 table
Result<?> result =
create.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values("Johann Wolfgang", "von Goethe")
.values("Friedrich", "Schiller")
// You can request any field. Also trigger-generated values
.returning(AUTHOR.ID, AUTHOR.CREATION_DATE)
.fetch();

Some databases have poor support for returning generated keys after INSERTs. In those cases, jOOQ might need to issue another SELECT statement in order to fetch an @@identity value. Be aware, that this can lead to race-conditions in those databases that cannot properly return generated ID values. For more information, please consider the jOOQ Javadoc for the returning() clause.

The UPDATE statement is used to modify one or several pre-existing records in a database table. UPDATE statements are only possible on single tables. Support for multi-table updates will be implemented in the near future. An example update query is given here:

UPDATE .. RETURNING

The Firebird and Postgres databases support a RETURNING clause on their UPDATE statements, similar as the RETURNING clause in INSERT statements. This is useful to fetch trigger-generated values in one go. An example is given here:

-- Fetch a trigger-generated value
UPDATE BOOK
SET TITLE = 'Animal Farm'
WHERE ID = 5
RETURNING TITLE

The DELETE statement removes records from a database table. DELETE statements are only possible on single tables. Support for multi-table deletes will be implemented in the near future. An example delete query is given here:

The MERGE statement is one of the most advanced standardised SQL constructs, which is supported by DB2, HSQLDB, Oracle, SQL Server and Sybase (MySQL has the similar INSERT .. ON DUPLICATE KEY UPDATE construct)

The point of the standard MERGE statement is to take a TARGET table, and merge (INSERT, UPDATE) data from a SOURCE table into it. DB2, Oracle, SQL Server and Sybase also allow for DELETING some data and for adding many additional clauses. With jOOQ 3.6.4, only Oracle's MERGE extensions are supported. Here is an example:

-- Check if there is already an author called 'Hitchcock'
-- If there is, rename him to John. If there isn't add him.
MERGE INTO AUTHOR
USING (SELECT 1 FROM DUAL)
ON (LAST_NAME = 'Hitchcock')
WHEN MATCHED THEN UPDATE SET FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME) VALUES ('Hitchcock');

MERGE Statement (H2-specific syntax)

The H2 database ships with a somewhat less powerful but a little more intuitive syntax for its own version of the MERGE statement. An example more or less equivalent to the previous one can be seen here:

-- Check if there is already an author called 'Hitchcock'
-- If there is, rename him to John. If there isn't add him.
MERGE INTO AUTHOR (FIRST_NAME, LAST_NAME)
KEY (LAST_NAME)
VALUES ('John', 'Hitchcock')

Typesafety of VALUES() for degrees up to 22

jOOQ's DDL support is currently still very limited. In the long run, jOOQ will support the most important statement types for frequent informal database migrations, though. Note that jOOQ will not aim to replace existing database migration frameworks. At Data Geekery, we usually recommend using Flyway for migrations. See also the tutorial about using jOOQ with Flyway for more information.

Views

Even if the TRUNCATE statement mainly modifies data, it is generally considered to be a DDL statement. It is popular in many databases when you want to bypass constraints for table truncation. Databases may behave differently, when a truncated table is referenced by other tables. For instance, they may fail if records from a truncated table are referenced, even with ON DELETE CASCADE clauses in place. Please, consider your database manual to learn more about its TRUNCATE implementation.

The TRUNCATE syntax is trivial:

TRUNCATE TABLE AUTHOR;

create.truncate(AUTHOR).execute();

TRUNCATE is not supported by Ingres and SQLite. jOOQ will execute a DELETE FROM AUTHOR statement instead.

Most of the times, when thinking about a table expression you're probably thinking about an actual table in your database schema. If you're using jOOQ's code generator, you will have all tables from your database schema available to you as type safe Java objects. You can then use these tables in SQL FROM clauses, JOIN clauses or in other SQL statements, just like any other table expression. An example is given here:

The strength of jOOQ's code generator becomes more obvious when you perform table aliasing and dereference fields from generated aliased tables. This can best be shown by example:

-- Select all books by authors born after 1920,
-- named "Paulo" from a catalogue:
SELECT *
FROM author a
JOIN book b ON a.id = b.author_id
WHERE a.year_of_birth > 1920
AND a.first_name = 'Paulo'
ORDER BY b.title

As you can see in the above example, calling as() on generated tables returns an object of the same type as the table. This means that the resulting object can be used to dereference fields from the aliased table. This is quite powerful in terms of having your Java compiler check the syntax of your SQL statements. If you remove a column from a table, dereferencing that column from that table alias will cause compilation errors.

Dereferencing columns from other table expressions

Only few table expressions provide the SQL syntax typesafety as shown above, where generated tables are used. Most tables, however, expose their fields through field() methods:

Derived column lists

The SQL standard specifies how a table can be renamed / aliased in one go along with its columns. It references the term "derived column list" for the following syntax (as supported by Postgres, for instance):

Only few databases really support such a syntax, but fortunately, jOOQ can emulate it easily using UNION ALL and an empty dummy record specifying the new column names. The two statements are equivalent:

CROSS JOIN or cartesian product: The basic JOIN in SQL, producing a relational cross product, combining every record of table A with every record of table B. Note that cartesian products can also be produced by listing comma-separated table expressions in the FROM clause of a SELECT statement

NATURAL JOIN: The basic JOIN in relational algebra, yet a rarely used JOIN in databases with everyday degree of normalisation. This JOIN type unconditionally equi-joins two tables by all columns with the same name (requiring foreign keys and primary keys to share the same name). Note that the JOIN columns will only figure once in the resulting table expression.

INNER JOIN or equi-join: This JOIN operation performs a cartesian product (CROSS JOIN) with a filtering predicate being applied to the resulting table expression. Most often, a equal comparison predicate comparing foreign keys and primary keys will be applied as a filter, but any other predicate will work, too.

OUTER JOIN: This JOIN operation performs a cartesian product (CROSS JOIN) with a filtering predicate being applied to the resulting table expression. Most often, a equal comparison predicate comparing foreign keys and primary keys will be applied as a filter, but any other predicate will work, too. Unlike the INNER JOIN, an OUTER JOIN will add "empty records" to the left (table A) or right (table B) or both tables, in case the conditional expression fails to produce a .

Some databases allow for expressing in-memory temporary tables using a VALUES() constructor. This constructor usually works the same way as the VALUES() clause known from the INSERT statement or from the MERGE statement. With jOOQ, you can also use the VALUES() table constructor, to create tables that can be used in a SELECT statement'sFROM clause:

A SELECT statement can appear almost anywhere a table expression can. Such a "nested SELECT" is often called a "derived table". Apart from many convenience methods accepting org.jooq.Select objects directly, a SELECT statement can always be transformed into a org.jooq.Table object using the asTable() method.

Example: Scalar subquery

SELECT *
FROM BOOK
WHERE BOOK.AUTHOR_ID = (
SELECT ID
FROM AUTHOR
WHERE LAST_NAME = 'Orwell')

If you are closely coupling your application to an Oracle database, you can take advantage of some Oracle-specific features, such as the PIVOT clause, used for statistical analyses. The formal syntax definition is as follows:

The PIVOT clause is available from the org.jooq.Table type, as pivoting is done directly on a table. Currently, only Oracle's PIVOT clause is supported. Support for SQL Server's slightly different PIVOT clause will be added later. Also, jOOQ may emulate PIVOT for other dialects in the future.

There is one operation in relational algebra that is not given a lot of attention, because it is rarely used in real-world applications. It is the relational division, the opposite operation of the cross product (or, relational multiplication). The following is an approximate definition of a relational division:

Assume the following cross join / cartesian product
C = A × B
Then it can be said that
A = C ÷ B
B = C ÷ A

With jOOQ, you can simplify using relational divisions by using the following syntax:

Or in plain text: Find those TEXT values in C whose ID's correspond to all ID's in B. Note that from the above SQL statement, it is immediately clear that proper indexing is of the essence. Be sure to have indexes on all columns referenced from the on(...) and returning(...) clauses.

For more information about relational division and some nice, real-life examples, see

The SQL standard specifies how SQL databases should implement ARRAY and TABLE types, as well as CURSOR types. Put simply, a CURSOR is a pointer to any materialised table expression. Depending on the cursor's features, this table expression can be scrolled through in both directions, records can be locked, updated, removed, inserted, etc. Often, CURSOR types contain s, whereas ARRAY and TABLE types contain simple scalar values, although that is not a requirement

ARRAY types in SQL are similar to Java's array types. They contain a "component type" or "element type" and a "dimension". This sort of ARRAY type is implemented in H2, HSQLDB and Postgres and supported by jOOQ as such. Oracle uses strongly-typed arrays, which means that an ARRAY type (VARRAY or TABLE type) has a name and possibly a maximum capacity associated with it.

Unnesting array and cursor types

The real power of these types become more obvious when you fetch them from stored procedures to unnest them as table expressions and use them in your FROM clause. An example is given here, where Oracle's DBMS_XPLAN package is used to fetch a cursor containing data about the most recent execution plan:

Some databases support functions that can produce tables for use in arbitrary SELECT statements. jOOQ supports these functions out-of-the-box for such databases. For instance, in SQL Server, the following function produces a table of (ID, TITLE) columns containing either all the books or just one book by ID:

CREATE FUNCTION f_books (@id INTEGER)
RETURNS @out_table TABLE (
id INTEGER,
title VARCHAR(400)
)
AS
BEGIN
INSERT @out_table
SELECT id, title
FROM book
WHERE @id IS NULL OR id = @id
ORDER BY id
RETURN
END

The jOOQ code generator will now produce a generated table from the above, which can be used as a SQL function:

The SQL standard specifies that the FROM clause is optional in a SELECT statement. However, according to the standard, you may then no longer use some other clauses, such as the WHERE clause. In the real world, there exist three types of databases:

The ones that always require a FROM clause (as required by the SQL standard)

The ones that never require a FROM clause (and still allow a WHERE clause)

The ones that require a FROM clause only with a WHERE clause, GROUP BY clause, or HAVING clause

With jOOQ, you don't have to worry about the above distinction of SQL dialects. jOOQ never requires a FROM clause, but renders the necessary "DUAL" table, if needed. The following program shows how jOOQ renders "DUAL" tables

Note, that some databases (H2, MySQL) can normally do without "DUAL". However, there exist some corner-cases with complex nested SELECT statements, where this will cause syntax errors (or parser bugs). To stay on the safe side, jOOQ will always render "dual" in those dialects.

Column expressions can be used in various SQL clauses in order to refer to one or several columns. This chapter explains how to form various types of column expressions with jOOQ. A particular type of column expression is given in the section about tuples or row value expressions, where an expression may have a degree of more than one.

Using column expressions in jOOQ

jOOQ allows you to freely create arbitrary column expressions using a fluent expression construction API. Many expressions can be formed as functions from DSL methods, other expressions can be formed based on a pre-existing column expression. For example:

In general, it is up to you whether you want to use the "prefix" notation or the "postfix" notation to create new column expressions based on existing ones. The "SQL way" would be to use the "prefix notation", with functions created from the DSL. The "Java way" or "object-oriented way" would be to use the "postfix" notation with functions created from org.jooq.Field objects. Both ways ultimately create the same query part, though.

Table columns are the most simple implementations of a column expression. They are mainly produced by jOOQ's code generator and can be dereferenced from the generated tables. This manual is full of examples involving table columns. Another example is given in this query:

SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
WHERE BOOK.TITLE LIKE '%SQL%'
ORDER BY BOOK.TITLE

A slightly different use case than CAST expressions are data type coercions, which are not rendered through to generated SQL. Sometimes, you may want to pretend that a numeric value is really treated as a string value, for instance when binding a numeric bind value:

In the above example, field1 will be treated by jOOQ as a Field<String>, binding the numeric literal 1 as a VARCHAR value. The same applies to field2, whose string literal "1" will be bound as an INTEGER value.

This technique is better than performing unsafe or rawtype casting in Java, if you cannot access the "right" field type from any given expression.

Numeric arithmetic expressions

Your database can do the math for you. Arithmetic operations are implemented just like numeric functions, with similar limitations as far as type restrictions are concerned. You can use any of these operators:

Operator precedence

jOOQ does not know any operator precedence (see also boolean operator precedence). All operations are evaluated from left to right, as with any object-oriented API. The two following expressions are the same:

The SQL standard defines the concatenation operator to be an infix operator, similar to the ones we've seen in the chapter about arithmetic expressions. This operator looks like this: ||. Some other dialects do not support this operator, but expect a concat() function, instead. jOOQ renders the right operator / function, depending on your SQL dialect:

Math can be done efficiently in the database before returning results to your Java application. In addition to the arithmetic expressions discussed previously, jOOQ also supports a variety of numeric functions. As discussed in the chapter about SQL dialects numeric functions (as any function type) are mostly emulated in your database, in case they are not natively supported.

Interestingly, bitwise functions and bitwise arithmetic is not very popular among SQL databases. Most databases only support a few bitwise operations, while others ship with the full set of operators. jOOQ's API includes most bitwise operations as listed below. In order to avoid ambiguities with conditional operators, all bitwise functions are prefixed with "bit"

BIT_COUNT: Count the number of bits set to 1 in a number

BIT_AND: Set only those bits that are set in two numbers

BIT_OR: Set all bits that are set in at least one number

BIT_NAND: Set only those bits that are set in two numbers, and inverse the result

BIT_NOR: Set all bits that are set in at least one number, and inverse the result

BIT_NOT: Inverse the bits in a number

BIT_XOR: Set all bits that are set in at exactly one number

BIT_XNOR: Set all bits that are set in at exactly one number, and inverse the result

String formatting can be done efficiently in the database before returning results to your Java application. As discussed in the chapter about SQL dialects string functions (as any function type) are mostly emulated in your database, in case they are not natively supported.

Regular expressions, REGEXP, REGEXP_LIKE, etc.

Various databases have some means of searching through columns using regular expressions if the LIKE predicate does not provide sufficient pattern matching power. While there are many different functions and operators in the various databases, jOOQ settled for the SQL:2008 standard REGEX_LIKE operator. Being an operator (and not a function), you should use the corresponding method on org.jooq.Field:

create.selectFrom(BOOK).where(TITLE.likeRegex("^.*SQL.*$")).fetch();

Note that the SQL standard specifies that patterns should follow the XQuery standards. In the real world, the POSIX regular expression standard is the most used one, some use Java regular expressions, and only a few ones use Perl regular expressions. jOOQ does not make any assumptions about regular expression syntax. For cross-database compatibility, please read the relevant database manuals carefully, to learn about the appropriate syntax. Please refer to the DSL Javadoc for more details.

Most databases allow for specifying a COLLATION which allows for re-defining the ordering of string values. By default, ASCII, ISO, or Unicode encodings are applied to character data, and ordering is applied according to the respective encoding.

Sometimes, however, certain queries like to ignore parts of the encoding by treating upper-case and lower-case characters alike, such that ABC = abc, or such that ABC, jkl, XyZ are an ordered list of strings (case-insensitively).

For these ad-hoc ordering use-cases, most people resort to using LOWER() or UPPER() as follows:

Aggregate functions have strong limitations about when they may be used and when not. For instance, you can use aggregate functions in scalar queries. Typically, this means you only select aggregate functions, no regular columns or other column expressions. Another use case is to use them along with a GROUP BY clause as seen in the previous example. Note, that jOOQ does not check whether your using of aggregate functions is correct according to the SQL standards, or according to your database's behaviour.

Ordered-set aggregate functions

Oracle and some other databases support "ordered-set aggregate functions". This means you can provide an ORDER BY clause to an aggregate function, which will be taken into consideration when aggregating. The best example for this is Oracle's LISTAGG() (also known as GROUP_CONCAT in other SQL dialects). The following query groups by authors and concatenates their books' titles

SELECT LISTAGG(TITLE, ', ')
WITHIN GROUP (ORDER BY TITLE)
FROM BOOK
GROUP BY AUTHOR_ID

FIRST and LAST: Oracle's "ranked" aggregate functions

Oracle allows for restricting aggregate functions using the KEEP() clause, which is supported by jOOQ. In Oracle, some aggregate functions (MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV) can be restricted by this clause, hence org.jooq.AggregateFunction also allows for specifying it. Here are a couple of examples using this clause:

Most major RDBMS support the concept of window functions. jOOQ knows of implementations in DB2, Oracle, Postgres, SQL Server, and Sybase SQL Anywhere, and supports most of their specific syntaxes. Note, that H2 and HSQLDB have implemented ROW_NUMBER() functions, without true windowing support.

As previously discussed, any org.jooq.AggregateFunction can be transformed into a window function using the over() method. See the chapter about aggregate functions for details. In addition to those, there are also some more window functions supported by jOOQ, as declared in the DSL:

SQL distinguishes between various window function types (e.g. "ranking functions"). Depending on the function, SQL expects mandatory PARTITION BY or ORDER BY clauses within the OVER() clause. jOOQ does not enforce those rules for two reasons:

Your JDBC driver or database already checks SQL syntax semantics

Not all databases behave correctly according to the SQL standard

If possible, however, jOOQ tries to render missing clauses for you, if a given SQL dialect is more restrictive.

An advanced window function example

Window functions can be used for things like calculating a "running total". The following example fetches transactions and the running total for every transaction going back to the beginning of the transaction table (ordered by booked_at). Window functions are accessible from the previously seen org.jooq.AggregateFunction type using the over() method:

SELECT booked_at, amount,
SUM(amount) OVER (PARTITION BY 1
ORDER BY booked_at
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS total
FROM transactions

Window functions created from ordered-set aggregate functions

In the previous chapter about aggregate functions, we have seen the concept of "ordered-set aggregate functions", such as Oracle's LISTAGG(). These functions have a window function / analytical function variant, as well. For example:

SELECT LISTAGG(TITLE, ', ')
WITHIN GROUP (ORDER BY TITLE)
OVER (PARTITION BY BOOK.AUTHOR_ID)
FROM BOOK

Window functions created from Oracle's FIRST and LAST aggregate functions

In the previous chapter about aggregate functions, we have seen the concept of "FIRST and LAST aggregate functions". These functions have a window function / analytical function variant, as well. For example:

SUM(BOOK.AMOUNT_SOLD)
KEEP(DENSE_RANK FIRST ORDER BY BOOK.AUTHOR_ID)
OVER(PARTITION BY 1)

ROLLUP() explained in SQL

The SQL standard defines special functions that can be used in the GROUP BY clause: the grouping functions. These functions can be used to generate several groupings in a single clause. This can best be explained in SQL. Let's take ROLLUP() for instance:

-- ROLLUP() with one argument
SELECT AUTHOR_ID, COUNT(*)
FROM BOOK
GROUP BY ROLLUP(AUTHOR_ID)
-- ROLLUP() with two arguments
SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY ROLLUP(AUTHOR_ID, PUBLISHED_IN)

-- The same query using UNION ALL:
SELECT AUTHOR_ID, COUNT(*) FROM BOOK GROUP BY (AUTHOR_ID)
UNION ALL
SELECT NULL, COUNT(*) FROM BOOK GROUP BY ()
ORDER BY 1 NULLS LAST
-- The same query using UNION ALL:
SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM BOOK GROUP BY (AUTHOR_ID, PUBLISHED_IN)
UNION ALL
SELECT AUTHOR_ID, NULL, COUNT(*)
FROM BOOK GROUP BY (AUTHOR_ID)
UNION ALL
SELECT NULL, NULL, COUNT(*)
FROM BOOK GROUP BY ()
ORDER BY 1 NULLS LAST, 2 NULLS LAST

In English, the ROLLUP() grouping function provides N+1 groupings, when N is the number of arguments to the ROLLUP() function. Each grouping has an additional group field from the ROLLUP() argument field list. The results of the second query might look something like this:

CUBE() explained in SQL

CUBE() is different from ROLLUP() in the way that it doesn't just create N+1 groupings, it creates all 2^N possible combinations between all group fields in the CUBE() function argument list. Let's re-consider our second query from before:

-- CUBE() with two arguments
SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY CUBE(AUTHOR_ID, PUBLISHED_IN)

-- The same query using UNION ALL:
SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM BOOK GROUP BY (AUTHOR_ID, PUBLISHED_IN)
UNION ALL
SELECT AUTHOR_ID, NULL, COUNT(*)
FROM BOOK GROUP BY (AUTHOR_ID)
UNION ALL
SELECT NULL, PUBLISHED_IN, COUNT(*)
FROM BOOK GROUP BY (PUBLISHED_IN)
UNION ALL
SELECT NULL, NULL, COUNT(*)
FROM BOOK GROUP BY ()
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST

MySQL's and CUBRID's WITH ROLLUP syntax

MySQL and CUBRID don't know any grouping functions, but they support a WITH ROLLUP clause, that is equivalent to simple ROLLUP() grouping functions. jOOQ emulates ROLLUP() in MySQL and CUBRID, by rendering this WITH ROLLUP clause. The following two statements mean the same:

Some databases support user-defined aggregate functions, which can then be used along with GROUP BY clauses or as window functions. An example for such a database is Oracle. With Oracle, you can define the following OBJECT type (the example was taken from the Oracle 11g documentation):

The CASE expression is part of the standard SQL syntax. While some RDBMS also offer an IF expression, or a DECODE function, you can always rely on the two types of CASE syntax:

CASE WHEN AUTHOR.FIRST_NAME = 'Paulo' THEN 'brazilian'
WHEN AUTHOR.FIRST_NAME = 'George' THEN 'english'
ELSE 'unknown'
END
-- OR:
CASE AUTHOR.FIRST_NAME WHEN 'Paulo' THEN 'brazilian'
WHEN 'George' THEN 'english'
ELSE 'unknown'
END

In jOOQ, both syntaxes are supported (The second one is emulated in Derby, which only knows the first one). Unfortunately, both case and else are reserved words in Java. jOOQ chose to use decode() from the Oracle DECODE function, or choose(), and otherwise(), which means the same as else.

A CASE expression can be used anywhere where you can place a column expression (or Field). For instance, you can SELECT the above expression, if you're selecting from AUTHOR:

The Oracle DECODE() function

Oracle knows a more succinct, but maybe less readable DECODE() function with a variable number of arguments. This function roughly does the same as the second case expression syntax. jOOQ supports the DECODE() function and emulates it using CASE expressions in all dialects other than Oracle:

According to the SQL standard, row value expressions can have a degree of more than one. This is commonly used in the INSERT statement, where the VALUES row value constructor allows for providing a row value expression as a source for INSERT data. Row value expressions can appear in various other places, though. They are supported by jOOQ as records / rows. jOOQ's DSL allows for the construction of type-safe records up to the degree of 22. Higher-degree Rows are supported as well, but without any type-safety. Row types are modelled as follows:

See the relevant sections for more details about how to use row value expressions in predicates.

Using row value expressions in UPDATE statements

The UPDATE statement also supports a variant where row value expressions are updated, rather than single columns. See the relevant section for more details

Higher-degree row value expressions

jOOQ chose to explicitly support degrees up to 22 to match Scala's typesafe tuple, function and product support. Unlike Scala, however, jOOQ also supports higher degrees without the additional typesafety.

Boolean types in SQL

Before SQL:1999, boolean types did not really exist in SQL. They were modelled by 0 and 1 numeric/char values. With SQL:1999, true booleans were introduced and are now supported by most databases. In short, these are possible boolean values:

1 or TRUE

0 or FALSE

NULL or UNKNOWN

It is important to know that SQL differs from many other languages in the way it interprets the NULL boolean value. Most importantly, the following facts are to be remembered:

Connect conditions using boolean operators

In SQL, as in most other languages, conditional expressions can be connected using the AND and OR binary operators, as well as the NOT unary operator, to form new conditional expressions. In jOOQ, this is modelled as such:

-- A simple conditional expression
TITLE = 'Animal Farm' OR TITLE = '1984'
-- A more complex conditional expression
(TITLE = 'Animal Farm' OR TITLE = '1984')
AND NOT (AUTHOR.LAST_NAME = 'Orwell')

The above example shows that the number of parentheses in Java can quickly explode. Proper indentation may become crucial in making such code readable. In order to understand how jOOQ composes combined conditional expressions, let's assign component expressions first:

The Condition API

and(Condition) // Combine conditions with AND
and(String) // Combine conditions with AND. Convenience for adding plain SQL to the right-hand side
and(String, Object...) // Combine conditions with AND. Convenience for adding plain SQL to the right-hand side
and(String, QueryPart...) // Combine conditions with AND. Convenience for adding plain SQL to the right-hand side
andExists(Select<?>) // Combine conditions with AND. Convenience for adding an exists predicate to the rhs
andNot(Condition) // Combine conditions with AND. Convenience for adding an inverted condition to the rhs
andNotExists(Select<?>) // Combine conditions with AND. Convenience for adding an inverted exists predicate to the rhs
or(Condition) // Combine conditions with OR
or(String) // Combine conditions with OR. Convenience for adding plain SQL to the right-hand side
or(String, Object...) // Combine conditions with OR. Convenience for adding plain SQL to the right-hand side
or(String, QueryPart...) // Combine conditions with OR. Convenience for adding plain SQL to the right-hand side
orExists(Select<?>) // Combine conditions with OR. Convenience for adding an exists predicate to the rhs
orNot(Condition) // Combine conditions with OR. Convenience for adding an inverted condition to the rhs
orNotExists(Select<?>) // Combine conditions with OR. Convenience for adding an inverted exists predicate to the rhs
not() // Invert a condition (synonym for DSL.not(Condition)

In SQL, comparison predicates are formed using common comparison operators:

= to test for equality

<> or != to test for non-equality

> to test for being strictly greater

>= to test for being greater or equal

< to test for being strictly less

<= to test for being less or equal

Unfortunately, Java does not support operator overloading, hence these operators are also implemented as methods in jOOQ, like any other SQL syntax elements. The relevant parts of the org.jooq.Field interface are these:

Note that every operator is represented by two methods. A verbose one (such as equal()) and a two-character one (such as eq()). Both methods are the same. You may choose either one, depending on your taste. The manual will always use the more verbose one.

jOOQ's convenience methods using comparison operators

In addition to the above, jOOQ provides a few convenience methods for common operations performed on strings using comparison predicates:

As previously mentioned in the manual's section about arithmetic expressions, jOOQ does not implement operator precedence. All operators are evaluated from left to right, as expected in an object-oriented API. This is important to understand when combining boolean operators, such as AND, OR, and NOT. The following expressions are equivalent:

A.and(B) .or(C) .and(D) .or(E)
(((A.and(B)).or(C)).and(D)).or(E)

In SQL, the two expressions wouldn't be the same, as SQL natively knows operator precedence.

A AND B OR C AND D OR E -- Precedence is applied
(((A AND B) OR C) AND D) OR E -- Precedence is overridden

All variants of the comparison predicate that we've seen in the previous chapter also work for row value expressions. If your database does not support row value expression comparison predicates, jOOQ emulates them the way they are defined in the SQL standard:

If the right-hand side of a comparison predicate turns out to be a non-scalar table subquery, you can wrap that subquery in a quantifier, such as ALL, ANY, or SOME. Note that the SQL standard defines ANY and SOME to be equivalent. jOOQ settled for the more intuitive ANY and doesn't support SOME. Here are some examples, supported by jOOQ:

The SQL NULL predicate also works well for row value expressions, although it has some subtle, counter-intuitive features when it comes to inversing predicates with the NOT() operator! Here are some examples:

In jOOQ, you would simply use the isNull() and isNotNull() methods on row value expressions. Again, as with the row value expression comparison predicate, the row value expression NULL predicate is emulated by jOOQ, if your database does not natively support it:

If your database does not natively support the DISTINCT predicate, jOOQ emulates it with an equivalent CASE expression, modelling the above truth table:

-- [A] IS DISTINCT FROM [B]
CASE WHEN [A] IS NULL AND [B] IS NULL THEN FALSE
WHEN [A] IS NULL AND [B] IS NOT NULL THEN TRUE
WHEN [A] IS NOT NULL AND [B] IS NULL THEN TRUE
WHEN [A] = [B] THEN FALSE
ELSE TRUE
END

-- [A] IS NOT DISTINCT FROM [B]
CASE WHEN [A] IS NULL AND [B] IS NULL THEN TRUE
WHEN [A] IS NULL AND [B] IS NOT NULL THEN FALSE
WHEN [A] IS NOT NULL AND [B] IS NULL THEN FALSE
WHEN [A] = [B] THEN TRUE
ELSE FALSE
END

BETWEEN SYMMETRIC

The SQL standard defines the SYMMETRIC keyword to be used along with BETWEEN to indicate that you do not care which bound of the range is larger than the other. A database system should simply swap range bounds, in case the first bound is greater than the second one. jOOQ supports this keyword as well, emulating it if necessary.

PUBLISHED_IN BETWEEN SYMMETRIC 1940 AND 1920
PUBLISHED_IN NOT BETWEEN SYMMETRIC 1940 AND 1920

Escaping operands with the LIKE predicate

Often, your pattern may contain any of the wildcard characters "_" and "%", in case of which you may want to escape them. jOOQ does not automatically escape patterns in like() and notLike() methods. Instead, you can explicitly define an escape character as such:

TITLE LIKE '%The !%-Sign Book%' ESCAPE '!'
TITLE NOT LIKE '%The !%-Sign Book%' ESCAPE '!'

In the above predicate expressions, the exclamation mark character is passed as the escape character to escape wildcard characters "!_" and "!%", as well as to escape the escape character itself: "!!"

Please refer to your database manual for more details about escaping patterns with the LIKE predicate.

jOOQ's convenience methods using the LIKE predicate

In addition to the above, jOOQ provides a few convenience methods for common operations performed on strings using the LIKE predicate. Typical operations are "contains predicates", "starts with predicates", "ends with predicates", etc. Here is the full convenience API wrapping LIKE predicates:

-- case insensitivity
LOWER(TITLE) LIKE LOWER('%abc%')
LOWER(TITLE) NOT LIKE LOWER('%abc%')
-- contains and similar methods
TITLE LIKE '%' || 'abc' || '%'
TITLE LIKE 'abc' || '%'
TITLE LIKE '%' || 'abc'

In SQL, apart from comparing a value against several values, the IN predicate can be used to create semi-joins or anti-joins. jOOQ knows the following methods on the org.jooq.Field interface, to construct such IN predicates:

in(Collection<T>) // Construct an IN predicate from a collection of bind values
in(T...) // Construct an IN predicate from bind values
in(Field<?>...) // Construct an IN predicate from column expressions
in(Select<? extends Record1<T>>) // Construct an IN predicate from a subselect
notIn(Collection<T>) // Construct a NOT IN predicate from a collection of bind values
notIn(T...) // Construct a NOT IN predicate from bind values
notIn(Field<?>...) // Construct a NOT IN predicate from column expressions
notIn(Select<? extends Record1<T>>) // Construct a NOT IN predicate from a subselect

NOT IN and NULL values

Beware that you should probably not have any NULL values in the right hand side of a NOT IN predicate, as the whole expression would evaluate to NULL, which is rarely desired. This can be shown informally using the following reasoning:

-- The following conditional expressions are formally or informally equivalent
A NOT IN (B, C)
A != ANY(B, C)
A != B AND A != C
-- Substitute C for NULL, you'll get
A NOT IN (B, NULL) -- Substitute C for NULL
A != B AND A != NULL -- From the above rules
A != B AND NULL -- [ANY] != NULL yields NULL
NULL -- [ANY] AND NULL yields NULL

A good way to prevent this from happening is to use the EXISTS predicate for anti-joins, which is NULL-value insensitive. See the manual's section about conditional expressions to see a boolean truth table.

Slightly less intuitive, yet more powerful than the previously discussed IN predicate is the EXISTS predicate, that can be used to form semi-joins or anti-joins. With jOOQ, the EXISTS predicate can be formed in various ways:

From the DSL, using static methods. This is probably the most used case

Note that in SQL, the projection of a subselect in an EXISTS predicate is irrelevant. To help you write queries like the above, you can use jOOQ's selectZero() or selectOne() DSL methods

Performance of IN vs. EXISTS

In theory, the two types of predicates can perform equally well. If your database system ships with a sophisticated cost-based optimiser, it will be able to transform one predicate into the other, if you have all necessary constraints set (e.g. referential constraints, not null constraints). However, in reality, performance between the two might differ substantially. An interesting blog post investigating this topic on the MySQL database can be seen here:http://blog.jooq.org/2012/07/27/not-in-vs-not-exists-vs-left-join-is-null-mysql/

// The date range tuples version
row(Date.valueOf('2010-01-01'), Date.valueOf('2010-01-03')).overlaps(Date.valueOf('2010-01-02'), Date.valueOf('2010-01-04'))
// The INTERVAL tuples version
row(Date.valueOf('2010-01-01'), new DayToSecond(2)).overlaps(Date.valueOf('2010-01-02'), new DayToSecond(2))

jOOQ's extensions to the standard

Unlike the standard (or any database implementing the standard), jOOQ also supports the OVERLAPS predicate for comparing arbitrary row vlaue expressions of degree 2. For instance, (1, 3) OVERLAPS (2, 4) will yield true in jOOQ. This is emulated as such

It is, however, interesting to think of all of the above expressions as what they are: expressions. And as such, nothing keeps users from extracting expressions and referencing them from outside the statement. The following statement is exactly equivalent:

The dynamic SQL building power may be one of the biggest advantages of using a runtime query model like the one offered by jOOQ. Queries can be created dynamically, of arbitrary complexity. In the above example, we've just constructed a dynamic WHERE clause. The same can be done for any other clauses, including dynamic FROM clauses (dynamic JOINs), or adding additional WITH clauses as needed.

A DSL is a nice thing to have, it feels "fluent" and "natural", especially if it models a well-known language, such as SQL. But a DSL is always expressed in a host language (Java in this case), which was not made for exactly the same purposes as its hosted DSL. If it were, then jOOQ would be implemented on a compiler-level, similar to LINQ in .NET. But it's not, and so, the DSL is limited by language constraints of its host language. We have seen many functionalities where the DSL becomes a bit verbose. This can be especially true for:

You'll probably find other examples. If verbosity scares you off, don't worry. The verbose use-cases for jOOQ are rather rare, and when they come up, you do have an option. Just write SQL the way you're used to!

jOOQ allows you to embed SQL as a String into any supported statement in these contexts:

Apart from the general factory methods, plain SQL is also available in various other contexts. For instance, when adding a .where("a = b") clause to a query. Hence, there exist several convenience methods where plain SQL can be inserted usefully. This is an example displaying all various use-cases in one single query:

// You can use your table aliases in plain SQL fields
// As long as that will produce syntactically correct SQL
Field<?> LAST_NAME = create.field("a.LAST_NAME");
// You can alias your plain SQL fields
Field<?> COUNT1 = create.field("count(*) x");
// If you know a reasonable Java type for your field, you
// can also provide jOOQ with that type
Field<Integer> COUNT2 = create.field("count(*) y", Integer.class);
// Use plain SQL as select fields
create.select(LAST_NAME, COUNT1, COUNT2)
// Use plain SQL as aliased tables (be aware of syntax!)
.from("author a")
.join("book b")
// Use plain SQL for conditions both in JOIN and WHERE clauses
.on("a.id = b.author_id")
// Bind a variable in plain SQL
.where("b.title != ?", "Brida")
// Use plain SQL again as fields in GROUP BY and ORDER BY clauses
.groupBy(LAST_NAME)
.orderBy(LAST_NAME)
.fetch();

Important things to note about plain SQL!

There are some important things to keep in mind when using plain SQL:

jOOQ doesn't know what you're doing. You're on your own again!

You have to provide something that will be syntactically correct. If it's not, then jOOQ won't know. Only your JDBC driver or your RDBMS will detect the syntax error.

You have to provide consistency when you use variable binding. The number of ? must match the number of variables

The plain SQL API, as documented in the previous chapter, supports a string templating mini-language that allows for constructing complex SQL string content from smaller parts. A simple example can be seen below, e.g. when looking for support for one of PostgreSQL's various vendor-specific operator types:

ARRAY[1,4,3] && ARRAY[2,1]

condition("{0} && {1}", array1, array2);

Such a plain SQL template always consists of two things:

The SQL string fragment

A set of org.jooq.QueryPart arguments, which are expected to be embedded in the SQL string

The SQL string may reference the arguments by 0-based indexing. Each argument may be referenced several times. For instance, SQLite's emulation of the REPEAT(string, count) function may look like this:

Increased speed. Advanced databases such as Oracle can keep execution plans of similar queries in a dedicated cache to prevent hard-parsing your query again and again. In many cases, the actual value of a bind variable does not influence the execution plan, hence it can be reused. Preparing a statement will thus be faster

On a JDBC level, you can also reuse the SQL string and prepared statement object instead of constructing it again, as you can bind new values to the prepared statement. jOOQ currently does not cache prepared statements, internally.

The following sections explain how you can introduce bind values in jOOQ, and how you can control the way they are rendered and bound to SQL.

With dynamic SQL, keeping track of the number of question marks and their corresponding index may turn out to be hard. jOOQ abstracts this and lets you provide the bind value right where it is needed. A trivial example is this:

create.select().from(BOOK).where(BOOK.ID.eq(5)).and(BOOK.TITLE.eq("Animal Farm")).fetch();
// This notation is in fact a short form for the equivalent:
create.select().from(BOOK).where(BOOK.ID.eq(val(5))).and(BOOK.TITLE.eq(val("Animal Farm"))).fetch();

Note the using of DSL.val() to explicitly create an indexed bind value. You don't have to worry about that index. When the query is rendered, each bind value will render a question mark. When the query binds its variables, each bind value will generate the appropriate bind value index.

Some SQL access abstractions that are built on top of JDBC, or some that bypass JDBC may support named parameters. jOOQ allows you to give names to your parameters as well, although those names are not rendered to SQL strings by default. Here is an example of how to create named parameters using the org.jooq.Param type:

// Create a query with a named parameter. You can then use that name for accessing the parameter again
Query query1 = create.select().from(AUTHOR).where(LAST_NAME.eq(param("lastName", "Poe")));
Param<?> param1 = query.getParam("lastName");
// Or, keep a reference to the typed parameter in order not to lose the <T> type information:
Param<String> param2 = param("lastName", "Poe");
Query query2 = create.select().from(AUTHOR).where(LAST_NAME.eq(param2));
// You can now change the bind value directly on the Param reference:
param2.setValue("Orwell");

The org.jooq.Query interface also allows for setting new bind values directly, without accessing the Param type:

Sometimes, you may wish to avoid rendering bind variables while still using custom values in SQL. jOOQ refers to that as "inlined" bind values. When bind values are inlined, they render the actual value in SQL rather than a JDBC question mark. Bind value inlining can be achieved in two ways:

SQL injection happens because a programming language (SQL) is used to dynamically create arbitrary server-side statements based on user input. Programmers must take lots of care not to mix the language parts (SQL) with the user input (bind variables)

SQL injection in jOOQ

With jOOQ, SQL is usually created via a type safe, non-dynamic Java abstract syntax tree, where bind variables are a part of that abstract syntax tree. It is not possible to expose SQL injection vulnerabilities this way.

However, jOOQ offers convenient ways of introducing plain SQL strings in various places of the jOOQ API (which are annotated using org.jooq.PlainSQL since jOOQ 3.6). While jOOQ's API allows you to specify bind values for use with plain SQL, you're not forced to do that. For instance, both of the following queries will lead to the same, valid result:

// This query will use bind values, internally.
create.fetch("SELECT * FROM BOOK WHERE ID = ? AND TITLE = ?", 5, "Animal Farm");
// This query will not use bind values, internally.
create.fetch("SELECT * FROM BOOK WHERE ID = 5 AND TITLE = 'Animal Farm'");

All methods in the jOOQ API that allow for plain (unescaped, untreated) SQL contain a warning message in their relevant Javadoc, to remind you of the risk of SQL injection in what is otherwise a SQL-injection-safe API.

An example of rendering SQL

A simple example can be provided by checking out jOOQ's internal representation of a (simplified) CompareCondition. It is used for any org.jooq.Condition comparing two fields as for example the AUTHOR.ID = BOOK.AUTHOR_ID condition here:

-- [...]
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
-- [...]

This is how jOOQ renders such a condition (simplified example):

@Override
public final void accept(Context<?> context) {
// The CompareCondition delegates rendering of the Fields to the Fields
// themselves and connects them using the Condition's comparator operator:
context.visit(field1)
.sql(" ")
.keyword(comparator.toSQL())
.sql(" ")
.visit(field2);
}

As mentioned in the previous chapter about SQL rendering, there are some elements in the org.jooq.RenderContext that are used for formatting / pretty-printing rendered SQL. In order to obtain pretty-printed SQL, just use the following custom settings:

An example of binding values to SQL

A simple example can be provided by checking out jOOQ's internal representation of a (simplified) CompareCondition. It is used for any org.jooq.Condition comparing two fields as for example the AUTHOR.ID = BOOK.AUTHOR_ID condition here:

-- [...]
WHERE AUTHOR.ID = ?
-- [...]

This is how jOOQ binds values on such a condition:

@Override
public final void bind(BindContext context) throws DataAccessException {
// The CompareCondition itself does not bind any variables.
// But the two fields involved in the condition might do so...
context.bind(field1).bind(field2);
}

jOOQ supports all the standard SQL data types out of the box, i.e. the types contained in java.sql.Types. But your domain model might be more specific, or you might be using a vendor-specific data type, such as JSON, HSTORE, or some other data structure. If this is the case, this section will be right for you, we'll see how you can create org.jooq.Converter types and org.jooq.Binding types.

Converters

The simplest use-case of injecting custom data types is by using org.jooq.Converter. A Converter can convert from a database type <T> to a user-defined type <U> and vice versa. You'll be implementing this SPI:

Bindings

While converters are very useful for simple use-cases, org.jooq.Binding is useful when you need to customise data type interactions at a JDBC level, e.g. when you want to bind a PostgreSQL JSON data type. Custom bindings implement the following SPI:

These classes are declared public and covered by jOOQ's integration tests. When you extend these classes, you will have to provide your own implementations for the QueryParts'accept() method, as discussed before:

// This method must produce valid SQL. If your QueryPart contains other parts, you may delegate SQL generation to them
// in the correct order, passing the render context.
//
// If context.inline() is true, you must inline all bind variables
// If context.inline() is false, you must generate ? for your bind variables
public void toSQL(RenderContext context);
// This method must bind all bind variables to a PreparedStatement. If your QueryPart contains other QueryParts, $
// you may delegate variable binding to them in the correct order, passing the bind context.
//
// Every QueryPart must ensure, that it starts binding its variables at context.nextIndex().
public void bind(BindContext context) throws DataAccessException;

An example for implementing multiplication.

The above contract may be a bit tricky to understand at first. The best thing is to check out jOOQ source code and have a look at a couple of QueryParts, to see how it's done. Here's an example org.jooq.impl.CustomField showing how to create a field multiplying another field by 2

An example for implementing vendor-specific functions.

Many vendor-specific functions are not officially supported by jOOQ, but you can implement such support yourself using CustomField, for instance. Here's an example showing how to implement Oracle's TO_CHAR() function, emulating it in SQL Server using CONVERT():

If you don't need the integration of rather complex QueryParts into jOOQ, then you might be safer using simple Plain SQL functionality, where you can provide jOOQ with a simple String representation of your embedded SQL. Plain SQL methods in jOOQ's API come in two flavours.

method(String, Object...): This is a method that accepts a SQL string and a list of bind values that are to be bound to the variables contained in the SQL string

method(String, QueryPart...): This is a method that accepts a SQL string and a list of QueryParts that are "injected" at the position of their respective placeholders in the SQL string

Note that for historic reasons the two API usages can also be mixed, although this is not recommended and the exact behaviour is unspecified.

Plain SQL templating specification

Templating with QueryPart placeholders (or bind value placeholders) requires a simple parsing logic to be applied to SQL strings. The jOOQ template parser behaves according to the following rules:

Single-line comments (starting with -- in all databases (or #) in MySQL) are rendered without modification. Any bind variable or QueryPart placeholders in such comments are ignored.

Multi-line comments (starting with /* and ending with */ in all databases) are rendered without modification. Any bind variable or QueryPart placeholders in such comments are ignored.

String literals (starting and ending with ' in all databases, where all databases support escaping of the quote character by duplication as such: '', or in MySQL by escaping as such: \' (if Settings.backslashEscaping is turned on)) are rendered without modification. Any bind variable or QueryPart placeholders in such comments are ignored.

Quoted names (starting and ending with " in most databases, with ` in MySQL, or with [ and ] in T-SQL databases) are rendered without modification. Any bind variable or QueryPart placeholders in such comments are ignored.

The only transient, non-serializable element in any jOOQ object is the Configuration's underlying java.sql.Connection. When you want to execute queries after de-serialisation, or when you want to store/refresh/delete Updatable Records, you may have to "re-attach" them to a Configuration

// Deserialise a SELECT statement
ObjectInputStream in = new ObjectInputStream(...);
Select<?> select = (Select<?>) in.readObject();
// This will throw a DetachedException:
select.execute();
// In order to execute the above select, attach it first
DSLContext create = DSL.using(connection, SQLDialect.ORACLE);
create.attach(select);

With jOOQ 3.2's org.jooq.VisitListener SPI, it is possible to perform custom SQL transformation to implement things like shared-schema multi-tenancy, or a security layer centrally preventing access to certain data. This SPI is extremely powerful, as you can make ad-hoc decisions at runtime regarding local or global transformation of your SQL statement. The following sections show a couple of simple, yet real-world use-cases.

When implementing a logger, one needs to carefully assess how much information should really be disclosed on what logger level. In log4j and similar frameworks, we distinguish between FATAL, ERROR, WARN, INFO, DEBUG, and TRACE. In DEBUG level, jOOQ's internal default logger logs all executed statements including inlined bind values as such:

But textual or binary bind values can get quite long, quickly filling your log files with irrelevant information. It would be good to be able to abbreviate such long values (and possibly add a remark to the logged statement). Instead of patching jOOQ's internals, we can just transform the SQL statements in the logger implementation, cleanly separating concerns. This can be done with the following VisitListener:

// This listener is inserted into a Configuration through a VisitListenerProvider that creates a
// new listener instance for every rendering lifecycle
public class BindValueAbbreviator extends DefaultVisitListener {
private boolean anyAbbreviations = false;
@Override
public void visitStart(VisitContext context) {
// Transform only when rendering values
if (context.renderContext() != null) {
QueryPart part = context.queryPart();
// Consider only bind variables, leave other QueryParts untouched
if (part instanceof Param<?>) {
Param<?> param = (Param<?>) part;
Object value = param.getValue();
// If the bind value is a String (or Clob) of a given length, abbreviate it
// e.g. using commons-lang's StringUtils.abbreviate()
if (value instanceof String && ((String) value).length() > maxLength) {
anyAbbreviations = true;
// ... and replace it in the current rendering context (not in the Query)
context.queryPart(val(abbreviate((String) value, maxLength)));
}
// If the bind value is a byte[] (or Blob) of a given length, abbreviate it
// e.g. by removing bytes from the array
else if (value instanceof byte[] && ((byte[]) value).length > maxLength) {
anyAbbreviations = true;
// ... and replace it in the current rendering context (not in the Query)
context.queryPart(val(Arrays.copyOf((byte[]) value, maxLength)));
}
}
}
}
@Override
public void visitEnd(VisitContext context) {
// If any abbreviations were performed before...
if (anyAbbreviations) {
// ... and if this is the top-level QueryPart, then append a SQL comment to indicate the abbreviation
if (context.queryPartsLength() == 1) {
context.renderContext().sql(" -- Bind values may have been abbreviated");
}
}
}
}

If maxLength were set to 5, the above listener would produce the following log output:

Executing query : select * from "BOOK" where "BOOK"."TITLE" like ?
-> with bind values : select * from "BOOK" where "BOOK"."TITLE" like 'Ho...' -- Bind values may have been abbreviated

Scala 2.10 Macros

In a previous section of the manual, we've seen how jOOQ can be used to build SQL that can be executed with any API including JDBC or ... jOOQ. This section of the manual deals with various means of actually executing SQL with jOOQ.

SQL execution with JDBC

JDBC calls executable objects "java.sql.Statement". It distinguishes between three types of statements:

java.sql.CallableStatement: This statement type is used for SQL statements that are "called" rather than "executed". In particular, this includes calls to stored procedures. Callable statements can register OUT parameters

Today, the JDBC API may look weird to users being used to object-oriented design. While statements hide a lot of SQL dialect-specific implementation details quite well, they assume a lot of knowledge about the internal state of a statement. For instance, you can use the PreparedStatement.addBatch() method, to add a the prepared statement being created to an "internal list" of batch statements. Instead of returning a new type, this method forces user to reflect on the prepared statement's internal state or "mode".

jOOQ is wrapping JDBC

These things are abstracted away by jOOQ, which exposes such concepts in a more object-oriented way. For more details about jOOQ's batch query execution, see the manual's section about batch execution.

The following sections of this manual will show how jOOQ is wrapping JDBC for SQL execution

Alternative execution modes

Just because you can, doesn't mean you must. At the end of this chapter, we'll show how you can use jOOQ to generate SQL statements that are then executed with other APIs, such as Spring's JdbcTemplate, or Hibernate. For more information see the section about alternative execution models.

org.jooq.Result: Unlike its JDBC counter-part, this type implements java.util.List and is fully loaded into Java memory, freeing resources as early as possible. Just like statements, this means that users don't have to deal with a "weird" internal result set state.

org.jooq.Cursor: If you want more fine-grained control over how many records are fetched into memory at once, you can still do that using jOOQ's lazy fetching feature

Statement type: jOOQ does not formally distinguish between static statements and prepared statements. By default, all statements are prepared statements in jOOQ, internally. Executing a statement as a static statement can be done simply using a custom settings flag

Closing Statements: JDBC keeps open resources even if they are already consumed. With JDBC, there is a lot of verbosity around safely closing resources. In jOOQ, resources are closed after consumption, by default. If you want to keep them open after consumption, you have to explicitly say so.

JDBC flags: JDBC execution flags and modes are not modified. They can be set fluently on a Query

Unlike JDBC, jOOQ has a lot of knowledge about a SQL query's structure and internals (see the manual's section about SQL building). Hence, jOOQ distinguishes between these two fundamental types of queries. While every org.jooq.Query can be executed, only org.jooq.ResultQuery can return results (see the manual's section about fetching to learn more about fetching results). With plain SQL, the distinction can be made clear most easily:

Fetching is something that has been completely neglegted by JDBC and also by various other database abstraction libraries. Fetching is much more than just looping or listing records or mapped objects. There are so many ways you may want to fetch data from a database, it should be considered a first-class feature of any database abstraction API. Just to name a few, here are some of jOOQ's fetching modes:

Untyped vs. typed fetching: Sometimes you care about the returned type of your records, sometimes (with arbitrary projections) you don't.

Fetching arrays, maps, or lists: Instead of letting you transform your result sets into any more suitable data type, a library should do that work for you.

jOOQ understands that SQL is much more expressive than Java, when it comes to the declarative typing of table expressions. As a declarative language, SQL allows for creating ad-hoc row value expressions (records with indexed columns, or tuples) and records (records with named columns). In Java, this is not possible to the same extent.

Yet, still, sometimes you wish to use strongly typed records, when you know that you're selecting only from a single table:

When you use the DSLContext.selectFrom() method, jOOQ will return the record type supplied with the argument table. Beware though, that you will no longer be able to use any clause that modifies the type of your table expression. This includes:

Mapping custom row types to strongly typed records

Sometimes, you may want to explicitly select only a subset of your columns, but still use strongly typed records. Alternatively, you may want to join a one-to-one relationship and receive the two individual strongly typed records after the join.

Higher-degree records

jOOQ chose to explicitly support degrees up to 22 to match Scala's typesafe tuple, function and product support. Unlike Scala, however, jOOQ also supports higher degrees without the additional typesafety.

By default, jOOQ returns an org.jooq.Result object, which is essentially a java.util.List of org.jooq.Record. Often, you will find yourself wanting to transform this result object into a type that corresponds more to your specific needs. Or you just want to list all values of one specific column. Here are some examples to illustrate those use cases:

In a more functional operating mode, you might want to write callbacks that receive records from your select statement results in order to do some processing. This is a common data access pattern in Spring's JdbcTemplate, and it is also available in jOOQ. With jOOQ, you can implement your own org.jooq.RecordHandler classes and plug them into jOOQ's org.jooq.ResultQuery:

In a more functional operating mode, you might want to write callbacks that map records from your select statement results in order to do some processing. This is a common data access pattern in Spring's JdbcTemplate, and it is also available in jOOQ. With jOOQ, you can implement your own org.jooq.RecordMapper classes and plug them into jOOQ's org.jooq.ResultQuery:

Fetching data in records is fine as long as your application is not really layered, or as long as you're still writing code in the DAO layer. But if you have a more advanced application architecture, you may not want to allow for jOOQ artefacts to leak into other layers. You may choose to write POJOs (Plain Old Java Objects) as your primary DTOs (Data Transfer Objects), without any dependencies on jOOQ's org.jooq.Record types, which may even potentially hold a reference to a Configuration, and thus a JDBC java.sql.Connection. Like Hibernate/JPA, jOOQ allows you to operate with POJOs. Unlike Hibernate/JPA, jOOQ does not "attach" those POJOs or create proxies with any magic in them.

Using JPA-annotated POJOs

jOOQ tries to find JPA annotations on your POJO types. If it finds any, they are used as the primary source for mapping meta-information. Only the javax.persistence.Column annotation is used and understood by jOOQ. An example:

Just as with any other JPA implementation, you can put the javax.persistence.Column annotation on any class member, including attributes, setters and getters. Please refer to the Record.into() Javadoc for more details.

Using simple POJOs

If jOOQ does not find any JPA-annotations, columns are mapped to the "best-matching" constructor, attribute or setter. An example illustrates this:

Using proxyable types

jOOQ also allows for fetching data into abstract classes or interfaces, or in other words, "proxyable" types. This means that jOOQ will return a java.util.HashMap wrapped in a java.lang.reflect.Proxy implementing your custom type. An example of this is given here:

Loading POJOs back into Records to store them

The above examples show how to fetch data into your own custom POJOs / DTOs. When you have modified the data contained in POJOs, you probably want to store those modifications back to the database. An example of this is given here:

Note: Because of your manual setting of ID = 10, jOOQ's store() method will asume that you want to insert a new record. See the manual's section about CRUD with UpdatableRecords for more details on this.

Interaction with DAOs

// Initialise a Configuration
Configuration configuration = new DefaultConfiguration().set(connection).set(SQLDialect.ORACLE);
// Initialise the DAO with the Configuration
BookDao bookDao = new BookDao(configuration);
// Start using the DAO
Book book = bookDao.findById(5);
// Modify and update the POJO
book.setTitle("1984");
book.setPublishedIn(1948);
bookDao.update(book);
// Delete it again
bookDao.delete(book);

More complex data structures

jOOQ currently doesn't support more complex data structures, the way Hibernate/JPA attempt to map relational data onto POJOs. While future developments in this direction are not excluded, jOOQ claims that generic mapping strategies lead to an enormous additional complexity that only serves very few use cases. You are likely to find a solution using any of jOOQ's various fetching modes, with only little boiler-plate code on the client side.

In the previous sections we have seen how to create RecordMapper types to map jOOQ records onto arbitrary objects. We have also seen how jOOQ provides default algorithms to map jOOQ records onto POJOs. Your own custom domain model might be much more complex, but you want to avoid looking up the most appropriate RecordMapper every time you need one. For this, you can provide jOOQ's Configuration with your own implementation of the org.jooq.RecordMapperProvider interface. An example is given here:

Unlike JDBC's java.sql.ResultSet, jOOQ's org.jooq.Result does not represent an open database cursor with various fetch modes and scroll modes, that needs to be closed after usage. jOOQ's results are simple in-memory Java java.util.List objects, containing all of the result values. If your result sets are large, or if you have a lot of network latency, you may wish to fetch records one-by-one, or in small chunks. jOOQ supports a org.jooq.Cursor type for that purpose. In order to obtain such a reference, use the ResultQuery.fetchLazy() method. An example is given here:

As a org.jooq.Cursor holds an internal reference to an open java.sql.ResultSet, it may need to be closed at the end of iteration. If a cursor is completely scrolled through, it will conveniently close the underlying ResultSet. However, you should not rely on that.

ResultSet rs = statement.executeQuery();
// Repeat until there are no more result sets
for (;;) {
// Empty the current result set
while (rs.next()) {
// [ .. do something with it .. ]
}
// Get the next result set, if available
if (statement.getMoreResults()) {
rs = statement.getResultSet();
}
else {
break;
}
}
// Be sure that all result sets are closed
statement.getMoreResults(Statement.CLOSE_ALL_RESULTS);
statement.close();

As previously discussed in the chapter about differences between jOOQ and JDBC, jOOQ does not rely on an internal state of any JDBC object, which is "externalised" by Javadoc. Instead, it has a straight-forward API allowing you to do the above in a one-liner:

Using Java 8 CompletableFutures

Java 8 has introduced the new java.util.concurrent.CompletableFuture type, which allows for functional composition of asynchronous execution units. When applying this to SQL and jOOQ, you might be writing code as follows:

Using deprecated API

Some queries take very long to execute, yet they are not crucial for the continuation of the main program. For instance, you could be generating a complicated report in a Swing application, and while this report is being calculated in your database, you want to display a background progress bar, allowing the user to pursue some other work. This can be achived simply with jOOQ, by creating a org.jooq.FutureResult, a type that extends java.util.concurrent.Future. An example is given here:

// Spawn off this query in a separate process:
FutureResult<BookRecord> future = create.selectFrom(BOOK).where(... complex predicates ...).fetchLater();
// This example actively waits for the result to be done
while (!future.isDone()) {
progressBar.increment(1);
Thread.sleep(50);
}
// The result should be ready, now
Result<BookRecord> result = future.get();

Transform jOOQ's Result into a JDBC ResultSet

Instead of operating on a JDBC ResultSet holding an open resource from your database, you can also let jOOQ's org.jooq.Result wrap itself in a java.sql.ResultSet. The advantage of this is that the so-created ResultSet has no open connection to the database. It is a completely in-memory ResultSet:

// Transform a jOOQ Result into a ResultSet
Result<BookRecord> result = create.selectFrom(BOOK).fetch();
ResultSet rs = result.intoResultSet();

The inverse: Fetch data from a legacy ResultSet using jOOQ

The inverse of the above is possible too. Maybe, a legacy part of your application produces JDBC java.sql.ResultSet, and you want to turn them into a org.jooq.Result:

// Transform a JDBC ResultSet into a jOOQ Result
ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM BOOK");
// As a Result:
Result<Record> result = create.fetch(rs);
// As a Cursor
Cursor<Record> cursor = create.fetchLazy(rs);

You can also tighten the interaction with jOOQ's data type system and data type conversion features, by passing the record type to the above fetch methods:

Apart from a few extra features (user-defined types), jOOQ only supports basic types as supported by the JDBC API. In your application, you may choose to transform these data types into your own ones, without writing too much boiler-plate code. This can be done using jOOQ's org.jooq.Converter types. A converter essentially allows for two-way conversion between two Java data types <T> and <U>. By convention, the <T> type corresponds to the type in your database whereas the <U> type corresponds to your own user type. The Converter API is given here:

Enum Converters

jOOQ ships with a built-in default org.jooq.impl.EnumConverter, that you can use to map VARCHAR values to enum literals or NUMBER values to enum ordinals (both modes are supported). Let's say, you want to map a YES / NO / MAYBE column to a custom Enum:

Using Converters in generated source code

jOOQ also allows for generated source code to reference your own custom converters, in order to permanently replace a table column's <T> type by your own, custom <U> type. See the manual's section about custom data types for details.

SQL result tables are not optimal in terms of used memory as they are not designed to represent hierarchical data as produced by JOIN operations. Specifically, FOREIGN KEY values may repeat themselves unnecessarily:

Now, if you have millions of records with only few distinct values for AUTHOR_ID, you may not want to hold references to distinct (but equal) java.lang.Integer objects. This is specifically true for IDs of type java.util.UUID or string representations thereof. jOOQ allows you to "intern" those values:

With JDBC, you have full control over your SQL statements. You can decide yourself, if you want to execute a static java.sql.Statement without bind values, or a java.sql.PreparedStatement with (or without) bind values. But you have to decide early, which way to go. And you'll have to prevent SQL injection and syntax errors manually, when inlining your bind variables.

With jOOQ, this is easier. As a matter of fact, it is plain simple. With jOOQ, you can just set a flag in your Configuration'sSettings, and all queries produced by that configuration will be executed as static statements, with all bind values inlined. An example is given here:

-- These statements are rendered by the two factories:
SELECT ? FROM DUAL WHERE ? = ?
SELECT 1 FROM DUAL WHERE 1 = 1

Reasons for choosing one or the other

Not all databases are equal. Some databases show improved performance if you use java.sql.PreparedStatement, as the database will then be able to re-use execution plans for identical SQL statements, regardless of actual bind values. This heavily improves the time it takes for soft-parsing a SQL statement. In other situations, assuming that bind values are irrelevant for SQL execution plans may be a bad idea, as you might run into "bind value peeking" issues. You may be better off spending the extra cost for a new hard-parse of your SQL statement and instead having the database fine-tune the new plan to the concrete bind values.

Whichever aproach is more optimal for you cannot be decided by jOOQ. In most cases, prepared statements are probably better. But you always have the option of forcing jOOQ to render inlined bind values.

Inlining bind values on a per-bind-value basis

Note that you don't have to inline all your bind values at once. If you know that a bind value is not really a variable and should be inlined explicitly, you can do so by using DSL.inline(), as documented in the manual's section about inlined parameters

The above technique can be quite useful when you want to reuse expensive database resources. This can be the case when your statement is executed very frequently and your database would take non-negligible time to soft-parse the prepared statement and generate a new statement / cursor resource.

Keeping open PreparedStatements with jOOQ

This is also modeled in jOOQ. However, the difference to JDBC is that closing a statement is the default action, whereas keeping it open has to be configured explicitly. This is better than JDBC, because the default action should be the one that is used most often. Keeping open statements is rarely done in average applications. Here's an example of how to keep open PreparedStatements with jOOQ:

// Create a query which is configured to keep its underlying PreparedStatement open
ResultQuery<Record> query = create.selectOne().keepStatement(true);
// Execute the query twice, against the same underlying PreparedStatement:
try {
Result<Record> result1 = query.fetch(); // This will lazily create a new PreparedStatement
Result<Record> result2 = query.fetch(); // This will reuse the previous PreparedStatement
}
// ... but now, you must not forget to close the query
finally {
query.close();
}

The above example shows how a query can be executed twice against the same underlying PreparedStatement. Unlike in other execution scenarios, you must not forget to close this query now

Beware of resource leaks

While jOOQ allows for explicitly keeping open PreparedStatement references in Query instances, the JDBC Connection may still be closed independently without jOOQ or the PreparedStatement noticing. It is the user's responsibility to close all resources according to the specification and behaviour of the concrete JDBC driver and the underlying database.

In the above example, your custom ExecuteListener callback is triggered before jOOQ loads a new Record from the JDBC ResultSet. With the concurrency being set to ResultSet.CONCUR_UPDATABLE, you can now modify the database cursor through the standard JDBC ResultSet API.

When creating a batch execution with a single query and multiple bind values, you will still have to provide jOOQ with dummy bind values for the original query. In the above example, these are set to null. For subsequent calls to bind(), there will be no type safety provided by jOOQ.

Most databases support sequences of some sort, to provide you with unique values to be used for primary keys and other enumerations. If you're using jOOQ's code generator, it will generate a sequence object per sequence for you. There are two ways of using such a sequence object:

Standalone calls to sequences

Instead of actually phrasing a select statement, you can also use the DSLContext's convenience methods:

// Fetch the next value from a sequence
BigInteger nextID = create.nextval(S_AUTHOR_ID);
// Fetch the current value from a sequence
BigInteger currID = create.currval(S_AUTHOR_ID);

Inlining sequence references in SQL

You can inline sequence references in jOOQ SQL statements. The following are examples of how to do that:

Many RDBMS support the concept of "routines", usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are:

Ada

BASIC

Pascal

etc...

The general distinction between (stored) procedures and (stored) functions can be summarised like this:

Procedures

Are called using JDBC CallableStatement

Have no return value

Usually support OUT parameters

Functions

Can be used in SQL statements

Have a return value

Usually don't support OUT parameters

Exceptions to these rules

DB2, H2, and HSQLDB don't allow for JDBC escape syntax when calling functions. Functions must be used in a SELECT statement

H2 only knows functions (without OUT parameters)

Oracle functions may have OUT parameters

Oracle knows functions that must not be used in SQL statements for transactional reasons

Postgres only knows functions (with all features combined). OUT parameters can also be interpreted as return values, which is quite elegant/surprising, depending on your taste

In general, it can be said that the field of routines (procedures / functions) is far from being standardised in modern RDBMS even if the SQL:2008 standard specifies things quite well. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures / functions implementations, especially when advanced data types such as cursors / UDT's / arrays are involved.

To simplify things a little bit, jOOQ handles both procedures and functions the same way, using a more general org.jooq.Routine type.

Using jOOQ for standalone calls to stored procedures and functions

-- Check whether there is an author in AUTHOR by that name and get his ID
CREATE OR REPLACE PROCEDURE author_exists (author_name VARCHAR2, result OUT NUMBER, id OUT NUMBER);

The generated artefacts can then be used as follows:

// Make an explicit call to the generated procedure object:
AuthorExists procedure = new AuthorExists();
// All IN and IN OUT parameters generate setters
procedure.setAuthorName("Paulo");
procedure.execute(configuration);
// All OUT and IN OUT parameters generate getters
assertEquals(new BigDecimal("1"), procedure.getResult());
assertEquals(new BigDecimal("2"), procedure.getId();

But you can also call the procedure using a generated convenience method in a global Routines class:

// The generated Routines class contains static methods for every procedure.
// Results are also returned in a generated object, holding getters for every OUT or IN OUT parameter.
AuthorExists procedure = Routines.authorExists(configuration, "Paulo");
// All OUT and IN OUT parameters generate getters
assertEquals(new BigDecimal("1"), procedure.getResult());
assertEquals(new BigDecimal("2"), procedure.getId();

Oracle uses the concept of a PACKAGE to group several procedures/functions into a sort of namespace. The SQL 92 standard talks about "modules", to represent this concept, even if this is rarely implemented as such. This is reflected in jOOQ by the use of Java sub-packages in the source code generation destination package. Every Oracle package will be reflected by

A Java package holding classes for formal Java representations of the procedure/function in that package

These member functions and procedures can simply be mapped to Java methods:

// Create an empty, attached UDT record from the DSLContext
UAuthorType author = create.newRecord(U_AUTHOR_TYPE);
// Set the author ID and load the record using the LOAD procedure
author.setId(1);
author.load();
// The record is now updated with the LOAD implementation's content
assertNotNull(author.getFirstName());
assertNotNull(author.getLastName());

If you are using jOOQ for scripting purposes or in a slim, unlayered application server, you might be interested in using jOOQ's exporting functionality (see also the importing functionality). You can export any Result<Record> into the formats discussed in the subsequent chapters of the manual

If you are using jOOQ for scripting purposes or in a slim, unlayered application server, you might be interested in using jOOQ's importing functionality (see also exporting functionality). You can import data directly into a table from the formats described in the subsequent sections of this manual.

Any of the above configuration methods can be combined to achieve the type of load you need. Please refer to the API's Javadoc to learn about more details. Errors that occur during the load are reported by the execute method's result:

Your database application probably consists of 50% - 80% CRUD, whereas only the remaining 20% - 50% of querying is actual querying. Most often, you will operate on records of tables without using any advanced relational concepts. This is called CRUD for

CRUD always uses the same patterns, regardless of the nature of underlying tables. This again, leads to a lot of boilerplate code, if you have to issue your statements yourself. Like Hibernate / JPA and other ORMs, jOOQ facilitates CRUD using a specific API involving org.jooq.UpdatableRecord types.

Primary keys and updatability

In normalised databases, every table has a primary key by which a tuple/record within that table can be uniquely identified. In simple cases, this is a (possibly auto-generated) number called ID. But in many cases, primary keys include several non-numeric columns. An important feature of such keys is the fact that in most databases, they are enforced using an index that allows for very fast random access to the table. A typical way to access / modify / delete a book is this:

-- Inserting uses a previously generated key value or generates it afresh
INSERT INTO BOOK (ID, TITLE) VALUES (5, 'Animal Farm');
-- Other operations can use a previously generated key value
SELECT * FROM BOOK WHERE ID = 5;
UPDATE BOOK SET TITLE = '1984' WHERE ID = 5;
DELETE FROM BOOK WHERE ID = 5;

Normalised databases assume that a primary key is unique "forever", i.e. that a key, once inserted into a table, will never be changed or re-inserted after deletion. In order to use jOOQ's CRUD operations correctly, you should design your database accordingly.

If you're using jOOQ's code generator, it will generate org.jooq.UpdatableRecord implementations for every table that has a primary key. When fetching such a record form the database, these records are "attached" to the Configuration that created them. This means that they hold an internal reference to the same database connection that was used to fetch them. This connection is used internally by any of the following methods of the UpdatableRecord:

// Refresh a record from the database.
void refresh() throws DataAccessException;
// Store (insert or update) a record to the database.
int store() throws DataAccessException;
// Delete a record from the database
int delete() throws DataAccessException;

See the manual's section about serializability for some more insight on "attached" objects.

Storing

Storing a record will perform an INSERT statement or an UPDATE statement. In general, new records are always inserted, whereas records loaded from the database are always updated. This is best visualised in code:

// Create a new record
BookRecord book1 = create.newRecord(BOOK);
// Insert the record: INSERT INTO BOOK (TITLE) VALUES ('1984');
book1.setTitle("1984");
book1.store();
// Update the record: UPDATE BOOK SET PUBLISHED_IN = 1984 WHERE ID = [id]
book1.setPublishedIn(1948);
book1.store();
// Get the (possibly) auto-generated ID from the record
Integer id = book1.getId();
// Get another instance of the same book
BookRecord book2 = create.fetchOne(BOOK, BOOK.ID.eq(id));
// Update the record: UPDATE BOOK SET TITLE = 'Animal Farm' WHERE ID = [id]
book2.setTitle("Animal Farm");
book2.store();

Some remarks about storing:

jOOQ sets only modified values in INSERT statements or UPDATE statements. This allows for default values to be applied to inserted records, as specified in CREATE TABLE DDL statements.

When store() performs an INSERT statement, jOOQ attempts to load any generated keys from the database back into the record. For more details, see the manual's section about IDENTITY values.

When loading records from POJOs, jOOQ will assume the record is a new record. It will hence attempt to INSERT it.

When you activate optimistic locking, storing a record may fail, if the underlying database record has been changed in the mean time.

Deleting

Deleting a record will remove it from the database. Here's how you delete records:

// Get a previously inserted book
BookRecord book = create.fetchOne(BOOK, BOOK.ID.eq(5));
// Delete the book
book.delete();

Refreshing

Refreshing a record from the database means that jOOQ will issue a SELECT statement to refresh all record values that are not the primary key. This is particularly useful when you use jOOQ's optimistic locking feature, in case a modified record is "stale" and cannot be stored to the database, because the underlying database record has changed in the mean time.

In order to perform a refresh, use the following Java code:

// Fetch an updatable record from the database
BookRecord book = create.fetchOne(BOOK, BOOK.ID.eq(5));
// Refresh the record
book.refresh();

CRUD and SELECT statements

CRUD operations can be combined with regular querying, if you select records from single database tables, as explained in the manual's section about SELECT statements. For this, you will need to use the selectFrom() method from the DSLContext:

// Loop over records returned from a SELECT statement
for (BookRecord book : create.fetch(BOOK, BOOK.PUBLISHED_IN.eq(1948))) {
// Perform actions on BookRecords depending on some conditions
if ("Orwell".equals(book.fetchParent(Keys.FK_BOOK_AUTHOR).getLastName())) {
book.delete();
}
}

Many databases support the concept of IDENTITY values, or SEQUENCE-generated key values. This is reflected by JDBC's getGeneratedKeys() method. jOOQ abstracts using this method as many databases and JDBC drivers behave differently with respect to generated keys. Let's assume the following SQL Server BOOK table:

Note that, unlike in Hibernate, jOOQ's navigation methods will always lazy-fetch relevant records, without caching any results. In other words, every time you run such a fetch method, a new query will be issued.

These fetch methods only work on "attached" records. See the manual's section about serializability for some more insight on "attached" objects.

jOOQ allows you to perform CRUD operations using optimistic locking. You can immediately take advantage of this feature by activating the relevant executeWithOptimisticLocking Setting. Without any further knowledge of the underlying data semantics, this will have the following impact on store() and delete() methods:

INSERT statements are not affected by this Setting flag

Prior to UPDATE or DELETE statements, jOOQ will run a SELECT .. FOR UPDATE statement, pessimistically locking the record for the subsequent UPDATE / DELETE

The data fetched with the previous SELECT will be compared against the data in the record being stored or deleted

The record is successfully stored / deleted, if the record had not been modified in the mean time.

The above changes to jOOQ's behaviour are transparent to the API, the only thing you need to do for it to be activated is to set the Settings flag. Here is an example illustrating optimistic locking:

// Properly configure the DSLContext
DSLContext optimistic = DSLContext.using(connection, SQLDialect.ORACLE,
new Settings().withExecuteWithOptimisticLocking(true));
// Fetch a book two times
BookRecord book1 = optimistic.fetch(BOOK, BOOK.ID.eq(5));
BookRecord book2 = optimistic.fetch(BOOK, BOOK.ID.eq(5));
// Change the title and store this book. The underlying database record has not been modified, it can be safely updated.
book1.setTitle("Animal Farm");
book1.store();
// Book2 still references the original TITLE value, but the database holds a new value from book1.store().
// This store() will thus fail:
book2.setTitle("1984");
book2.store();

Optimised optimistic locking using TIMESTAMP fields

CREATE TABLE book (
-- This column indicates when each book record was modified for the last time
MODIFIED TIMESTAMP NOT NULL,
-- [...]
)

The MODIFIED column will contain a timestamp indicating the last modification timestamp for any book in the BOOK table. If you're using jOOQ and it's store() methods on UpdatableRecords, jOOQ will then generate this TIMESTAMP value for you, automatically. However, instead of running an additional SELECT .. FOR UPDATE statement prior to an UPDATE or DELETE statement, jOOQ adds a WHERE-clause to the UPDATE or DELETE statement, checking for TIMESTAMP's integrity. This can be best illustrated with an example:

// Properly configure the DSLContext
DSLContext optimistic = DSL.using(connection, SQLDialect.ORACLE,
new Settings().withExecuteWithOptimisticLocking(true));
// Fetch a book two times
BookRecord book1 = optimistic.fetch(BOOK, BOOK.ID.eq(5));
BookRecord book2 = optimistic.fetch(BOOK, BOOK.ID.eq(5));
// Change the title and store this book. The MODIFIED value has not been changed since the book was fetched.
// It can be safely updated
book1.setTitle("Animal Farm");
book1.store();
// Book2 still references the original MODIFIED value, but the database holds a new value from book1.store().
// This store() will thus fail:
book2.setTitle("1984");
book2.store();

As before, without the added TIMESTAMP column, optimistic locking is transparent to the API.

Optimised optimistic locking using VERSION fields

Instead of using TIMESTAMPs, you may also use numeric VERSION fields, containing version numbers that are incremented by jOOQ upon store() calls.

When inserting, updating, deleting a lot of records, you may wish to profit from JDBC batch operations, which can be performed by jOOQ. These are available through jOOQ's DSLContext as shown in the following example:

When performing CRUD, you may want to be able to centrally register one or several listener objects that receive notification every time CRUD is performed on an UpdatableRecord. Example use cases of such a listener are:

Adding a central ID generation algorithm, generating UUIDs for all of your records.

Adding a central record initialisation mechanism, preparing the database prior to inserting a new record.

If you're using jOOQ's code generator, you can configure it to generate POJOs and DAOs for you. jOOQ then generates one DAO per UpdatableRecord, i.e. per table with a single-column primary key. Generated DAOs implement a common jOOQ type called org.jooq.DAO. This type contains the following methods:

You can use a JTA-compliant Java EE transaction manager from your container.

While jOOQ does not aim to replace any of the above, it offers a simple API (and a corresponding SPI) to provide you with jOOQ-style programmatic fluency to express your transactions. Below are some Java examples showing how to implement (nested) transactions with jOOQ. For these examples, we're using Java 8 syntax. Java 8 is not a requirement, though.

While some org.jooq.TransactionProvider implementations (e.g. ones based on ThreadLocals, e.g. Spring or JTA) may allow you to reuse the globally scoped DSLContext reference, the jOOQ transaction API design allows for TransactionProvider implementations that require your transactional code to use the new, locally scoped Configuration, instead.

Rollbacks

Any uncaught checked or unchecked exception thrown from your transactional code will rollback the transaction to the beginning of the block. This behaviour will allow for nesting transactions, if your configured org.jooq.TransactionProvider supports nesting of transactions. An example can be seen here:

Checked vs. unchecked exceptions

This is an eternal and religious debate. Pros and cons have been discussed time and again, and it still is a matter of taste, today. In this case, jOOQ clearly takes a side. jOOQ's exception strategy is simple:

All "system exceptions" are unchecked. If in the middle of a transaction involving business logic, there is no way that you can recover sensibly from a lost database connection, or a constraint violation that indicates a bug in your understanding of your database model.

All "business exceptions" are checked. Business exceptions are true exceptions that you should handle (e.g. not enough funds to complete a transaction).

With jOOQ, it's simple. All of jOOQ's exceptions are "system exceptions", hence they are all unchecked.

InvalidResultException: An operation was performed expecting only one result, but several results were returned.

MappingException: Something went wrong when loading a record from a POJO or when mapping a record into a POJO

Override jOOQ's exception handling

The following section about execute listeners documents means of overriding jOOQ's exception handling, if you wish to deal separately with some types of constraint violations, or if you raise business errors from your database, etc.

The Configuration lets you specify a list of org.jooq.ExecuteListener instances. The ExecuteListener is essentially an event listener for Query, Routine, or ResultSet render, prepare, bind, execute, fetch steps. It is a base type for loggers, debuggers, profilers, data collectors, triggers, etc. Advanced ExecuteListeners can also provide custom implementations of Connection, PreparedStatement and ResultSet to jOOQ in apropriate methods.

See also the manual's sections about logging for more sample implementations of actual ExecuteListeners.

Example: Bad query execution ExecuteListener

You can also use ExecuteListeners to interact with your SQL statements, for instance when you want to check if executed UPDATE or DELETE statements contain a WHERE clause. This can be achieved trivially with the following sample ExecuteListener:

Since jOOQ 3.0, a simple wrapping API has been added to wrap JDBC's rather awkward java.sql.DatabaseMetaData. This API is still experimental, as the calls to the underlying JDBC type are not always available for all SQL dialects.

jOOQ logs all SQL queries and fetched result sets to its internal DEBUG logger, which is implemented as an execute listener. By default, execute logging is activated in the jOOQ Settings. In order to see any DEBUG log output, put either log4j or slf4j on jOOQ's classpath along with their respective configuration. A sample log4j configuration can be seen here:

Many users may have switched from higher-level abstractions such as Hibernate to jOOQ, because of Hibernate's difficult-to-manage performance, when it comes to large database schemas and complex second-level caching strategies. However, jOOQ itself is not a lightweight database abstraction framework, and it comes with its own overhead. Please be sure to consider the following points:

It takes some time to construct jOOQ queries. If you can reuse the same queries, you might cache them. Beware of thread-safety issues, though, as jOOQ's Configuration is not necessarily threadsafe, and queries are "attached" to their creating DSLContext

It takes some time to render SQL strings. Internally, jOOQ reuses the same java.lang.StringBuilder for the complete query, but some rendering elements may take their time. You could, of course, cache SQL generated by jOOQ and prepare your own java.sql.PreparedStatement objects

It takes some time to bind values to prepared statements. jOOQ does not keep any open prepared statements, internally. Use a sophisticated connection pool, that will cache prepared statements and inject them into jOOQ through the standard JDBC API

It takes some time to fetch results. By default, jOOQ will always fetch the complete java.sql.ResultSet into memory. Use lazy fetching to prevent that, and scroll over an open underlying database cursor

Optimise wisely

Don't be put off by the above paragraphs. You should optimise wisely, i.e. only in places where you really need very high throughput to your database. jOOQ's overhead compared to plain JDBC is typically less than 1ms per query.

Just because you can, doesn't mean you must. In this chapter, we'll show how you can use jOOQ to generate SQL statements that are then executed with other APIs, such as Spring's JdbcTemplate, or Hibernate.

When adding jOOQ to a project that is using JdbcTemplate extensively, a pragmatic first step is to use jOOQ as a SQL builder and pass the query string and bind variables to JdbcTemplate for execution. For instance, you may have the following class to store authors and their number of books in our stores:

The simplest way to fetch entities via the native query API is by passing the entity class along to the native query method. The following example maps jOOQ query results to JPA entities (from the previous section). Just add the following utility method:

While JPA specifies how the mapping should be implemented (e.g. using javax.persistence.SqlResultSetMapping), there are no limitations regarding how you want to generate the SQL statement. The following, simple example shows how you can produce JPABook and JPAAuthor entities (from the previous section) from a jOOQ-generated SQL statement.

While optional, source code generation is one of jOOQ's main assets if you wish to increase developer productivity. jOOQ's code generator takes your database schema and reverse-engineers it into a set of Java classes modelling tables, records, sequences, POJOs, DAOs, stored procedures, user-defined types and many more.

jooq-3.6.4.jar
The main library that you will include in your application to run jOOQ

jooq-meta-3.6.4.jar
The utility that you will include in your build to navigate your database schema for code generation. This can be used as a schema crawler as well.

jooq-codegen-3.6.4.jar
The utility that you will include in your build to generate your database schema

Configure jOOQ's code generator

You need to tell jOOQ some things about your database connection. Here's an example of how to do it for an Oracle database

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration>
<!-- Configure the database connection here -->
<jdbc>
<driver>oracle.jdbc.OracleDriver</driver>
<url>jdbc:oracle:thin:@[your jdbc connection parameters]</url>
<user>[your database user]</user>
<password>[your database password]</password>
<!-- You can also pass user/password and other JDBC properties in the optional properties tag: -->
<properties>
<property><key>user</key><value>[db-user]</value></property>
<property><key>password</key><value>[db-password]</value></property>
</properties>
</jdbc>
<generator>
<database>
<!-- The database dialect from jooq-meta. Available dialects are
named org.util.[database].[database]Database.
Natively supported values are:
org.jooq.util.ase.ASEDatabase
org.jooq.util.cubrid.CUBRIDDatabase
org.jooq.util.db2.DB2Database
org.jooq.util.derby.DerbyDatabase
org.jooq.util.firebird.FirebirdDatabase
org.jooq.util.h2.H2Database
org.jooq.util.hsqldb.HSQLDBDatabase
org.jooq.util.informix.InformixDatabase
org.jooq.util.ingres.IngresDatabase
org.jooq.util.mariadb.MariaDBDatabase
org.jooq.util.mysql.MySQLDatabase
org.jooq.util.oracle.OracleDatabase
org.jooq.util.postgres.PostgresDatabase
org.jooq.util.sqlite.SQLiteDatabase
org.jooq.util.sqlserver.SQLServerDatabase
org.jooq.util.sybase.SybaseDatabase
This value can be used to reverse-engineer generic JDBC DatabaseMetaData (e.g. for MS Access)
org.jooq.util.jdbc.JDBCDatabase
This value can be used to reverse-engineer standard jOOQ-meta XML formats
org.jooq.util.xml.XMLDatabase
You can also provide your own org.jooq.util.Database implementation
here, if your database is currently not supported -->
<name>org.jooq.util.oracle.OracleDatabase</name>
<!-- All elements that are generated from your schema (A Java regular expression.
Use the pipe to separate several expressions) Watch out for
case-sensitivity. Depending on your database, this might be
important!
You can create case-insensitive regular expressions using this syntax: (?i:expr)
Whitespace is ignored and comments are possible.
-->
<includes>.*</includes>
<!-- All elements that are excluded from your schema (A Java regular expression.
Use the pipe to separate several expressions). Excludes match before
includes, i.e. excludes have a higher priority -->
<excludes>
UNUSED_TABLE # This table (unqualified name) should not be generated
| PREFIX_.* # Objects with a given prefix should not be generated
| SECRET_SCHEMA\.SECRET_TABLE # This table (qualified name) should not be generated
| SECRET_ROUTINE # This routine (unqualified name) ...
</excludes>
<!-- The schema that is used locally as a source for meta information.
This could be your development schema or the production schema, etc
This cannot be combined with the schemata element.
If left empty, jOOQ will generate all available schemata. See the
manual's next section to learn how to generate several schemata -->
<inputSchema>[your database schema / owner / name]</inputSchema>
</database>
<generate>
<!-- Generation flags: See advanced configuration properties -->
</generate>
<target>
<!-- The destination package of your generated classes (within the
destination directory)
jOOQ may append the schema name to this package if generating multiple schemas,
e.g. org.jooq.your.packagename.schema1
org.jooq.your.packagename.schema2 -->
<packageName>[org.jooq.your.packagename]</packageName>
<!-- The destination directory of your generated classes -->
<directory>[/path/to/your/dir]</directory>
</target>
</generator>
</configuration>

In the previous section we have seen how jOOQ's source code generator is configured and run within a few steps. In this chapter we'll cover some advanced settings

jooq-meta configuration

Within the <generator/> element, there are other configuration elements:

<!-- These properties can be added to the database element: -->
<database>
<!-- This flag indicates whether include / exclude patterns should also match
columns within tables. -->
<includeExcludeColumns>false</includeExcludeColumns>
<!-- All table and view columns that are used as "version" fields for
optimistic locking (A Java regular expression. Use the pipe to separate several expressions).
See UpdatableRecord.store() and UpdatableRecord.delete() for details -->
<recordVersionFields>REC_VERSION</recordVersionFields>
<!-- All table and view columns that are used as "timestamp" fields for
optimistic locking (A Java regular expression. Use the pipe to separate several expressions).
See UpdatableRecord.store() and UpdatableRecord.delete() for details -->
<recordTimestampFields>REC_TIMESTAMP</recordTimestampFields>
<!-- A regular expression matching all columns that participate in "synthetic" primary keys,
which should be placed on generated UpdatableRecords, to be used with
- UpdatableRecord.store()
- UpdatableRecord.update()
- UpdatableRecord.delete()
- UpdatableRecord.refresh()
Synthetic primary keys will override existing primary keys. -->
<syntheticPrimaryKeys>SCHEMA\.TABLE\.COLUMN(1|2)</syntheticPrimaryKeys>
<!-- All (UNIQUE) key names that should be used instead of primary keys on
generated UpdatableRecords, to be used with
- UpdatableRecord.store()
- UpdatableRecord.update()
- UpdatableRecord.delete()
- UpdatableRecord.refresh()
If several keys match, a warning is emitted and the first one encountered will be used.
This flag will also replace synthetic primary keys, if it matches. -->
<overridePrimaryKeys>MY_UNIQUE_KEY_NAME</overridePrimaryKeys>
<!-- Generate java.sql.Timestamp fields for DATE columns. This is
particularly useful for Oracle databases.
With jOOQ 3.5, this flag has been deprecated. Use an org.jooq.Binding instead
Defaults to false -->
<dateAsTimestamp>false</dateAsTimestamp>
<!-- Generate jOOU data types for your unsigned data types, which are
not natively supported in Java.
Defaults to true -->
<unsignedTypes>true</unsignedTypes>
<!-- The schema that is used in generated source code. This will be the
production schema. Use this to override your local development
schema name for source code generation. If not specified, this
will be the same as the input-schema.
This will be ignored if outputSchemaToDefault is set to true -->
<outputSchema>[your database schema / owner / name]</outputSchema>
<!-- A flag to indicate that the outputSchema should be the "default" schema,
which generates schema-less, unqualified tables, procedures, etc. -->
<outputSchemaToDefault>false</outputSchemaToDefault>
<!-- A configuration element to configure several input and/or output
schemata for jooq-meta, in case you're using jooq-meta in a multi-
schema environment.
This cannot be combined with the above inputSchema / outputSchema -->
<schemata>
<schema>
<inputSchema>...</inputSchema>
<outputSchema>...</outputSchema>
<outputSchemaToDefault>...</outputSchemaToDefault>
</schema>
[ <schema>...</schema> ... ]
</schemata>
<!-- A custom version number that, if available, will be used to assess whether the above
<inputSchema/> will need to be regenerated.
There are three operation modes for this element:
- The value is a class that can be found on the classpath and that implements
org.jooq.util.SchemaVersionProvider. Such classes must provide a default constructor
- The value is a SELECT statement that returns one record with one column. The
SELECT statement may contain a named variable called :schema_name
- The value is a constant, such as a Maven property
Schema versions will be generated into the javax.annotation.Generated annotation on
generated artefacts. -->
<schemaVersionProvider>SELECT :schema_name || '_' || MAX("version") FROM "schema_version"</schemaVersionProvider>
<!-- A configuration element to configure custom data types -->
<customTypes>...</customTypes>
<!-- A configuration element to configure type overrides for generated
artefacts (e.g. in combination with customTypes) -->
<forcedTypes>...</forcedTypes>
</database>

Check out the some of the manual's "advanced" sections to find out more about the advanced configuration parameters.

jooq-codegen configuration

Also, you can add some optional advanced configuration parameters for the generator:

<!-- These properties can be added to the generate element: -->
<generate>
<!-- Primary key / foreign key relations should be generated and used.
This is a prerequisite for various advanced features.
Defaults to true -->
<relations>true</relations>
<!-- Generate deprecated code for backwards compatibility
Defaults to true -->
<deprecated>true</deprecated>
<!-- Do not reuse this property. It is deprecated as of jOOQ 3.3.0 -->
<instanceFields>true</instanceFields>
<!-- Generate the javax.annotation.Generated annotation to indicate
jOOQ version used for source code.
Defaults to true -->
<generatedAnnotation>true</generatedAnnotation>
<!-- Generate jOOQ Record classes for type-safe querying. You can
turn this off, if you don't need "active records" for CRUD
Defaults to true -->
<records>true</records>
<!-- Generate POJOs in addition to Record classes for usage of the
ResultQuery.fetchInto(Class) API
Defaults to false -->
<pojos>false</pojos>
<!-- Generate immutable POJOs for usage of the ResultQuery.fetchInto(Class) API
This overrides any value set in <pojos/>
Defaults to false -->
<immutablePojos>false</immutablePojos>
<!-- Generate interfaces that will be implemented by records and/or pojos.
You can also use these interfaces in Record.into(Class<?>) and similar
methods, to let jOOQ return proxy objects for them.
Defaults to false -->
<interfaces>false</interfaces>
<!-- Generate DAOs in addition to POJO classes
Defaults to false -->
<daos>false</daos>
<!-- Annotate POJOs and Records with JPA annotations for increased
compatibility and better integration with JPA/Hibernate, etc
Defaults to false -->
<jpaAnnotations>false</jpaAnnotations>
<!-- Annotate POJOs and Records with JSR-303 validation annotations
Defaults to false -->
<validationAnnotations>false</validationAnnotations>
<!-- Allow to turn off the generation of global object references, which include
- Tables.java
- Sequences.java
- UDTs.java
Turning off the generation of the above files may be necessary for very
large schemas, which exceed the amount of allowed constants in a class's
constant pool (64k) or, whose static initialiser would exceed 64k of
byte code
Defaults to true -->
<globalObjectReferences>true</globalObjectReferences>
<!-- Generate fluent setters in
- records
- pojos
- interfaces
Fluent setters are against the JavaBeans specification, but can be quite
useful to those users who do not depend on EL, JSP, JSF, etc.
Defaults to false -->
<fluentSetters>false</fluentSetters>
</generate>

Property interdependencies

Some of the above properties depend on other properties to work correctly. For instance, when generating immutable pojos, pojos must be generated. jOOQ will enforce such properties even if you tell it otherwise. Here is a list of property interdependencies:

Configuring your code generator with Java, Groovy, etc.

In the previous sections, we have covered how to set up jOOQ's code generator using XML, either by running a standalone Java application, or by using Maven. However, it is also possible to use jOOQ's GenerationTool programmatically. The XSD file used for the configuration (http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd) is processed using XJC to produce Java artefacts. The below example uses those artefacts to produce the equivalent configuration of the previous PostgreSQL / Maven example:

Using custom generator strategies to override naming schemes

jOOQ allows you to override default implementations of the code generator or the generator strategy. Specifically, the latter can be very useful if you want to inject custom behaviour into jOOQ's code generator with respect to naming classes, members, methods, and other Java objects.

<!-- These properties can be added directly to the generator element: -->
<generator>
<!-- The default code generator. You can override this one, to generate your own code style
Defaults to org.jooq.util.JavaGenerator -->
<name>org.jooq.util.JavaGenerator</name>
<!-- The naming strategy used for class and field names.
You may override this with your custom naming strategy. Some examples follow
Defaults to org.jooq.util.DefaultGeneratorStrategy -->
<strategy>
<name>org.jooq.util.DefaultGeneratorStrategy</name>
</strategy>
</generator>

The following example shows how you can override the DefaultGeneratorStrategy to render table and column names the way they are defined in the database, rather than switching them to camel case:

/**
* It is recommended that you extend the DefaultGeneratorStrategy. Most of the
* GeneratorStrategy API is already declared final. You only need to override any
* of the following methods, for whatever generation behaviour you'd like to achieve
*
* Beware that most methods also receive a "Mode" object, to tell you whether a
* TableDefinition is being rendered as a Table, Record, POJO, etc. Depending on
* that information, you can add a suffix only for TableRecords, not for Tables
*/
public class AsInDatabaseStrategy extends DefaultGeneratorStrategy {
/**
* Override this to specifiy what identifiers in Java should look like.
* This will just take the identifier as defined in the database.
*/
@Override
public String getJavaIdentifier(Definition definition) {
return definition.getOutputName();
}
/**
* Override these to specify what a setter in Java should look like. Setters
* are used in TableRecords, UDTRecords, and POJOs. This example will name
* setters "set[NAME_IN_DATABASE]"
*/
@Override
public String getJavaSetterName(Definition definition, Mode mode) {
return "set" + definition.getOutputName();
}
/**
* Just like setters...
*/
@Override
public String getJavaGetterName(Definition definition, Mode mode) {
return "get" + definition.getOutputName();
}
/**
* Override this method to define what a Java method generated from a database
* Definition should look like. This is used mostly for convenience methods
* when calling stored procedures and functions. This example shows how to
* set a prefix to a CamelCase version of your procedure
*/
@Override
public String getJavaMethodName(Definition definition, Mode mode) {
return "call" + org.jooq.tools.StringUtils.toCamelCase(definition.getOutputName());
}
/**
* Override this method to define how your Java classes and Java files should
* be named. This example applies no custom setting and uses CamelCase versions
* instead
*/
@Override
public String getJavaClassName(Definition definition, Mode mode) {
return super.getJavaClassName(definition, mode);
}
/**
* Override this method to re-define the package names of your generated
* artefacts.
*/
@Override
public String getJavaPackageName(Definition definition, Mode mode) {
return super.getJavaPackageName(definition, mode);
}
/**
* Override this method to define how Java members should be named. This is
* used for POJOs and method arguments
*/
@Override
public String getJavaMemberName(Definition definition, Mode mode) {
return definition.getOutputName();
}
/**
* Override this method to define the base class for those artefacts that
* allow for custom base classes
*/
@Override
public String getJavaClassExtends(Definition definition, Mode mode) {
return Object.class.getName();
}
/**
* Override this method to define the interfaces to be implemented by those
* artefacts that allow for custom interface implementation
*/
@Override
public List<String> getJavaClassImplements(Definition definition, Mode mode) {
return Arrays.asList(Serializable.class.getName(), Cloneable.class.getName());
}
/**
* Override this method to define the suffix to apply to routines when
* they are overloaded.
*
* Use this to resolve compile-time conflicts in generated source code, in
* case you make heavy use of procedure overloading
*/
@Override
public String getOverloadSuffix(Definition definition, Mode mode, String overloadIndex) {
return "_OverloadIndex_" + overloadIndex;
}
}

An org.jooq.Table example:

This is an example showing which generator strategy method will be called in what place when generating tables. For improved readability, full qualification is omitted:

Using custom matcher strategies

In the previous section, we have seen how to override generator strategies programmatically. In this chapter, we'll see how such strategies can be configured in the XML or Maven code generator configuration. Instead of specifying a strategy name, you can also specify a <matchers/> element as such:

NOTE: All regular expressions that match object identifiers try to match identifiers first by unqualified name (org.jooq.util.Definition.getName()), then by qualified name (org.jooq.util.Definition.getQualifiedName()).

NOTE: There had been an incompatible change between jOOQ 3.2 and jOOQ 3.3 in the configuration of these matcher strategies. See Issue #3217 for details.

Power users might choose to re-implement large parts of the org.jooq.util.JavaGenerator class. If you only want to add some custom code sections, however, you can extend the JavaGenerator and override only parts of it.

Flags controlling sequence generation

Every procedure or function (routine) in your database will generate a org.jooq.Routine implementation that looks like this:

public class AuthorExists extends AbstractRoutine<java.lang.Void> {
// All IN, IN OUT, OUT parameters and function return values generate a static member
public static final Parameter<String> AUTHOR_NAME = createParameter("AUTHOR_NAME", SQLDataType.VARCHAR);
public static final Parameter<BigDecimal> RESULT = createParameter("RESULT", SQLDataType.NUMERIC);
// A constructor for a new "empty" procedure call
public AuthorExists() {
super("AUTHOR_EXISTS", TEST);
addInParameter(AUTHOR_NAME);
addOutParameter(RESULT);
}
// Every IN and IN OUT parameter generates a setter
public void setAuthorName(String value) {
setValue(AUTHOR_NAME, value);
}
// Every IN OUT, OUT and RETURN_VALUE generates a getter
public BigDecimal getResult() {
return getValue(RESULT);
}
// [...]
}

Package and member procedures or functions

Procedures or functions contained in packages or UDTs are generated in a sub-package that corresponds to the package or UDT name.

Flags controlling UDT generation

Sometimes, the actual database data type does not match the SQL data type that you would like to use in Java. This is often the case for ill-supported SQL data types, such as BOOLEAN or UUID. jOOQ's code generator allows you to apply simple data type rewriting. The following configuration will rewrite IS_VALID columns in all tables to be of type BOOLEAN.

When using a custom type in jOOQ, you need to let jOOQ know about its associated org.jooq.Converter. Ad-hoc usages of such converters has been discussed in the chapter about data type conversion. However, when mapping a custom type onto a standard JDBC type, a more common use-case is to let jOOQ know about custom types at code generation time (if you're using non-standard JDBC types, like for example JSON or HSTORE, see the manual's section about custom data type bindings). Use the following configuration elements to specify, that you'd like to use GregorianCalendar for all database fields that start with DATE_OF_

This means that the bound type of <T> will be GregorianCalendar, wherever you reference DATE_OF_BIRTH. jOOQ will use your custom converter when binding variables and when fetching data from java.util.ResultSet:

// Get all date of births of authors born after 1980
List<GregorianCalendar> result =
create.selectFrom(AUTHOR)
.where(AUTHOR.DATE_OF_BIRTH.gt(new GregorianCalendar(1980, 0, 1)))
.fetch(AUTHOR.DATE_OF_BIRTH);

The previous section discussed the case where your custom data type is mapped onto a standard JDBC type as contained in org.jooq.impl.SQLDataType. In some cases, however, you want to map your own type onto a type that is not explicitly supported by JDBC, such as for instance, PostgreSQL's various advanced data types like JSON or HSTORE, or PostGIS types. For this, you can register an org.jooq.Binding for relevant columns in your code generator. Consider the following trivial implementation of a binding for PostgreSQL's JSON data type, which binds the JSON string in PostgreSQL to a Google GSON object:

Registering bindings to the code generator

The above org.jooq.Binding implementation intercepts all the interaction on a JDBC level, such that jOOQ will never need to know how to crrectly serialise / deserialise your custom data type. Similar to what we've seen in the previous section about how to register Converters to the code generator, we can now register such a binding to the code generator. Note that you will reuse the same types of XML elements (<customType/> and <forcedType/>):

We've seen previously in the chapter about runtime schema mapping, that schemata and tables can be mapped at runtime to other names. But you can also hard-wire schema mapping in generated artefacts at code generation time, e.g. when you have 5 developers with their own dedicated developer databases, and a common integration database. In the code generation configuration, you would then write.

<schemata>
<schema>
<!-- Use this as the developer's schema: -->
<inputSchema>LUKAS_DEV_SCHEMA</inputSchema>
<!-- Use this as the integration / production database: -->
<outputSchema>PROD</outputSchema>
</schema>
</schemata>

Databases can become very large in real-world applications. This is not a problem for jOOQ's code generator, but it can be for the Java compiler. jOOQ generates some classes for global access. These classes can hit two sorts of limits of the compiler / JVM:

While there exist workarounds for the above two limitations (delegating initialisations to nested classes, inheriting constant literals from implemented interfaces), the preferred approach is either one of these:

Distribute your database objects in several schemas. That is probably a good idea anyway for such large databases

When using jOOQ's code generation capabilities, you will need to make a strategic decision about whether you consider your generated code as

Part of your code base

Derived artefacts

In this section we'll see that both approaches have their merits and that none of them is clearly better.

Part of your code base

When you consider generated code as part of your code base, you will want to:

Check in generated sources in your version control system

Use manual source code generation

Possibly use even partial source code generation

This approach is particularly useful when your Java developers are not in full control of or do not have full access to your database schema, or if you have many developers that work simultaneously on the same database schema, which changes all the time. It is also useful to be able to track side-effects of database changes, as your checked-in database schema can be considered when you want to analyse the history of your schema.

With this approach, you can also keep track of the change of behaviour in the jOOQ code generator, e.g. when upgrading jOOQ, or when modifying the code generation configuration.

The drawback of this approach is that it is more error-prone as the actual schema may go out of sync with the generated schema.

Derived artefacts

When you consider generated code to be derived artefacts, you will want to:

Regenerate jOOQ code on every machine - including continuous integration

This approach is particularly useful when you have a smaller database schema that is under full control by your Java developers, who want to profit from the increased quality of being able to regenerate all derived artefacts in every step of your build.

The drawback of this approach is that the build may break in perfectly acceptable situations, when parts of your database are temporarily unavailable.

Pragmatic combination

In some situations, you may want to choose a pragmatic combination, where you put only some parts of the generated code under version control. For instance, jOOQ-meta's generated sources are put under version control as few contributors will be able to run the jOOQ-meta code generator against all supported databases.

Many jOOQ users use jOOQ as a complementary SQL API in applications that mostly use JPA for their database interactions, e.g. to perform reporting, batch processing, analytics, etc.

In such a setup, you might have a pre-existing schema implemented using JPA-annotated entities. Your real database schema might not be accessible while developing, or it is not a first-class citizen in your application (i.e. you follow a Java-first approach). This section explains how you can generate jOOQ classes from such a JPA model. Consider this model:

Now, instead of connecting the jOOQ code generator to a database that holds a representation of the above schema, you can use jOOQ's JPADatabase and feed that to the code generator. The JPADatabase uses Hibernate internally, to generate an in-memory H2 database from your entities, and reverse-engineers that again back to jOOQ classes.

The easiest way forward is to use Maven in order to include the jooq-meta-extensions library (which then includes the H2 and Hibernate dependencies)

By default, jOOQ's code generator takes live database connections as a database meta data source. In many project setups, this might not be optimal, as the live database is not always available.

One way to circumvent this issue is by providing jOOQ with a database meta definition file in XML format and by passing this XML file to jOOQ's XMLDatabase.

The XMLDatabase can read a standardised XML file that implements the http://www.jooq.org/xsd/jooq-meta-3.5.4.xsd schema. Essentially, this schema is an XML representation of the SQL standard INFORMATION_SCHEMA, as implemented by databases like H2, HSQLDB, MySQL, PostgreSQL, or SQL Server.

If you already have a different XML format for your database, you can either XSL transform your own format into the one above via an additional Maven plugin, or pass the location of an XSL file to the XMLDatabase by providing an additional property:

There is no substantial difference between running the code generator with Maven or in standalone mode. Both modes use the exact same <configuration/> element. The Maven plugin configuration adds some additional boilerplate around that:

Using the Ant Maven plugin

Sometimes, ant can be useful to work around a limitation (misunderstanding?) of the Maven build. Just as with the above standalone ant usage example, the jOOQ code generator can be called from the maven-antrun-plugin:

Run generation with the Gradle plugin

We recommend using the Gradle plugin by Etienne Studer (from Gradle Inc.). It provides a concise DSL that allows you to tune all configuration properties supported by each jOOQ version. Please direct any support questions or issues you may find directly to the third party plugin vendor.

Alternatively, the XML MarkupBuilder can be used

If you don't want to use the above third party plugin, there's also the possibility to use jOOQ's standalone code generator for simplicity. The following working example build.gradle script should work out of the box:

When writing unit tests for your data access layer, you have probably used some generic mocking tool offered by popular providers like Mockito, jmock, mockrunner, or even DBUnit. With jOOQ, you can take advantage of the built-in JDBC mock API that allows you to emulate a simple database on the JDBC level for precisely those SQL/JDBC use cases supported by jOOQ.

Disclaimer: The general idea of mocking a JDBC connection with this jOOQ API is to provide quick workarounds, injection points, etc. using a very simple JDBC abstraction. It is NOT RECOMMENDED to emulate an entire database (including complex state transitions, transactions, locking, etc.) using this mock API. Once you have this requirement, please consider using an actual database instead for integration testing, rather than implementing your test database inside of a MockDataProvider.

Mocking the JDBC API

JDBC is a very complex API. It takes a lot of time to write a useful and correct mock implementation, implementing at least these interfaces:

Optionally, you may even want to implement interfaces, such as java.sql.Array, java.sql.Blob, java.sql.Clob, and many others. In addition to the above, you might need to find a way to simultaneously support incompatible JDBC minor versions, such as 4.0, 4.1

Using jOOQ's own mock API

This work is greatly simplified, when using jOOQ's own mock API. The org.jooq.tools.jdbc package contains all the essential implementations for both JDBC 4.0 and 4.1, which are needed to mock JDBC for jOOQ. In order to write mock tests, provide the jOOQ Configuration with a MockConnection, and implement the MockDataProvider:

You should return as many MockResult objects as there were query executions (in batch mode) or results (in fetch-many mode). Instead of an awkward JDBC ResultSet, however, you can construct a "friendlier" org.jooq.Result with your own record types. The jOOQ mock API will use meta data provided with this Result in order to create the necessary JDBC java.sql.ResultSetMetaData

Together with Gudu Software, we have created an Open Source SQL 2 jOOQ parser that takes native SQL statements as input and generates jOOQ code as output.

Gudu Software Ltd has been selling enterprise quality SQL software to hundreds of customers to help them migrate from one database to another using the General SQL Parser. Now you can take advantage of their knowledge to parse your SQL statements and transform them directly into jOOQ statements using a free trial version of SQL 2 jOOQ!

SQL 2 jOOQ is a joint venture by Gudu Software Limited and Data Geekery GmbH. We will ship, test and maintain this awesome new addition with our own deliverables. So far, SQL 2 jOOQ supports the MySQL and PostgreSQL dialects and it is in an alpha stadium. Please, community, provide as much feedback as possible to make this great tool rock even more!

Please take note of the fact that the sql2jooq library is Open Source, but it depends on the commercial gsp.jar parser, whose trial licensing terms can be seen here:

A list of supported databases

Every RDMBS out there has its own little specialties. jOOQ considers those specialties as much as possible, while trying to standardise the behaviour in jOOQ. In order to increase the quality of jOOQ, some 70 unit tests are run for syntax and variable binding verification, as well as some 400 integration tests with an overall of around 4000 queries for any of these databases:

jOOQ aims for hiding all JDBC details from jOOQ client API. Specifically, java.sql.Clob and java.sql.Blob are quite "harsh" APIs with a few caveats that may even depend on JDBC driver specifics.

Clob and Blob are resources (but not java.lang.AutoCloseable!) with open connections to the database. This makes no sense in an ordinary jOOQ context, when eagerly fetching all the results through fetch() methods. fetchLazy() and fetchStream() might be candidates where Clob and Blob types could make sense as the underlying java.sql.ResultSet and java.sql.PreparedStatement are still open while consuming these resources.

ByteArrayInputStream and ByteArrayOutputStream on the other hand are two different types which cannot be represented as a single Field<T> type. If either would be chosen as the <T> type, we'd get read-only or write-only fields. So for full lazy streaming support, we'd need another 2-way wrapper type, similar to Clob and Blob.

In many cases, streaming binary data isn't really necessary as thebyte[] can be easily kept in memory (and it is done so for further processing anyway, e.g. when working with images), so the extra work might not really be needed. This is particularly true in Oracle, where BLOBs are the only binary types in the absences of a formal (VAR)BINARY type, and CLOBs start at 4000 bytes.

Hence, jOOQ currently doesn't explicitly support JDBC BLOB and CLOB data types. If you use any of these data types in your database, jOOQ will map them to byte[] and String instead. In simple cases (small data), this simplification is sufficient. In more sophisticated cases, you may have to bypass jOOQ, in order to deal with these data types and their respective resources.

Some databases explicitly support unsigned integer data types. In most normal JDBC-based applications, they would just be mapped to their signed counterparts letting bit-wise shifting and tweaking to the user. jOOQ ships with a set of unsigned java.lang.Number implementations modelling the following types:

jOOQ fills a gap opened by JDBC, which neglects an important SQL data type as defined by the SQL standards: INTERVAL types. SQL knows two different types of intervals:

YEAR TO MONTH: This interval type models a number of months and years

DAY TO SECOND: This interval type models a number of days, hours, minutes, seconds and milliseconds

Both interval types ship with a variant of subtypes, such as DAY TO HOUR, HOUR TO SECOND, etc. jOOQ models these types as Java objects extending java.lang.Number: org.jooq.types.YearToMonth (where Number.intValue() corresponds to the absolute number of months) and org.jooq.types.DayToSecond (where Number.intValue() corresponds to the absolute number of milliseconds)

Interval arithmetic

In addition to the arithmetic expressions documented previously, interval arithmetic is also supported by jOOQ. Essentially, the following operations are supported:

Oracle typed arrays

Oracle's DATE data type does not conform to the SQL standard. It is really a TIMESTAMP(0), i.e. a TIMESTAMP with a fractional second precision of zero. The most appropriate JDBC type for Oracle DATE types is java.sql.Timestamp.

jOOQ takes SQL as an external domain-specific language and maps it onto Java, creating an internal domain-specific language. Internal DSLs cannot 100% implement their external language counter parts, as they have to adhere to the syntax rules of their host or target language (i.e. Java). This section explains the various problems and workarounds encountered and implemented in jOOQ.

SQL allows for "keywordless" syntax

SQL syntax does not always need keywords to form expressions. The UPDATE .. SET clause takes various argument assignments:

UPDATE t SET a = 1, b = 2

update(t).set(a, 1).set(b, 2)

The above example also shows missing operator overloading capabilities, where "=" is replaced by "," in jOOQ. Another example are row value expressions, which can be formed with parentheses only in SQL:

(a, b) IN ((1, 2), (3, 4))

row(a, b).in(row(1, 2), row(3, 4))

In this case, ROW is an actual (optional) SQL keyword, implemented by at least PostgreSQL.

SQL contains "composed" keywords

As most languages, SQL does not attribute any meaning to whitespace. However, whitespace is important when forming "composed" keywords, i.e. SQL clauses composed of several keywords. jOOQ follows standard Java method naming conventions to map SQL keywords (case-insensitive) to Java methods (case-sensitive, camel-cased). Some examples:

GROUP BY
ORDER BY
WHEN MATCHED THEN UPDATE

groupBy()
orderBy()
whenMatchedThenUpdate()

Future versions of jOOQ may use all-uppercased method names in addition to the camel-cased ones (to prevent collisions with Java keywords):

GROUP BY
ORDER BY
WHEN MATCHED THEN UPDATE

GROUP_BY()
ORDER_BY()
WHEN_MATCHED_THEN_UPDATE()

SQL contains "superfluous" keywords

Some SQL keywords aren't really necessary. They are just part of a keyword-rich language, the way Java developers aren't used to anymore. These keywords date from times when languages such as ADA, BASIC, COBOL, FORTRAN, PASCAL were more verbose:

BEGIN .. END

REPEAT .. UNTIL

IF .. THEN .. ELSE .. END IF

jOOQ omits some of those keywords when it is too tedious to write them in Java.

CASE WHEN .. THEN .. END

decode().when(.., ..)

The above example omits THEN and END keywords in Java. Future versions of jOOQ may comprise a more complete DSL, including such keywords again though, to provide a more 1:1 match for the SQL language.

SQL contains "superfluous" syntactic elements

Some SQL constructs are hard to map to Java, but they are also not really necessary. SQL often expects syntactic parentheses where they wouldn't really be needed, or where they feel slightly inconsistent with the rest of the SQL language.

LISTAGG(a, b) WITHIN GROUP (ORDER BY c)
OVER (PARTITION BY d)

listagg(a, b).withinGroupOrderBy(c)
.over().partitionBy(d)

The parentheses used for the WITHIN GROUP (..) and OVER (..) clauses are required in SQL but do not seem to add any immediate value. In some cases, jOOQ omits them, although the above might be optionally re-phrased in the future to form a more SQLesque experience:

SQL operators cannot be overloaded in Java

Most SQL operators have to be mapped to descriptive method names in Java, as Java does not allow operator overloading:

=
<>, !=
||
SET a = b

equal(), eq()
notEqual(), ne()
concat()
set(a, b)

For those users using jOOQ with Scala or Groovy, operator overloading and implicit conversion can be leveraged to enhance jOOQ:

=
<>, !=
||

===
<>, !==
||

SQL's reference before declaration capability

This is less of a syntactic SQL feature than a semantic one. In SQL, objects can be referenced before (i.e. "lexicographically before") they are declared. This is particularly true for aliasing

SELECT t.a
FROM my_table t

MyTable t = MY_TABLE.as("t");
select(t.a).from(t)

A more sophisticated example are common table expressions (CTE), which are currently not supported by jOOQ:

WITH t(a, b) AS (
SELECT 1, 2 FROM DUAL
)
SELECT t.a, t.b
FROM t

Common table expressions define a "derived column list", just like table aliases can do. The formal record type thus created cannot be typesafely verified by the Java compiler, i.e. it is not possible to formally dereference t.a from t.

With jOOQ being in the core of your application, you want to be sure that you can trust jOOQ. That is why jOOQ is heavily unit and integration tested with a strong focus on integration tests:

Unit tests

Unit tests are performed against dummy JDBC interfaces using http://jmock.org/. These tests verify that various org.jooq.QueryPart implementations render correct SQL and bind variables correctly.

Integration tests

This is the most important part of the jOOQ test suites. Some 1500 queries are currently run against a standard integration test database. Both the test database and the queries are translated into every one of the 14 supported SQL dialects to ensure that regressions are unlikely to be introduced into the code base.

For libraries like jOOQ, integration tests are much more expressive than unit tests, as there are so many subtle differences in SQL dialects. Simple mocks just don't give as much feedback as an actual database instance.

jOOQ integration tests run the weirdest and most unrealistic queries. As a side-effect of these extensive integration test suites, many corner-case bugs for JDBC drivers and/or open source databases have been discovered, feature requests submitted through jOOQ and reported mainly to CUBRID, Derby, H2, HSQLDB.

Code generation tests

For every one of the 14 supported integration test databases, source code is generated and the tiniest differences in generated source code can be discovered. In case of compilation errors in generated source code, new test tables/views/columns are added to avoid regressions in this field.

API Usability tests and proofs of concept

jOOQ is used in jOOQ-meta as a proof of concept. This includes complex queries such as the following Postgres query

This section is for all users of jOOQ 2.x who wish to upgrade to the next major release. In the next sub-sections, the most important changes are explained. Some code hints are also added to help you fix compilation errors.

Type-safe row value expressions

Support for row value expressions has been added in jOOQ 2.6. In jOOQ 3.0, many API parts were thoroughly (but often incompatibly) changed, in order to provide you with even more type-safety.

Here are some affected API parts:

[N] in Row[N] has been raised from 8 to 22. This means that existing row value expressions with degree >= 9 are now type-safe

Subqueries returned from DSL.select(...) now implement Select<Record[N]>, not Select<Record>

// SELECT statements are now more typesafe:
Record2<String, Integer> record = create.select(BOOK.TITLE, BOOK.ID).from(BOOK).where(ID.eq(1)).fetchOne();
Result<Record2<String, Integer>> result = create.select(BOOK.TITLE, BOOK.ID).from(BOOK).fetch();
// But Record2 extends Record. You don't have to use the additional typesafety:
Record record = create.select(BOOK.TITLE, BOOK.ID).from(BOOK).where(ID.eq(1)).fetchOne();
Result<?> result = create.select(BOOK.TITLE, BOOK.ID).from(BOOK).fetch();

SelectQuery and SelectXXXStep are now generic

In order to support type-safe row value expressions and type-safe Record[N] types, SelectQuery is now generic: SelectQuery<R>

SimpleSelectQuery and SimpleSelectXXXStep API were removed

The duplication of the SELECT API is no longer useful, now that SelectQuery and SelectXXXStep are generic.

The DSL: This class contains only static factory methods. All QueryParts constructed from this class are "unattached", i.e. queries that are constructed through DSL cannot be executed immediately. This is useful for subqueries.The DSL class corresponds to the static part of the jOOQ 2.x Factory type

The DSLContext: This type holds a reference to a Configuration and can construct executable ("attached") QueryParts.The DSLContext type corresponds to the non-static part of the jOOQ 2.x Factory / FactoryOperations type.

The FactoryOperations interface has been renamed to DSLContext. An example:

FieldProvider

The FieldProvider marker interface was removed. Its methods still exist on FieldProvider subtypes. Note, they have changed names from getField() to field() and from getIndex() to indexOf()

GroupField

GroupField has been introduced as a DSL marker interface to denote fields that can be passed to GROUP BY clauses. This includes all org.jooq.Field types. However, fields obtained from ROLLUP(), CUBE(), and GROUPING SETS() functions no longer implement Field. Instead, they only implement GroupField. An example:

NULL predicate

Beware! Previously, Field.eq(null) was translated internally to an IS NULL predicate. This is no longer the case. Binding Java "null" to a comparison predicate will result in a regular comparison predicate (which never returns true). This was changed for several reasons:

To most users, this was a surprising "feature".

Other predicates didn't behave in such a way, e.g. the IN predicate, the BETWEEN predicate, or the LIKE predicate.

Configuration

DSLContext, ExecuteContext, RenderContext, BindContext no longer extend Configuration for "convenience". From jOOQ 3.0 onwards, composition is chosen over inheritance as these objects are not really configurations. Most importantly

DSLContext is only a DSL entry point for constructing "attached" QueryParts

ExecuteContext has a well-defined lifecycle, tied to that of a single query execution

RenderContext has a well-defined lifecycle, tied to that of a single rendering operation

BindContext has a well-defined lifecycle, tied to that of a single variable binding operation

In order to resolve confusion that used to arise because of different lifecycle durations, these types are now no longer formally connected through inheritance.

ConnectionProvider

In order to allow for simpler connection / data source management, jOOQ externalised connection handling in a new ConnectionProvider type. The previous two connection modes are maintained backwards-compatibly (JDBC standalone connection mode, pooled DataSource mode). Other connection modes can be injected using:

Connection-related JDBC wrapper utility methods (commit, rollback, etc) have been moved to the new DefaultConnectionProvider. They're no longer available from the DSLContext. This had been confusing to some users who called upon these methods while operating in pool DataSource mode.

ExecuteListeners

ExecuteListeners can no longer be configured via Settings. Instead they have to be injected into the Configuration. This resolves many class loader issues that were encountered before. It also helps listener implementations control their lifecycles themselves.

Data type API

The data type API has been changed drastically in order to enable some new DataType-related features. These changes include:

The code generation configuration can no longer be loaded from .properties files. Only XML configurations are supported.

The master data type feature is no longer supported. This feature was unlikely to behave exactly as users expected. It is better if users write their own code generators to generate master enum data types from their database tables. jOOQ's enum mapping and converter features sufficiently cover interacting with such user-defined types.

The DSL subtypes are no longer instanciable. As DSL now only contains static methods, subclassing is no longer useful. There are still dialect-specific DSL types providing static methods for dialect-specific functions. But the code-generator no longer generates a schema-specific DSL

The concept of a "main key" is no longer supported. The code generator produces UpdatableRecords only if the underlying table has a PRIMARY KEY. The reason for this removal is the fact that "main keys" are not reliable enough. They were chosen arbitrarily among UNIQUE KEYs.

The UpdatableTable type has been removed. While adding significant complexity to the type hierarchy, this type adds not much value over a simple Table.getPrimaryKey() != null check.

The USE statement support has been removed from jOOQ. Its behaviour was ill-defined, while it didn't work the same way (or didn't work at all) in some databases.

jOOQ lives in a very challenging ecosystem. The Java to SQL interface is still one of the most important system interfaces. Yet there are still a lot of open questions, best practices and no "true" standard has been established. This situation gave way to a lot of tools, APIs, utilities which essentially tackle the same problem domain as jOOQ. jOOQ has gotten great inspiration from pre-existing tools and this section should give them some credit. Here is a list of inspirational tools in alphabetical order:

JPA: The de-facto standard in the javax.persistence packages, supplied by Oracle. Its annotations are useful to jOOQ as well.

OneWebSQL: A commercial SQL abstraction API with support for DAO source code generation, which was integrated also in jOOQ

QueryDSL: A "LINQ-port" to Java. It has a similar fluent API, a similar code-generation facility, yet quite a different purpose. While jOOQ is all about SQL, QueryDSL (like LINQ) is mostly about querying.