这是用户在 2025-1-17 8:54 为 https://www.jooq.org/doc/3.14/manual-single-page/#settings-attach-records 保存的双语快照页面,由 沉浸式翻译 提供双语支持。了解如何保存?

Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
可用版本: Dev3.20) |最新3.19) |3.18 |3.17 |3.16 |3.15 |3.14 |3.13 |3.12 |3.11 |3,10

Overview  概述

This manual is divided into six main sections:
本手册分为六个主要部分:

  • Getting started with jOOQ
    jOOQ 入门

    This section will get you started with jOOQ quickly. It contains simple explanations about what jOOQ is, what jOOQ isn't and how to set it up for the first time
    本节将帮助您快速开始使用 jOOQ。它包含关于 jOOQ 是什么、jOOQ 不是什么以及如何首次设置它的简单解释

  • SQL building  SQL 构建

    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
    本节介绍用于通过查询 DSL 和查询模型 API 构建查询的 jOOQ 语法的所有信息。它解释了中央工厂、支持的 SQL 语句和各种其他语法元素

  • Code generation  代码生成

    This section explains how to configure and use the built-in source code generator
    本节介绍如何配置和使用内置源代码生成器

  • SQL execution  SQL 执行

    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 执行哪些操作,以便执行查询、执行 CRUD 操作、导入和导出数据以及挂接到 jOOQ 执行生命周期中进行调试

  • Tools  工具

    This section is dedicated to tools that ship with jOOQ.
    本节专门介绍 jOOQ 附带的工具。

  • Reference  参考

    This section is a reference for elements in this manual
    本节是本手册中元素的参考

Table of contents  目录

1.
Copyright, License, and Trademarks
版权、许可和商标
2.
Getting started with jOOQ
jOOQ 入门
2.1.
How to read this manual
如何阅读本手册
2.2.
The sample database used in this manual
本手册中使用的示例数据库
2.3.
Different use cases for jOOQ
jOOQ 的不同用例
2.3.1.
jOOQ as a SQL builder without code generation
jOOQ 作为 SQL 构建器,无需生成代码
2.3.2.
jOOQ as a SQL builder with code generation
jOOQ 作为具有代码生成的 SQL 构建器
2.3.3.
jOOQ as a SQL executor
jOOQ 作为 SQL 执行程序
2.3.4.
jOOQ for CRUD  用于 CRUD 的 jOOQ
2.3.5.
jOOQ for PROs  专业人士的 jOOQ
2.4.
Downloading jOOQ  下载 jOOQ
2.5.
Tutorials  教程
2.5.1.
jOOQ in 7 easy steps
jOOQ 只需 7 个简单步骤
2.5.1.1.
Step 1: Preparation  第 1 步:准备
2.5.1.2.
Step 2: Your database  第 2 步:您的数据库
2.5.1.3.
Step 3: Code generation  第 3 步:代码生成
2.5.1.4.
Step 4: Connect to your database
第 4 步:连接到您的数据库
2.5.1.5.
Step 5: Querying  步骤 5:查询
2.5.1.6.
Step 6: Iterating  第 6 步:迭代
2.5.1.7.
Step 7: Explore!  第 7 步:探索!
2.5.2.
Using jOOQ with Flyway  将 jOOQ 与 Flyway 一起使用
2.5.3.
Using jOOQ with jbang  将 jOOQ 与 jbang 一起使用
2.6.
jOOQ and Java 8  jOOQ 和 Java 8
2.7.
jOOQ and Scala  jOOQ 和 Scala
2.8.
jOOQ and Groovy  jOOQ 和 Groovy
2.9.
jOOQ and Kotlin  jOOQ 和 Kotlin
2.10.
jOOQ and NoSQL  jOOQ 和 NoSQL
2.11.
jOOQ and JPA  jOOQ 和 JPA
2.12.
Build your own  构建您自己的
2.13.
jOOQ and backwards-compatibility
jOOQ 和向后兼容性
3.
SQL building  SQL 构建
3.1.
The query DSL type  查询 DSL 类型
3.1.1.
DSL subclasses  DSL 子类
3.2.
The DSLContext API
3.2.1.
SQL Dialect  SQL 方言
3.2.2.
SQL Dialect Family 
3.2.3.
Connection vs. DataSource 
3.2.4.
Custom data 
3.2.5.
Custom ExecuteListeners 
3.2.6.
Custom Unwrappers 
3.2.7.
Custom Settings 
3.2.7.1.
Auto-attach Records 
3.2.7.2.
Backslash Escaping 
3.2.7.3.
Batch size (new) 
3.2.7.4.
Execute Logging 
3.2.7.5.
Fetch Warnings 
3.2.7.6.
Identifier style 
3.2.7.7.
Implicit join type (new) 
3.2.7.8.
Inline Threshold 
3.2.7.9.
IN-list Padding 
3.2.7.10.
Interpreter Configuration 
3.2.7.11.
JDBC Flags 
3.2.7.12.
Keyword style 
3.2.7.13.
Listener Invocation Order 
3.2.7.14.
Locales 
3.2.7.15.
Map JPA Annotations 
3.2.7.16.
Object qualification 
3.2.7.17.
Optimistic Locking 
3.2.7.18.
Parameter name prefix 
3.2.7.19.
Parameter types 
3.2.7.20.
Parser Configuration 
3.2.7.21.
Reflection caching 
3.2.7.22.
Return all columns on store 
3.2.7.23.
Return Identity Value On Store 
3.2.7.24.
Runtime catalog, schema and table mapping 
3.2.7.25.
Scalar subqueries for stored functions 
3.2.7.26.
Statement Type 
3.2.7.27.
Updatable Primary Keys 
3.2.8.
Thread safety 
3.3.
SQL Statements (DML) 
3.3.1.
jOOQ's DSL and model API 
3.3.2.
The WITH clause 
3.3.3.
The WITH RECURSIVE clause 
3.3.4.
The SELECT statement 
3.3.4.1.
SELECT clause 
3.3.4.1.1.
Projection type safety 
3.3.4.1.2.
SelectField 
3.3.4.1.3.
SELECT * 
3.3.4.1.4.
SELECT * EXCEPT (...) 
3.3.4.1.5.
SELECT DISTINCT 
3.3.4.1.6.
SELECT DISTINCT ON 
3.3.4.1.7.
Convenience methods 
3.3.4.2.
FROM clause 
3.3.4.3.
JOIN operator 
3.3.4.4.
Implicit path JOIN 
3.3.4.5.
WHERE clause 
3.3.4.6.
CONNECT BY clause 
3.3.4.7.
GROUP BY clause 
3.3.4.7.1.
GROUP BY columns 
3.3.4.7.2.
GROUP BY ROLLUP 
3.3.4.7.3.
GROUP BY CUBE 
3.3.4.7.4.
GROUP BY GROUPING SETS 
3.3.4.7.5.
GROUP BY empty grouping set 
3.3.4.8.
HAVING clause 
3.3.4.9.
WINDOW clauseWINDOW clause 
3.3.4.10.
QUALIFY clause 
3.3.4.11.
ORDER BY clause 
3.3.4.11.1.
Ordering by field index 
3.3.4.11.2.
Ordering and NULLS 
3.3.4.11.3.
Ordering using CASE expressions 
3.3.4.11.4.
Oracle's ORDER SIBLINGS BY clause 
3.3.4.12.
LIMIT .. OFFSET clause 
3.3.4.13.
WITH TIES clause 
3.3.4.14.
SEEK clause 
3.3.4.15.
FOR clause (new) 
3.3.4.16.
FOR UPDATE clause 
3.3.4.17.
Set operations 
3.3.4.17.1.
Type safety 
3.3.4.17.2.
Projection rowtype 
3.3.4.17.3.
Differences to standard SQL 
3.3.4.17.4.
UNION 
3.3.4.17.5.
INTERSECT 
3.3.4.17.6.
EXCEPT 
3.3.4.18.
Lexical and logical SELECT clause order 
3.3.5.
The INSERT statement 
3.3.5.1.
INSERT .. VALUES 
3.3.5.2.
INSERT .. DEFAULT VALUES 
3.3.5.3.
INSERT .. SET 
3.3.5.4.
INSERT .. SELECT 
3.3.5.5.
INSERT .. ON DUPLICATE KEY UPDATE 
3.3.5.6.
INSERT .. ON DUPLICATE KEY IGNORE 
3.3.5.7.
INSERT .. ON CONFLICT 
3.3.5.8.
INSERT .. RETURNING 
3.3.6.
The UPDATE statement 
3.3.6.1.
UPDATE .. SET 
3.3.6.2.
UPDATE .. SET ROW 
3.3.6.3.
UPDATE .. FROM 
3.3.6.4.
UPDATE .. WHERE 
3.3.6.5.
UPDATE .. ORDER BY .. LIMIT 
3.3.6.6.
UPDATE .. RETURNING 
3.3.7.
The DELETE statement 
3.3.7.1.
DELETE .. USING 
3.3.7.2.
DELETE .. WHERE 
3.3.7.3.
DELETE .. ORDER BY .. LIMIT 
3.3.7.4.
UPDATE .. RETURNING 
3.3.8.
The MERGE statement 
3.4.
SQL Statements (DDL) 
3.4.1.
The ALTER statement 
3.4.1.1.
ALTER DATABASE (new) 
3.4.1.1.1.
ALTER DATABASE .. RENAME (new) 
3.4.1.1.2.
ALTER DATABASE IF EXISTS (new) 
3.4.1.2.
ALTER DOMAIN (new) 
3.4.1.2.1.
ALTER DOMAIN .. RENAME (new) 
3.4.1.2.2.
ALTER DOMAIN .. SET DEFAULT (new) 
3.4.1.2.3.
ALTER DOMAIN .. DROP DEFAULT (new) 
3.4.1.2.4.
ALTER DOMAIN .. SET NOT NULL (new) 
3.4.1.2.5.
ALTER DOMAIN .. DROP NOT NULL (new) 
3.4.1.2.6.
ALTER DOMAIN .. ADD CONSTRAINT (new) 
3.4.1.2.7.
ALTER DOMAIN .. RENAME CONSTRAINT (new) 
3.4.1.2.8.
ALTER DOMAIN .. RENAME CONSTRAINT IF EXISTS (new) 
3.4.1.2.9.
ALTER DOMAIN .. DROP CONSTRAINT (new) 
3.4.1.2.10.
ALTER DOMAIN .. DROP CONSTRAINT IF EXISTS (new) 
3.4.1.2.11.
ALTER DOMAIN IF EXISTS (new) 
3.4.1.3.
ALTER INDEX 
3.4.1.3.1.
ALTER INDEX .. RENAME 
3.4.1.3.2.
ALTER INDEX IF EXISTS 
3.4.1.4.
ALTER SCHEMA 
3.4.1.4.1.
ALTER SCHEMA .. RENAME 
3.4.1.4.2.
ALTER SCHEMA IF EXISTS 
3.4.1.5.
ALTER SEQUENCE 
3.4.1.5.1.
ALTER SEQUENCE .. RENAME 
3.4.1.5.2.
ALTER SEQUENCE .. CACHE 
3.4.1.5.3.
ALTER SEQUENCE .. CYCLE 
3.4.1.5.4.
ALTER SEQUENCE .. MINVALUE 
3.4.1.5.5.
ALTER SEQUENCE .. t reaches its MAXVALUE 
3.4.1.5.6.
ALTER SEQUENCE .. INCREMENT BY
3.4.1.5.7.
ALTER SEQUENCE .. START WITH
3.4.1.5.8.
ALTER SEQUENCE .. RESTART
3.4.1.5.9.
ALTER SEQUENCE IF EXISTS
3.4.1.6.
ALTER TABLE
3.4.1.6.1.
ALTER TABLE .. ADD COLUMN
3.4.1.6.2.
ALTER TABLE .. ADD COLUMN .. FIRST, BEFORE, AFTER
3.4.1.6.3.
ALTER TABLE .. ADD COLUMNS
3.4.1.6.4.
ALTER TABLE .. ADD COLUMN IF NOT EXISTS
3.4.1.6.5.
ALTER TABLE .. ADD PRIMARY KEY
3.4.1.6.6.
ALTER TABLE .. ADD UNIQUE
3.4.1.6.7.
ALTER TABLE .. ADD FOREIGN KEY
3.4.1.6.8.
ALTER TABLE .. ADD CHECK
3.4.1.6.9.
ALTER TABLE .. RENAME
3.4.1.6.10.
ALTER TABLE .. COMMENT
3.4.1.6.11.
ALTER TABLE .. ALTER COLUMN .. SET DEFAULT
3.4.1.6.12.
ALTER TABLE .. ALTER COLUMN .. DROP DEFAULT
3.4.1.6.13.
ALTER TABLE .. ALTER COLUMN .. SET NOT NULL
3.4.1.6.14.
ALTER TABLE .. ALTER COLUMN .. DROP NOT NULL
3.4.1.6.15.
ALTER TABLE .. ALTER COLUMN .. SET TYPE
3.4.1.6.16.
ALTER TABLE .. ALTER CONSTRAINT .. ENFORCED
3.4.1.6.17.
ALTER TABLE .. ALTER CONSTRAINT .. NOT ENFORCED
3.4.1.6.18.
ALTER TABLE .. RENAME COLUMN
3.4.1.6.19.
ALTER TABLE .. RENAME CONSTRAINT
3.4.1.6.20.
ALTER TABLE .. RENAME INDEX
3.4.1.6.21.
ALTER TABLE .. DROP COLUMN
3.4.1.6.22.
ALTER TABLE .. DROP COLUMN RESTRICT
3.4.1.6.23.
ALTER TABLE .. DROP COLUMN CASCADE
3.4.1.6.24.
ALTER TABLE .. DROP COLUMNS
3.4.1.6.25.
ALTER TABLE .. ADD COLUMN IF NOT EXISTS
3.4.1.6.26.
ALTER TABLE .. DROP CONSTRAINT
3.4.1.6.27.
ALTER TABLE .. DROP PRIMARY KEY
3.4.1.6.28.
ALTER TABLE .. DROP UNIQUE
3.4.1.6.29.
ALTER TABLE .. DROP FOREIGN KEY
3.4.1.6.30.
ALTER TABLE .. DROP CONSTRAINT IF EXISTS
3.4.1.6.31.
ALTER TABLE IF EXISTS
3.4.1.7.
ALTER TYPE
3.4.1.7.1.
ALTER TYPE .. RENAME
3.4.1.7.2.
ALTER TYPE .. for enum alterations
3.4.1.8.
ALTER VIEW
3.4.1.8.1.
ALTER VIEW .. COMMENT
3.4.1.8.2.
ALTER VIEW .. RENAME
3.4.1.8.3.
ALTER VIEW IF EXISTS
3.4.2.
The COMMENT statement
3.4.2.1.
COMMENT ON TABLE
3.4.2.2.
COMMENT ON VIEW
3.4.2.3.
COMMENT ON COLUMN
3.4.3.
The CREATE statement
3.4.3.1.
CREATE DATABASE (new)
3.4.3.2.
CREATE DOMAIN (new)
3.4.3.3.
CREATE INDEX
3.4.3.4.
CREATE SCHEMA
3.4.3.5.
CREATE SEQUENCE
3.4.3.5.1.
CREATE SEQUENCE IF NOT EXISTS
3.4.3.5.2.
CREATE SEQUENCE .. CACHE
3.4.3.5.3.
CREATE SEQUENCE .. CYCLE
3.4.3.5.4.
CREATE SEQUENCE .. MINVALUE
3.4.3.5.5.
CREATE SEQUENCE .. MAXVALUE
3.4.3.5.6.
CREATE SEQUENCE .. INCREMENT BY
3.4.3.5.7.
CREATE SEQUENCE .. START WITH
3.4.3.6.
CREATE TABLE
3.4.3.6.1.
Columns
3.4.3.6.2.
Nullability
3.4.3.6.3.
Defaults
3.4.3.6.4.
Identities
3.4.3.6.5.
Primary key
3.4.3.6.6.
Unique constraints
3.4.3.6.7.
Foreign keys
3.4.3.6.8.
Check constraints
3.4.3.6.9.
From a SELECT
3.4.3.6.10.
Temporary tables
3.4.3.7.
CREATE TYPE
3.4.3.8.
CREATE VIEW
3.4.3.8.1.
CREATE OR REPLACE VIEW
3.4.3.8.2.
WITH CHECK OPTION
3.4.3.8.3.
WITH READ ONLY
3.4.4.
The DROP statement
3.4.4.1.
DROP DATABASE (new)
3.4.4.1.1.
DROP DATABASE IF EXISTS (new)
3.4.4.2.
DROP DOMAIN (new)
3.4.4.2.1.
DROP DOMAIN IF EXISTS (new)
3.4.4.3.
DROP INDEX
3.4.4.3.1.
DROP INDEX IF EXISTS
3.4.4.4.
DROP SCHEMA
3.4.4.4.1.
DROP SCHEMA IF EXISTS
3.4.4.5.
DROP SEQUENCE
3.4.4.5.1.
DROP SEQUENCE IF EXISTS
3.4.4.6.
DROP TABLE
3.4.4.6.1.
DROP TABLE IF EXISTS
3.4.4.7.
DROP TYPE
3.4.4.7.1.
DROP TYPE IF EXISTS
3.4.4.8.
DROP VIEW
3.4.4.8.1.
DROP VIEW IF EXISTS
3.4.5.
The GRANT statement
3.4.6.
The REVOKE statement
3.4.7.
The SET statement
3.4.7.1.
SET CATALOG
3.4.7.2.
SET SCHEMA
3.4.8.
The TRUNCATE statement
3.4.9.
Generating DDL from objects
3.5.
Procedural statements
3.5.1.
Block statement
3.5.2.
CONTINUE statement
3.5.3.
EXECUTE statement
3.5.4.
EXIT statement
3.5.5.
FOR statement
3.5.6.
GOTO statement
3.5.7.
IF statement
3.5.8.
Labels
3.5.9.
LOOP statement
3.5.10.
REPEAT statement
3.5.11.
Variables
3.5.12.
WHILE statement
3.6.
Catalog and schema expressions
3.7.
Table expressions
3.7.1.
Generated Tables
3.7.2.
Aliased Tables
3.7.2.1.
Aliased generated tables
3.7.2.2.
Aliased table expressions
3.7.2.3.
Derived column lists
3.7.2.4.
Unnamed derived tables
3.7.3.
Joined tables
3.7.3.1.
CROSS JOIN
3.7.3.2.
INNER JOIN
3.7.3.3.
OUTER JOIN
3.7.3.4.
SEMI JOIN
3.7.3.5.
ANTI JOIN
3.7.3.6.
ON clause
3.7.3.7.
ON KEY clause
3.7.3.8.
USING clause
3.7.3.9.
NATURAL clause
3.7.3.10.
LATERAL
3.7.3.11.
APPLY
3.7.3.12.
PARTITION BY
3.7.4.
The VALUES() table constructor
3.7.5.
Derived tables
3.7.6.
Inline derived tables
3.7.7.
The Oracle PIVOT clause
3.7.8.
Relational division
3.7.9.
Array and cursor unnesting
3.7.10.
Table-valued functions
3.7.11.
GENERATE_SERIES
3.7.12.
JSON_TABLE (new)
3.7.13.
XMLTABLE (new)
3.7.14.
The DUAL table
3.7.15.
Temporal tables
3.8.
Column expressions
3.8.1.
Table columns
3.8.1.1.
Generated table columns
3.8.1.2.
Dereferenced table columns
3.8.1.3.
Named table columns
3.8.2.
Aliased columns
3.8.3.
Cast expressions
3.8.4.
Datatype coercions
3.8.5.
Collations
3.8.6.
Arithmetic expressions
3.8.7.
String concatenation
3.8.8.
Case sensitivity with strings
3.8.9.
General functions
3.8.9.1.
CHOOSE
3.8.9.2.
COALESCE
3.8.9.3.
DECODE
3.8.9.4.
IIF
3.8.9.5.
NULLIF
3.8.9.6.
NVL
3.8.9.7.
NVL2
3.8.10.
Numeric functions
3.8.10.1.
ABS
3.8.10.2.
ACOS
3.8.10.3.
ASIN
3.8.10.4.
ATAN
3.8.10.5.
ATAN2
3.8.10.6.
CEIL
3.8.10.7.
COS
3.8.10.8.
COSH
3.8.10.9.
COT
3.8.10.10.
COTH
3.8.10.11.
DEG
3.8.10.12.
E
3.8.10.13.
EXP
3.8.10.14.
FLOOR
3.8.10.15.
GREATEST
3.8.10.16.
LEAST
3.8.10.17.
LN
3.8.10.18.
LOG
3.8.10.19.
NEG
3.8.10.20.
PI
3.8.10.21.
POWER
3.8.10.22.
RAD
3.8.10.23.
RAND
3.8.10.24.
ROUND
3.8.10.25.
SIGN
3.8.10.26.
SIN
3.8.10.27.
SINH
3.8.10.28.
SQRT
3.8.10.29.
TAN
3.8.10.30.
TANH
3.8.10.31.
TRUNC
3.8.10.32.
WIDTH_BUCKET
3.8.11.
Bitwise functions
3.8.11.1.
BIT_AND
3.8.11.2.
BIT_COUNT
3.8.11.3.
BIT_NAND
3.8.11.4.
BIT_NOR
3.8.11.5.
BIT_NOT
3.8.11.6.
BIT_OR
3.8.11.7.
BIT_XNOR
3.8.11.8.
BIT_XOR
3.8.11.9.
SHL
3.8.11.10.
SHR
3.8.12.
String functions
3.8.12.1.
ASCII
3.8.12.2.
BIT_LENGTH
3.8.12.3.
CONCAT (|| operator)
3.8.12.4.
LEFT
3.8.12.5.
LENGTH
3.8.12.6.
LOWER
3.8.12.7.
LPAD
3.8.12.8.
LTRIM
3.8.12.9.
MD5
3.8.12.10.
MID
3.8.12.11.
OCTET_LENGTH
3.8.12.12.
OVERLAY (new)
3.8.12.13.
POSITION
3.8.12.14.
REGEXP_REPLACE (new)
3.8.12.15.
REPEAT
3.8.12.16.
REPLACE
3.8.12.17.
REVERSE
3.8.12.18.
RIGHT
3.8.12.19.
RPAD
3.8.12.20.
RTRIM
3.8.12.21.
SPACE
3.8.12.22.
SUBSTRING
3.8.12.23.
TO_CHAR (new)
3.8.12.24.
TRANSLATE
3.8.12.25.
TRIM
3.8.12.26.
UPPER
3.8.13.
Datetime functions
3.8.13.1.
CENTURY
3.8.13.2.
CURRENT_DATE
3.8.13.3.
CURRENT_LOCALDATE
3.8.13.4.
CURRENT_LOCALDATETIME
3.8.13.5.
CURRENT_LOCALTIME
3.8.13.6.
CURRENT_OFFSETDATETIME
3.8.13.7.
CURRENT_OFFSETTIME
3.8.13.8.
CURRENT_TIME
3.8.13.9.
CURRENT_TIMESTAMP
3.8.13.10.
DATE
3.8.13.11.
DATEADD
3.8.13.12.
DATEDIFF
3.8.13.13.
DATESUB
3.8.13.14.
DAY
3.8.13.15.
DAY_OF_YEAR
3.8.13.16.
DECADE
3.8.13.17.
EPOCH
3.8.13.18.
EXTRACT
3.8.13.19.
HOUR
3.8.13.20.
ISO_DAY_OF_WEEK
3.8.13.21.
LOCALDATE
3.8.13.22.
LOCALDATEADD
3.8.13.23.
LOCALDATESUB
3.8.13.24.
LOCALDATETIME
3.8.13.25.
LOCALDATETIMEADD
3.8.13.26.
LOCALDATETIMESUB
3.8.13.27.
LOCALTIME
3.8.13.28.
MILLENNIUM
3.8.13.29.
MINUTE
3.8.13.30.
MONTH
3.8.13.31.
QUARTER
3.8.13.32.
SECOND
3.8.13.33.
TIME
3.8.13.34.
TIMESTAMP
3.8.13.35.
TIMESTAMPADD
3.8.13.36.
TIMESTAMPSUB
3.8.13.37.
TO_DATE
3.8.13.38.
TO_LOCALDATE
3.8.13.39.
TO_LOCALDATETIME
3.8.13.40.
TO_TIMESTAMP
3.8.13.41.
TRUNC
3.8.13.42.
YEAR
3.8.14.
ARRAY functions
3.8.14.1.
ARRAY_GET (new)
3.8.14.2.
ARRAY constructor
3.8.14.3.
CARDINALITY (new)
3.8.15.
JSON functions
3.8.15.1.
JSON_ARRAY
3.8.15.2.
JSON_OBJECT
3.8.15.3.
JSON_VALUE (new)
3.8.16.
XML functions (new)
3.8.16.1.
XMLATTRIBUTES (new)
3.8.16.2.
XMLCOMMENT (new)
3.8.16.3.
XMLCONCAT (new)
3.8.16.4.
XMLDOCUMENT (new)
3.8.16.5.
XMLELEMENT (new)
3.8.16.6.
XMLFOREST (new)
3.8.16.7.
XMLPARSE (new)
3.8.16.8.
XMLPI (new)
3.8.16.9.
XMLQUERY (new)
3.8.17.
System functions
3.8.17.1.
CURRENT_SCHEMA
3.8.17.2.
CURRENT_USER
3.8.18.
Aggregate functions
3.8.18.1.
Grouping
3.8.18.2.
Distinctness
3.8.18.3.
Filtering
3.8.18.4.
Ordering
3.8.18.5.
Ordering WITHIN GROUP
3.8.18.6.
Keeping
3.8.18.7.
ARRAY_AGG
3.8.18.8.
AVG
3.8.18.9.
BOOL_AND
3.8.18.10.
BOOL_OR
3.8.18.11.
COLLECT
3.8.18.12.
COUNT
3.8.18.13.
CUME_DIST
3.8.18.14.
DENSE_RANK
3.8.18.15.
EVERY
3.8.18.16.
GROUP_CONCAT
3.8.18.17.
JSON_ARRAYAGG (new)
3.8.18.18.
JSON_OBJECTAGG (new)
3.8.18.19.
LISTAGG
3.8.18.20.
MAX
3.8.18.21.
MEDIAN
3.8.18.22.
MIN
3.8.18.23.
MODE
3.8.18.24.
PERCENT_RANK
3.8.18.25.
PERCENTILE_CONT
3.8.18.26.
PERCENTILE_DISC
3.8.18.27.
PRODUCT
3.8.18.28.
RANK
3.8.18.29.
SUM
3.8.18.30.
XMLAGG (new)
3.8.19.
Window functions
3.8.19.1.
PARTITION BY
3.8.19.2.
ORDER BY
3.8.19.3.
ROWS, RANGE, GROUPS (frame clause)
3.8.19.4.
EXCLUDE
3.8.19.5.
NULL treatment
3.8.19.6.
FROM FIRST, FROM LAST
3.8.19.7.
Nested aggregate functions
3.8.19.8.
Window aggregation
3.8.19.9.
Window ordered aggregate
3.8.19.10.
ROW_NUMBER
3.8.19.11.
RANK
3.8.19.12.
DENSE_RANK
3.8.19.13.
PERCENT_RANK
3.8.19.14.
CUME_DIST
3.8.19.15.
NTILE
3.8.19.16.
LEAD
3.8.19.17.
LAG
3.8.19.18.
FIRST_VALUE
3.8.19.19.
LAST_VALUE
3.8.19.20.
NTH_VALUE
3.8.20.
User-defined functions
3.8.21.
User-defined aggregate functions
3.8.22.
The CASE expression
3.8.23.
Sequences and serials
3.8.24.
Scalar subqueries
3.8.25.
Tuples or row value expressions
3.9.
Conditional expressions
3.9.1.
Condition building
3.9.2.
TRUE and FALSE condition
3.9.3.
BOOLEAN columns
3.9.4.
AND, OR, NOT boolean operators
3.9.5.
Boolean operator precedence
3.9.6.
Comparison predicate
3.9.7.
Comparison predicate (degree > 1)
3.9.8.
Quantified comparison predicate
3.9.9.
BETWEEN predicate
3.9.10.
BETWEEN predicate (degree > 1)
3.9.11.
DISTINCT predicate
3.9.12.
DISTINCT predicate (degree > 1)
3.9.13.
DOCUMENT predicate (new)
3.9.14.
EXISTS predicate
3.9.15.
IN predicate
3.9.16.
IN predicate (degree > 1)
3.9.17.
JSON predicate
3.9.18.
JSON_EXISTS predicate (new)
3.9.19.
LIKE predicate
3.9.20.
Quantified LIKE predicate
3.9.21.
NULL predicate
3.9.22.
NULL predicate (degree > 1)
3.9.23.
OVERLAPS predicate
3.9.24.
SIMILAR TO predicate
3.9.25.
UNIQUE predicate
3.9.26.
XMLEXISTS predicate (new)
3.9.27.
Query By Example (QBE)
3.10.
Operator precedence
3.11.
Synthetic SQL clauses
3.12.
Dynamic SQL
3.12.1.
Optional conditional expressions
3.13.
Plain SQL
3.14.
Plain SQL Templating Language
3.15.
Hints
3.15.1.
MySQL hints
3.15.1.1.
Index hints
3.15.1.2.
STRAIGHT_JOIN
3.15.1.3.
Oracle style hints in MySQL
3.15.2.
Oracle hints
3.15.3.
SQL Server hints
3.15.3.1.
WITH
3.15.3.2.
OPTION
3.16.
SQL Parser
3.16.1.
SQL Parser API
3.16.2.
SQL Parser CLI
3.16.3.
SQL translator
3.16.4.
SQL Parser Grammar
3.17.
SQL interpreter
3.18.
Schema diff
3.19.
Schema diff CLI
3.20.
Names and identifiers
3.21.
Bind values and parameters
3.21.1.
Indexed parameters
3.21.2.
Named parameters
3.21.3.
Inlined parameters
3.21.4.
SQL injection
3.22.
QueryParts
3.22.1.
SQL rendering
3.22.2.
Declaration vs reference
3.22.3.
Pretty printing SQL
3.22.4.
Variable binding
3.22.5.
Custom data type bindings
3.22.6.
Custom syntax elements
3.22.7.
Plain SQL QueryParts
3.22.8.
Serializability
3.22.9.
SQL transformation
3.22.9.1.
ANSI JOIN to table lists
3.22.9.2.
Table lists to ANSI JOIN (new)
3.22.9.3.
ROWNUM to LIMIT (new)
3.22.9.4.
Unnecessary arithmetic expressions (new)
3.22.10.
Custom SQL transformation with VisitListener
3.22.10.1.
Example: Logging abbreviated bind values
3.23.
Zero-based vs one-based APIs
3.24.
SQL building in Kotlin (new)
3.24.1.
Kotlin BOOLEAN value expressions (new)
3.25.
SQL building in Scala
4.
SQL execution
4.1.
Comparison between jOOQ and JDBC
4.2.
Query vs. ResultQuery
4.3.
Fetching
4.3.1.
Record vs. TableRecord
4.3.2.
Record1 to Record22
4.3.3.
Arrays, Maps and Lists
4.3.4.
ResultQuery as Iterable
4.3.5.
RecordMapper
4.3.6.
POJOs
4.3.7.
RecordMapperProvider
4.3.8.
ConverterProvider (new)
4.3.9.
Lazy fetching
4.3.10.
Lazy fetching with Streams
4.3.11.
Many fetching
4.3.12.
Later fetching
4.3.13.
Reactive Fetching
4.3.14.
ResultSet fetching
4.3.15.
Auto data type conversion
4.3.16.
Custom data type conversion
4.4.
Static statements vs. Prepared Statements
4.5.
Reusing a Query's PreparedStatement
4.6.
JDBC flags
4.7.
Using JDBC batch operations
4.8.
Sequence execution
4.9.
Stored procedures and functions
4.9.1.
Oracle Packages
4.9.2.
Oracle member procedures
4.10.
Exporting to XML, CSV, JSON, HTML, Text, Charts
4.10.1.
Exporting XML
4.10.2.
Exporting CSV
4.10.3.
Exporting JSON
4.10.4.
Exporting HTML
4.10.5.
Exporting Text
4.10.6.
Exporting Charts
4.11.
Importing data
4.11.1.
The Loader API
4.11.2.
Import options
4.11.2.1.
Throttling
4.11.2.2.
Duplicate handling
4.11.2.3.
Error handling
4.11.3.
Import data sources
4.11.3.1.
Importing CSV
4.11.3.2.
Importing JSON
4.11.3.3.
Importing records
4.11.3.4.
Importing arrays
4.11.3.5.
Importing XML
4.11.4.
Import listeners
4.11.5.
Import result and error handling
4.12.
CRUD with UpdatableRecords
4.12.1.
Simple CRUD
4.12.2.
Records' internal flags
4.12.3.
IDENTITY values
4.12.4.
Navigation methods
4.12.5.
Non-updatable records
4.12.6.
Optimistic locking
4.12.7.
Batch execution
4.12.8.
CRUD SPI: RecordListener
4.13.
DAOs
4.14.
Transaction management
4.15.
Exception handling
4.16.
ExecuteListeners
4.17.
Database meta data
4.17.1.
JDBC meta data
4.17.2.
Interpreted meta data
4.17.3.
XML meta data
4.17.4.
Generated meta data
4.18.
JDBC Connection
4.19.
Batched Connection (new)
4.20.
Mocking Connection
4.21.
Mock File Database
4.22.
Parsing Connection
4.23.
Diagnostics
4.23.1.
Too Many Rows
4.23.2.
Too Many Columns
4.23.3.
Duplicate Statements
4.23.4.
Repeated statements
4.23.5.
WasNull calls
4.24.
Logging with LoggerListener
4.25.
Logging Connection
4.26.
Performance considerations
4.27.
Alternative execution models
4.27.1.
Using jOOQ with Spring's JdbcTemplate
4.27.2.
Using jOOQ with JPA
4.27.2.1.
Using jOOQ with JPA Native Query
4.27.2.2.
Using jOOQ with JPA entities
4.27.2.3.
Using jOOQ with JPA EntityResult
5.
Code generation
5.1.
Configuration and setup of the generator
5.2.
Advanced generator configuration
5.2.1.
Logging
5.2.2.
Error handling
5.2.3.
Jdbc
5.2.4.
Generator
5.2.5.
Database
5.2.5.1.
Database name and properties
5.2.5.2.
RegexFlags
5.2.5.3.
Includes and Excludes
5.2.5.4.
Include object types
5.2.5.5.
Record Version and Timestamp Fields
5.2.5.6.
Synthetic objects (new)
5.2.5.6.1.
Synthetic identities
5.2.5.6.2.
Synthetic primary keys
5.2.5.6.3.
Synthetic unique keys (new)
5.2.5.6.4.
Synthetic foreign keys (new)
5.2.5.7.
Date as timestamp
5.2.5.8.
Ignore procedure return values (deprecated)
5.2.5.9.
Unsigned types
5.2.5.10.
Catalog and schema mapping
5.2.5.11.
Catalog and schema version providers
5.2.5.12.
Custom ordering of generated code
5.2.5.13.
Forced types
5.2.5.13.1.
Matching of forced types
5.2.5.13.2.
Data type rewriting
5.2.5.13.3.
Qualified converters
5.2.5.13.4.
Inline converters
5.2.5.13.5.
Lambda converters (new)
5.2.5.13.6.
Enum converters
5.2.5.13.7.
Data type bindings
5.2.5.14.
Table valued functions
5.2.6.
Generate
5.2.6.1.
Annotations
5.2.6.2.
Covariant overrides
5.2.6.2.1.
Overriding as()
5.2.6.2.2.
Overriding rename()
5.2.6.3.
Default catalog and schema
5.2.6.4.
Fluent setters
5.2.6.5.
Fully Qualified Types
5.2.6.6.
Global Artefacts
5.2.6.7.
Implicit JOIN paths
5.2.6.8.
Java Time Types
5.2.6.9.
Serial Version UID (new)
5.2.6.10.
Sources
5.2.6.11.
Whitespace (newlines and indentation)
5.2.6.12.
Zero Scale Decimal Types
5.2.7.
Output target configuration
5.3.
Custom code generation dependencies
5.4.
Programmatic generator configuration
5.5.
Custom generator strategies
5.6.
Matcher strategies
5.6.1.
MatcherRule
5.6.2.
Matching catalogs (new)
5.6.3.
Matching schemas
5.6.4.
Matching tables
5.6.5.
Matching fields
5.6.6.
Matching routines
5.6.7.
Matching sequences
5.6.8.
Matching enums
5.6.9.
Matching embeddables (new)
5.6.10.
Matcher examples
5.7.
Custom code sections
5.8.
Generated global artefacts
5.9.
Generated tables
5.10.
Generated records
5.11.
Generated POJOs
5.12.
Generated Interfaces
5.13.
Generated DAOs
5.14.
Generated sequences
5.15.
Generated procedures
5.16.
Generated domains (new)
5.17.
Generated UDTs
5.18.
Embeddable types (new)
5.18.1.
Configuration (new)
5.18.2.
Overlapping embeddable types (new)
5.18.3.
Field replacement (new)
5.18.4.
Embedded keys (new)
5.18.5.
Embedded domains (new)
5.19.
Mapping generated catalogs and schemas
5.20.
Code generation for large schemas
5.21.
Code generation and version control
5.22.
JPADatabase: Code generation from entities
5.23.
XMLDatabase: Code generation from XML files
5.24.
DDLDatabase: Code generation from SQL files
5.25.
LiquibaseDatabase: Code generation from Liquibase XML, YAML, JSON files
5.26.
XMLGenerator: Generating XML
5.27.
KotlinGenerator (new)
5.28.
ScalaGenerator (new)
5.29.
Running the code generator with Maven
5.30.
Running the code generator with Ant
5.31.
Running the code generator with Gradle
5.32.
System properties governing code generation
5.33.
Features requiring generated code
6.
Tools
6.1.
API validation using the Checker Framework or Error Prone
6.2.
jOOQ Refaster
6.3.
jOOQ Console
7.
Coming from JPA
7.1.
Set based thinking
7.2.
Database first
7.3.
Eager or lazy loading
7.4.
First level cache and second level cache
7.5.
Embeddable (new)
7.6.
AttributeConverter
7.7.
User types
7.8.
Implicit JOIN
8.
Reference
8.1.
Supported RDBMS
8.2.
Commercial only features
8.3.
Experimental features
8.4.
Data types
8.4.1.
BLOBs and CLOBs
8.4.2.
BOOLEAN data type
8.4.3.
Unsigned integer types
8.4.4.
INTERVAL data types
8.4.5.
JSON data types
8.4.6.
XML data types
8.4.7.
Geospatial data types
8.4.8.
CURSOR data types
8.4.9.
ARRAY and TABLE data types
8.4.10.
Oracle DATE data type
8.4.11.
Domains (new)
8.5.
SQL to DSL mapping rules
8.6.
Quality Assurance
8.7.
Security
8.7.1.
SQL Injection
8.7.2.
Debug logging
8.7.3.
Exception message
8.7.4.
Contact
8.8.
Migrating to jOOQ 3.0
8.9.
Don't do this
8.9.1.
jOOQ: Implementing the DSL types
8.9.2.
jOOQ: Referencing the Step types
8.9.3.
Schema: NULL columns
8.9.4.
Schema: Unnamed constraints
8.9.5.
Schema: Unnecessary surrogate keys
8.9.6.
Schema: Wrong data types
8.9.7.
SQL: COUNT(*) instead of EXISTS()
8.9.8.
SQL: N+1
8.9.9.
SQL: NATURAL JOIN or JOIN USING
8.9.10.
SQL: NOT IN predicate
8.9.11.
SQL: ORDER BY [column index]
8.9.12.
SQL: Rely on implicit ordering
8.9.13.
SQL: SELECT *
8.9.14.
SQL: SELECT DISTINCT
8.9.15.
SQL: Unnecessary UNION instead of UNION ALL
8.10.
The most important jOOQ types
8.11.
Credits
 

1. Copyright, License, and Trademarks

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

Please contact legal@datageekery.com, should you have any questions regarding licensing.

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 https://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

 https://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: https://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

    https://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 Data Geekery™ GmbH

  • jOOλ™ is a trademark by Data Geekery™ GmbH
  • jOOQ™ is a trademark by Data Geekery™ GmbH
  • jOOR™ is a trademark by Data Geekery™ GmbH
  • jOOU™ is a trademark by Data Geekery™ GmbH
  • jOOX™ is a trademark by Data Geekery™ GmbH

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.
  • 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
  • Liquibase is a trademark by Datical, Inc
  • Flyway is a trademark by Red Gate Software Ltd
  • 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:

  • Aaron Digulla
  • Andreas Franzén
  • Anuraag Agrawal
  • Arnaud Roger
  • Art O Cathain
  • Artur Dryomov
  • Ben Manes
  • Brent Douglas
  • Brett Meyer
  • Christian Stein
  • Christopher Deckers
  • Dennis Neufeld
  • Ed Schaller
  • Eric Peters
  • Ernest Mishkin
  • Espen Stromsnes
  • Eugeny Karpov
  • Fabrice Le Roy
  • Gonzalo Ortiz Jaureguizar
  • Gregory Hlavac
  • Henrik Sjöstrand
  • Ivan Dugic
  • Javier Durante
  • Johannes Bühler
  • Joseph B Phillips
  • Joseph Pachod
  • Knut Wannheden
  • Laurent Pireyn
  • Logan Hauspie
  • Luc Marchaud
  • Lukas Eder
  • Matti Tahvonen
  • Michael Doberenz
  • Michael Simons
  • Michał Kołodziejski
  • Miguel Gonzalez Sanchez
  • Mustafa Yücel
  • Nathaniel Fischer
  • Nicholas Chong W.B.
  • Octavia Togami
  • Oliver Flege
  • Per Lundberg
  • Peter Ertl
  • Richard Bradley
  • Robin Stocker
  • Roland Weisleder
  • Samy Deghou
  • Sander Plas
  • Sean Wellington
  • Sergey Epik
  • Sergey Zhuravlev
  • Stanislas Nanchen
  • Stephan Schroevers
  • Sugiharto Lim
  • Sven Jacobs
  • Szymon Jachim
  • Terence Zhang
  • Thomas Darimont
  • Timothy Wilson
  • Timur Shaidullin
  • Tsukasa Kitachi
  • Victor Bronstein
  • Victor Z. Peng
  • Vladimir Kulev
  • Vladimir Vinogradov
  • Vojtech Polivka
  • Wang Gaoyuan
  • Wyke Oskar
  • Xavier Oliver
  • Zoltan Tamasi

See the following website for details about contributing to jOOQ:
https://www.jooq.org/legal/contributions

 

2. Getting started with jOOQ

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

 

2.1. How to read this manual

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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 // com.example.generated.Tables.BOOK.TITLE, com.example.generated.Tables.AUTHOR.LAST_NAME
FK_BOOK_AUTHOR               // 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.

Sample database

jOOQ query examples run against the sample database. See the manual's section about the sample database used in this manual to learn more about the sample database.

 

2.2. The sample database used in this manual

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

For the examples in this manual, the same database will always be referred to. It essentially consists of these entities created using the Oracle dialect

CREATE TABLE language (
  id              NUMBER(7)     NOT NULL PRIMARY KEY,
  cd              CHAR(2)       NOT NULL,
  description     VARCHAR2(50)
);

CREATE TABLE author (
  id              NUMBER(7)     NOT NULL PRIMARY KEY,
  first_name      VARCHAR2(50),
  last_name       VARCHAR2(50)  NOT NULL,
  date_of_birth   DATE,
  year_of_birth   NUMBER(7),
  distinguished   NUMBER(1)
);

CREATE TABLE book (
  id              NUMBER(7)     NOT NULL PRIMARY KEY,
  author_id       NUMBER(7)     NOT NULL,
  title           VARCHAR2(400) NOT NULL,
  published_in    NUMBER(7)     NOT NULL,
  language_id     NUMBER(7)     NOT NULL,

  CONSTRAINT fk_book_author     FOREIGN KEY (author_id)   REFERENCES author(id),
  CONSTRAINT fk_book_language   FOREIGN KEY (language_id) REFERENCES language(id)
);

CREATE TABLE book_store (
  name            VARCHAR2(400) NOT NULL UNIQUE
);

CREATE TABLE book_to_book_store (
  name            VARCHAR2(400) NOT NULL,
  book_id         INTEGER       NOT NULL,
  stock           INTEGER,

  PRIMARY KEY(name, book_id),
  CONSTRAINT fk_b2bs_book_store FOREIGN KEY (name)        REFERENCES book_store (name) ON DELETE CASCADE,
  CONSTRAINT fk_b2bs_book       FOREIGN KEY (book_id)     REFERENCES book (id)         ON DELETE CASCADE
);

More entities, types (e.g. UDT's, ARRAY types, ENUM types, etc), stored procedures and packages are introduced for specific examples

In addition to the above, you may assume the following sample data:

INSERT INTO language (id, cd, description) VALUES (1, 'en', 'English');
INSERT INTO language (id, cd, description) VALUES (2, 'de', 'Deutsch');
INSERT INTO language (id, cd, description) VALUES (3, 'fr', 'Français');
INSERT INTO language (id, cd, description) VALUES (4, 'pt', 'Português');

INSERT INTO author (id, first_name, last_name, date_of_birth    , year_of_birth)
  VALUES           (1 , 'George'  , 'Orwell' , DATE '1903-06-26', 1903         );
INSERT INTO author (id, first_name, last_name, date_of_birth    , year_of_birth)
  VALUES           (2 , 'Paulo'   , 'Coelho' , DATE '1947-08-24', 1947         );

INSERT INTO book (id, author_id, title         , published_in, language_id)
  VALUES         (1 , 1        , '1984'        , 1948        , 1          );
INSERT INTO book (id, author_id, title         , published_in, language_id)
  VALUES         (2 , 1        , 'Animal Farm' , 1945        , 1          );
INSERT INTO book (id, author_id, title         , published_in, language_id)
  VALUES         (3 , 2        , 'O Alquimista', 1988        , 4          );
INSERT INTO book (id, author_id, title         , published_in, language_id)
  VALUES         (4 , 2        , 'Brida'       , 1990        , 2          );

INSERT INTO book_store VALUES ('Orell Füssli');
INSERT INTO book_store VALUES ('Ex Libris');
INSERT INTO book_store VALUES ('Buchhandlung im Volkshaus');

INSERT INTO book_to_book_store VALUES ('Orell Füssli'             , 1, 10);
INSERT INTO book_to_book_store VALUES ('Orell Füssli'             , 2, 10);
INSERT INTO book_to_book_store VALUES ('Orell Füssli'             , 3, 10);
INSERT INTO book_to_book_store VALUES ('Ex Libris'                , 1, 1 );
INSERT INTO book_to_book_store VALUES ('Ex Libris'                , 3, 2 );
INSERT INTO book_to_book_store VALUES ('Buchhandlung im Volkshaus', 3, 1 );
 

2.3. Different use cases for jOOQ

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

  • Typesafe database object referencing through generated schema, table, column, record, procedure, type, dao, pojo artefacts (see the chapter about code generation)
  • Typesafe SQL construction / SQL building through a complete querying DSL API modelling SQL as a domain specific language in Java (see the chapter about the query DSL API)
  • Convenient query execution through an improved API for result fetching (see the chapters about the various types of data fetching)
  • SQL dialect abstraction and SQL clause emulation to improve cross-database compatibility and to enable missing features in simpler databases (see the chapter about SQL dialects)
  • SQL logging and debugging using jOOQ as an integral part of your development process (see the chapters about logging)

Effectively, jOOQ was originally designed to replace any other database abstraction framework short of the ones handling connection pooling (and more sophisticated transaction management)

Use jOOQ the way you prefer

... but open source is community-driven. And the community has shown various ways of using jOOQ that diverge from its original intent. Some use cases encountered are:

  • Using Hibernate for 70% of the queries (i.e. CRUD) and jOOQ for the remaining 30% where SQL is really needed
  • Using jOOQ for SQL building and JDBC for SQL execution
  • Using jOOQ for SQL building and Spring Data for SQL execution
  • Using jOOQ without the source code generator to build the basis of a framework for dynamic SQL execution.

The following sections explain about various use cases for using jOOQ in your application.

 

2.3.1. jOOQ as a SQL builder without code generation

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

We strongly recommend to use jOOQ with its code generator to get the most out of jOOQ!

However, if you have a dynamic schema, you don't have to use the code generator. 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 maybe 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.
Query query = 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));
String sql = query.getSQL();
List<Object> bindValues = query.getBindValues();

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 java.sql.PreparedStatement by default, this will generate a bind variable for "1948". Read more about bind variables here).

You can also avoid getting the SQL string and bind values separately:

String sql = query.getSQL(ParamType.INLINED);

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
  • Plain SQL: This section contains information useful in particular to those that want to supply table expressions, column expressions, etc. as plain SQL to jOOQ, rather than through generated artefacts
  • Bind values: This section explains how bind values are managed and/or inlined in jOOQ.
 

2.3.2. jOOQ as a SQL builder with code generation

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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. We strongly recommend using this approach. An example is given here:

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.
Query query = 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));

String sql = query.getSQL();
List<Object> bindValues = query.getBindValues();

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 java.sql.PreparedStatement by default, this will generate a bind variable for "1948". Read more about bind variables here).

You can also avoid getting the SQL string and bind values separately:

String sql = query.getSQL(ParamType.INLINED);

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
  • Code generation: This section contains the necessary information to run jOOQ's code generator against your developer database
  • Bind values: This section explains how bind values are managed and/or inlined in jOOQ.
 

2.3.3. jOOQ as a SQL executor

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

// Typesafely execute the SQL statement directly with jOOQ
Result<Record3<String, String, String>> result =
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))
      .fetch();

By having jOOQ execute your SQL, the jOOQ query DSL becomes truly embedded SQL.

jOOQ doesn't stop here, though! You can execute any SQL with jOOQ. In other words, you can use any other SQL building tool and run the SQL statements with jOOQ. An example is given here:

// Use your favourite tool to construct SQL strings:
String sql = "SELECT title, first_name, last_name FROM book JOIN author ON book.author_id = author.id " +
             "WHERE book.published_in = 1984";

// Fetch results using jOOQ
Result<Record> result = create.fetch(sql);

// Or execute that SQL with JDBC, fetching the ResultSet with jOOQ:
ResultSet rs = connection.createStatement().executeQuery(sql);
Result<Record> result = create.fetch(rs);

If you wish to use jOOQ as a SQL executor with (or without) 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
  • Code generation: This section contains the necessary information to run jOOQ's code generator against your developer database
  • SQL execution: This section contains a lot of information about executing SQL statements using the jOOQ API
  • Fetching: This section contains some useful information about the various ways of fetching data with jOOQ
 

2.3.4. jOOQ for CRUD

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Apart from jOOQ's fluent API for query construction, jOOQ can also help you execute everyday CRUD operations. An example is given here:

// Fetch an author
AuthorRecord author = create.fetchOne(AUTHOR, AUTHOR.ID.eq(1));

// Create a new author, if it doesn't exist yet
if (author == null) {
    author = create.newRecord(AUTHOR);
    author.setId(1);
    author.setFirstName("Dan");
    author.setLastName("Brown");
}

// Mark the author as a "distinguished" author and store it
author.setDistinguished(1);

// Executes an update on existing authors, or insert on new ones
author.store();

If you wish to use all of jOOQ's features, the following sections of the manual will be of interest to you (including all sub-sections):

  • SQL building: This section contains a lot of information about creating SQL statements using the jOOQ API
  • Code generation: This section contains the necessary information to run jOOQ's code generator against your developer database
  • SQL execution: This section contains a lot of information about executing SQL statements using the jOOQ API
 

2.3.5. jOOQ for PROs

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ isn't just a library that helps you build and execute SQL against your generated, compilable schema. jOOQ ships with a lot of tools. Here are some of the most important tools shipped with jOOQ:

  • 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.

 

2.4. Downloading jOOQ

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ is distributed over 3 main channels:

The ZIP file

If you choose to download jOOQ over the website, you will be able to download a ZIP file with the following layout:

  • maven-deploy.bat: A Windows batch script to deploy artifacts to a maven repository
  • maven-deploy.sh: A bash script to deploy artifacts to a maven repository
  • maven-install.bat: A Windows batch script to install artifacts to the local maven repository
  • maven-install.sh: A bash script to install artifacts to the local maven repository

The website hosts the latest versions of the jOOQ Open Source Edition as well as all the historic versions of the commercial jOOQ editions including snapshot builds of all distributions that are available to paying customers only.

The commercial artifact repository

The commercial artifact repository hosts all the historic versions of the commercial jOOQ editions including snapshot builds of all distributions that are available to paying customers only.

Below is information regarding how to include these dependencies in Maven / Gradle:

Maven
Gradle (Kotlin)
Gradle (Groovy)

settings.xml

<server>
    <id>jooq-pro</id>
    <username>[your licensee email]</username>
    <password>[your license key]</password>
</server>

pom.xml

<repositories>
    <repository>
        <id>central</id>
        <url>https://repo1.maven.org/maven2/</url>
    </repository>
    <!-- Other repositories ... -->

    <repository>
        <id>jooq-pro</id>
        <url>https://repo.jooq.org/repo</url>
    </repository>
</repositories>
<pluginRepositories>
    <pluginRepository>
        <id>central</id>
        <url>https://repo1.maven.org/maven2/</url>
    </pluginRepository>
    <!-- Other repositories ... -->

    <pluginRepository>
        <id>jooq-pro</id>
        <url>https://repo.jooq.org/repo</url>
    </pluginRepository>
</pluginRepositories>
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.

Dependencies

Depending on the edition you're using, please declare the following dependencies in Maven or Gradle:

Maven
Gradle (Kotlin)
Gradle (Groovy)
<dependency>
    <!-- Use org.jooq              for the Open Source Edition
             org.jooq.pro          for commercial editions with Java 11 support,
             org.jooq.pro-java-8   for commercial editions with Java 8 support,
             org.jooq.pro-java-6   for commercial editions with Java 6 support,
             org.jooq.trial        for the free trial edition with Java 11 support,
             org.jooq.trial-java-8 for the free trial edition with Java 8 support,
             org.jooq.trial-java-6 for the free trial edition with Java 6 support
    
     Note: Only the Open Source Edition is hosted on Maven Central.
           Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
           See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk -->

    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.14.16</version>
</dependency>
dependencies {
    // Use org.jooq                for the Open Source Edition
    //     org.jooq.pro            for commercial editions with Java 17 support,
    //     org.jooq.pro-java-8     for commercial editions with Java 8 support,
    //     org.jooq.pro-java-6     for commercial editions with Java 6 support,
    //     org.jooq.trial          for the free trial edition with Java 17 support,
    //     org.jooq.trial-java-8   for the free trial edition with Java 8 support,
    //     org.jooq.trial-java-6   for the free trial edition with Java 6 support
    //
    // Note: Only the Open Source Edition is hosted on Maven Central.
    //       Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
    //       See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk

    implementation("org.jooq:jooq:3.14.16")
}
dependencies {
    // Use org.jooq                for the Open Source Edition
    //     org.jooq.pro            for commercial editions with Java 17 support,
    //     org.jooq.pro-java-8     for commercial editions with Java 8 support,
    //     org.jooq.pro-java-6     for commercial editions with Java 6 support,
    //     org.jooq.trial          for the free trial edition with Java 17 support,
    //     org.jooq.trial-java-8   for the free trial edition with Java 8 support,
    //     org.jooq.trial-java-6   for the free trial edition with Java 6 support
    //
    // Note: Only the Open Source Edition is hosted on Maven Central.
    //       Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
    //       See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk

    implementation "org.jooq:jooq:3.14.16"
}
 

2.5. Tutorials

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Don't have time to read the full manual? Here are a couple of tutorials that will get you into the most essential parts of jOOQ as quick as possible.

 

2.5.1. jOOQ in 7 easy steps

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

This manual section is intended for new users, to help them get a running application with jOOQ, quickly.

 

2.5.1.1. Step 1: Preparation

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

If you haven't already downloaded it, download jOOQ:
https://www.jooq.org/download

Alternatively, you can create a Maven dependency to download jOOQ artefacts:

Open Source Edition

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>3.14.16</version>
</dependency>

<!-- These may not be required, unless you use the GenerationTool manually for code generation -->
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.14.16</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.14.16</version>
</dependency>

Commercial Editions (Java 9+)

<!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution -->
<dependency>
  <groupId>org.jooq.pro</groupId>
  <artifactId>jooq</artifactId>
  <version>3.14.16</version>
</dependency>

<!-- These may not be required, unless you use the GenerationTool manually for code generation -->
<dependency>
  <groupId>org.jooq.pro</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.14.16</version>
</dependency>
<dependency>
  <groupId>org.jooq.pro</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.14.16</version>
</dependency>

Commercial Editions (Java 8+)

<!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution -->
<dependency>
  <groupId>org.jooq.pro-java-8</groupId>
  <artifactId>jooq</artifactId>
  <version>3.14.16</version>
</dependency>

<!-- These may not be required, unless you use the GenerationTool manually for code generation -->
<dependency>
  <groupId>org.jooq.pro-java-8</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.14.16</version>
</dependency>
<dependency>
  <groupId>org.jooq.pro-java-8</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.14.16</version>
</dependency>

Commercial Editions (Java 6+)

<!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution -->
<dependency>
  <groupId>org.jooq.pro-java-6</groupId>
  <artifactId>jooq</artifactId>
  <version>3.14.16</version>
</dependency>

<!-- These may not be required, unless you use the GenerationTool manually for code generation -->
<dependency>
  <groupId>org.jooq.pro-java-6</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.14.16</version>
</dependency>
<dependency>
  <groupId>org.jooq.pro-java-6</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.14.16</version>
</dependency>

Commercial Editions (Free Trial)

<!-- Note: These aren't hosted on Maven Central. Import them manually from your distribution -->
<dependency>
  <groupId>org.jooq.trial</groupId>
  <artifactId>jooq</artifactId>
  <version>3.14.16</version>
</dependency>

<!-- These may not be required, unless you use the GenerationTool manually for code generation -->
<dependency>
  <groupId>org.jooq.trial</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.14.16</version>
</dependency>
<dependency>
  <groupId>org.jooq.trial</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.14.16</version>
</dependency>

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.

Please refer to the manual's section about Code generation configuration to learn how to use jOOQ's code generator with Maven.

For this example, we'll be using MySQL. If you haven't already downloaded MySQL Connector/J, download it here:
https://dev.mysql.com/downloads/connector/j/

If you don't have a MySQL instance up and running yet, get it from https://www.mysql.com or https://hub.docker.com/_/mysql now!

 

2.5.1.2. Step 2: Your database

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

We're going to create a database called "library" and a corresponding "author" table. Connect to MySQL via your command line client and type the following:

CREATE DATABASE `library`;

USE `library`;

CREATE TABLE `author` (
  `id` int NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 

2.5.1.3. Step 3: Code generation

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration>
  <!-- Configure the database connection here -->
  <jdbc>
    <driver>com.mysql.cj.jdbc.Driver</driver>
    <url>jdbc:mysql://localhost:3306/library</url>
    <user>root</user>
    <password></password>
  </jdbc>

  <generator>
    <!-- The default code generator. You can override this one, to generate your own code style.
         Supported generators:
         - org.jooq.codegen.JavaGenerator
         - org.jooq.codegen.KotlinGenerator
         - org.jooq.codegen.ScalaGenerator
         Defaults to org.jooq.codegen.JavaGenerator -->
    <name>org.jooq.codegen.JavaGenerator</name>

    <database>
      <!-- The database type. The format here is:
           org.jooq.meta.[database].[database]Database -->
      <name>org.jooq.meta.mysql.MySQLDatabase</name>

      <!-- The database schema (or in the absence of schema support, in your RDBMS this
           can be the owner, user, database name) to be generated -->
      <inputSchema>library</inputSchema>

      <!-- 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! -->
      <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></excludes>
    </database>

    <target>
      <!-- The destination package of your generated classes (within the destination directory) -->
      <packageName>test.generated</packageName>

      <!-- The destination directory of your generated classes. Using Maven directory layout here -->
      <directory>C:/workspace/MySQLTest/src/main/java</directory>
    </target>
  </generator>
</configuration>

Replace the username (<username/> or <user/>) 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:

<packageName/> - set this to the parent package you want to create for the generated classes. Setting the value to test.generated will cause the test.generated.tables.Author and test.generated.tables.records.AuthorRecord classes to be created

<directory/> - the directory to output the generated classes to.

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

java -classpath jooq-3.14.16.jar;^
jooq-meta-3.14.16.jar;^
jooq-codegen-3.14.16.jar;^
reactive-streams-1.0.2.jar;^
jaxb-api-2.3.1.jar;^
mysql-connector-java.jar;. ^
org.jooq.codegen.GenerationTool library.xml

... or type this on a UNIX / Linux / Mac system (colons instead of semi-colons):

java -classpath jooq-3.14.16.jar:\
jooq-meta-3.14.16.jar:\
jooq-codegen-3.14.16.jar:\
reactive-streams-1.0.2.jar:\
jaxb-api-2.3.1.jar:\
mysql-connector-java.jar:. \
org.jooq.codegen.GenerationTool library.xml
  • 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.14.16 is being used.
  • If you're using a linux style shell on Windows, but a Windows JDK/JRE, you still need to use semi-colons in your classpath! (;) In git-bash, you might have to quote your classpath ("jooq-3.14.16.jar;jooq-meta-3.14.16.jar;...")

If everything has worked, you should see this in your console output:

Nov 1, 2011 7:25:06 PM org.jooq.impl.JooqLogger info
INFO: Initialising properties  : /library.xml
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Database parameters
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: ----------------------------------------------------------
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO:   dialect                : MYSQL
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO:   schema                 : library
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO:   target dir             : C:/workspace/MySQLTest/src
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO:   target package         : test.generated
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: ----------------------------------------------------------
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Emptying                 : C:/workspace/MySQLTest/src/test/generated
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating classes in    : C:/workspace/MySQLTest/src/test/generated
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating schema        : Library.java
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Schema generated         : Total: 122.18ms
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Sequences fetched        : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Tables fetched           : 5 (5 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating tables        : C:/workspace/MySQLTest/src/test/generated/tables
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: ARRAYs fetched           : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Enums fetched            : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: UDTs fetched             : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating table         : Author.java
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Tables generated         : Total: 680.464ms, +558.284ms
Nov 1, 2011 7:25:07 PM org.jooq.impl.JooqLogger info
INFO: Generating Keys          : C:/workspace/MySQLTest/src/test/generated/tables
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Keys generated           : Total: 718.621ms, +38.157ms
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Generating records       : C:/workspace/MySQLTest/src/test/generated/tables/records
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Generating record        : AuthorRecord.java
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Table records generated  : Total: 782.545ms, +63.924ms
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Routines fetched         : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: Packages fetched         : 0 (0 included, 0 excluded)
Nov 1, 2011 7:25:08 PM org.jooq.impl.JooqLogger info
INFO: GENERATION FINISHED!     : Total: 791.688ms, +9.143ms
 

2.5.1.4. Step 4: Connect to your database

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Let's just write a vanilla main class in the project containing the generated classes:

// For convenience, always static import your generated tables and jOOQ functions to decrease verbosity:
import static test.generated.Tables.*;
import static org.jooq.impl.DSL.*;

import java.sql.*;

public class Main {
    public static void main(String[] args) {
        String userName = "root";
        String password = "";
        String url = "jdbc:mysql://localhost:3306/library";

        // Connection is the only JDBC resource that we need
        // PreparedStatement and ResultSet are handled by jOOQ, internally
        try (Connection conn = DriverManager.getConnection(url, userName, password)) {
            // ...
        }

        // For the sake of this tutorial, let's keep exception handling simple
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

This is pretty standard code for establishing a MySQL connection.

 

2.5.1.5. Step 5: Querying

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Let's add a simple query constructed with jOOQ's query DSL:

DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
Result<Record> result = create.select().from(AUTHOR).fetch();

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.

 

2.5.1.6. Step 6: Iterating

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

After the line where we retrieve the results, let's iterate over the results and print out the data:

for (Record r : result) {
    Integer id = r.getValue(AUTHOR.ID);
    String firstName = r.getValue(AUTHOR.FIRST_NAME);
    String lastName = r.getValue(AUTHOR.LAST_NAME);

    System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
}

The full program should now look like this:

package test;

// For convenience, always static import your generated tables and
// jOOQ functions to decrease verbosity:
import static test.generated.Tables.*;
import static org.jooq.impl.DSL.*;

import java.sql.*;

import org.jooq.*;
import org.jooq.impl.*;

public class Main {

    /**
     * @param args
     */
    public static void main(String[] args) {
        String userName = "root";
        String password = "";
        String url = "jdbc:mysql://localhost:3306/library";

        // Connection is the only JDBC resource that we need
        // PreparedStatement and ResultSet are handled by jOOQ, internally
        try (Connection conn = DriverManager.getConnection(url, userName, password)) {
            DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
            Result<Record> result = create.select().from(AUTHOR).fetch();

            for (Record r : result) {
                Integer id = r.getValue(AUTHOR.ID);
                String firstName = r.getValue(AUTHOR.FIRST_NAME);
                String lastName = r.getValue(AUTHOR.LAST_NAME);

                System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
            }
        }

        // For the sake of this tutorial, let's keep exception handling simple
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}
 

2.5.1.7. Step 7: Explore!

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ has grown to be a comprehensive SQL library. For more information, please consider the documentation:
https://www.jooq.org/learn

... explore the Javadoc:
https://www.jooq.org/javadoc/latest/

... or join the news group:
https://groups.google.com/forum/#!forum/jooq-user

This tutorial is the courtesy of Ikai Lan. See the original source here:
https://ikaisays.com/2011/11/01/getting-started-with-jooq-a-tutorial/

 

2.5.2. Using jOOQ with Flyway

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Flyway - Database Migrations Made EasyWhen 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
  • 3. Code re-generation - Once the database is migrated, you regenerate all jOOQ artefacts (see code generation), locally
  • 4. Development - You continue developing your business logic, writing code against the updated, generated database schema

Maven Project Configuration - Properties

The following properties are defined in our pom.xml, to be able to reuse them between plugin configurations:

<properties>
    <db.url>jdbc:h2:~/flyway-test</db.url>
    <db.username>sa</db.username>
</properties>

0. Maven Project Configuration - Dependencies

While jOOQ and Flyway could be used in standalone migration scripts, in this tutorial, we'll be using Maven for the standard project setup.

These are the dependencies that we're using in our Maven configuration:

<!-- We'll add the latest version of jOOQ and our JDBC driver - in this case H2 -->
<dependency>
    <!-- Use org.jooq            for the Open Source Edition
             org.jooq.pro        for commercial editions,
             org.jooq.pro-java-8 for commercial editions with Java 8 support,
             org.jooq.pro-java-6 for commercial editions with Java 6 support,
             org.jooq.trial      for the free trial edition

         Note: Only the Open Source Edition is hosted on Maven Central.
               Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org -->
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.14.16</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.197</version>
</dependency>

<!-- For improved logging, we'll be using log4j via slf4j to see what's going on during migration and code generation -->
<dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-slf4j-impl</artifactId>
    <version>2.11.0</version>
</dependency>

<!-- To ensure our code is working, we're using JUnit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
    <scope>test</scope>
</dependency>

0. Maven Project Configuration - Plugins

After the dependencies, let's simply add the Flyway and jOOQ Maven plugins like so. The Flyway plugin:

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>3.0</version>

    <!-- Note that we're executing the Flyway plugin in the "generate-sources" phase -->
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
        </execution>
    </executions>

    <!-- Note that we need to prefix the db/migration path with filesystem: to prevent Flyway
         from looking for our migration scripts only on the classpath -->
    <configuration>
        <url>${db.url}</url>
        <user>${db.username}</user>
        <locations>
            <location>filesystem:src/main/resources/db/migration</location>
        </locations>
    </configuration>
</plugin>

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 may suggest that migrations be done in the compile phase, the jOOQ code generator relies on such migrations having been done prior to code generation.

After the Flyway plugin, we'll add the jOOQ Maven Plugin. For more details, please refer to the manual's section about the code generation configuration.

<plugin>
    <!-- Use org.jooq            for the Open Source Edition
             org.jooq.pro        for commercial editions,
             org.jooq.pro-java-8 for commercial editions with Java 8 support,
             org.jooq.pro-java-6 for commercial editions with Java 6 support,
             org.jooq.trial      for the free trial edition

         Note: Only the Open Source Edition is hosted on Maven Central.
               Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org -->
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.version}</version>

    <!-- The jOOQ code generation plugin is also executed in the generate-sources phase, prior to compilation -->
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <!-- This is a minimal working configuration. See the manual's section about the code generator for more details -->
    <configuration>
        <jdbc>
            <url>${db.url}</url>
            <user>${db.username}</user>
        </jdbc>
        <generator>
            <database>
                <includes>.*</includes>
                <inputSchema>FLYWAY_TEST</inputSchema>
            </database>
            <target>
                <packageName>org.jooq.example.flyway.db.h2</packageName>
                <directory>target/generated-sources/jooq-h2</directory>
            </target>
        </generator>
    </configuration>
</plugin>

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:

  • V1__initialise_database.sql
  • V2__create_author_table.sql
  • V3__create_book_table_and_records.sql

These three scripts model our schema versions 1-3 (note the capital V!). Here are the scripts' contents

-- V1__initialise_database.sql
DROP SCHEMA flyway_test IF EXISTS;

CREATE SCHEMA flyway_test;
-- V2__create_author_table.sql
CREATE SEQUENCE flyway_test.s_author_id START WITH 1;

CREATE TABLE flyway_test.author (
  id INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INT,
  address VARCHAR(50),

  CONSTRAINT pk_author PRIMARY KEY (ID)
);
-- V3__create_book_table_and_records.sql
CREATE TABLE flyway_test.book (
  id INT NOT NULL,
  author_id INT NOT NULL,
  title VARCHAR(400) NOT NULL,

  CONSTRAINT pk_book PRIMARY KEY (id),
  CONSTRAINT fk_book_author_id FOREIGN KEY (author_id) REFERENCES flyway_test.author(id)
);


INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null);
INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null);

INSERT INTO flyway_test.book VALUES (1, 1, '1984');
INSERT INTO flyway_test.book VALUES (2, 1, 'Animal Farm');
INSERT INTO flyway_test.book VALUES (3, 2, 'O Alquimista');
INSERT INTO flyway_test.book VALUES (4, 2, 'Brida');

2. Database migration and 3. Code regeneration

The above three scripts are picked up by Flyway and executed in the order of the versions. This can be seen very simply by executing:

mvn clean install

And then observing the log output from Flyway...

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 3 migrations (execution time 00:00.004s)
[INFO] Creating Metadata table: "PUBLIC"."schema_version"
[INFO] Current version of schema "PUBLIC": << Empty Schema >>
[INFO] Migrating schema "PUBLIC" to version 1
[INFO] Migrating schema "PUBLIC" to version 2
[INFO] Migrating schema "PUBLIC" to version 3
[INFO] Successfully applied 3 migrations to schema "PUBLIC" (execution time 00:00.073s).

... and from jOOQ on the console:

[INFO] --- jooq-codegen-maven:3.14.16:generate (default) @ jooq-flyway-example ---
[INFO] --- jooq-codegen-maven:3.14.16:generate (default) @ jooq-flyway-example ---
[INFO] Using this configuration:
...
[INFO] Generating schemata      : Total: 1
[INFO] Generating schema        : FlywayTest.java
[INFO] ----------------------------------------------------------
[....]
[INFO] GENERATION FINISHED!     : Total: 337.576ms, +4.299ms

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

import org.jooq.Result;
import org.jooq.impl.DSL;
import org.junit.Test;

import java.sql.DriverManager;

import static java.util.Arrays.asList;
import static org.jooq.example.flyway.db.h2.Tables.*;
import static org.junit.Assert.assertEquals;

public class AfterMigrationTest {

    @Test
    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =
            DSL.using(c)
               .select(
                   AUTHOR.FIRST_NAME,
                   AUTHOR.LAST_NAME,
                   BOOK.ID,
                   BOOK.TITLE
               )
               .from(AUTHOR)
               .join(BOOK)
               .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
               .orderBy(BOOK.ID.asc())
               .fetch();

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
        }
    }
}

Reiterate

The power of this approach becomes clear once you start performing database modifications this way. Let's assume that the French guy on our team prefers to have things his way:

-- V4__le_french.sql
ALTER TABLE flyway_test.book ALTER COLUMN title RENAME TO le_titre;

They check it in, you check out the new database migration script, run

mvn clean install

And then observing the log output:

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 4 migrations (execution time 00:00.005s)
[INFO] Current version of schema "PUBLIC": 3
[INFO] Migrating schema "PUBLIC" to version 4
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.016s).

So far so good, but later on:

[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] C:\...\jOOQ-flyway-example\src\test\java\AfterMigrationTest.java:[24,19] error: cannot find symbol
[INFO] 1 error

When we go back to our Java integration test, we can immediately see that the TITLE column is still being referenced, but it no longer exists:

public class AfterMigrationTest {

    @Test
    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =
            DSL.using(c)
                .select(
                    AUTHOR.FIRST_NAME,
                    AUTHOR.LAST_NAME,
                    BOOK.ID,
                    BOOK.TITLE
                    //   ^^^^^ This column no longer exists. We'll have to rename it to LE_TITRE
                )
                .from(AUTHOR)
                .join(BOOK)
                .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
                .orderBy(BOOK.ID.asc())
                .fetch();

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
        }
    }
}

Automation

The above steps can be automated in your build using another third party called testcontainers. Please look at this article here for examples on how to do that: https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/

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!

Please, visit the Flyway website for more information about Flyway.

 

2.5.3. Using jOOQ with jbang

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jbang allows for quickly working with all sorts of Java libraries without the hassle of setting up environments, dependencies, etc. This catalog allows for using jOOQ's code generator right away on an existing database.

For more information on jbang, see:

An example

In a shell, type

git clone https://github.com/jOOQ/jbang-example
cd jbang-example
jbang Example.java

In order to re-generate the example code, e.g. when your schema changes, just type:

jbang codegen@jooq db.xml

If you prefer working with a pre-existing database, just edit the db.xml file and point it to your database. Add the JDBC driver dependency like this:

jbang --deps org.postgresql:postgresql:RELEASE codegen@jooq db.xml

To override the jOOQ version from the default RELEASE to a specific version, use

jbang -Djooq.version=<version> codegen@jooq db.xml
 

2.6. jOOQ and Java 8

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

try (Connection c = getConnection()) {
    String sql = "select schema_name, is_default " +
                 "from information_schema.schemata " +
                 "order by schema_name";

    DSL.using(c)
       .fetch(sql)

       // We can use lambda expressions to map jOOQ Records
       .map(rs -> new Schema(
           rs.getValue("SCHEMA_NAME", String.class),
           rs.getValue("IS_DEFAULT", boolean.class)
       ))

       // ... and then profit from the new Collection methods
       .forEach(System.out::println);
}

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:

DSL.using(c)
   .select(
       COLUMNS.TABLE_NAME,
       COLUMNS.COLUMN_NAME,
       COLUMNS.TYPE_NAME
   )
   .from(COLUMNS)
   .orderBy(
       COLUMNS.TABLE_CATALOG,
       COLUMNS.TABLE_SCHEMA,
       COLUMNS.TABLE_NAME,
       COLUMNS.ORDINAL_POSITION
   )
   .fetch()  // jOOQ ends here
   .stream() // JDK 8 Streams start here
   .collect(groupingBy(
       r -> r.getValue(COLUMNS.TABLE_NAME),
       LinkedHashMap::new,
       mapping(
           r -> new Column(
               r.getValue(COLUMNS.COLUMN_NAME),
               r.getValue(COLUMNS.TYPE_NAME)
           ),
           toList()
       )
   ))
   .forEach(
       (table, columns) -> {
            // Just emit a CREATE TABLE statement
            System.out.println(
                "CREATE TABLE " + table + " (");

            // Map each "Column" type into a String
            // containing the column specification,
            // and join them using comma and
            // newline. Done!
            System.out.println(
                columns.stream()
                       .map(col -> "  " + col.name +
                                    " " + col.type)
                       .collect(Collectors.joining(",\n"))
            );

           System.out.println(");");
       }
   );
 

2.7. jOOQ and Scala

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

As any other library, jOOQ can be easily used in Scala, taking advantage of the many Scala language features such as for example:

  • Optional "." to dereference methods from expressions
  • Optional "(" and ")" to delimit method argument lists
  • Optional ";" at the end of a Scala statement
  • Type inference using "var" and "val" keywords
  • Lambda expressions and for-comprehension syntax for record iteration and data type conversion

But jOOQ also leverages other useful Scala features, such as

  • implicit defs for operator overloading
  • Scala Macros (soon to come)

All of the above heavily improve jOOQ's querying DSL API experience for Scala developers.

A short example jOOQ application in Scala might look like this:

import collection.JavaConversions._                                  // Import implicit defs for iteration over org.jooq.Result
                                                                     //
import java.sql.DriverManager                                        //
                                                                     //
import org.jooq._                                                    //
import org.jooq.impl._                                               //
import org.jooq.impl.DSL._                                           //
import org.jooq.examples.scala.h2.Tables._                           //
import org.jooq.scalaextensions.Conversions._                        // Import implicit defs for overloaded jOOQ/SQL operators
                                                                     //
object Test {                                                        //
  def main(args: Array[String]): Unit = {                            //
    val c = DriverManager.getConnection("jdbc:h2:~/test", "sa", ""); // Standard JDBC connection
    val e = DSL.using(c, SQLDialect.H2);                             //
    val x = AUTHOR as "x"                                            // SQL-esque table aliasing
                                                                     //
    for (r <- e                                                      // Iteration over Result. "r" is an org.jooq.Record3
        select (                                                     //
          BOOK.ID * BOOK.AUTHOR_ID,                                  // Using the overloaded "*" operator
          BOOK.ID + BOOK.AUTHOR_ID * 3 + 4,                          // Using the overloaded "+" operator
          BOOK.TITLE || " abc" || " xy"                              // Using the overloaded "||" operator
        )                                                            //
        from BOOK                                                    // No need to use parentheses or "." here
        leftOuterJoin (                                              //
          select (x.ID, x.YEAR_OF_BIRTH)                             // Dereference fields from aliased table
          from x                                                     //
          limit 1                                                    //
          asTable x.getName()                                        //
        )                                                            //
        on BOOK.AUTHOR_ID === x.ID                                   // Using the overloaded "===" operator
        where (BOOK.ID <> 2)                                         // Using the olerloaded "<>" operator
        or (BOOK.TITLE in ("O Alquimista", "Brida"))                 // Neat IN predicate expression
        fetch                                                        //
    ) {                                                              //
      println(r)                                                     //
    }                                                                //
  }                                                                  //
}

For more details about jOOQ's Scala integration, please refer to the manual's section about SQL building with Scala.

 

2.8. jOOQ and Groovy

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

As any other library, jOOQ can be easily used in Groovy, taking advantage of the many Groovy language features such as for example:

  • Optional ";" at the end of a Groovy statement
  • Type inference for local variables

A short example jOOQ application in Groovy might look like this:

package org.jooq.groovy

import static org.jooq.impl.DSL.*
import static org.jooq.groovy.example.h2.Tables.*

import groovy.sql.Sql
import org.jooq.*
import org.jooq.impl.DSL

sql = Sql.newInstance('jdbc:h2:~/groovy-test', 'sa', '', 'org.h2.Driver');

a = AUTHOR.as("a");
b = BOOK.as("b")

DSL.using(sql.connection)
   .select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
   .from(a)
   .join(b).on(a.ID.eq(b.AUTHOR_ID))
   .fetchInto ({
       r -> println(
           "${r.getValue(a.FIRST_NAME)} " +
           "${r.getValue(a.LAST_NAME)} " +
           "has written ${r.getValue(b.TITLE)}"
       )
   } as RecordHandler)

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.

 

2.9. jOOQ and Kotlin

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

As any other library, jOOQ can be easily used in Kotlin, taking advantage of the many Kotlin language features such as for example:

  • Optional ";" at the end of a Kotlin statement
  • Type inference for local variables

A short example jOOQ application in Kotlin might look like this:

package org.jooq.example.kotlin

import java.util.Properties

import org.jooq.*
import org.jooq.impl.DSL
import org.jooq.impl.DSL.*

import org.jooq.example.db.h2.Tables.*

fun main(args: Array<String>) {

    val properties = Properties();
    properties.load(Properties::class.java.getResourceAsStream("/config.properties"));

    DSL.using(
        properties.getProperty("db.url"),
        properties.getProperty("db.username"),
        properties.getProperty("db.password")
    ).use { ctx ->
        val a = AUTHOR
        val b = BOOK

        ctx.select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
           .from(a)
           .join(b).on(a.ID.eq(b.AUTHOR_ID))
           .orderBy(1, 2, 3)
           .forEach {
               println("${it[b.TITLE]} by ${it[a.FIRST_NAME]} ${it[a.LAST_NAME]}")
           }
    }
}

Note that Kotlin supports some means of operator overloading. For instance, a + b in Kotlin maps to a formal a.plus(b) method invocation, and jOOQ provides the required synonyms in its API to help you write such expressions.

One particularly nice language feature is the fact that [square brackets] allow for accessing any object's contents via get() and set() methods. Instead of using the above value1(), value2(), and value3() methods, we could also iterate as such:

        ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, BOOK.TITLE)
           .from(AUTHOR)
           .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
           .orderBy(1, 2, 3)
           .forEach {
               println("${it[BOOK.TITLE]} by ${it[AUTHOR.FIRST_NAME]} ${it[AUTHOR.LAST_NAME]}")
               // Notice:   ^^^^^^^^^^^^         ^^^^^^^^^^^^^^^^^^^      ^^^^^^^^^^^^^^^^^^
           }

A caveat of Kotlin 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.

 

2.10. jOOQ and NoSQL

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ users often get excited about jOOQ's intuitive API and would then wish for NoSQL support.

There are a variety of NoSQL databases that implement some sort of proprietary query language. Some of these query languages even look like SQL. Examples are JCR-SQL2, CQL (Cassandra Query Language), Cypher (Neo4j's Query Language), and many more.

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. E.g. for Cypher, there's Cypher-DSL, which is a much better fit.

jOOQ is about SQL, and about SQL alone.

 

2.11. jOOQ and JPA

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Just because you're using jOOQ doesn't mean you have to use it for everything!

When introducing jOOQ into an existing application that uses JPA, the common question is always: "Should we replace JPA by jOOQ?" and "How do we proceed doing that?"

Beware that jOOQ is not a replacement for JPA. Think of jOOQ as a complement. JPA (and ORMs in general) try to solve the object graph persistence problem. In short, this problem is about

  • Loading an entity graph into client memory from a database
  • Manipulating that graph in the client
  • Storing the modification back to the database

As the above graph gets more complex, a lot of tricky questions arise like:

  • What's the optimal order of SQL DML operations for loading and storing entities?
  • How can we batch the commands more efficiently?
  • How can we keep the transaction footprint as low as possible without compromising on ACID?
  • How can we implement optimistic locking?

jOOQ only has some of the answers.

While jOOQ does offer updatable records that help running simple CRUD, a batch API, optimistic locking capabilities, jOOQ mainly focuses on executing actual SQL statements.

SQL is the preferred language of database interaction, when any of the following are given:

  • You run reports and analytics on large data sets directly in the database
  • You import / export data using ETL
  • You run complex business logic as SQL queries

Whenever SQL is a good fit, jOOQ is a good fit. Whenever you're operating and persisting the object graph, JPA is a good fit.

And sometimes, it's best to combine both

 

2.12. Build your own

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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. The jOOQ Open Source Edition requires Java 8+ to compile and run. The commercial jOOQ Editions require Java 8+ or Java 6+ to compile and run, depending on the distribution.

Some useful hints to build jOOQ yourself:

  • Get the latest version of Git or EGit
  • Get the latest version of Maven or M2E
  • Check out the jOOQ sources from https://github.com/jOOQ/jOOQ
  • Optionally, import Maven artefacts into an Eclipse workspace using the following command (see the maven-eclipse-plugin documentation for details):
    • mvn eclipse:eclipse
  • Build the jooq-parent artefact by using any of these commands:
    • mvn clean package
      create .jar files in ${project.build.directory}
    • mvn clean install
      install the .jar files in your local repository (e.g. ~/.m2)
    • mvn clean {goal} -Dmaven.test.skip=true
      don't run unit tests when building artefacts
 

2.13. jOOQ and backwards-compatibility

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Semantic versioning

jOOQ's understanding of backwards compatibility is inspired by the rules of semantic versioning according to https://semver.org. 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.

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 (including raw type or binary compatibility implications)

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 rules 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).
  • Generated code implements such interfaces and extends internal classes, and as such is recommended to be re-generated with a matching code generator version every time the runtime library is upgraded.
  • Binary compatibility can be expected from patch releases, but not from minor releases as it is not practical to maintain binary compatibility in an internal DSL.
  • Source compatibility can be expected from patch and minor releases, the exception being raw type compatibility (see #11879), and rare exceptions where API design is clearly lacking.
  • Behavioural compatibility can be expected from patch and minor releases.
  • Any jOOQ SPI XYZ that is meant to be implemented ships with a DefaultXYZ or AbstractXYZ, which can be used safely as a default implementation.

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.

 

3. SQL building

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

 

3.1. The query DSL type

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ exposes a lot of interfaces and hides most implementation facts from client code. The reasons for this are:

  • Interface-driven design. This allows for modelling queries in a fluent API most efficiently
  • Reduction of complexity for client code.
  • API guarantee. You only depend on the exposed interfaces, not concrete (potentially dialect-specific) implementations.

The org.jooq.impl.DSL class is the main class from where you will create all jOOQ objects. It serves as a static factory for table expressions, column expressions (or "fields"), conditional expressions and many other QueryParts.

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));
 

3.1.1. DSL subclasses

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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)
 

3.2. The DSLContext API

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

Fluent creation of a DSLContext object

The DSLContext object can be created fluently from the DSL type:

// Create it from a pre-existing configuration
DSLContext create = DSL.using(configuration);

// Create it from ad-hoc arguments
DSLContext create = DSL.using(connection, dialect);

If you do not have a reference to a pre-existing Configuration object (e.g. created from org.jooq.impl.DefaultConfiguration), the various overloaded DSL.using() methods will create one for you.

Contents of a Configuration object

A Configuration can be supplied with these objects:

Usage of DSLContext

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:

// Execute a statement from a single execution chain:
Result<?> result =
DSL.using(connection, dialect)
   .select()
   .from(BOOK)
   .where(BOOK.TITLE.like("Animal%"))
   .fetch();
 

3.2.1. SQL Dialect

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

The SQL dialect is one of the main attributes of a Configuration. Queries created from DSLContexts will assume dialect-specific behaviour when rendering SQL and binding bind values.

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 Informix databases, is annotated with a org.jooq.Support annotation, as such:

/**
 * Add an Oracle-specific <code>CONNECT BY</code> clause to the query
 */
@Support({ SQLDialect.INFORMIX, SQLDialect.ORACLE })
SelectConnectByConditionStep<R> connectBy(Condition condition);

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:

/**
 * Create a new DSL select statement.
 */
@Support
SelectSelectStep<R> select(Field<?>... fields);

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

jOOQ has a historic affinity to Oracle's SQL extensions. If something is supported in Oracle SQL, it has a high probability of making it into the jOOQ API

 

3.2.2. SQL Dialect Family

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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
  • SQL Server 2012: The SQL Server version 2012
  • SQL Server 2008: The SQL Server version 2008

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).

 

3.2.3. Connection vs. DataSource

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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 Java EE 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.

Note, in this case, jOOQ will internally use a org.jooq.impl.DataSourceConnectionProvider, which you can reference directly if you prefer that.

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:

public interface ConnectionProvider {

    // Provide jOOQ with a connection
    Connection acquire() throws DataAccessException;

    // Get a connection back from jOOQ
    void release(Connection connection) throws DataAccessException;
}
 

3.2.4. Custom data

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In advanced use cases of integrating your application with jOOQ, you may want to put custom data into your Configuration, which you can then access from your...

Here is an example of how to use the custom data API. Let's assume that you have written an ExecuteListener, that prevents INSERT statements, when a given flag is set to true:

public class NoInsertListener extends DefaultExecuteListener {

    @Override
    public void start(ExecuteContext ctx) {

        // This listener is active only, when your custom flag is set to true
        if (Boolean.TRUE.equals(ctx.configuration().data("com.example.my-namespace.no-inserts"))) {

            // If active, fail this execution, if an INSERT statement is being executed
            if (ctx.query() instanceof Insert) {
                throw new DataAccessException("No INSERT statements allowed");
            }
        }
    }
}

See the manual's section about ExecuteListeners to learn more about how to implement an ExecuteListener.

Now, the above listener can be added to your Configuration, but you will also need to pass the flag to the Configuration, in order for the listener to work:

// Create your Configuration
Configuration configuration = new DefaultConfiguration().set(connection).set(dialect);

// Set a new execute listener provider onto the configuration:
configuration.set(new DefaultExecuteListenerProvider(new NoInsertListener()));

// Use any String literal to identify your custom data
configuration.data("com.example.my-namespace.no-inserts", true);

// Try to execute an INSERT statement
try {
    DSL.using(configuration)
       .insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
       .values(1, "Orwell")
       .execute();

    // You shouldn't get here
    Assert.fail();
}

// Your NoInsertListener should be throwing this exception here:
catch (DataAccessException expected) {
    Assert.assertEquals("No INSERT statements allowed", expected.getMessage());
}

Using the data() methods, you can store and retrieve custom data in your Configurations.

 

3.2.5. Custom ExecuteListeners

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

ExecuteListeners are a useful tool to...

  • implement custom logging
  • apply triggers written in Java
  • collect query execution statistics

ExecuteListeners are hooked into your Configuration by returning them from an org.jooq.ExecuteListenerProvider:

// Create your Configuration
Configuration configuration = new DefaultConfiguration().set(connection).set(dialect);

// Hook your listener providers into the configuration:
configuration.set(
    new DefaultExecuteListenerProvider(new MyFirstListener()),
    new DefaultExecuteListenerProvider(new PerformanceLoggingListener()),
    new DefaultExecuteListenerProvider(new NoInsertListener())
);

See the manual's section about ExecuteListeners to see examples of such listener implementations.

 

3.2.6. Custom Unwrappers

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

JDBC knows the java.sql.Wrapper API, which is implemented by all JDBC types in order to be able to "unwrap" a native driver implementation for any given type. For example:

// This may be some proxy from a connection pool
Connection c = getConnection();

// Sometimes, we want the native driver connection instance
OracleConnection oc = c.unwrap(OracleConnection.class);
Array array = oc.createARRAY("ARRAY_TYPE", new Object[] { "a", "b" });

jOOQ internally makes similar calls occasionally. For this, it needs to unwrap the native java.sql.Connection or java.sql.PreparedStatement instance. Unfortunately, not all third party libraries correctly implement the Wrapper API contract, so this unwrapping might not work. The org.jooq.Unwrapper SPI is designed to allow for custom implementations to be injected into jOOQ configurations:

// Your jOOQ configuration
Configuration c1 = getConfiguration();
Configuration c2 = c.derive(new Unwrapper() {
    @Override
    public <T> T unwrap(Wrapper wrapper, Class<T> iface) {
        try {
            if (wrapper instanceof Connection)
                // ...
            else if (wrapper instanceof Statement)
                // ...
            else
                wrapper.unwrap(iface);
        }
        catch (SQLException e) {
            // ...
        }
    }
});

// Work with the derived configuration, where needed
DSL.using(c2).fetch("...");
 

3.2.7. Custom Settings

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

The most specific settings for a given context will apply.

If you wish to configure your settings through XML, but explicitly load them for a given Configuration, you can do so as well, using JAXB:

Settings settings = JAXB.unmarshal(new File("/path/to/settings.xml"), Settings.class);

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:

Settings settings = new Settings();
settings.setStatementType(StatementType.STATIC_STATEMENT);
DSLContext create = DSL.using(connection, dialect, settings);

More details

Please refer to the jOOQ runtime configuration XSD for more details:
https://www.jooq.org/xsd/jooq-runtime-3.14.9.xsd

 

3.2.7.1. Auto-attach Records

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

Example configuration

Settings settings = new Settings()
    .withAttachRecords(false); // Defaults to true
 

3.2.7.2. Backslash Escaping

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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
  • ON: Turn the feature on.
  • OFF: Turn the feature off.

Example configuration

Settings settings = new Settings()
    .withBackslashEscaping(BackslashEscaping.OFF); // Default to DEFAULT
 

3.2.7.3. Batch size

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ offers a transparent batching API, which can buffer all statements generated by jOOQ and other JDBC backed APIs transparently in order to batch them:

// Everything in the below lambda will be buffered and batched
DSL.using(configuration).batched(c -> {
    module1.insertSomething(c);
    module2.insertSomethingElse(c);
});

Use the Settings.batchSize flag to govern the maximum batch statement size of this API:
使用 Settings.batchSize 标志控制此 API 的最大批处理语句大小:

Settings settings = new Settings()
    .withBatchSize(100); // Default Integer.MAX_VALUE
 

3.2.7.4. Execute Logging  3.2.7.4. 执行日志记录

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition
适用于✅开源版 ✅ Express Edition ✅专业版 ✅ 企业版

The executeLogging setting turns off the default logging implemented through org.jooq.tools.LoggerListener
executeLogging 设置关闭通过 org.jooq.tools.LoggerListener 实现的默认日志记录

Example configuration  示例配置

Settings settings = new Settings()
    .withExecuteLogging(false); // Defaults to true
 

3.2.7.5. Fetch Warnings  3.2.7.5. 获取警告

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition
适用于✅开源版 ✅ Express Edition ✅专业版 ✅ 企业版

Apart from JDBC exceptions, there is also the possibility to handle java.sql.SQLWarning, which are made available to jOOQ users through the java.sql.ExecuteListener SPI and the log. Users who do not wish to get these notifications (e.g. for performance reasons), may turn off fetching of warnings through the fetchWarnings setting:

Example configuration

Settings settings = new Settings()
    .withFetchWarnings(false); // Defaults to true
 

3.2.7.6. Identifier style

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

By default, jOOQ will always generate quoted names for all identifiers (even if this manual omits this for readability).

For instance:

SELECT "TABLE"."COLUMN" FROM "TABLE" -- SQL standard style
SELECT `TABLE`.`COLUMN` FROM `TABLE` -- MySQL style
SELECT [TABLE].[COLUMN] FROM [TABLE] -- SQL Server style

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 renderQuotedNames and renderNameCase settings allow for overriding the name of all identifiers in jOOQ to a consistent style. Possible options are:

RenderQuotedNames

  • ALWAYS: This will quote all identifiers.
  • EXPLICIT_DEFAULT_QUOTED: This will quote all identifiers, which are not explicitly unquoted using DSL.unquotedName().
  • EXPLICIT_DEFAULT_UNQUOTED: This will not quote any identifiers, unless they are explicitly quoted using DSL.quotedName().
  • NEVER: This will not quote any identifiers.

RenderNameCase

  • AS_IS: This will generate all names in their proper case.
  • LOWER: This will transform all names to lower case.
  • LOWER_IF_UNQUOTED: This will transform all names to lower case if the name is unquoted.
  • UPPER: This will transform all names to upper case.
  • UPPER_IF_UNQUOTED: This will transform all names to upper case if the name is unquoted.

The two flags are independent of one another. If your database supports quoted, case sensitive identifiers, then using LOWER or UPPER on quoted identifiers may not work.

Example configuration

Settings settings = new Settings()
    .withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED) // Defaults to EXPLICIT_DEFAULT_QUOTED
    .withRenderNameCase(RenderNameCase.LOWER_IF_UNQUOTED);              // Defaults to AS_IS

The behaviour of this setting is influenced by the renderLocale setting.

 

3.2.7.7. Implicit join type

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ's very useful implicit JOIN feature can be used to use a path notation to join tables on their actual, or synthetic foreign keys. For example:

// Get all books, their authors, and their respective language
create.select(
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.TITLE,
          BOOK.language().CD.as("language"))
      .from(BOOK)
      .fetch();

By default, this produces:

  • An INNER_JOIN if all columns of the foreign key are NOT NULL
  • A LEFT_JOIN if the foreign key is nullable / optional

The above defaults are important to prevent implicit joins from filtering results when placed in clauses that are not meant to filter, such as the SELECT clause or the ORDER BY clause.

Users may prefer to enforce a different behaviour, including:

  • Always produce a LEFT_JOIN, e.g. because this was the behaviour before jOOQ 3.14
  • Always produce an INNER_JOIN, e.g. because they're migrating off Hibernate / JPA, and depend on Hibernate's implicit joins producing inner joins

This change of behaviour can be achieved with the following setting:

Example configuration

Settings settings = new Settings()
    .withRenderImplicitJoinType(RenderImplicitJoinType.INNER_JOIN);
 

3.2.7.8. Inline Threshold

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Previous sections showed how the SQL generation of bind values can be controlled, e.g. by forcing them to be inlined, or by running a static JDBC statement.

Sometimes, inlining needs to be enforced dynamically, depending on the query content. This is the case when there are a great number of bind variables. Known vendor-specific limits are:

  • Access : 768
  • Ingres : 1024
  • Oracle : 32767
  • PostgreSQL : 32767
  • SQLite : 999
  • SQL Server : 2100
  • Sybase ASE : 2000
  • Teradata : 2536

By default, jOOQ will automatically inline all bind variables in any SQL statement, once these thresholds have been reached. However, it is possible to override this default and provide a setting to re-define a global threshold for all dialects.

Example configuration

Settings settings = new Settings()
    .withInlineThreshold(100); // Defaults to 0, which means the default thresholds are applied
 

3.2.7.9. IN-list Padding

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Databases that feature a cursor cache / statement cache (e.g. Oracle, SQL Server, DB2, etc.) are highly optimised for prepared statement re-use. When a client sends a prepared statement to the server, the server will go to the cache and look up whether there already exists a previously calculated execution plan for the statement (i.e. the SQL string). This is called a "soft-parse" (in Oracle). If not, the execution plan is calculated on the fly. This is called a "hard-parse" (in Oracle).

Preventing hard-parses is extremely important in high throughput OLTP systems where queries are usually not very complex but are run millions of times in a short amount of time. Using bind variables, this is usually not a problem, with the exception of the IN predicate, which generates different SQL strings even when using bind variables:

-- All of these are different SQL statements:
SELECT * FROM AUTHOR WHERE ID IN (?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?)

This problem may not be obvious to Java / jOOQ developers, as they are always produced from the same jOOQ statement:

// All of these are the same jOOQ statement
DSL.using(configuration)
   .select()
   .from(AUTHOR)
   .where(AUTHOR.ID.in(collection))
   .fetch();

Depending on the possible sizes of the collection, it may be worth exploring using arrays or temporary tables as a workaround, or to reuse the original query that produced the set of IDs in the first place (through a semi-join). But sometimes, this is not possible. In this case, users can opt in to a third workaround: enabling the inListPadding setting. If enabled, jOOQ will "pad" the IN list to a length that is a power of two (configurable with Settings.inListPadBase). So, the original queries would look like this instead:

-- Original
SELECT * FROM AUTHOR WHERE ID IN (?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?)
-- Padded
SELECT * FROM AUTHOR WHERE ID IN (?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?)
SELECT * FROM AUTHOR WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?)

This technique will drastically reduce the number of possible SQL strings without impairing too much the usual cases where the IN list is small. When padding, the last bind variable will simply be repeated many times.

Usually, there is a better way - use this as a last resort!

Example configuration

Settings settings = new Settings()
    .withInListPadding(true) // Default to false
    .withInListPadBase(4);   // Default to 2
 

3.2.7.10. Interpreter Configuration

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SQL Interpreter API ships with a variety of settings that govern its behaviour. These settings include:

  • interpreterDialect: The interpreter input dialect. This dialect is used to decide whether DDL interpretation should be done on an actual in-memory database of a specific type, or using jOOQ's built in DDL interpretation.
  • interpreterDelayForeignKeyDeclarations: Whether the interpreter should delay the application of foreign key declarations (in case of which forward references are possible).
  • interpreterLocale: The locale to use for things like case insensitive comparisons.
  • interpreterNameLookupCaseSensitivity: The identifier case sensitivity that should be applied when interpreting SQL, depending on whether identifiers are quoted or not.
  • interpreterSearchPath: The search path for unqualified schema objects used by the interpreter.

Example configuration

Settings settings = new Settings()
    .withInterpreterDialect(H2)                           // Defaults to DEFAULT
    .withInterpreterDelayForeignKeyDeclarations(true)     // Defaults to false
    .withInterpreterLocale(Locale.forLanguageTag("de"))   // Defaults to Locale.getDefault()
    .withInterpreterNameLookupCaseSensitivity(NEVER)      // Defaults to WHEN_QUOTED
    .withInterpreterSearchPath(...);                      // Defaults to an empty list
 

3.2.7.11. JDBC Flags

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

JDBC statements feature a couple of flags that influence the execution of such a statement. Each of these flags can be configured through jOOQ's org.jooq.Query and org.jooq.ResultQuery on a statement-per-statement basis, but there's also the possibility to centrally specify a value for these flags. These are the three flags:

All of these flags are JDBC-only features with no direct effect on jOOQ. jOOQ only passes them through to the underlying statement.

Example configuration

Settings settings = new Settings()
    .withQueryTimeout(5)
    .withQueryPoolable(DEFAULT)
    .withMaxRows(1000)
    .withFetchSize(20);
 

3.2.7.12. Keyword style

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In all SQL dialects, keywords are case insensitive, and this is also the default in jOOQ, which mostly generates lower-case keywords. Users may wish to adapt this and they have these options for the renderKeywordCase setting:

  • AS_IS (the default): Generate keywords as they are defined in the codebase (mostly lower case).
  • LOWER: Generate keywords in lower case.
  • UPPER: Generate keywords in upper case.
  • PASCAL: Generate keywords in pascal case.

Example configuration

Settings settings = new Settings()
    .withRenderKeywordCase(RenderKeywordCase.UPPER); // Defaults to AS_IS
 

3.2.7.13. Listener Invocation Order

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ offers a variety of SPIs in the Configuration object. Some of those SPIs are event listeners, that can listen to "start" and "end" events, such as for example the ExecuteListener that listens to the query execution lifecycle.

When registering multiple listeners of a type, the invocation order may be relevant as custom listeners might communicate with each other. In such a case, the following settings allow for overriding the invocation order of "start" and "end" events for each type of listener:

Example configuration

Settings settings = new Settings()
    .withTransactionListenerStartInvocationOrder(DEFAULT) // Defaults to DEFAULT
    .withTransactionListenerEndInvocationOrder(REVERSE)   // Defaults to DEFAULT
    .withVisitListenerStartInvocationOrder(DEFAULT)       // Defaults to DEFAULT
    .withVisitListenerEndInvocationOrder(REVERSE)         // Defaults to DEFAULT
    .withRecordListenerStartInvocationOrder(DEFAULT)      // Defaults to DEFAULT
    .withRecordListenerEndInvocationOrder(REVERSE)        // Defaults to DEFAULT
    .withExecuteListenerStartInvocationOrder(DEFAULT)     // Defaults to DEFAULT
    .withExecuteListenerEndInvocationOrder(REVERSE);      // Defaults to DEFAULT
 

3.2.7.14. Locales

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When doing locale sensitive operations, such as upper casing or lower casing a name (see Name styles), then it may be important in some areas to be able to specify the java.util.Locale for the operation.

Example configuration

// All of these default to Locale.getDefault(), if not specified explicitly
Settings settings = new Settings()
    .withLocale(Locale.forLanguageTag("de"))             // The default locale if no more specific locales are specified
    .withRenderLocale(Locale.forLanguageTag("de"))       // The locale used when rendering SQL
    .withParseLocale(Locale.forLanguageTag("de"))        // The locale used when parsing SQL
    .withInterpreterLocale(Locale.forLanguageTag("de")); // The locale used when interpreting SQL
 

3.2.7.15. Map JPA Annotations

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The org.jooq.impl.DefaultRecordMapper supports basic JPA mapping (mostly @Table and @Column annotations). Looking up these annotations costs a slight extra overhead (mostly taken care of through reflection caching). It can be turned off using the mapJPAAnnotations setting:

Example configuration

Settings settings = new Settings()
    .withMapJPAAnnotations(false); // Defaults to true
 

3.2.7.16. Object qualification

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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 catalog.schema.table.column
FROM catalog.schema.table
DSL.using(configuration)
   .select(TABLE.COLUMN) // Column only qualified with table
   .from(TABLE)          // No qualification on table

While the jOOQ code is also implicitly fully qualified (see implied imports), it may not be desireable to use fully qualified object names in SQL. The renderCatalog and renderSchema settings are used for this.

Example configuration

new Settings()
  .withRenderCatalog(false)  // Defaults to true
  .withRenderSchema(false);  // Defaults to true

More sophisticated multitenancy approaches are available through the render mapping feature.

 

3.2.7.17. Optimistic Locking

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

There are two settings governing the behaviour of the jOOQ optimistic locking feature:

  • updateRecordVersion: Whether UpdatableRecord instances should modify the record version prior to storing the record. This feature is independent of, but related to optimistic locking.
  • updateRecordTimestamp: Whether UpdatableRecord instances should modify the record timestamp prior to storing the record. This feature is independent of, but related to optimistic locking.
  • executeWithOptimisticLocking: This allows for turning off the feature entirely.
  • executeWithOptimisticLockingExcludeUnversioned: This allows for turning off the feature for updatable records who are not explicitly versioned.

Example configuration

Settings settings = new Settings()
    .withUpdateRecordVersion(true)                              // Defaults to true
    .withUpdateRecordTimestamp(true)                            // Defaults to true
    .withExecuteWithOptimisticLocking(true)                     // Defaults to false
    .withExecuteWithOptimisticLockingExcludeUnversioned(false); // Defaults to false

For more details, please refer to the manual's section about the optimistic locking feature.

 

3.2.7.18. Parameter name prefix

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When choosing a ParameterType.NAMED to produce named parameters, the default is to use a colon as a prefix to the parameter name.

For example:

-- NAMED
SELECT FIRST_NAME || :1 FROM AUTHOR WHERE ID = :x

Depending on how the named parameters are interpreted, this default is not optimal. A better character might be the $ sign, e.g. in PostgreSQL or R2DBC. For this, the renderNamedParamPrefix setting can be used:

Example configuration

Settings settings = new Settings()
    .withRenderNamedParamPrefix("$"); // Defaults to ":"
 

3.2.7.19. Parameter types

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

  • INDEXED (the default): Generates indexed parameter placeholders using ?.
  • NAMED: Generates named parameter placeholders, such as :param for parameters that are named explicitly or :1 for unnamed, indexed parameters.
  • NAMED_OR_INLINED: Generates named parameter placeholders for parameters that are named explicitly and inlines all unnamed parameters.
  • INLINED: Inlines all parameters.

An example:

-- INDEXED
SELECT FIRST_NAME || ? FROM AUTHOR WHERE ID = ?
-- NAMED
SELECT FIRST_NAME || :1 FROM AUTHOR WHERE ID = :x
-- NAMED_OR_INLINED
SELECT FIRST_NAME || 'x' FROM AUTHOR WHERE ID = :x
-- INLINED
SELECT FIRST_NAME || 'x' FROM AUTHOR WHERE ID = 42
Param<String> x = val("x");
Param<Integer> i = param("x", 42);

DSL.using(configuration)
   .select(FIRST_NAME.concat(x))
   .from(AUTHOR)
   .where(ID.eq(i))
   .fetch();

Example configuration

Settings settings = new Settings()
    .withParamType(ParamType.NAMED); // Defaults to INDEXED

The following setting statementType may override this setting.

 

3.2.7.20. Parser Configuration

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SQL Parser API ships with a variety of settings that govern its behaviour. These settings include:

  • parseDialect: The parser input dialect. This dialect is used to decide what vendor specific grammar should be applied in case of ambiguities that cannot be resolved from the context.
  • parseIgnoreComments: Using this flag, the parser can ignore certain sections that would otherwise be executed by RDBMS. Everything between an parseIgnoreCommentStart and the parseIgnoreCommentStop token will be ignored.
  • parseIgnoreCommentStart: The token that delimits the beginning of a section to be ignored by jOOQ. Ideally, this token is placed inside of a SQL comment.
  • parseIgnoreCommentStop: The token that delimits the end of a section to be ignored by jOOQ. Ideally, this token is placed inside of a SQL comment.
  • parseSearchPath: The search path to look up unqualified identifiers to be used when using parseWithMetaLookups. Most dialects support a single schema on their search path (the CURRENT_SCHEMA). PostgreSQL supports a 'search_path', which allows for listing multiple schemata to use to look up unqualified tables, procedures, etc. in.
  • parseUnsupportedSyntax: The parser can parse some syntax that jOOQ does not support. By default, such syntax is ignored. Use this flag if you want to fail in such cases.
  • parseUnknownFunctions: The parser only parses "known" (to jOOQ) built in functions, and fails otherwise. This flag allows for parsing any built in function using a standard func_name(arg1, arg2, ...) syntax.
  • parseWithMetaLookups: Whether org.jooq.Meta should be used to look up meta information such as schemas, tables, columns, column types, etc.

An example of using the parseIgnoreComments feature:

-- What you execute
/* [jooq ignore start] */
CREATE SCHEMA s1;
SET SCHEMA s1;
/* [jooq ignore stop] */

/* [jooq ignore start] */ -- /* [jooq ignore stop] */ CREATE SCHEMA s2;
/* [jooq ignore start] */ -- /* [jooq ignore stop] */ SET SCHEMA s2;

CREATE TABLE t (i INTEGER);
-- What the jOOQ parser sees
/*


                      */

/*                                                 */ CREATE SCHEMA s2;
/*                                                 */ SET SCHEMA s2;

CREATE TABLE t (i INTEGER);

Example configuration

Settings settings = new Settings()
    .withParseDialect(SQLSERVER)                         // Defaults to DEFAULT
    .withParseWithMetaLookups(THROW_ON_FAILURE)          // Defaults to OFF
    .withParseSearchPath(
        new ParseSearchSchemata().withSchema("PUBLIC"),
        new ParseSearchSchemata().withSchema("TEST"))
    .withParseUnsupportedSyntax(FAIL)                    // Defaults to IGNORE
    .withParseUnknownFunctions(IGNORE)                   // Defaults to FAIL
    .withParseIgnoreComments(true)                       // Defaults to false
    .withParseIgnoreCommentStart("<ignore>")             // Defaults to "[jooq ignore start]"
    .withParseIgnoreCommentStop("</ignore>")             // Defaults to "[jooq ignore stop]"
 

3.2.7.21. Reflection caching

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

All operations of the DefaultRecordMapper are cached in the Configuration by default for improved mapping and reflection speed. Users who prefer to override this cache, or work with their own custom record mapper provider may wish to turn off the out-of-the-box caching feature.

Example configuration

Settings settings = new Settings()
    .withReflectionCaching(false); // Defaults to true
 

3.2.7.22. Return all columns on store

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When using the updatable records feature, jOOQ always fetches the generated identity value, if such a value is available and if the return identity on store feature is enabled (it is, by default).

The identity value is not the only value that is generated by default. Specifically, there may be triggers that are used for auditing or other reasons, which generate LAST_UPDATE or LAST_UPDATE_BY values in a record. Users who wish to also automatically fetch these values after all store(), insert(), or update() calls may do so by specifying the returnAllOnUpdatableRecord setting. This setting depends on the availability of INSERT .. RETURNING, UPDATE .. RETURNING, and DELETE .. RETURNING statements, which are not available from all databases, in case of which a refresh() call may be issued, creating a separate round trip to the server.

Example configuration

Settings settings = new Settings()
    .withReturnAllOnUpdatableRecord(true); // Defaults to false
 

3.2.7.23. Return Identity Value On Store

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When using the updatable records feature, jOOQ by default fetches the generated identity value. In some situations, it is desirable for this feature to be turned off using the following flag:

Example configuration

Settings settings = new Settings()
    .withReturnIdentityOnUpdatableRecord(false); // Defaults to true
 

3.2.7.24. Runtime catalog, schema and table mapping

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Most SQL object types are qualified with a org.jooq.Catalog and org.jooq.Schema. In multi-tenant application, users may want to map these identifier namespaces to something other than the default.

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:

Example configuration

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withSchemata(
        new MappedSchema().withInput("DEV")
                          .withOutput("MY_BOOK_WORLD"),
        new MappedSchema().withInput("LOG")
                          .withOutput("MY_BOOK_WORLD_LOG")));

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:

Example configuration

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withSchemata(
        new MappedSchema().withInput("DEV")
                          .withTables(
         new MappedTable().withInput("AUTHOR")
                          .withOutput("MY_APP__AUTHOR"))));

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.

Mapping of catalogs

For databases like SQL Server, it is also possible to map catalogs in addition to schemata. The mechanism is exactly the same. So let's assume that we generated code for a table [dev].[dbo].[author] and want to map it to [my_book_world].[dbo].[author] at runtime. This can be achieved as follows:

Example configuration

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withCatalogs(
        new MappedCatalog().withInput("DEV")
                           .withOutput("MY_BOOK_WORLD")));

To give you full control of how each and every table gets mapped, a MappedCatalog object can contain MappedSchema (and thus also MappedTable) definitions.

Using regular expressions

All of the above examples were using 1:1 constant name mappings where the input and output schema or table names are fixed by the configuration. With jOOQ 3.8, regular expression can be used as well for mapping, for example:

Example configuration

Settings settings = new Settings()
    .withRenderMapping(new RenderMapping()
    .withSchemata(
        new MappedSchema().withInputExpression(Pattern.compile("DEV_(.*)"))
                          .withOutput("PROD_$1")
                          .withTables(
         new MappedTable().withInputExpression(Pattern.compile("DEV_(.*)"))
                          .withOutput("PROD_$1"))));

The only difference to the constant version is that the input field is replaced by the inputExpression field of type java.util.regex.Pattern, in case of which the meaning of the output field is a pattern replacement, not a constant replacement.

Hard-wiring mappings at code-generation time

Note that the manual's section about code generation schema mapping explains how you can hard-wire your catalog, schema and table mappings at code generation time.

Limitations

Mapped objects need to be known to the jOOQ org.jooq.RenderContext, which means that for example plain SQL templates and their contents cannot be mapped. See also features requiring code generation for more details.

 

3.2.7.25. Scalar subqueries for stored functions

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

SELECT
  (SELECT my_package.format(LANGUAGE_ID) FROM dual)
FROM BOOK
DSL.using(configuration)
   .select(MyPackage.format(BOOK.LANGUAGE_ID))
   .from(BOOK)

If our table contains thousands of books, but only a dozen of LANGUAGE_ID values, then with scalar subquery caching, we can avoid most of the function calls and cache the result per LANGUAGE_ID.

Example configuration

Settings settings = new Settings()
    .withRenderScalarSubqueriesForStoredFunctions(true);
 

3.2.7.26. Statement Type

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

JDBC knows two types of statements:

The statementType setting allows for overriding the default of using prepared statements internally. There are two possible options for this setting:

  • PREPARED_STATEMENT (the default): Use prepared statements.
  • STATIC_STATEMENT: Use static statements. This enforces the paramType == INLINED. See parameter types

Example configuration

Settings settings = new Settings()
    .withStatementType(StatementType.STATIC_STATEMENT); // Defaults to PREPARED_STATEMENT
 

3.2.7.27. Updatable Primary Keys

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

Example configuration

Settings settings = new Settings()
    .withUpdatablePrimaryKeys(true); // Defaults to false
 

3.2.8. Thread safety

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

org.jooq.Configuration, and by consequence org.jooq.DSLContext, make no thread safety guarantees, but by carefully observing a few rules, they can be shared in a thread safe way. We encourage sharing Configuration instances, because they contain caches for work not worth repeating, such as reflection field and method lookups for org.jooq.impl.DefaultRecordMapper. If you're using Spring or CDI for dependency injection, you will want to be able to inject a DSLContext instance everywhere you use it.

The following needs to be considered when attempting to share Configuration and DSLContext among threads:

  • Configuration is mutable for historic reasons. Calls to various Configuration.set() methods must be avoided after initialisation, should a Configuration (and by consequence DSLContext) instance be shared among threads. If you wish to modify some elements of a Configuration for single use, use the Configuration.derive() methods instead, which create a copy.
  • Configuration components, such as org.jooq.conf.Settings are mutable as well. The same rules for modification apply here.
  • Configuration allows for supplying user-defined SPI implementations (see above for examples). All of these must be thread safe as well, for their wrapping Configuration to be thread safe. If you are using a org.jooq.impl.DataSourceConnectionProvider, for instance, you must make sure that your javax.sql.DataSource is thread safe as well. This is usually the case when you use a third party connection pool.

As can be seen above, Configuration was designed to work in a thread safe way, despite it not making any such guarantee.

 

3.3. SQL Statements (DML)

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

 

3.3.1. jOOQ's DSL and model API

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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
Result<Record> result =
create.select()
      .from(AUTHOR.as("a"))
      .join(BOOK.as("b")).on(a.ID.eq(b.AUTHOR_ID))
      .where(a.YEAR_OF_BIRTH.gt(1920)
      .and(a.FIRST_NAME.eq("Paulo")))
      .orderBy(b.TITLE)
      .fetch();

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 with IDE syntax auto 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:

DSLContext create = DSL.using(connection, dialect);
Result<?> result = create.select()
                         .from(AUTHOR)
                         .join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                         .fetch();

Unlike other, simpler frameworks that use "fluent APIs" or "method chaining", jOOQ's BNF-based interface hierarchy will not allow bad query syntax. The following will not compile, for instance:

DSLContext create = DSL.using(connection, dialect);
Result<?> result = create.select()
                         .join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                      //  ^^^^ "join" is not possible here
                         .from(AUTHOR)
                         .fetch();

Result<?> result = create.select()
                         .from(AUTHOR)
                         .join(BOOK)
                         .fetch();
                      //  ^^^^^ "on" is missing here

Result<?> result = create.select(rowNumber())
                      //         ^^^^^^^^^ "over()" is missing here
                         .from(AUTHOR)
                         .fetch();

Result<?> result = create.select()
                         .from(AUTHOR)
                         .where(AUTHOR.ID.in(select(BOOK.TITLE).from(BOOK)))
                      //                     ^^^^^^^^^^^^^^^^^^
                      // AUTHOR.ID is of type Field<Integer> but subselect returns Record1<String>
                         .fetch();

Result<?> result = create.select()
                         .from(AUTHOR)
                         .where(AUTHOR.ID.in(select(BOOK.AUTHOR_ID, BOOK.ID).from(BOOK)))
                      //                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                      // AUTHOR.ID is of degree 1 but subselect returns Record2<Integer, Integer>
                         .fetch();

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:

DSLContext create = DSL.using(connection, dialect);
SelectQuery<Record> query = create.selectQuery();
query.addFrom(AUTHOR);

// Join books only under certain circumstances
if (join) {
    query.addJoin(BOOK, BOOK.AUTHOR_ID.eq(AUTHOR.ID));
}

Result<?> result = query.fetch();

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:

DSLContext create = DSL.using(connection, dialect);
SelectFinalStep<?> select = create.select().from(AUTHOR);

// Add the JOIN clause on the internal QueryObject representation
SelectQuery<?> query = select.getQuery();
query.addJoin(BOOK, BOOK.AUTHOR_ID.eq(AUTHOR.ID));

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:

// Conditional expressions (immutable)
// -----------------------------------
Condition a = BOOK.TITLE.eq("1984");
Condition b = BOOK.TITLE.eq("Animal Farm");

// The following can be said
a       != a.or(b); // or() does not modify a
a.or(b) != a.or(b); // or() always creates new objects

// Statements (mutable)
// --------------------
SelectFromStep<?> s1 = select();
SelectJoinStep<?> s2 = s1.from(BOOK);
SelectJoinStep<?> s3 = s1.from(AUTHOR);

// The following can be said
s1 == s2; // The internal object is always the same
s2 == s3; // The internal object is always the same

On the other hand, beware that you can always extract and modify bind values from any QueryPart.

 

3.3.2. The WITH clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SQL:1999 standard specifies the WITH clause to be an optional clause for the SELECT statement, in order to specify common table expressions (also: CTE). Many other databases (such as PostgreSQL, SQL Server) also allow for using common table expressions also in other DML clauses, such as the INSERT statement, UPDATE statement, DELETE statement, or MERGE statement.

When using common table expressions with jOOQ, there are essentially two approaches:

  • Declaring and assigning common table expressions explicitly to names
  • Inlining common table expressions into a SELECT statement

Explicit common table expressions

The following example makes use of names to construct common table expressions, which can then be supplied to a WITH clause or a FROM clause of a SELECT statement:

-- Pseudo-SQL for a common table expression specification
"t1" ("f1", "f2") AS (SELECT 1, 'a')
// Code for creating a CommonTableExpression instance
name("t1").fields("f1", "f2").as(select(val(1), val("a")));

The above expression can be assigned to a variable in Java and then be used to create a full SELECT statement:







WITH "t1" ("f1", "f2") AS (SELECT 1, 'a'),
     "t2" ("f3", "f4") AS (SELECT 2, 'b')
SELECT
    "t1"."f1" + "t2"."f3" AS "add",
    "t1"."f2" || "t2"."f4" AS "concat"
FROM "t1", "t2"
;
CommonTableExpression<Record2<Integer, String>> t1 =
  name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression<Record2<Integer, String>> t2 =
  name("t2").fields("f3", "f4").as(select(val(2), val("b")));

Result<?> result2 =
create.with(t1)
      .with(t2)
      .select(
          t1.field("f1").add(t2.field("f3")).as("add"),
          t1.field("f2").concat(t2.field("f4")).as("concat"))
      .from(t1, t2)
      .fetch();

Note that the org.jooq.CommonTableExpression type extends the commonly used org.jooq.Table type, and can thus be used wherever a table can be used.

Inlined common table expressions

If you're just operating on plain SQL, you may not need to keep intermediate references to such common table expressions. An example of such usage would be this:

WITH "a" AS (SELECT
               1 AS "x",
               'a' AS "y"
            )
SELECT
FROM "a"
;
create.with("a").as(select(
                        val(1).as("x"),
                        val("a").as("y")
                   ))
      .select()
      .from(table(name("a")))
      .fetch();
 

3.3.3. The WITH RECURSIVE clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

Assuming a table like this:

CREATE TABLE directory (
  id           INT NOT NULL,
  parent_id    INT,

  -- In PostgreSQL, use TEXT instead, to work around https://github.com/jOOQ/jOOQ/issues/12067
  label        VARCHAR(50),

  CONSTRAINT pk_directory PRIMARY KEY (id),
  CONSTRAINT fk_directory FOREIGN KEY (parent_id) REFERENCES directory (id)
);

INSERT INTO directory VALUES ( 1, null, 'C:');
INSERT INTO directory VALUES ( 2,    1, 'eclipse');
INSERT INTO directory VALUES ( 3,    2, 'configuration');
INSERT INTO directory VALUES ( 4,    2, 'dropins');
INSERT INTO directory VALUES ( 5,    2, 'features');
INSERT INTO directory VALUES ( 7,    2, 'plugins');
INSERT INTO directory VALUES ( 8,    2, 'readme');
INSERT INTO directory VALUES ( 9,    8, 'readme_eclipse.html');
INSERT INTO directory VALUES (10,    2, 'src');
INSERT INTO directory VALUES (11,    2, 'eclipse.exe');

Using WITH RECURSIVE, you can now query the structure of this directory as follows:

WITH RECURSIVE t (
  id,
  name,
  path
) AS (
  SELECT
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    DIRECTORY.LABEL
  FROM
    DIRECTORY
  WHERE
    DIRECTORY.PARENT_ID IS NULL
  UNION ALL
  SELECT
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    t.path
      || '\'
      || DIRECTORY.LABEL
  FROM
    t
  JOIN
    DIRECTORY
  ON t.id = DIRECTORY.PARENT_ID
)
SELECT *
FROM
  t;
CommonTableExpression<?> cte = name("t").fields(
  "id",
  "name",
  "path"
).as(
  select(
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    DIRECTORY.LABEL)
  .from(DIRECTORY)
  .where(DIRECTORY.PARENT_ID.isNull())
  .unionAll(
  select(
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    field(name("t", "path"), VARCHAR)
      .concat("\\")
      .concat(DIRECTORY.LABEL))
  .from(table(name("t")))
  .join(DIRECTORY)
  .on(field(name("t", "id"), INTEGER)
    .eq(DIRECTORY.PARENT_ID)))
);

System.out.println(
    create.withRecursive(cte)
          .selectFrom(cte)
          .fetch()
);

The output would look like this:

+----+---------------------+---------------------------------------+
| id | name                | path                                  |
+----+---------------------+---------------------------------------+
| 1  | C:                  | C:                                    |
| 2  | eclipse             | C:\eclipse                            |
| 3  | configuration       | C:\eclipse\configuration              |
| 4  | dropins             | C:\eclipse\dropins                    |
| 11 | eclipse.exe         | C:\eclipse\eclipse.exe                |
| 5  | features            | C:\eclipse\features                   |
| 7  | plugins             | C:\eclipse\plugins                    |
| 8  | readme              | C:\eclipse\readme                     |
| 9  | readme_eclipse.html | C:\eclipse\readme\readme_eclipse.html |
| 10 | src                 | C:\eclipse\src                        |
+----+---------------------+---------------------------------------+

Caveats

The SQL language expresses the recursion syntactically, meaning the table t in the above example is being referenced from within the declaration of t. This isn't possible in a language like Java. Hence, we must use the identifier API to construct identifier references for tables and columns. This technique usually appears a bit more verbose than ordinary jOOQ API usage that is based on generated code for your schema.

 

3.3.4. The SELECT statement

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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_IN > 2008
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
// And with jOOQ...



DSLContext create = DSL.using(connection, dialect);

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
      .from(AUTHOR)
      .join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .where(BOOK.LANGUAGE.eq("DE"))
      .and(BOOK.PUBLISHED_IN.gt(2008))
      .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .having(count().gt(5))
      .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
      .limit(2)
      .offset(1)
      .forUpdate()
      .fetch();

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:

public <R extends Record> SelectWhereStep<R> selectFrom(Table<R> table);

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:

BookRecord book = create.selectFrom(BOOK)
                        .where(BOOK.LANGUAGE.eq("DE"))
                        .orderBy(BOOK.TITLE)
                        .fetchAny();

The "reduced" SELECT API is limited in the way that it skips DSL access to any of these clauses:

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.

 

3.3.4.1. SELECT clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SELECT clause lets you project your own record types, referencing table fields, functions, arithmetic expressions, etc. The DSL type provides several methods for expressing a SELECT clause:

-- The SELECT clause
SELECT BOOK.ID, BOOK.TITLE
SELECT BOOK.ID, TRIM(BOOK.TITLE)
// Provide a varargs Fields list to the SELECT clause:
Select<?> s1 = create.select(BOOK.ID, BOOK.TITLE);
Select<?> s2 = create.select(BOOK.ID, trim(BOOK.TITLE));

The following sections illustrate various features and subclauses of the SELECT clause.

 

3.3.4.1.1. Projection type safety

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

// Non-typesafe select methods:
public static SelectSelectStep<Record> select(Collection<? extends SelectField<?>> fields);
public static SelectSelectStep<Record> select(SelectField<?>... fields);

// Typesafe select methods:
public static <T1>         SelectSelectStep<Record1<T1>>         select(SelectField<T1> field1);
public static <T1, T2>     SelectSelectStep<Record2<T1, T2>>     select(SelectField<T1> field1, SelectField<T2> field2);
// [...]

The type that is being projected is the org.jooq.SelectField, see also the next section about SelectField. Since the generic R type is bound to some Record[N], the associated T type information can be used in various other contexts, e.g. the IN predicate. Such a SELECT statement can be assigned typesafely:

Select<Record2<Integer, String>> s1 = create.select(BOOK.ID, BOOK.TITLE);
Select<Record2<Integer, String>> s2 = create.select(BOOK.ID, trim(BOOK.TITLE));

// Alternatively, just use var to infer the type:
var s3 = create.select(BOOK.ID, trim(BOOK.TITLE));

For more information about typesafe record types with degree up to 22, see the manual's section about Record1 to Record22.

 

3.3.4.1.2. SelectField

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The org.jooq.SelectField type is used by any projection of the SELECT clause and the INSERT .. RETURNING clause. It has numerous subtypes, which are allowed as projections in jOOQ:

  • More subtypes are available from future jOOQ versions.
 

3.3.4.1.3. SELECT *

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ supports the asterisk operator in projections both as a qualified asterisk (through Table.asterisk()) and as an unqualified asterisk (through DSL.asterisk()). It is also possible to omit the projection entirely, in case of which an asterisk may appear in generated SQL, if not all column names are known to jOOQ.

Whenever jOOQ generates an asterisk (explicitly, or because jOOQ doesn't know the exact projection), the column order, and the column set are defined by the database server, not jOOQ. If you're using generated code, this may lead to problems as there might be a different column order than expected, as well as too many or too few columns might be projected.

// Explicitly selects all columns available from BOOK - No asterisk
create.select().from(BOOK).fetch();

// Explicitly selects all columns available from BOOK and AUTHOR - No asterisk
create.select().from(BOOK, AUTHOR).fetch();
create.select().from(BOOK).crossJoin(AUTHOR).fetch();

// Renders a SELECT * statement, as columns are unknown to jOOQ - Implicit unqualified asterisk
create.select().from(table(name("BOOK"))).fetch();

// Renders a SELECT * statement - Explicit unqualified asterisk
create.select(asterisk()).from(BOOK).fetch();

// Renders a SELECT BOOK.* statement - Explicit qualified asterisk
create.select(BOOK.asterisk()).from(BOOK).fetch();
create.select(BOOK.asterisk(), AUTHOR.asterisk()).from(BOOK, AUTHOR).fetch();

With all of the above syntaxes, the row type (as discussed below) is unknown to jOOQ and to the Java compiler.

It is worth mentioning that in many cases, using an asterisk is a sign of an inefficient query because if not all columns are needed, too much data is transferred between client and server, plus some joins that could be eliminated otherwise, cannot.

 

3.3.4.1.4. SELECT * EXCEPT (...)

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A useful extension to the previously mentioned standard SQL SELECT * syntax is the BigQuery inspired * EXCEPT (columns) syntax, which takes all of a projection's columns, except some columns. Just like the asterisk itself, this is mainly useful for ad-hoc querying, but it can also be useful for an occasional jOOQ query.

// Renders a SELECT * statement - Explicit unqualified asterisk
create.select(asterisk().except(BOOK.ID)).from(BOOK).fetch();

// Renders a SELECT BOOK.* statement - Explicit qualified asterisk
create.select(BOOK.asterisk().except(BOOK.ID))
      .from(BOOK)
      .fetch();

create.select(BOOK.asterisk().except(BOOK.ID), AUTHOR.asterisk().except(AUTHOR.ID))
      .from(BOOK, AUTHOR)
      .fetch();

If a dialect doesn't support this syntax natively, jOOQ will just expand the syntax for you, explicitly, given the knowledge about meta data in generated code.

Dialect support

This example using jOOQ:

select(asterisk().except(LANGUAGE.ID)).from(LANGUAGE)

Translates to the following dialect specific expressions:

ASE, Access, Aurora MySQL, Aurora Postgres, ClickHouse, CockroachDB, DB2, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Sybase, Teradata, Trino, Vertica, YugabyteDB

SELECT LANGUAGE.CD, LANGUAGE.DESCRIPTION
FROM LANGUAGE

BigQuery

SELECT * EXCEPT (ID)
FROM LANGUAGE

H2

SELECT * EXCEPT (LANGUAGE.ID)
FROM LANGUAGE

Snowflake

SELECT * EXCLUDE (ID)
FROM LANGUAGE
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.1.5. SELECT DISTINCT

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The DISTINCT keyword can be included in the method name, when constructing a SELECT clause, to remove duplicate tuples from the projection.

SELECT DISTINCT BOOK.TITLE FROM BOOK
create.selectDistinct(BOOK.TITLE).from(BOOK).fetch();

Dialect support

This example using jOOQ:

selectDistinct(BOOK.TITLE).from(BOOK)

Translates to the following dialect specific expressions:

All dialects

SELECT DISTINCT BOOK.TITLE
FROM BOOK
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.1.6. SELECT DISTINCT ON

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A useful, though perhaps a bit esoteric PostgreSQL specific extension to SELECT DISTINCT is the ON clause. Using this clause, PostgreSQL users can specify a distinctness criteria, but then produce other columns per distinct group from one of the group's tuples. This is normally not possible in SQL, but with ON, the first tuple in the group according to the ORDER BY clause can be accessed nonetheless. An example:

SELECT DISTINCT ON (BOOK.LANGUAGE_ID)
  BOOK.LANGUAGE_ID, BOOK.TITLE
FROM BOOK
ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE
Select<?> select1 = create.select(BOOK.LANGUAGE_ID, BOOK.TITLE)
      .distinctOn(BOOK.LANGUAGE_ID)
      .from(BOOK)
      .orderBy(BOOK.LANGUAGE_ID, BOOK.TITLE).fetch();

For syntactic reasons, the order of keywords had to be inversed as the PostgreSQL syntax cannot be easily reproduced in jOOQ's internal DSL. Quite likely, you might find jOOQ's syntax a bit more intuitive, though, as it more clearly separates the SELECT parts and the DISTINCT ON parts. Arguably, the DISTINCT ON clause should be positioned after ORDER BY, where it logically belongs.

Standard SQL equivalence

The PostgreSQL extension isn't really necessary as there is a standard SQL equivalence using ROW_NUMBER filtering. In the below example, we're using an extension to the standard, the QUALIFY clause, to illustrate:

SELECT BOOK.LANGUAGE_ID, BOOK.TITLE
FROM BOOK
QUALIFY ROW_NUMBER() OVER (PARTITION BY BOOK.LANGUAGE_ID ORDER BY BOOK.TITLE) = 1
ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE
Select<?> select1 = create.select(BOOK.LANGUAGE_ID, BOOK.TITLE)
      .from(BOOK)
      .qualify(rowNumber().over(partitionBy(BOOK.LANGUAGE_ID).orderBy(BOOK.TITLE)).eq(1))
      .orderBy(BOOK.LANGUAGE_ID, BOOK.TITLE).fetch();

Dialect support

This example using jOOQ:

select(BOOK.LANGUAGE_ID, BOOK.TITLE).distinctOn(BOOK.LANGUAGE_ID).from(BOOK).orderBy(BOOK.LANGUAGE_ID, BOOK.TITLE)

Translates to the following dialect specific expressions:

Aurora Postgres, ClickHouse, CockroachDB, DuckDB, H2, Postgres, YugabyteDB

SELECT DISTINCT ON (BOOK.LANGUAGE_ID) BOOK.LANGUAGE_ID, BOOK.TITLE
FROM BOOK
ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE

DB2, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica

SELECT t.LANGUAGE_ID, t.TITLE
FROM (
  SELECT
    BOOK.LANGUAGE_ID,
    BOOK.TITLE,
    row_number() OVER (
      PARTITION BY BOOK.LANGUAGE_ID
      ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE
    ) rn
  FROM BOOK
) t
WHERE rn = 1
ORDER BY LANGUAGE_ID, TITLE

ASE, Access, Aurora MySQL, BigQuery, Derby, HSQLDB

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.1.7. Convenience methods

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Some commonly used projections can be easily created using convenience methods:

-- Simple SELECTs
SELECT COUNT(*)
SELECT 0 -- Not a bind variable
SELECT 1 -- Not a bind variable
// Select commonly used values
Result<?> result1 = create.selectCount().fetch();
Result<?> result2 = create.selectZero().fetch();
Result<?> result3 = create.selectOne().fetch();

Which are short forms for creating Column expressions from the org.jooq.impl.DSL API

-- Simple SELECTs
SELECT COUNT(*)
SELECT 0 -- Not a bind variable
SELECT ? -- A bind variable
// Select commonly used values
Result<?> result1 = create.select(count()).fetch();
Result<?> result2 = create.select(inline(0)).fetch();
Result<?> result3 = create.select(val(1)).fetch();
 

3.3.4.2. FROM clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SQL FROM clause allows for specifying any number of table expressions to select data from. The following are examples of how to form normal FROM clauses:

SELECT 1 FROM BOOK
SELECT 1 FROM BOOK, AUTHOR
SELECT 1 FROM BOOK "b", AUTHOR "a"
create.selectOne().from(BOOK).fetch();
create.selectOne().from(BOOK, AUTHOR).fetch();
create.selectOne().from(BOOK.as("b"), AUTHOR.as("a")).fetch();

Read more about aliasing in the manual's section about aliased tables.

More advanced table expressions

Apart from simple tables, you can pass any arbitrary table expression to the jOOQ FROM clause. This may include unnested cursors in Oracle:

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS')
);
create.select()
      .from(table(
          DbmsXplan.displayCursor(null, null, "ALLSTATS")
      ).fetch();

Note, in order to access the DbmsXplan package, you can use the code generator to generate Oracle's SYS schema.

Selecting FROM DUAL with jOOQ

In many SQL dialects, FROM is a mandatory clause, in some it isn't. jOOQ allows you to omit the FROM clause, returning just one record. An example:

SELECT 1 FROM DUAL
SELECT 1
DSL.using(SQLDialect.ORACLE).selectOne().fetch();
DSL.using(SQLDialect.POSTGRES).selectOne().fetch();

Read more about dual or dummy tables in the manual's section about the DUAL table. The following are examples of how to form normal FROM clauses:

 

3.3.4.3. JOIN operator

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ supports many different types of standard and non-standard SQL JOIN operations. All of these JOIN methods can be called on org.jooq.Table types the (more info in joined tables section), or directly after the FROM clause for convenience. The following example joins AUTHOR and BOOK

DSLContext create = DSL.using(connection, dialect);

// Call "join" directly on the AUTHOR table
Result<?> result = create.select()
                         .from(AUTHOR.join(BOOK)
                                     .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
                         .fetch();

// Call "join" on the type returned by "from"
Result<?> result = create.select()
                         .from(AUTHOR)
                         .join(BOOK)
                         .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                         .fetch();

The two syntaxes will produce the same SQL statement. However, calling "join" on org.jooq.Table objects allows for more powerful, nested JOIN expressions (if you can handle the parentheses):

SELECT *
FROM AUTHOR
LEFT OUTER JOIN (
  BOOK JOIN BOOK_TO_BOOK_STORE
       ON BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
)
ON BOOK.AUTHOR_ID = AUTHOR.ID
 
// Nest joins and provide JOIN conditions only at the end
create.select()
      .from(AUTHOR
      .leftOuterJoin(BOOK
        .join(BOOK_TO_BOOK_STORE)
        .on(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)))
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
      .fetch();

For more information about the different types of join, please refer to the joined tables section.

 

3.3.4.4. Implicit path JOIN

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In SQL, a lot of explicit JOIN clauses are written simply to retrieve a parent table's column from a given child table. For example, we'll write:

-- Get all books, their authors, and their respective language
SELECT
  a.first_name,
  a.last_name,
  b.title,
  l.cd AS language
FROM book b
JOIN author a ON b.author_id = a.id
JOIN language l ON b.language_id = l.id;

-- Count the number of books by author and language
SELECT
  a.first_name,
  a.last_name,
  l.cd AS language,
  COUNT(*)
FROM book
JOIN author a ON b.author_id = a.id
JOIN language l ON b.language_id = l.id
GROUP BY a.id, a.first_name, a.last_name, l.cd
ORDER BY a.first_name, a.last_name, l.cd

There is quite a bit of syntactic ceremony (or we could even call it "noise") to get a relatively simple job done. A much simpler notation would be using implicit joins:

-- Get all books, their authors, and their respective language
SELECT
  b.author.first_name,
  b.author.last_name,
  b.title,
  b.language.cd AS language
FROM book b;

-- Count the number of books by author and language
SELECT
  b.author.first_name,
  b.author.last_name,
  b.language.cd AS language,
  COUNT(*)
FROM book b
GROUP BY
  b.author_id,
  b.author.first_name,
  b.author.last_name,
  b.language.cd
ORDER BY
  b.author.first_name,
  b.author.last_name,
  b.language.cd

Notice how this alternative notation (depending on your taste) may look more tidy and straightforward, as the semantics of accessing a table's parent table (or an entity's parent entity) is straightforward.

From jOOQ 3.11 onwards, this syntax is supported for to-one relationship navigation, and from jOOQ 3.19 also for to-many relationship navigation. The code generator produces relevant navigation methods on generated tables, which can be used in a type safe way. The navigation method names are:

  • The parent table name, if there is only one foreign key between child table and parent table
  • The foreign key name, if there are more than one foreign keys between child table and parent table

This default behaviour can be overridden by using a Code Generator Strategy.

The jOOQ version of the previous queries looks like this:

// Get all books, their authors, and their respective language
create.select(
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.TITLE,
          BOOK.language().CD.as("language"))
      .from(BOOK)
      .fetch();

// Count the number of books by author and language
create.select(
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.language().CD.as("language"),
          count())
      .from(BOOK)
      .groupBy(
          BOOK.AUTHOR_ID,
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.language().CD)
      .orderBy(
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.language().CD)
      .fetch();

The generated SQL is almost identical to the original one - there is no performance penalty to this syntax.

Default JOIN type

The default type of join that is generated is:

  • INNER JOIN for to-one path segments with non-nullable parent
  • LEFT JOIN for to-one path segments with nullable parent

These defaults can be overridden with Settings.renderImplicitJoinType

How it works

During the SQL generation phase, implicit join paths are replaced by generated aliases for the path's last table. The paths are translated to a join graph, which is always LEFT JOINed to the path's "root table". If two paths share a common prefix, that prefix is also shared in the join graph.

Known limitations

  • Until jOOQ 3.17, implicit JOINs were only supported in SELECT statements (including any type of subquery), but not in the WHERE clause of UPDATE statements or DELETE statements, for instance.
  • Implicit JOINs can currently only be used to access columns, not to produce joins. I.e. it is not possible to write things like FROM book IMPLICIT JOIN book.author
  • Implicit JOINs are added to the SQL string after the entire SQL statement is available, for performance reasons. This means, that VisitListener SPI implementations cannot observe implicitly joined tables
 

3.3.4.5. WHERE clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

SELECT *
FROM BOOK
WHERE AUTHOR_ID = 1
AND TITLE = '1984'
 
create.select()
      .from(BOOK)
      .where(BOOK.AUTHOR_ID.eq(1))
      .and(BOOK.TITLE.eq("1984"))
      .fetch();

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:

SELECT *
FROM BOOK
WHERE AUTHOR_ID = 1
AND TITLE = '1984'
 
create.select()
      .from(BOOK)
      .where(BOOK.AUTHOR_ID.eq(1).and(
             BOOK.TITLE.eq("1984")))
      .fetch();

You will find more information about creating conditional expressions later in the manual.

 

3.3.4.6. CONNECT BY clause

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

--   SELECT ..
--     FROM ..
--    WHERE ..
 CONNECT BY [ NOCYCLE ] condition [ AND condition, ... ] [ START WITH condition ]
-- GROUP BY ..
-- ORDER [ SIBLINGS ] BY ..

An example for an iterative query, iterating through values between 1 and 5 is this:

SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 5
 
// Get a table with elements 1, 2, 3, 4, 5
create.select(level())
      .connectBy(level().le(5))
      .fetch();

Here's a more complex example where you can recursively fetch directories in your database, and concatenate them to a path:

SELECT
  SUBSTR(SYS_CONNECT_BY_PATH(DIRECTORY.NAME, '/'), 2)
FROM DIRECTORY
CONNECT BY
  PRIOR DIRECTORY.ID = DIRECTORY.PARENT_ID
START WITH DIRECTORY.PARENT_ID IS NULL
ORDER BY 1
 
.select(
   substring(sysConnectByPath(DIRECTORY.NAME, "/"), 2))
.from(DIRECTORY)
.connectBy(
   prior(DIRECTORY.ID).eq(DIRECTORY.PARENT_ID))
.startWith(DIRECTORY.PARENT_ID.isNull())
.orderBy(1)
.fetch();

The output might then look like this

+------------------------------------------------+
|substring                                       |
+------------------------------------------------+
|C:                                              |
|C:/eclipse                                      |
|C:/eclipse/configuration                        |
|C:/eclipse/dropins                              |
|C:/eclipse/eclipse.exe                          |
+------------------------------------------------+
|...21 record(s) truncated...

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

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
 
.select(DIRECTORY.NAME)
.from(DIRECTORY)
.connectBy(
   prior(DIRECTORY.ID).eq(DIRECTORY.PARENT_ID))
.startWith(DIRECTORY.PARENT_ID.isNull())
.orderSiblingsBy(1)
.fetch();
 

3.3.4.7. GROUP BY clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

 

3.3.4.7.1. GROUP BY columns

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The GROUP BY columns list specifies the columns whose values are used to form groups. The group columns can then be projected, whereas all the non-group columns can be aggregated. An example of such a grouped aggregation is this query:

SELECT AUTHOR_ID, COUNT(*)
FROM BOOK
GROUP BY AUTHOR_ID
 
create.select(BOOK.AUTHOR_ID, count())
      .from(BOOK)
      .groupBy(BOOK.AUTHOR_ID)
      .fetch();

The above example counts all books per author.

Note: a different and more powerful way of grouping data is to use the WINDOW clause and window functions.

Dialect support

This example using jOOQ:

select(BOOK.AUTHOR_ID, count()).from(BOOK).groupBy(BOOK.AUTHOR_ID)

Translates to the following dialect specific expressions:

All dialects

SELECT
  BOOK.AUTHOR_ID,
  count(*)
FROM BOOK
GROUP BY BOOK.AUTHOR_ID
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.7.2. GROUP BY ROLLUP

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In reports, it may be useful to run multiple aggregations across multiple dimensions of the data in one go. ROLLUP is one way to do this.

SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY ROLLUP (AUTHOR_ID, PUBLISHED_IN)
 
create.select(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN, count())
      .from(BOOK)
      .groupBy(rollup(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN))
      .fetch();

The above is a more concise (and possibly more performant) form of writing the following UNION ALL query:

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 ()

The ROLLUP function is just syntax sugar for a more complex GROUPING SETS specification. In general:

-- This
ROLLUP (A, B, C)

-- Is just short for this
GROUPING SETS ((A, B, C), (A, B), (A), ())

An example result set might look like this:

+-----------+--------------+----------+
| AUTHOR_ID | PUBLISHED_IN | COUNT(*) |
+-----------+--------------+----------+
|         1 |         1945 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
|         1 |         1948 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
|         1 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
|         2 |         1988 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
|         2 |         1990 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
|         2 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
|      NULL |         NULL |        4 | <- GROUP BY ()
+-----------+--------------+----------+

Dialect support

This example using jOOQ:

select(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID, count()).from(BOOK).groupBy(rollup(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID))

Translates to the following dialect specific expressions:

Aurora MySQL, MariaDB, MySQL

SELECT
  BOOK.AUTHOR_ID,
  BOOK.LANGUAGE_ID,
  count(*)
FROM BOOK
GROUP BY BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID
WITH ROLLUP

Aurora Postgres, ClickHouse, DB2, DuckDB, Hana, MemSQL, Oracle, Postgres, SQLDataWarehouse, SQLServer, Snowflake, Sybase, Teradata, Trino, Vertica

SELECT
  BOOK.AUTHOR_ID,
  BOOK.LANGUAGE_ID,
  count(*)
FROM BOOK
GROUP BY ROLLUP (BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID)

ASE, Access, BigQuery, CockroachDB, Derby, Exasol, Firebird, H2, HSQLDB, Informix, Redshift, SQLite, YugabyteDB

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.7.3. GROUP BY CUBE

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In reports, it may be useful to run multiple aggregations across multiple dimensions of the data in one go. CUBE is one way to do this.

SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY CUBE (AUTHOR_ID, PUBLISHED_IN)
 
create.select(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN, count())
      .from(BOOK)
      .groupBy(cube(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN))
      .fetch();

The above is a more concise (and possibly more performant) form of writing the following UNION ALL query:

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
SELECT NULL, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY LANGUAGE_ID
UNION ALL
SELECT NULL, NULL, COUNT(*)
FROM BOOK
GROUP BY ()

The CUBE function is just syntax sugar for a more complex GROUPING SETS specification. In general:

-- This
CUBE (A, B, C)

-- Is just short for this
GROUPING SETS ((A, B, C), (A, B), (A, C), (B, C), (A), (B), (C), ())

An example result set might look like this:

+-----------+--------------+----------+
| AUTHOR_ID | PUBLISHED_IN | COUNT(*) |
+-----------+--------------+----------+
|      NULL |         NULL |        4 | <- GROUP BY ()
|      NULL |         1945 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1948 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1988 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1990 |        1 | <- GROUP BY (PUBLISHED_IN)
|         1 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
|         1 |         1945 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
|         1 |         1948 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
|         2 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
|         2 |         1988 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
|         2 |         1990 |        1 | <- GROUP BY (AUTHOR_ID, PUBLISHED_IN)
+-----------+--------------+----------+

Dialect support

This example using jOOQ:

select(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID, count()).from(BOOK).groupBy(cube(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID))

Translates to the following dialect specific expressions:

Aurora Postgres, ClickHouse, DB2, DuckDB, Hana, Oracle, Postgres, SQLServer, Snowflake, Sybase, Teradata, Trino, Vertica

SELECT
  BOOK.AUTHOR_ID,
  BOOK.LANGUAGE_ID,
  count(*)
FROM BOOK
GROUP BY CUBE (BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID)

ASE, Access, Aurora MySQL, BigQuery, CockroachDB, Derby, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, YugabyteDB

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.7.4. GROUP BY GROUPING SETS

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In reports, it may be useful to run multiple aggregations across multiple dimensions of the data in one go. GROUPING SETS is one way to do this.

SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY GROUPING SETS ((AUTHOR_ID), (PUBLISHED_IN))
 
create.select(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN, count())
      .from(BOOK)
      .groupBy(groupingSets(BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN))
      .fetch();

The above is a more concise (and possibly more performant) form of writing the following UNION ALL query:

SELECT AUTHOR_ID, NULL AS PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY AUTHOR_ID
UNION ALL
SELECT NULL, PUBLISHED_IN, COUNT(*)
FROM BOOK
GROUP BY LANGUAGE_ID

An example result set might look like this:

+-----------+--------------+----------+
| AUTHOR_ID | PUBLISHED_IN | COUNT(*) |
+-----------+--------------+----------+
|      NULL |         1945 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1948 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1988 |        1 | <- GROUP BY (PUBLISHED_IN)
|      NULL |         1990 |        1 | <- GROUP BY (PUBLISHED_IN)
|         1 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
|         2 |         NULL |        2 | <- GROUP BY (AUTHOR_ID)
+-----------+--------------+----------+

Note that the most common GROUPING SETS specifications have a dedicated, special syntax:

Dialect support

This example using jOOQ:

select(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID, count()).from(BOOK).groupBy(groupingSets(BOOK.AUTHOR_ID, BOOK.LANGUAGE_ID))

Translates to the following dialect specific expressions:

Aurora Postgres, ClickHouse, DB2, DuckDB, Hana, Oracle, Postgres, SQLServer, Snowflake, Sybase, Teradata, Trino, Vertica

SELECT
  BOOK.AUTHOR_ID,
  BOOK.LANGUAGE_ID,
  count(*)
FROM BOOK
GROUP BY GROUPING SETS (
  (BOOK.AUTHOR_ID),
  (BOOK.LANGUAGE_ID)
)

ASE, Access, Aurora MySQL, BigQuery, CockroachDB, Derby, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, YugabyteDB

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.7.5. GROUP BY empty grouping set

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A special kind of GROUPING SET is the empty grouping set, which can be achieved in standard SQL and many SQL dialects using GROUP BY (). It is implicit, whenever an aggregate function is present in a query, but not an explicit GROUP BY clause.

SELECT COUNT(*)
FROM BOOK
GROUP BY ()
 
create.selectCount()
      .from(BOOK)
      .groupBy()
      .fetch();

Dialect support

This example using jOOQ:

selectCount().from(BOOK).groupBy()

Translates to the following dialect specific expressions:

Access

SELECT count(*)
FROM BOOK, (select count(*) dual from MSysResources) as empty_grouping_dummy_table
GROUP BY empty_grouping_dummy_table.dual

ASE, SQLDataWarehouse

SELECT count(*)
FROM BOOK, (select 1 as dual) as empty_grouping_dummy_table
GROUP BY empty_grouping_dummy_table.dual

Aurora MySQL, MemSQL

SELECT count(*)
FROM BOOK
GROUP BY (SELECT 1
FROM DUAL)

Aurora Postgres, BigQuery, DB2, DuckDB, Exasol, H2, Oracle, Postgres, SQLServer, Sybase, Teradata, Trino

SELECT count(*)
FROM BOOK
GROUP BY ()

ClickHouse, CockroachDB, MariaDB, MySQL, Redshift, SQLite, Vertica, YugabyteDB

SELECT count(*)
FROM BOOK
GROUP BY (SELECT 1)

Derby, HSQLDB

SELECT count(*)
FROM BOOK
GROUP BY 0

Firebird

SELECT count(*)
FROM BOOK
GROUP BY (SELECT 1
FROM RDB$DATABASE)

Hana, Snowflake

SELECT count(*)
FROM BOOK
GROUP BY GROUPING SETS (())

Informix

SELECT count(*)
FROM BOOK, (select 1 as dual from systables where tabid = 1) as empty_grouping_dummy_table
GROUP BY empty_grouping_dummy_table.dual
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.8. HAVING clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The HAVING clause is commonly used to further restrict data resulting from a previously issued GROUP BY clause. An example, selecting only those authors that have written at least two books:

SELECT AUTHOR_ID, COUNT(*)
FROM BOOK
GROUP BY AUTHOR_ID
HAVING COUNT(*) >= 2
 
create.select(BOOK.AUTHOR_ID, count())
      .from(BOOK)
      .groupBy(AUTHOR_ID)
      .having(count().ge(2))
      .fetch();

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:

SELECT COUNT(*)
FROM BOOK
HAVING COUNT(*) >= 4
 
create.select(count(*))
      .from(BOOK)
      .having(count().ge(4))
      .fetch();
 

3.3.4.9. WINDOW clauseWINDOW clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SQL:2003 standard supports a WINDOW clause that allows for specifying WINDOW frames for reuse in SELECT clauses and ORDER BY clauses.




SELECT
  LAG(first_name, 1) OVER w "prev",
  first_name,
  LEAD(first_name, 1) OVER w "next"
FROM author
WINDOW w AS (ORDER first_name)
ORDER BY first_name DESC
 
WindowDefinition w = name("w").as(
  orderBy(PEOPLE.FIRST_NAME));

create.select(
         lag(AUTHOR.FIRST_NAME, 1).over(w).as("prev"),
         AUTHOR.FIRST_NAME,
         lead(AUTHOR.FIRST_NAME, 1).over(w).as("next"))
      .from(AUTHOR)
      .window(w)
      .orderBy(AUTHOR.FIRST_NAME.desc())
      .fetch();

Note that in order to create such a window definition, we need to first create a name reference using DSL.name().

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.

Some more information about window functions and the WINDOW clause can be found on our blog: https://blog.jooq.org/probably-the-coolest-sql-feature-window-functions/

 

3.3.4.10. QUALIFY clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A select few dialects support a very useful QUALIFY clause, which can be used to filter using window functions without having to nest the window function calculation in a derived table.

For example, if you do not have access to the WITH TIES clause, you could easily emulate it like this. The following query finds the top 5 author WITH TIES, counting their books:

SELECT AUTHOR_ID, count(*)
FROM BOOK
GROUP BY AUTHOR_ID
QUALIFY rank() OVER (ORDER BY count(*) DESC) <= 5
ORDER BY count(*) DESC
 
create.select(BOOK.AUTHOR_ID, count())
      .from(BOOK)
      .groupBy(BOOK.AUTHOR_ID)
      .qualify(rank().over(orderBy(count().desc())).le(5))
      .orderBy(count().desc())
      .fetch();
 

3.3.4.11. ORDER BY clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Databases are allowed to return data in any arbitrary order, unless you explicitly declare that order in the ORDER BY clause.

SELECT AUTHOR_ID, TITLE
FROM BOOK
ORDER BY AUTHOR_ID ASC, TITLE DESC
 
create.select(BOOK.AUTHOR_ID, BOOK.TITLE)
      .from(BOOK)
      .orderBy(BOOK.AUTHOR_ID.asc(), BOOK.TITLE.desc())
      .fetch();

Any jOOQ column expression (or field) can be transformed into an org.jooq.SortField by calling the asc() and desc() methods.

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.

 

3.3.4.11.1. Ordering by field index

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

SELECT AUTHOR_ID, TITLE
FROM BOOK
ORDER BY 1 ASC, 2 DESC
 
create.select(BOOK.AUTHOR_ID, BOOK.TITLE)
      .from(BOOK)
      .orderBy(inline(1).asc(), inline(2).desc())
      .fetch();
This practice is generally discouraged as field indexes may shift in the SELECT clause, and developers might forget to update the indexes in ORDER BY. It is mainly useful for quick-and-dirty ad-hoc SQL. See also the don't do this section about this topic.

Dialect support

This example using jOOQ:

select(BOOK.ID).from(BOOK).orderBy(1)

Translates to the following dialect specific expressions:

All dialects

SELECT BOOK.ID
FROM BOOK
ORDER BY 1
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.11.2. Ordering and NULLS

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A few databases support the SQL standard "null ordering" clause in sort specification lists, to define whether NULL values should come first or last in an ordered result.

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME
FROM AUTHOR
ORDER BY LAST_NAME ASC,
         FIRST_NAME ASC NULLS LAST
 
create.select(
         AUTHOR.FIRST_NAME,
         AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .orderBy(AUTHOR.LAST_NAME.asc(),
               AUTHOR.FIRST_NAME.asc().nullsLast())
      .fetch();

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

Dialect support

This example using jOOQ:

select(AUTHOR.FIRST_NAME).from(AUTHOR).orderBy(AUTHOR.FIRST_NAME.asc().nullsLast())

Translates to the following dialect specific expressions:

Access, SQLServer

SELECT AUTHOR.FIRST_NAME
FROM AUTHOR
ORDER BY iif(AUTHOR.FIRST_NAME IS NOT NULL, 0, 1), AUTHOR.FIRST_NAME ASC

ASE, Aurora MySQL, MemSQL, MySQL, SQLDataWarehouse, Sybase

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

Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, Oracle, Postgres, SQLite, Snowflake, Teradata, Trino, YugabyteDB

SELECT AUTHOR.FIRST_NAME
FROM AUTHOR
ORDER BY AUTHOR.FIRST_NAME ASC NULLS LAST

DB2, MariaDB, Redshift, Vertica

SELECT AUTHOR.FIRST_NAME
FROM AUTHOR
ORDER BY nvl2(AUTHOR.FIRST_NAME, 0, 1), AUTHOR.FIRST_NAME ASC
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.11.3. Ordering using CASE expressions

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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
 
create.select()
      .from(BOOK)
      .orderBy(case_(BOOK.TITLE)
               .when("1984", 0)
               .when("Animal Farm", 1)
               .else_(2).asc())
      .fetch();

But writing these things can become quite verbose. jOOQ supports a convenient syntax for specifying sort mappings. The same query can be written in jOOQ as such:

create.select()
      .from(BOOK)
      .orderBy(BOOK.TITLE.sortAsc("1984", "Animal Farm"))
      .fetch();

More complex sort indirections can be provided using a Map:

create.select()
      .from(BOOK)
      .orderBy(BOOK.TITLE.sort(Map.of(
          "1984", 1,
          "Animal Farm", 13,
          "The jOOQ book", 10
      )))
      .fetch();

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

create.select()
      .from(BOOK)
      .orderBy(BOOK.TITLE.sortAsc("1984", "Animal Farm").nullsFirst())
      .fetch();

Dialect support

This example using jOOQ:

select(BOOK.ID).from(BOOK).orderBy(BOOK.TITLE.sortAsc("1984", "Animal Farm"))

Translates to the following dialect specific expressions:

Access

SELECT BOOK.ID
FROM BOOK
ORDER BY SWITCH(BOOK.TITLE = '1984', 0, BOOK.TITLE = 'Animal Farm', 1) ASC

ASE, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB

SELECT BOOK.ID
FROM BOOK
ORDER BY CASE BOOK.TITLE
  WHEN '1984' THEN 0
  WHEN 'Animal Farm' THEN 1
END ASC

Derby

SELECT BOOK.ID
FROM BOOK
ORDER BY CASE
  WHEN BOOK.TITLE = '1984' THEN 0
  WHEN BOOK.TITLE = 'Animal Farm' THEN 1
END ASC
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.11.4. Oracle's ORDER SIBLINGS BY clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ also supports Oracle's SIBLINGS keyword to be used with ORDER BY clauses for hierarchical queries using CONNECT BY

create.select(sysConnectByPath(DIRECTORY.LABEL, "/").as("dir"))
      .from(DIRECTORY)
      .startWith(DIRECTORY.PARENT_ID.isNull())
      .connectBy(prior(DIRECTORY.ID).eq(DIRECTORY.PARENT_ID))
      .orderSiblingsBy(DIRECTORY.LABEL)
      .fetch();
 

3.3.4.12. LIMIT .. OFFSET clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

While being extremely useful for every application that does pagination, or just to limit result sets to reasonable sizes, this clause has not been standardised 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).orderBy(BOOK.ID).limit(1).offset(2).fetch();

This will limit the result to 1 books skipping the first 2 books (offset 2). 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:

-- MySQL, H2, HSQLDB, and SQLite
SELECT * FROM BOOK ORDER BY ID LIMIT 1 OFFSET 2

-- Derby, SQL Server 2012, Oracle 12c, PostgreSQL, the SQL:2008 standard
SELECT * FROM BOOK ORDER BY ID OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

-- Informix has SKIP .. FIRST support
SELECT SKIP 2 FIRST 1 * FROM BOOK ORDER BY ID

-- Ingres (almost the SQL:2008 standard)
SELECT * FROM BOOK ORDER BY ID OFFSET 2 FETCH FIRST 1 ROWS ONLY

-- Firebird
SELECT * FROM BOOK ORDER BY ID ROWS 2 TO 3

-- Sybase SQL Anywhere
SELECT TOP 1 START AT 3 * FROM BOOK ORDER BY ID

-- DB2 (almost the SQL:2008 standard, without OFFSET)
SELECT * FROM BOOK ORDER BY ID FETCH FIRST 1 ROWS ONLY

-- Sybase ASE, SQL Server 2008 (without OFFSET)
SELECT TOP 1 * FROM BOOK ORDER BY ID

Things get a little more tricky in those databases that have no native idiom for OFFSET pagination (actual queries may vary):

-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET)
SELECT * FROM (
  SELECT BOOK.*,
    ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
  FROM BOOK
) AS X
WHERE RN > 2
AND RN <= 3

-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET)
SELECT * FROM (
  SELECT DISTINCT BOOK.ID, BOOK.TITLE,
    DENSE_RANK() OVER (ORDER BY ID ASC, TITLE ASC) AS RN
  FROM BOOK
) AS X
WHERE RN > 2
AND RN <= 3

-- Oracle 11g and less
SELECT *
FROM (
  SELECT b.*, ROWNUM RN
  FROM (
    SELECT *
    FROM BOOK
    ORDER BY ID ASC
  ) b
  WHERE ROWNUM <= 3
)
WHERE RN > 2

As you can see, jOOQ will take care of the incredibly painful ROW_NUMBER() OVER() (or ROWNUM for Oracle) filtering in subselects for you, you'll just have to write limit(1).offset(2) in any dialect.

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.

Keyset pagination

Note, the LIMIT clause can also be used with the SEEK clause for keyset pagination.

 

3.3.4.13. WITH TIES clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The previous chapter talked about the LIMIT clause, which limits the result set to a certain number of rows. The SQL standard specifies the following syntax:

OFFSET m { ROW | ROWS }
FETCH { FIRST | NEXT } n { ROW | ROWS } { ONLY | WITH TIES }

By default, most users will use the semantics of the ONLY keyword, meaning a LIMIT 5 expression (or FETCH NEXT 5 ROWS ONLY expression) will result in at most 5 rows. The alternative clause WITH TIES will return at most 5 rows, except if the 5th row and the 6th row (and so on) are "tied" according to the ORDER BY clause, meaning that the ORDER BY clause does not deterministically produce a 5th or 6th row. For example, let's look at our book table:

SELECT *
FROM book
ORDER BY author_id
FETCH NEXT 1 ROWS WITH TIES
DSL.using(configuration)
   .selectFrom(BOOK)
   .orderBy(BOOK.AUTHOR_ID)
   .limit(1).withTies()
   .fetch();

Resulting in:

+----+----------+-------------+
| id | actor_id | title       |
+----+----------+-------------+
|  1 |        1 | 1984        |
|  2 |        1 | Animal Farm |
+----+----------+-------------+

We're now getting two rows because both rows "tied" when ordering them by ACTOR_ID. The database cannot really pick the next 1 row, so they're both returned. If we omit the WITH TIES clause, then only a random one of the rows would be returned.

Dialect support

This example using jOOQ:

select(BOOK.ID).from(BOOK).orderBy(BOOK.AUTHOR_ID).limit(1).withTies()

Translates to the following dialect specific expressions:

Aurora Postgres, CockroachDB, DB2, Firebird, Hana, MySQL, Redshift, Sybase, Vertica, YugabyteDB

SELECT v0 ID
FROM (
  SELECT
    BOOK.ID v0,
    rank() OVER (ORDER BY BOOK.AUTHOR_ID) rn
  FROM BOOK
) x
WHERE rn BETWEEN (0 + 1) AND (0 + 1)
ORDER BY rn

BigQuery, DuckDB, Exasol, Snowflake

SELECT BOOK.ID
FROM BOOK
QUALIFY rank() OVER (ORDER BY BOOK.AUTHOR_ID) BETWEEN (0 + 1) AND (0 + 1)

ClickHouse, H2, MariaDB, Oracle, Postgres, Trino

SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.AUTHOR_ID
FETCH NEXT 1 ROWS WITH TIES

Informix

SELECT v0 ID
FROM (
  SELECT *
  FROM (
    SELECT
      BOOK.ID v0,
      rank() OVER (ORDER BY BOOK.AUTHOR_ID) rn
    FROM BOOK
  ) x
) x
WHERE rn BETWEEN (0 + 1) AND (0 + 1)
ORDER BY rn

SQLDataWarehouse, SQLServer, Teradata

SELECT TOP 1 WITH TIES BOOK.ID
FROM BOOK
ORDER BY BOOK.AUTHOR_ID

ASE, Access, Aurora MySQL, Derby, HSQLDB, MemSQL, SQLite

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
 

3.3.4.14. SEEK clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

One of the previous chapters 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 (learn about other synthetic sql syntaxes). Imagine we have these data:

+------+-------+---------------+
|   ID | VALUE | PAGE_BOUNDARY |
+------+-------+---------------+
|  ... |   ... |           ... |
|  474 |     2 |             0 |
|  533 |     2 |             1 | <-- Before page 6
|  640 |     2 |             0 |
|  776 |     2 |             0 |
|  815 |     2 |             0 |
|  947 |     2 |             0 |
|   37 |     3 |             1 | <-- Last on page 6
|  287 |     3 |             0 |
|  450 |     3 |             0 |
|  ... |   ... |           ... |
+------+-------+---------------+

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:


SELECT id, value
FROM t
WHERE (value, id) > (2, 533)
ORDER BY value, id
LIMIT 5
 
DSL.using(configuration)
   .select(T.ID, T.VALUE)
   .from(T)
   .orderBy(T.VALUE, T.ID)
   .seek(lastValue, lastId) // from last page: value = 2, id = 533
   .limit(5)
   .fetch();

As you can see, the jOOQ SEEK clause is a synthetic clause that does not really exist in SQL. However, the jOOQ syntax is far more intuitive for a variety of reasons:

  • It replaces OFFSET where you would expect
  • It doesn't force you to mix regular predicates with "seek" predicates
  • It is typesafe
  • It emulates row value expression predicates for you, in those databases that do not support them

This query now yields:

+-----+-------+
|  ID | VALUE |
+-----+-------+
| 640 |     2 |
| 776 |     2 |
| 815 |     2 |
| 947 |     2 |
|  37 |     3 |
+-----+-------+

Note that you cannot combine the SEEK clause with the OFFSET clause.

More information about this great feature can be found in the jOOQ blog:

Further information about offset pagination vs. keyset pagination performance can be found on our partner page:

 

3.3.4.15. FOR clause

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

While both XML and JSON usage in SQL has been standardised in more recent versions of the SQL standard, SQL Server has always had some very convenient utilities at the end of a SELECT statement, which allow for converting SQL tables into the most common XML or JSON representations.

Starting with jOOQ 3.14, these syntaxes are supported in jOOQ as well, and if possible, emulated in other dialects which have native XML or JSON support.

FOR XML

Consider the following query

SELECT id, title
FROM book
ORDER BY id
FOR XML PATH ('book'), ROOT ('books')
 
create.select(BOOK.ID, BOOK.TITLE)
      .from(BOOK)
      .orderBy(BOOK.ID)
      .forXML().path("book").root("books")
      .fetch();

This query produces a document like this:

<books>
  <book><id>1</id><title>1984</title></book>
  <book><id>2</id><title>Animal Farm</title></book>
  <book><id>3</id><title>O Alquimista</title></book>
  <book><id>4</id><title>Brida</title></book>
</books>

FOR JSON

JSON is just XML with less syntax and less features. So the FOR JSON syntax in SQL Server is almost the same as the above FOR XML syntax:

SELECT id, title
FROM book
ORDER BY id
FOR JSON PATH
 
create.select(BOOK.ID, BOOK.TITLE)
      .from(BOOK)
      .orderBy(BOOK.ID)
      .forJSON().path()
      .fetch();

This query produces a document like this:

[
  {"id": 1, "title": "1984"},
  {"id": 2, "title": "Animal Farm"},
  {"id": 3, "title": "O Alquimista"},
  {"id": 4, "title": "Brida"}
]
 

3.3.4.16. FOR UPDATE clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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:

SELECT *
FROM BOOK
WHERE ID = 3
FOR UPDATE
 
create.select()
      .from(BOOK)
      .where(BOOK.ID.eq(3))
      .forUpdate()
      .fetch();

The above example will produce a record-lock, locking the whole record for updates. Some databases also support cell-locks using FOR UPDATE OF ..

SELECT *
FROM BOOK
WHERE ID = 3
FOR UPDATE OF TITLE
 
create.select()
      .from(BOOK)
      .where(BOOK.ID.eq(3))
      .forUpdate().of(BOOK.TITLE)
      .fetch();

Oracle goes a bit further and also allows to specify the actual locking behaviour. It features these additional clauses, which are all supported by jOOQ:

  • FOR UPDATE NOWAIT: This is the default behaviour. If the lock cannot be acquired, the query fails immediately
  • FOR UPDATE WAIT n: Try to wait for [n] seconds for the lock acquisition. The query will fail only afterwards
  • FOR UPDATE SKIP LOCKED: This peculiar syntax will skip all locked records. This is particularly useful when implementing queue tables with multiple consumers

With jOOQ, you can use those Oracle extensions as such:

create.select().from(BOOK).where(BOOK.ID.eq(3)).forUpdate().nowait().fetch();
create.select().from(BOOK).where(BOOK.ID.eq(3)).forUpdate().wait(5).fetch();
create.select().from(BOOK).where(BOOK.ID.eq(3)).forUpdate().skipLocked().fetch();

FOR UPDATE in 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 SQL Server database, 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:

try (
    PreparedStatement stmt = connection.prepareStatement(
        "SELECT * FROM author WHERE id IN (3, 4, 5)",
        ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery()
) {
    while (rs.next()) {
        // UPDATE the primary key for row-locks, or any other columns for cell-locks
        rs.updateObject(1, rs.getObject(1));
        rs.updateRow();

        // Do more stuff with this record
    }
}

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:

-- thread 1
SELECT * FROM author ORDER BY id ASC;

-- thread 2
SELECT * FROM author ORDER BY id DESC;

So use this technique with care, possibly only ever locking single rows!

Pessimistic (shared) locking with the FOR SHARE clause

Some databases (MySQL, Postgres) also allow to issue a non-exclusive lock explicitly using a FOR SHARE clause. This is also supported by jOOQ

Optimistic locking in jOOQ

Note, that jOOQ also supports optimistic locking, if you're doing simple CRUD. This is documented in the section's manual about optimistic locking.

 

3.3.4.17. Set operations

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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.

All of these set operations come with 2 flavours:

  • DISTINCT (the default): Removing duplicates after applying the set operation
  • ALL: Retaining duplicates after applying the set operation
 

3.3.4.17.1. Type safety

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Two subselects of degree less than 22 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:

// Some sample SELECT statements
Select<Record2<Integer, String>>  s1 = select(BOOK.ID, BOOK.TITLE).from(BOOK);
Select<Record1<Integer>>          s2 = selectOne();
Select<Record2<Integer, Integer>> s3 = select(one(), zero());
Select<Record2<Integer, String>>  s4 = select(one(), inline("abc"));

// Let's try to combine them:
s1.union(s2); // Doesn't compile because of a degree mismatch. Expected: Record2<...>, got: Record1<...>
s1.union(s3); // Doesn't compile because of a type mismatch. Expected: <Integer, String>, got: <Integer, Integer>
s1.union(s4); // OK. The two Record[N] types match
 

3.3.4.17.2. Projection rowtype

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Much like most dialects use only the first set operation subquery's column names and types for the resulting row type, so does jOOQ.

Since jOOQ does not know which row is produced by which union subquery, it cannot disambiguate these rows in case the projection row type isn't exactly identical. As such, the ad-hoc converter in the following example is ignored:

Result<Record1<Integer>> result =
create.select(BOOK.ID)
   .from(BOOK)
   .union(

    // This has no effect
    select(AUTHOR.ID.convertFrom(i -> -i))
   .from(AUTHOR))
   .fetch();

While this can lead to subtle bugs, it makes perfect sense, knowing that a Converter is always applied at the client side of the execution.

 

3.3.4.17.3. Differences to standard SQL

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

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 set operation subselect may not immediately contain any ORDER BY clause or LIMIT clause (unless you wrap the subselect into a derived table), 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
 
create.selectFrom(AUTHOR)
      .orderBy(AUTHOR.DATE_OF_BIRTH.asc()).limit(1)
      .union(
       selectFrom(AUTHOR)
      .orderBy(AUTHOR.DATE_OF_BIRTH.desc()).limit(1))
      .orderBy(1)
      .fetch();

In case your database doesn't support ordered UNION subselects, the subselects are nested in derived tables.

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

Dialect support

This example using jOOQ:

select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).limit(1).union(select(AUTHOR.ID).from(AUTHOR).orderBy(AUTHOR.ID).limit(1)).orderBy(1)

Translates to the following dialect specific expressions:

Access, SQLDataWarehouse, Sybase

(
  SELECT TOP 1 BOOK.ID
  FROM BOOK
  ORDER BY BOOK.ID
)
UNION (
  SELECT TOP 1 AUTHOR.ID
  FROM AUTHOR
  ORDER BY AUTHOR.ID
)
ORDER BY 1

ASE, SQLServer

SELECT *
FROM (
  SELECT TOP 1 BOOK.ID
  FROM BOOK
  ORDER BY BOOK.ID
) x
UNION 
SELECT *
FROM (
  SELECT TOP 1 AUTHOR.ID
  FROM AUTHOR
  ORDER BY AUTHOR.ID
) x
ORDER BY 1

Aurora MySQL, Aurora Postgres, CockroachDB, DuckDB, Exasol, HSQLDB, Hana, MySQL, Redshift, Snowflake, Vertica, YugabyteDB

(
  SELECT BOOK.ID
  FROM BOOK
  ORDER BY BOOK.ID
  LIMIT 1
)
UNION (
  SELECT AUTHOR.ID
  FROM