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.

Table of contents

4.1.
The Factory class
4.1.1.
SQL Dialect
4.1.2.
Connection vs. DataSource
4.1.3.
Custom Settings
4.1.4.
Runtime schema and table mapping
4.1.5.
Factory subclasses
4.2.
SQL Statements
4.2.1.
jOOQ's DSL and non-DSL API
4.2.2.
The SELECT statement
4.2.2.1.
SELECT clause
4.2.2.2.
FROM clause
4.2.2.3.
JOIN operator
4.2.2.4.
WHERE clause
4.2.2.5.
CONNECT BY clause
4.2.2.6.
GROUP BY clause
4.2.2.7.
HAVING clause
4.2.2.8.
ORDER BY clause
4.2.2.9.
LIMIT .. OFFSET clause
4.2.2.10.
FOR UPDATE clause
4.2.2.11.
UNION, INTERSECTION and EXCEPT
4.2.2.12.
Oracle-style hints
4.2.3.
The INSERT statement
4.2.4.
The UPDATE statement
4.2.5.
The DELETE statement
4.2.6.
The MERGE statement
4.2.7.
The TRUNCATE statement
4.3.
Table expressions
4.3.1.
Generated Tables
4.3.2.
Aliased Tables
4.3.3.
Joined tables
4.3.4.
Nested SELECTs
4.3.5.
The Oracle 11g PIVOT clause
4.3.6.
jOOQ's relational division syntax
4.3.7.
Array and cursor unnesting
4.3.8.
The DUAL table
4.4.
Column expressions
4.4.1.
Table columns
4.4.2.
Aliased columns
4.4.3.
Cast expressions
4.4.4.
Arithmetic expressions
4.4.5.
String concatenation
4.4.6.
General functions
4.4.7.
Numeric functions
4.4.8.
Bitwise functions
4.4.9.
String functions
4.4.10.
Datetime functions
4.4.10.1.
CURRENT_DATE
4.4.10.2.
CURRENT_TIME
4.4.10.3.
CURRENT_TIMESTAMP
4.4.10.4.
DATEADD
4.4.10.5.
DATEDIFF
4.4.10.6.
DAY
4.4.10.7.
EXTRACT
4.4.10.8.
HOUR
4.4.10.9.
MINUTE
4.4.10.10.
MONTH
4.4.10.11.
SECOND
4.4.10.12.
TIMESTAMPADD
4.4.10.13.
TRUNC
4.4.10.14.
YEAR
4.4.11.
System functions
4.4.12.
Aggregate functions
4.4.13.
Window functions
4.4.14.
Grouping functions
4.4.15.
User-defined functions
4.4.16.
User-defined aggregate functions
4.4.17.
The CASE expression
4.4.18.
Sequences and serials
4.4.19.
Tuples or row value expressions
4.5.
Conditional expressions
4.5.1.
Condition building
4.5.2.
AND, OR, NOT boolean operators
4.5.3.
Comparison predicate
4.5.4.
Quantified comparison predicate
4.5.5.
NULL predicate
4.5.6.
DISTINCT predicate
4.5.7.
BETWEEN predicate
4.5.8.
LIKE predicate
4.5.9.
IN predicate
4.5.10.
EXISTS predicate
4.5.11.
OVERLAPS predicate
4.6.
Plain SQL
4.7.
Bind values and parameters
4.7.1.
Indexed parameters
4.7.2.
Named parameters
4.7.3.
Inlined parameters
4.7.4.
SQL injection and plain SQL QueryParts
4.8.
QueryParts
4.8.1.
SQL rendering
4.8.2.
Pretty printing SQL
4.8.3.
Variable binding
4.8.4.
Extend jOOQ with custom types
4.8.5.
Plain SQL QueryParts
4.8.6.
Serializability
4.9.
SQL building in Scala

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