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 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:
ConnectionProviderthat defines the semantics of
ConnectionProvider.release(Connection)for all queries executed in the context of this
- A set of
Queryexecution lifecycle management.
RecordMapperProviderthat defines and implements the behaviour of
Cursor.fetchInto(Class), and various related methods.
And many more, which you can see from the
It is made available from every
Scope type in the API, see below for details
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
QueryPartexpression tree to produce a SQL string and / or a list of bind variables.
DSLAPI that creates
Queryinstances in the context of a
Configuration. It shares the wrapped
ExecuteContext: Used for a single execution of a
Query, containing JDBC resources and other execution relevant objects. Can be accessed by the
For other types, refer to the
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
fetchSizeon the created
Settings.statementType: Whether to execute
PreparedStatementto 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
The DSL API is the most important API to work with jOOQ. It comes in 2 flavours.
The static DSL
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
The “context” DSL
Query that has been constructed from the
DSLContext type can be executed directly by using
ResultQuery.fetch(), or many other execution methods, including asynchronous or reactive ones.
You should never reference these types directly, nor see them in your own code. They are intermediate DSL artifacts
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 is a type that can be rendered in the context of a
String sql = ctx.render(TABLE.COLUMN.eq(1));
The most important
QueryPart subtypes include:
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
- A non-generated
Tablereference can be constructed using
- A plain SQL
Tablecan 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
- Aliased tables are table expressions that are provided with an alias to be declared in the
VALUES()table constructor can be used to generate in-memory table data.
- Derived tables are subqueries in the
- Table-valued functions are functions that produce table expressions
XMLTABLEis a table expression based on an XML document
JSON_TABLEis a table expression based on a JSON document
There are many more possible table expressions in jOOQ, all implementing the
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.
GROUP BYclause (special types of
GroupFieldexist to model
ORDER BYclause (special types of
OrderFieldexist to model sort specifications)
- As function arguments (e.g.
- As expression arguments (e.g.
And much more.
Condition is just a
Field<Boolean> with some additional API specific to
Condition building, including the possibility of calling
Condition::or, and others. Various clauses accept
Condition explicitly, including:
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
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.
ResultQuery is a
Query that can produce
Record values in various collection forms (e.g.
CompletionStage, etc.). It can be created from various
Query types by adding the
- A SQL string is generated
- Bind values are bound
- Possibly, a
In order to execute a
Query, it must be attached to a
Configuration, which is done most easily by creating the
Query from a
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 type is an
Iterable< 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.
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:
Record22to represent type safe versions of 1 – 22 column records produced from a
TableRecordto represent a
that originates from a single
, in case the
was generated using the code generator.
UpdatableRecordto represent a
TableRecordthat 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.
Found this list useful? Bookmark it, as we’ll add more types in the future in case new important concepts arise.