A Brief Overview over the Most Common jOOQ Types

Posted on by lukaseder

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:

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:

For other types, refer to the Scope Javadoc.

Settings

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

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:

… 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:

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:

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:

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:

And more.

Row

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

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:

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:

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:

SPI Types

jOOQ offers many SPI (Service Provider Interface) types to allow for enhancing jOOQ behaviour. Only some of them are listed here.

Converter

A Converter<T, U> is a simple pair of functions converting between a built-in JDBC type T (e.g. String, Integer, Date) and any arbitrary user-defined type U. The from (as in “from the database”) Function<T, U> converts database data whenever it is fetched from the JDBC ResultSet. The to (as in “to the database”) Function<U, T> converts the user defined type back to the database type whenever it is bound to a JDBC PreparedStatement.

A Converter can be added to generated code, optionally in addition to a Binding.

Binding

A Binding<T, U> is a set of functions specifying how jOOQ should interact with various JDBC “get” (on ResultSet, CallableStatement, SQLInput) and “set” methods (on PreparedStatement, SQLOutput), as well as functions that specify how a bind value should be rendered in generated SQL.

Unlike a Converter, a Binding overrides all of jOOQ’s interactions with JDBC in order to provide or bind a user defined type U.

Bookmark this

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

Published by lukaseder

I made jOOQ

Leave a Reply