A Brief Overview over the Most Common jOOQ Types

For new users working with jOOQ for the first time, the number of types in the jOOQ API can be overwhelming. The SQL language doesn’t have many such “visible” types, although if you think about SQL the way jOOQ does, then they’re there just the same, but hidden from users via an English style syntax.

This overview will list the most important jOOQ types in a cheat sheet form.

Configuration types

The Configuration is the single most important configuration type, which contains references to all other types of configuration, Settings, custom SPI implementations, JDBC or R2DBC Connection, etc. SPIs include:

• The ConnectionProvider that defines the semantics of ConnectionProvider.acquire() and ConnectionProvider.release(Connection) for all queries executed in the context of this Configuration.
• A set of ExecuteListenerProvider that implement Query execution lifecycle management.
• The RecordMapperProvider that defines and implements the behaviour of Record.into(Class), ResultQuery.fetchInto(Class), Cursor.fetchInto(Class), and various related methods.

And many more, which you can see from the Configuration Javadoc

It is made available from every Scope type in the API, see below for details

Scopes

The Scope types are various types that are created “in the scope” of a Configuration, and as such can provide access to all of the Configuration‘s contained objects and SPIs. This design allows for extremely flexible, programmatic dependency injection throughout the internals of jOOQ. Some of the most important Scope types include:

• Context: Used for a single traversal of a QueryPart expression tree to produce a SQL string and / or a list of bind variables.
• DSLContext: The DSL API that creates Query instances in the context of a Configuration. It shares the wrapped Configuration‘s lifecycle.
• ExecuteContext: Used for a single execution of a Query, containing JDBC resources and other execution relevant objects. Can be accessed by the ExecuteListener SPI.

For other types, refer to the Scope Javadoc.

Settings

Settings are mostly scalar flags that specify detailed behaviour in jOOQ. Some select examples include:

• Settings.executeLogging: To turn on/off the built in execute logging in jOOQ
• Settings.fetchSize: To specify the default JDBC fetchSize on the created Statement and PreparedStatement
• Settings.statementType: Whether to execute Statement or PreparedStatement to run your queries.

As of jOOQ 3.17, there are over 160 such settings, so we can’t list them all here. For more details, refer to the Settings Javadoc.

DSL types

The DSL API is the most important API to work with jOOQ. It comes in 2 flavours.

The static DSL

The static DSL contains entry points to every type of QueryPart construction DSLs, including:

• Query types
• Field types
• Condition types
• Table types

… and a lot more. All of these types are constructed statically, and as such, they do not have any Configuration attached.

The “context” DSL

The “context” DSL, represented by the DSLContext type, only offers constructing QueryPart types that profit from being created “in the context” of a Configuration. This is mainly just including:

• Query types

A Query that has been constructed from the DSLContext type can be executed directly by using Query.execute() or ResultQuery.fetch(), or many other execution methods, including asynchronous or reactive ones.

Step types

Throughout the DSL API, you will see so-called “Step” types, i.e. types with a “Step” suffix, such as e.g. SelectFromStep, which is the “Step” that gives access to the Select DSL’s FROM clause.

You should never reference these types directly, nor see them in your own code. They are intermediate DSL artifacts

QueryPart types

QueryPart is the common base type of the entire jOOQ expression tree, or model API. Every type that you construct with the DSL API will extend QueryPart, for example:

QueryPart p1 = TABLE;
QueryPart p2 = TABLE.COLUMN;
QueryPart p3 = TABLE.COLUMN.eq(1);

The above expressions produce a more specific type than QueryPart, which we’ll explain after, but they all extend QueryPart.

A QueryPart is a type that can be rendered in the context of a Configuration using DSLContext::render

String sql = ctx.render(TABLE.COLUMN.eq(1));

The most important QueryPart subtypes include:

Table

A Table can be used in a FROM clause of a SELECT statement, or as a target of a DML statement, and more. There are various different table types, including:

• The most common type of Table: a table reference from generated code, such as TABLE
• A non-generated Table reference can be constructed using DSL.table(Name)
• A plain SQL Table can be created using DSL.table(String). It can contain any SQL logic, including table expressions, which jOOQ may not support natively
• Joined tables are table expressions based on join operators and Table operands
• Aliased tables are table expressions that are provided with an alias to be declared in the FROM clause.
• The VALUES() table constructor can be used to generate in-memory table data.
• Derived tables are subqueries in the FROM clause.
• Table-valued functions are functions that produce table expressions
• XMLTABLE is a table expression based on an XML document
• JSON_TABLE is a table expression based on a JSON document

There are many more possible table expressions in jOOQ, all implementing the Table type. An example of using Table expressions is:

Table<?> joined = CUSTOMER
    .join(ADDRESS)
    .on(ADDRESS.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID));

While most jOOQ statements won’t work with such local variables, it’s always interesting to remember that with jOOQ, every query is a dynamic SQL query, and every SQL fragment is a fully self contained expression tree in Java, which can be assigned to any local variable or returned from a method, etc.

Field

A Field is a column expression, which can be used in lots of places throughout the jOOQ API, everywhere where column expressions can be used, including:

• The SELECT clause
• The GROUP BY clause (special types of GroupField exist to model GROUPING SETS)
• The ORDER BY clause (special types of OrderField exist to model sort specifications)
• As function arguments (e.g. LOG function)
• As expression arguments (e.g. CASE expression)

And much more.

Condition

A Condition is just a Field<Boolean> with some additional API specific to Condition building, including the possibility of calling Condition::and, Condition::or, and others. Various clauses accept Condition explicitly, including:

• The WHERE clause
• The CONNECT BY clause
• The HAVING clause
• The QUALIFY clause

And more.

Row

A Row or row value expression is used to model a tuple of values both for:

• Condition building where conditional operands have degree > 1
• Projecting nested records

Such tuples are useful to create a structural type that groups Field expressions into groups of re-usable objects. Some dialects also support nominal variants of this, called UDT (User Defined Type), and jOOQ can emulate UDTs via embeddable types.

Select

A Select is a special type of ResultQuery, which can be used as:

• Top level ResultQuery
• Scalar subquery
• Derived table
• Union subquery

ResultQuery

A ResultQuery is a Query that can produce Record values in various collection forms (e.g. Stream, Result, Publisher, CompletionStage, etc.). It can be created from various Query types by adding the RETURNING clause

Query

A Query is a Statement that can be executed, meaning:

• A SQL string is generated
• A PreparedStatement is prepared
• Bind values are bound
• The PreparedStatement is executed
• Possibly, a ResultSet is fetched.

In order to execute a Query, it must be attached to a Configuration, which is done most easily by creating the Query from a DSLContext.

Statement

A Statement (not the JDBC Statement!) is a QueryPart that represents a procedural statement in:

• An anonymous block
• A PROCEDURE body
• A FUNCTION body
• A TRIGGER body

All Query implementations can be used as Statement in such a procedural context.

QOM Types

The QOM (Query Object Model) types are an experimental set of types publicly declaring the internal model API, which is very useful for tree traversal and SQL transformation

Result types

When executing a ResultQuery, there are different types of Result supported by jOOQ, Result being the default:

Result

The Result type is a List<Record> with a lot of mapping convenience API. It models an eagerly fetched JDBC ResultSet, which contains all the results from the database and no more reference to the ResultSet itself. This is useful when the result set is moderately sized.

Cursor

The Cursor type is an Iterable<Record> with similar mapping convenience API as the Result type, but it contains an open JDBC ResultSet, allowing for fetching data from the server in a lazy way. This is useful when the result set is huge.

Record

A Record is a base type for a database record. It allows field based access of individual attributes as well as mapping convenience into custom data types. It specialises as:

• Record1 – Record22 to represent type safe versions of 1 – 22 column records produced from a ResultQuery.
• TableRecord to represent a Record that originates from a single Table, in case the Table was generated using the code generator.
• UpdatableRecord to represent a TableRecord that has a known primary key, meaning that any modifications to the record can be stored back to the database. This is very useful for basic CRUD operations.

Bookmark this

Found this list useful? Bookmark it, as we’ll add more types in the future in case new important concepts arise.