Share jOOQ on Facebook
Share jOOQ on Twitter

All versions: 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Development versions: 3.10 | Unsupported versions: 3.2 | 3.1 | 3.0 | 2.6 | 2.5

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 as well as jOOQ's BNF notation

Table of contents

4.1.
The query DSL type
4.1.1.
DSL subclasses
4.2.
The DSLContext class
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.
Return All Columns On Store
4.2.6.14.
Map JPA Annotations
4.2.6.15.
JDBC Flags
4.2.6.16.
IN-list Padding
4.2.6.17.
Backslash Escaping
4.2.6.18.
Scalar subqueries for stored functions
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.
The SELECT clause
4.3.3.2.
The FROM clause
4.3.3.3.
The JOIN clause
4.3.3.4.
The WHERE clause
4.3.3.5.
The CONNECT BY clause
4.3.3.6.
The GROUP BY clause
4.3.3.7.
The HAVING clause
4.3.3.8.
The WINDOW clause
4.3.3.9.
The ORDER BY clause
4.3.3.10.
The LIMIT .. OFFSET clause
4.3.3.11.
The SEEK clause
4.3.3.12.
The 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.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.2.
The CREATE statement
4.4.3.
The DROP statement
4.4.4.
The TRUNCATE statement
4.4.5.
Generating DDL from objects
4.5.
Catalog and schema expressions
4.6.
Table expressions
4.6.1.
Generated Tables
4.6.2.
Aliased Tables
4.6.3.
Joined tables
4.6.4.
The VALUES() table constructor
4.6.5.
Nested SELECTs
4.6.6.
The Oracle 11g PIVOT clause
4.6.7.
jOOQ's relational division syntax
4.6.8.
Array and cursor unnesting
4.6.9.
Table-valued functions
4.6.10.
The DUAL table
4.7.
Column expressions
4.7.1.
Table columns
4.7.2.
Aliased columns
4.7.3.
Cast expressions
4.7.4.
Datatype coercions
4.7.5.
Arithmetic expressions
4.7.6.
String concatenation
4.7.7.
General functions
4.7.8.
Numeric functions
4.7.9.
Bitwise functions
4.7.10.
String functions
4.7.11.
Case sensitivity with strings
4.7.12.
Date and time functions
4.7.13.
System functions
4.7.14.
Aggregate functions
4.7.15.
Window functions
4.7.16.
Grouping functions
4.7.17.
User-defined functions
4.7.18.
User-defined aggregate functions
4.7.19.
The CASE expression
4.7.20.
Sequences and serials
4.7.21.
Tuples or row value expressions
4.8.
Conditional expressions
4.8.1.
Condition building
4.8.2.
AND, OR, NOT boolean operators
4.8.3.
Comparison predicate
4.8.4.
Boolean operator precedence
4.8.5.
Comparison predicate (degree > 1)
4.8.6.
Quantified comparison predicate
4.8.7.
NULL predicate
4.8.8.
NULL predicate (degree > 1)
4.8.9.
DISTINCT predicate
4.8.10.
BETWEEN predicate
4.8.11.
BETWEEN predicate (degree > 1)
4.8.12.
LIKE predicate
4.8.13.
IN predicate
4.8.14.
IN predicate (degree > 1)
4.8.15.
EXISTS predicate
4.8.16.
OVERLAPS predicate
4.8.17.
Query By Example (QBE)
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
4.14.
QueryParts
4.14.1.
SQL rendering
4.14.2.
Pretty printing SQL
4.14.3.
Variable binding
4.14.4.
Custom data type bindings
4.14.5.
Custom syntax elements
4.14.6.
Plain SQL QueryParts
4.14.7.
Serializability
4.14.8.
Custom SQL transformation
4.14.8.1.
Logging abbreviated bind values
4.15.
SQL Parser
4.16.
SQL building in Scala

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