New versions: Dev (3.15) | Latest (3.14) | 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 Settings
4.2.6.1.
Object qualification
4.2.6.2.
Runtime schema and table mapping
4.2.6.3.
Identifier style
4.2.6.4.
Keyword style
4.2.6.5.
Parameter types
4.2.6.6.
Statement Type
4.2.6.7.
Execute Logging
4.2.6.8.
Optimistic Locking
4.2.6.9.
Auto-attach Records
4.2.6.10.
Updatable Primary Keys
4.2.6.11.
Reflection caching
4.2.6.12.
Fetch Warnings
4.2.6.13.
Backslash Escaping
4.2.6.14.
Scalar subqueries for stored functions
4.2.7.
Thread safety
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.
WHERE clause
4.3.3.5.
CONNECT BY clause
4.3.3.6.
GROUP BY clause
4.3.3.7.
HAVING clause
4.3.3.8.
WINDOW clause
4.3.3.9.
ORDER BY clause
4.3.3.10.
LIMIT .. OFFSET clause
4.3.3.11.
SEEK clause
4.3.3.12.
FOR UPDATE clause
4.3.3.13.
UNION, INTERSECTION and EXCEPT
4.3.3.14.
Oracle-style hints
4.3.3.15.
Lexical and logical SELECT clause order
4.3.4.
The INSERT statement
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 SEQUENCE
4.4.1.2.
ALTER TABLE
4.4.2.
The DROP statement
4.4.2.1.
DROP TABLE
4.4.3.
The TRUNCATE statement
4.5.
Table expressions
4.5.1.
Generated Tables
4.5.2.
Aliased Tables
4.5.3.
Joined tables
4.5.4.
The VALUES() table constructor
4.5.5.
Nested SELECTs
4.5.6.
The Oracle 11g PIVOT clause
4.5.7.
jOOQ's relational division syntax
4.5.8.
Array and cursor unnesting
4.5.9.
The DUAL table
4.6.
Column expressions
4.6.1.
Table columns
4.6.2.
Aliased columns
4.6.3.
Cast expressions
4.6.4.
Datatype coercions
4.6.5.
Arithmetic expressions
4.6.6.
String concatenation
4.6.7.
Case sensitivity with strings
4.6.8.
General functions
4.6.8.1.
COALESCE
4.6.8.2.
DECODE
4.6.8.3.
NULLIF
4.6.8.4.
NVL
4.6.8.5.
NVL2
4.6.9.
Numeric functions
4.6.9.1.
ABS
4.6.9.2.
ACOS
4.6.9.3.
ACOS
4.6.9.4.
ATAN
4.6.9.5.
ATAN2
4.6.9.6.
CEIL
4.6.9.7.
COS
4.6.9.8.
COSH
4.6.9.9.
COT
4.6.9.10.
COTH
4.6.9.11.
DEG
4.6.9.12.
EXP
4.6.9.13.
FLOOR
4.6.9.14.
GREATEST
4.6.9.15.
LEAST
4.6.9.16.
LN
4.6.9.17.
LN
4.6.9.18.
NEG
4.6.9.19.
POWER
4.6.9.20.
RAD
4.6.9.21.
RAND
4.6.9.22.
ROUND
4.6.9.23.
SIGN
4.6.9.24.
SIN
4.6.9.25.
SINH
4.6.9.26.
SQRT
4.6.9.27.
TAN
4.6.9.28.
TANH
4.6.9.29.
TRUNC
4.6.10.
Bitwise functions
4.6.10.1.
BIT_COUNT
4.6.10.2.
BIT_AND
4.6.10.3.
BIT_NAND
4.6.10.4.
BIT_NOR
4.6.10.5.
BIT_NOT
4.6.10.6.
BIT_OR
4.6.10.7.
SHL
4.6.10.8.
SHR
4.6.10.9.
BIT_XNOR
4.6.10.10.
BIT_XOR
4.6.11.
String functions
4.6.11.1.
ASCII
4.6.11.2.
CONCAT
4.6.11.3.
LEFT
4.6.11.4.
LENGTH
4.6.11.5.
LOWER
4.6.11.6.
LPAD
4.6.11.7.
LTRIM
4.6.11.8.
MD5
4.6.11.9.
POSITION
4.6.11.10.
REPEAT
4.6.11.11.
REPLACE
4.6.11.12.
REVERSE
4.6.11.13.
RIGHT
4.6.11.14.
RPAD
4.6.11.15.
RTRIM
4.6.11.16.
SPACE
4.6.11.17.
SUBSTRING
4.6.11.18.
TRANSLATE
4.6.11.19.
TRIM
4.6.11.20.
UPPER
4.6.12.
Datetime functions
4.6.12.1.
CURRENT_DATE
4.6.12.2.
CURRENT_TIME
4.6.12.3.
CURRENT_TIMESTAMP
4.6.12.4.
DATE
4.6.12.5.
DATEADD
4.6.12.6.
DATEDIFF
4.6.12.7.
DAY
4.6.12.8.
EXTRACT
4.6.12.9.
HOUR
4.6.12.10.
MINUTE
4.6.12.11.
MONTH
4.6.12.12.
SECOND
4.6.12.13.
TIME
4.6.12.14.
TIMESTAMP
4.6.12.15.
TIMESTAMPADD
4.6.12.16.
TRUNC
4.6.12.17.
YEAR
4.6.13.
System functions
4.6.13.1.
CURRENT_USER
4.6.14.
Aggregate functions
4.6.15.
Window functions
4.6.16.
Grouping functions
4.6.17.
User-defined functions
4.6.18.
User-defined aggregate functions
4.6.19.
The CASE expression
4.6.20.
Sequences and serials
4.6.21.
Tuples or row value expressions
4.7.
Conditional expressions
4.7.1.
Condition building
4.7.2.
BOOLEAN columns
4.7.3.
AND, OR, NOT boolean operators
4.7.4.
Comparison predicate
4.7.5.
Boolean operator precedence
4.7.6.
Comparison predicate (degree > 1)
4.7.7.
Quantified comparison predicate
4.7.8.
NULL predicate
4.7.9.
NULL predicate (degree > 1)
4.7.10.
DISTINCT predicate
4.7.11.
BETWEEN predicate
4.7.12.
BETWEEN predicate (degree > 1)
4.7.13.
LIKE predicate
4.7.14.
IN predicate
4.7.15.
IN predicate (degree > 1)
4.7.16.
EXISTS predicate
4.7.17.
OVERLAPS predicate
4.8.
Synthetic SQL clauses
4.9.
Dynamic SQL
4.10.
Plain SQL
4.11.
Plain SQL Templating Language
4.12.
Names and identifiers
4.13.
Bind values and parameters
4.13.1.
Indexed parameters
4.13.2.
Named parameters
4.13.3.
Inlined parameters
4.13.4.
SQL injection and plain SQL QueryParts
4.14.
QueryParts
4.14.1.
SQL rendering
4.14.2.
Pretty printing SQL
4.14.3.
Variable binding
4.14.4.
Extend jOOQ with custom types
4.14.5.
Plain SQL QueryParts
4.14.6.
Serializability
4.14.7.
Custom SQL transformation
4.14.7.1.
Logging abbreviated bind values
4.15.
Zero-based vs one-based APIs
4.16.
SQL building in Scala

The jOOQ User Manual : SQL buildingprevious : next
The jOOQ Logo