New versions: Dev (3.14) | Latest (3.13) | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6

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.

Table of contents

4.1.
The query DSL type
4.1.1.
DSL subclasses
4.2.
The DSLContext API
4.2.1.
SQL Dialect
4.2.2.
SQL Dialect Family
4.2.3.
Connection vs. DataSource
4.2.4.
Custom data
4.2.5.
Custom ExecuteListeners
4.2.6.
Custom Unwrappers
4.2.7.
Custom Settings
4.2.7.1.
Object qualification
4.2.7.2.
Runtime catalog, schema and table mapping
4.2.7.3.
Identifier style
4.2.7.4.
Keyword style
4.2.7.5.
Locales
4.2.7.6.
Parameter types
4.2.7.7.
Parameter name prefix
4.2.7.8.
Statement Type
4.2.7.9.
Inline Threshold
4.2.7.10.
Execute Logging
4.2.7.11.
Listener Invocation Order
4.2.7.12.
Optimistic Locking
4.2.7.13.
Auto-attach Records
4.2.7.14.
Updatable Primary Keys
4.2.7.15.
Reflection caching
4.2.7.16.
Fetch Warnings
4.2.7.17.
Return Identity Value On Store
4.2.7.18.
Return All Columns On Store
4.2.7.19.
Map JPA Annotations
4.2.7.20.
JDBC Flags
4.2.7.21.
IN-list Padding
4.2.7.22.
Backslash Escaping
4.2.7.23.
Scalar subqueries for stored functions
4.2.7.24.
Parser Configuration
4.2.7.25.
Interpreter Configuration
4.3.
SQL Statements (DML)
4.3.1.
jOOQ's DSL and model API
4.3.2.
The WITH clause
4.3.3.
The SELECT statement
4.3.3.1.
SELECT clause
4.3.3.2.
FROM clause
4.3.3.3.
JOIN operator
4.3.3.4.
Implicit JOIN
4.3.3.5.
WHERE clause
4.3.3.6.
CONNECT BY clause
4.3.3.7.
GROUP BY clause
4.3.3.8.
HAVING clause
4.3.3.9.
WINDOW clause
4.3.3.10.
ORDER BY clause
4.3.3.11.
LIMIT .. OFFSET clause
4.3.3.12.
WITH TIES clause
4.3.3.13.
SEEK clause
4.3.3.14.
FOR clause
4.3.3.15.
FOR UPDATE clause
4.3.3.16.
UNION, INTERSECTION and EXCEPT
4.3.3.17.
Oracle-style hints
4.3.3.18.
Lexical and logical SELECT clause order
4.3.4.
The INSERT statement
4.3.4.1.
INSERT .. VALUES
4.3.4.2.
INSERT .. DEFAULT VALUES
4.3.4.3.
INSERT .. SET
4.3.4.4.
INSERT .. SELECT
4.3.4.5.
INSERT .. ON DUPLICATE KEY
4.3.4.6.
INSERT .. RETURNING
4.3.5.
The UPDATE statement
4.3.6.
The DELETE statement
4.3.7.
The MERGE statement
4.4.
SQL Statements (DDL)
4.4.1.
The ALTER statement
4.4.1.1.
ALTER DOMAIN
4.4.1.2.
ALTER INDEX
4.4.1.3.
ALTER SCHEMA
4.4.1.4.
ALTER SEQUENCE
4.4.1.5.
ALTER TABLE
4.4.1.6.
ALTER TYPE
4.4.1.7.
ALTER VIEW
4.4.2.
The COMMENT statement
4.4.3.
The CREATE statement
4.4.3.1.
CREATE DOMAIN
4.4.3.2.
CREATE INDEX
4.4.3.3.
CREATE SCHEMA
4.4.3.4.
CREATE SEQUENCE
4.4.3.5.
CREATE TABLE
4.4.3.6.
CREATE TYPE
4.4.3.7.
CREATE VIEW
4.4.4.
The DROP statement
4.4.4.1.
DROP DOMAIN
4.4.4.2.
DROP INDEX
4.4.4.3.
DROP SCHEMA
4.4.4.4.
DROP SEQUENCE
4.4.4.5.
DROP TABLE
4.4.4.6.
DROP TYPE
4.4.4.7.
DROP VIEW
4.4.5.
The GRANT statement
4.4.6.
The REVOKE statement
4.4.7.
The SET statement
4.4.8.
The TRUNCATE statement
4.4.9.
Generating DDL from objects
4.5.
Procedural statements
4.5.1.
Block statement
4.5.2.
Variables
4.5.3.
IF statement
4.5.4.
LOOP statement
4.5.5.
WHILE statement
4.5.6.
REPEAT statement
4.5.7.
FOR statement
4.5.8.
Labels
4.5.9.
GOTO statement
4.5.10.
EXIT statement
4.5.11.
CONTINUE statement
4.5.12.
EXECUTE statement
4.6.
Catalog and schema expressions
4.7.
Table expressions
4.7.1.
Generated Tables
4.7.2.
Aliased Tables
4.7.3.
Joined tables
4.7.4.
The VALUES() table constructor
4.7.5.
Nested SELECTs
4.7.6.
The Oracle 11g PIVOT clause
4.7.7.
jOOQ's relational division syntax
4.7.8.
Array and cursor unnesting
4.7.9.
Table-valued functions
4.7.10.
JSON_TABLE
4.7.11.
XMLTABLE
4.7.12.
The DUAL table
4.7.13.
Temporal tables
4.8.
Column expressions
4.8.1.
Table columns
4.8.2.
Aliased columns
4.8.3.
Cast expressions
4.8.4.
Datatype coercions
4.8.5.
Collations
4.8.6.
Arithmetic expressions
4.8.7.
String concatenation
4.8.8.
Case sensitivity with strings
4.8.9.
General functions
4.8.9.1.
COALESCE
4.8.9.2.
DECODE
4.8.9.3.
IIF
4.8.9.4.
NULLIF
4.8.9.5.
NVL
4.8.9.6.
NVL2
4.8.10.
Numeric functions
4.8.10.1.
ABS
4.8.10.2.
ACOS
4.8.10.3.
ACOS
4.8.10.4.
ATAN
4.8.10.5.
ATAN2
4.8.10.6.
CEIL
4.8.10.7.
COS
4.8.10.8.
COSH
4.8.10.9.
COT
4.8.10.10.
COTH
4.8.10.11.
DEG
4.8.10.12.
EXP
4.8.10.13.
FLOOR
4.8.10.14.
GREATEST
4.8.10.15.
LEAST
4.8.10.16.
LN
4.8.10.17.
LN
4.8.10.18.
NEG
4.8.10.19.
POWER
4.8.10.20.
RAD
4.8.10.21.
RAND
4.8.10.22.
ROUND
4.8.10.23.
SIGN
4.8.10.24.
SIN
4.8.10.25.
SINH
4.8.10.26.
SQRT
4.8.10.27.
TAN
4.8.10.28.
TANH
4.8.10.29.
TRUNC
4.8.10.30.
WIDTH_BUCKET
4.8.11.
Bitwise functions
4.8.11.1.
BIT_COUNT
4.8.11.2.
BIT_AND
4.8.11.3.
BIT_NAND
4.8.11.4.
BIT_NOR
4.8.11.5.
BIT_NOT
4.8.11.6.
BIT_OR
4.8.11.7.
SHL
4.8.11.8.
SHR
4.8.11.9.
BIT_XNOR
4.8.11.10.
BIT_XOR
4.8.12.
String functions
4.8.12.1.
ASCII
4.8.12.2.
CONCAT
4.8.12.3.
LEFT
4.8.12.4.
LENGTH
4.8.12.5.
LOWER
4.8.12.6.
LPAD
4.8.12.7.
LTRIM
4.8.12.8.
MD5
4.8.12.9.
OVERLAY
4.8.12.10.
POSITION
4.8.12.11.
REPEAT
4.8.12.12.
REPLACE
4.8.12.13.
REVERSE
4.8.12.14.
RIGHT
4.8.12.15.
RPAD
4.8.12.16.
RTRIM
4.8.12.17.
SPACE
4.8.12.18.
SUBSTRING
4.8.12.19.
TRANSLATE
4.8.12.20.
TRIM
4.8.12.21.
UPPER
4.8.13.
Datetime functions
4.8.13.1.
CENTURY
4.8.13.2.
CURRENT_DATE
4.8.13.3.
CURRENT_LOCALDATE
4.8.13.4.
CURRENT_LOCALDATETIME
4.8.13.5.
CURRENT_LOCALTIME
4.8.13.6.
CURRENT_OFFSETDATETIME
4.8.13.7.
CURRENT_OFFSETTIME
4.8.13.8.
CURRENT_TIME
4.8.13.9.
CURRENT_TIMESTAMP
4.8.13.10.
DATE
4.8.13.11.
DATEADD
4.8.13.12.
DATEDIFF
4.8.13.13.
DATESUB
4.8.13.14.
DAY
4.8.13.15.
DAY_OF_YEAR
4.8.13.16.
DECADE
4.8.13.17.
EPOCH
4.8.13.18.
EXTRACT
4.8.13.19.
HOUR
4.8.13.20.
ISO_DAY_OF_WEEK
4.8.13.21.
LOCALDATE
4.8.13.22.
LOCALDATEADD
4.8.13.23.
LOCALDATESUB
4.8.13.24.
LOCALDATETIME
4.8.13.25.
LOCALDATETIMEADD
4.8.13.26.
LOCALDATETIMESUB
4.8.13.27.
LOCALTIME
4.8.13.28.
MILLENNIUM
4.8.13.29.
MINUTE
4.8.13.30.
MONTH
4.8.13.31.
QUARTER
4.8.13.32.
SECOND
4.8.13.33.
TIME
4.8.13.34.
TIMESTAMP
4.8.13.35.
TIMESTAMPADD
4.8.13.36.
TIMESTAMPSUB
4.8.13.37.
TO_DATE
4.8.13.38.
TO_LOCALDATE
4.8.13.39.
TO_LOCALDATETIME
4.8.13.40.
TO_TIMESTAMP
4.8.13.41.
TRUNC
4.8.13.42.
YEAR
4.8.14.
JSON functions
4.8.14.1.
JSON_ARRAY
4.8.14.2.
JSON_OBJECT
4.8.14.3.
JSON_VALUE
4.8.15.
XML functions
4.8.15.1.
XMLATTRIBUTES
4.8.15.2.
XMLCOMMENT
4.8.15.3.
XMLCONCAT
4.8.15.4.
XMLDOCUMENT
4.8.15.5.
XMLELEMENT
4.8.15.6.
XMLFOREST
4.8.15.7.
XMLPARSE
4.8.15.8.
XMLPI
4.8.15.9.
XMLQUERY
4.8.16.
System functions
4.8.16.1.
CURRENT_SCHEMA
4.8.16.2.
CURRENT_USER
4.8.17.
Aggregate functions
4.8.17.1.
Grouping
4.8.17.2.
Distinctness
4.8.17.3.
Filtering
4.8.17.4.
Ordering
4.8.17.5.
Ordering WITHIN GROUP
4.8.17.6.
Keeping
4.8.17.7.
ARRAY_AGG
4.8.17.8.
AVG
4.8.17.9.
BOOL_AND
4.8.17.10.
BOOL_OR
4.8.17.11.
COLLECT
4.8.17.12.
COUNT
4.8.17.13.
CUME_DIST
4.8.17.14.
DENSE_RANK
4.8.17.15.
EVERY
4.8.17.16.
GROUP_CONCAT
4.8.17.17.
JSON_ARRAYAGG
4.8.17.18.
JSON_OBJECTAGG
4.8.17.19.
LISTAGG
4.8.17.20.
MAX
4.8.17.21.
MEDIAN
4.8.17.22.
MIN
4.8.17.23.
MODE
4.8.17.24.
PERCENT_RANK
4.8.17.25.
PERCENTILE_CONT
4.8.17.26.
PERCENTILE_DISC
4.8.17.27.
PRODUCT
4.8.17.28.
RANK
4.8.17.29.
SUM
4.8.17.30.
XMLAGG
4.8.18.
Window functions
4.8.19.
Grouping functions
4.8.20.
User-defined functions
4.8.21.
User-defined aggregate functions
4.8.22.
The CASE expression
4.8.23.
Sequences and serials
4.8.24.
Tuples or row value expressions
4.9.
Conditional expressions
4.9.1.
Condition building
4.9.2.
AND, OR, NOT boolean operators
4.9.3.
Comparison predicate
4.9.4.
Boolean operator precedence
4.9.5.
Comparison predicate (degree > 1)
4.9.6.
Quantified comparison predicate
4.9.7.
BETWEEN predicate
4.9.8.
BETWEEN predicate (degree > 1)
4.9.9.
DISTINCT predicate
4.9.10.
DOCUMENT predicate
4.9.11.
EXISTS predicate
4.9.12.
IN predicate
4.9.13.
IN predicate (degree > 1)
4.9.14.
JSON predicate
4.9.15.
JSON_EXISTS predicate
4.9.16.
LIKE predicate
4.9.17.
NULL predicate
4.9.18.
NULL predicate (degree > 1)
4.9.19.
OVERLAPS predicate
4.9.20.
SIMILAR TO predicate
4.9.21.
UNIQUE predicate
4.9.22.
XMLEXISTS predicate
4.9.23.
Query By Example (QBE)
4.10.
Synthetic SQL clauses
4.11.
Dynamic SQL
4.12.
Plain SQL
4.13.
Plain SQL Templating Language
4.14.
SQL Parser
4.14.1.
SQL Parser API
4.14.2.
SQL Parser CLI
4.14.3.
SQL Parser Grammar
4.15.
SQL interpreter
4.16.
Schema diff
4.17.
Schema diff CLI
4.18.
Names and identifiers
4.19.
Bind values and parameters
4.19.1.
Indexed parameters
4.19.2.
Named parameters
4.19.3.
Inlined parameters
4.19.4.
SQL injection
4.20.
QueryParts
4.20.1.
SQL rendering
4.20.2.
Pretty printing SQL
4.20.3.
Variable binding
4.20.4.
Custom data type bindings
4.20.5.
Custom syntax elements
4.20.6.
Plain SQL QueryParts
4.20.7.
Serializability
4.20.8.
SQL transformation
4.20.8.1.
ANSI JOIN to table lists
4.20.9.
Custom SQL transformation
4.20.9.1.
Logging abbreviated bind values
4.21.
Zero-based vs one-based APIs
4.22.
SQL building in Scala

The jOOQ User Manual. Multiple Pages : SQL buildingprevious : next
The jOOQ Logo