This is a note about jOOQ SQL building based on the official jOOQ document. jOOQ use BNF-based hierarchy DSL to emulate SQL in Java. It enables compile-time checking. jOOQ build a preser internally that consists of Query, ResultQuery, Table, Field, Row, Condition and Name.

1 SQL Statements

Internally, jOOQ uses query parts that delegates SQL rendering and variable binding to child components. Conditional expressions and column expressions are immutable. The constructed statements are muttable.

1.1 The Select Statement

The selectFrom() method fetches all columns from a single table. The select() method projects data. By default, it use Table.asterisk() or DSL.asterisk() to select all columns.

Records and raw value expressions up to degree 22 are typesafe.

The FROM clause specifies a number of table expressions to select data from. selectOne() returns 1 without a from clause.

jOOQ supports many types joins: inner join, left join, right join, full outer join, cross join, natural join and ect. It can be called on org.jooq.Table types or directly after the FROM clause.

InORDER BY clause, use asc() or desc() to transform a column expression into a SortField expression. Order by field index is also supported as one().asc() or inline(2).desc(). Use nullsLast() to put null values to the end. Case expression in order is also supported.

Use limit(m).offset(n) for paging. For better paing performance, use seek().

Use forUpdate() to create a record-lock.

Use union() or unionAll() to combine result sets. Similary, use intersect(), intersectAll(), except() and exceptAll() to process multiple sets.

1.2 The Insert Statement

Use insertInto(...).values(...).values(...) to insert one or more rows. Use defaultValues() to insert default values. jOOQ supports set(Column, value) to insert values. insertInto(table).select(selectFrom(...)) is used to insert from select.

Use onDuplicateKeyUpdate() or onDuplicateKeyIgnore() to support merge (or upsert).

jOOQ supports returnning() after insert.

1.3 The Update Statement

Update statement can only modify a single table. It supports set with select or rwo values expression. Update supports returning().

2 Expressions

2.1 Table Experssion

jOOQ’s can generate all tables from db schema as type safe Java ojbects. These tables can be used in SQL from, join and other places where table can be used.

Use as('alias') method to define a table alias. use field(colnm-name) to access a table column.

A select statement can appear almost anywhere a table expression can.

jOOQ use DUAL table if there is no from clause.

2.2 Column Expression

There are different ways to create a column expression:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// A regular table column expression
Field<String> field1 = BOOK.TITLE;
// A function created from the DSL using "prefix" notation
Field<String> field2 = trim(BOOK.TITLE);
// The same function created from a pre-existing Field using "postfix" notation
Field<String> field3 = BOOK.TITLE.trim();
// More complex function with advanced DSL syntax
Field<String> field4 = listAgg(BOOK.TITLE)
    .withinGroupOrderBy(BOOK.ID.asc())
    .over().partitionBy(AUTHOR.ID);

Columns can use aliases. Use cast(SQLDataType.VARCHAR(100)) or cast(String.class) to specify data type. Use coerce() to convert a type. Use collate() to specify a sorting order on a character set.

jOOQ suports arithmatic operations/functions, sting functions, date time functions , system functions, regular expressions, aggregate functions, window functions, group functions, and etc.

2.3 Conditional Expressions

eq(), ne(), or(), and(), lessThan(), between... and etc. Use all, any, some, in to compare a non-scalar table suquery with a scalar value. isNull() and isNotNull() to compare null value. For null-safe comparison, use isDistincFrom() or isNotDistinctFrom().

Use QBE (query by examaple) like condition(book) where book is a new book record.

2.4 Dynamic SQL

The table expressions, column expressions and conditional expressions can be constructed dynamically and resused across queries.

jOOQ supports using plain SQL strings in table/column/conditional expressions as well as funciton and query.

For example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
// 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();