# The jOOQ User Manual, version 3.13
> [!NOTE]
> This is the complete jOOQ 3.13 manual in a single Markdown document, generated from the manual sources. Sections are numbered as in the online manual. Each section heading is preceded by an anchor named after the section ID. Links to other sections point to these anchors (e.g. `#select-statement`). The online version of this manual is available at https://www.jooq.org/doc/3.13/manual/
## Overview
This manual is divided into these main sections:
- [Getting started with jOOQ](#getting-started)
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
- [SQL building](#sql-building)
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
- [Code generation](#code-generation)
This section explains how to configure and use the built-in source code generator
- [SQL execution](#sql-execution)
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
- [What's new in version 3.13.0](#whats-new)
This section shows what's new in version 3.13.0 of jOOQ.
- [Reference](#reference)
This section is a reference for elements in this manual
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`](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html) by default, this will generate a bind variable for "1948". [Read more about bind variables here](#bind-values)).
You can also avoid getting the SQL string and bind values separately:
```java
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](#sql-building): This section contains a lot of information about creating SQL statements using the jOOQ API
- [Plain SQL](#plain-sql): This section contains information useful in particular to those that want to supply [table expressions](#table-expressions), [column expressions](#column-expressions), etc. as plain SQL to jOOQ, rather than through generated artefacts
- [Bind values](#bind-values): This section explains how bind values are managed and/or inlined in jOOQ.
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`](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html) by default, this will generate a bind variable for "1948". [Read more about bind variables here](#bind-values)).
You can also avoid getting the SQL string and bind values separately:
```java
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](#sql-building): This section contains a lot of information about creating SQL statements using the jOOQ API
- [Code generation](#code-generation): This section contains the necessary information to run jOOQ's code generator against your developer database
- [Bind values](#bind-values): This section explains how bind values are managed and/or inlined in jOOQ.
> 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:
```java
// 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 result = create.fetch(sql);
// Or execute that SQL with JDBC, fetching the ResultSet with jOOQ:
ResultSet rs = connection.createStatement().executeQuery(sql);
Result 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](#sql-building): This section contains a lot of information about creating SQL statements using the jOOQ API
- [Code generation](#code-generation): This section contains the necessary information to run jOOQ's code generator against your developer database
- [SQL execution](#sql-execution): This section contains a lot of information about executing SQL statements using the jOOQ API
- [Fetching](#fetching): This section contains some useful information about the various ways of fetching data with jOOQ
jooq-pro
[your licensee email]
[your license key]
```
#### pom.xml
```xml
central
https://repo1.maven.org/maven2/
jooq-pro
https://repo.jooq.org/repo
central
https://repo1.maven.org/maven2/
jooq-pro
https://repo.jooq.org/repo
```
**Gradle (Kotlin):**
```kotlin
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
```
**Gradle (Groovy):**
```groovy
// 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:**
```xml
org.jooq
jooq
3.13.6
```
**Gradle (Kotlin):**
```kotlin
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.13.6")
}
```
**Gradle (Groovy):**
```groovy
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.13.6"
}
```
org.jooq
jooq
3.13.6
org.jooq
jooq-meta
3.13.6
org.jooq
jooq-codegen
3.13.6
```
###### Commercial Editions (Java 9+)
```xml
org.jooq.pro
jooq
3.13.6
org.jooq.pro
jooq-meta
3.13.6
org.jooq.pro
jooq-codegen
3.13.6
```
###### Commercial Editions (Java 8+)
```xml
org.jooq.pro-java-8
jooq
3.13.6
org.jooq.pro-java-8
jooq-meta
3.13.6
org.jooq.pro-java-8
jooq-codegen
3.13.6
```
###### Commercial Editions (Java 6+)
```xml
org.jooq.pro-java-6
jooq
3.13.6
org.jooq.pro-java-6
jooq-meta
3.13.6
org.jooq.pro-java-6
jooq-codegen
3.13.6
```
###### Commercial Editions (Free Trial)
```xml
org.jooq.trial
jooq
3.13.6
org.jooq.trial
jooq-meta
3.13.6
org.jooq.trial
jooq-codegen
3.13.6
```
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](https://www.jooq.org/licensing).
Please refer to the manual's section about [Code generation configuration](#codegen-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/](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](https://www.mysql.com/) or [https://hub.docker.com/_/mysql](https://hub.docker.com/_/mysql) now!
com.mysql.cj.jdbc.Driver
jdbc:mysql://localhost:3306/library
root
org.jooq.codegen.JavaGenerator
org.jooq.meta.mysql.MySQLDatabase
library
.*
test.generated
C:/workspace/MySQLTest/src/main/java
```
Replace the username (` 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.
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();
}
}
}
```
jdbc:h2:~/flyway-test
sa
```
##### 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:
```xml
org.jooq
jooq
3.13.6
com.h2database
h2
1.4.197
org.apache.logging.log4j
log4j-slf4j-impl
2.11.0
junit
junit
4.11
test
```
##### 0. Maven Project Configuration - Plugins
After the dependencies, let's simply add the Flyway and jOOQ Maven plugins like so. The Flyway plugin:
```xml
org.flywaydb
flyway-maven-plugin
3.0
generate-sources
migrate
${db.url}
${db.username}
filesystem:src/main/resources/db/migration
```
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](#codegen-configuration).
```xml
org.jooq
jooq-codegen-maven
${org.jooq.version}
generate-sources
generate
${db.url}
${db.username}
.*
FLYWAY_TEST
org.jooq.example.flyway.db.h2
target/generated-sources/jooq-h2
```
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
```sql
-- V1__initialise_database.sql
DROP SCHEMA flyway_test IF EXISTS;
CREATE SCHEMA flyway_test;
```
```sql
-- 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)
);
```
```sql
-- 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:
```text
mvn clean install
```
And then observing the log output from Flyway...
```text
[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:
```text
[INFO] --- jooq-codegen-maven:3.13.6:generate (default) @ jooq-flyway-example ---
[INFO] --- jooq-codegen-maven:3.13.6: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
```java
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:
```sql
-- 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
```text
mvn clean install
```
And then observing the log output:
```text
[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:
```text
[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:
```java
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](https://www.testcontainers.org). Please look at this article here for examples on how to do that: [https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/](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](https://flywaydb.org/) for more information about Flyway.
codegen@jooq db.xml
```
) {
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](https://kotlinlang.org/docs/reference/operator-overloading.html). 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:
```groovy
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.
CONNECT BY clause to the query
*/
@Support({ SQLDialect.INFORMIX, SQLDialect.ORACLE })
SelectConnectByConditionStep connectBy(Condition condition);
```
jOOQ API methods which are not annotated with the [`org.jooq.Support`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Support.html) 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](#select-statement) factory method:
```java
/**
* Create a new DSL select statement.
*/
@Support
SelectSelectStep 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](#distinct-predicate), a predicate syntax defined by SQL:1999 and implemented only by H2, HSQLDB, and Postgres:
```sql
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](#case-expressions). For more details, see the manual's section about the [DISTINCT predicate](#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
- The [CONNECT BY clause](#connect-by-clause), for hierarchical queries
- The [PIVOT](#pivot-tables) keyword for creating PIVOT tables
- [Packages](#oracle-packages), [object-oriented user-defined types, member procedures](#oracle-member-procedures) as described in the section about [stored procedures and functions](#stored-procedures)
- Advanced analytical functions as described in the section about [window functions](#window-functions)
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
T unwrap(Wrapper wrapper, Class 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("...");
```
x = val("x");
Param i = param("x", 42);
DSL.using(configuration)
.select(FIRST_NAME.concat(x))
.from(AUTHOR)
.where(ID.eq(i))
.fetch();
```
**Example configuration**
```java
Settings settings = new Settings()
.withParamType(ParamType.NAMED); // Defaults to INDEXED
```
The following setting [statementType](#settings-statement-type) may override this setting.
") // Defaults to "[jooq ignore start]"
.withParseIgnoreCommentStop(" ") // Defaults to "[jooq ignore stop]"
```
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](#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](https://blog.jooq.org/the-java-fluent-api-designer-crash-course/) 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](https://blog.jooq.org/why-you-should-use-jooq-with-code-generation/). 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:
```java
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"](https://en.wikipedia.org/wiki/Fluent_interface) or ["method chaining"](https://en.wikipedia.org/wiki/Method_chaining), jOOQ's BNF-based interface hierarchy will not allow bad query syntax. The following will not compile, for instance:
```java
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 but subselect returns Record1
.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
.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](#queryparts), which delegate [SQL rendering](#sql-rendering) and [variable binding](#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:
```java
DSLContext create = DSL.using(connection, dialect);
SelectQuery 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:
```java
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](#queryparts) being constructed. While creating [conditional expressions](#conditional-expressions), [column expressions](#column-expressions) (such as functions) assumes immutable behaviour, creating [SQL statements](#sql-statements) does not. In other words, the following can be said:
```java
// 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](#bind-values) from any `QueryPart`.
> t1 =
name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression> 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();
```
See also the section about [common table expressions](#common-table-expressions).
SelectWhereStep selectFrom(Table 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 to your Table's associated Record. An example of such a Query would then be:
```java
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:
- [SELECT clause](#select-clause)
- [JOIN operator](#join-clause)
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](#record-vs-tablerecord).
select(Collection> fields);
public static SelectSelectStep select(SelectField... fields);
// Typesafe select methods:
public static SelectSelectStep> select(SelectField field1);
public static SelectSelectStep> select(SelectField field1, SelectField field2);
// [...]
```
The type that is being projected is the [`org.jooq.SelectField`](https://www.jooq.org/javadoc/3.13.x/org/jooq/SelectField.html), see also the [next section about SelectField](#select-clause-selectfield). Since the generic R type is bound to some [Record[N]](#record-n), the associated T type information can be used in various other contexts, e.g. the [IN predicate](#in-predicate). Such a `SELECT` statement can be assigned typesafely:
```java
Select> s1 = create.select(BOOK.ID, BOOK.TITLE);
Select> 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](#record-n).
> s1 = select(BOOK.ID, BOOK.TITLE).from(BOOK);
Select> s2 = selectOne();
Select> s3 = select(one(), zero());
Select> 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: , got:
s1.union(s4); // OK. The two Record[N] types match
```
> 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.
step =
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME);
step.values("A", "B", "C");
// ^^^ Doesn't compile, the expected type is Integer
```
###### INSERT .. VALUES with multiple rows
The SQL standard specifies that multiple rows can be supplied to the VALUES() constructor in an INSERT statement. Here's an example of a multi-record INSERT
```sql
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse'),
(101, 'Alfred', 'Döblin');
```
```java
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(100, "Hermann", "Hesse")
.values(101, "Alfred", "Döblin")
.execute()
```
jOOQ tries to stay close to actual SQL. In detail, however, Java's expressiveness is limited. That's why the values() clause is repeated for every record in multi-record inserts.
Some RDBMS do not support inserting several records in a single statement. In those cases, jOOQ emulates multi-record INSERTs using the following SQL:
```sql
INSERT INTO AUTHOR
(ID, FIRST_NAME, LAST_NAME)
SELECT 100, 'Hermann', 'Hesse' FROM DUAL UNION ALL
SELECT 101, 'Alfred', 'Döblin' FROM DUAL;
```
```java
create.insertInto(AUTHOR,
AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(100, "Hermann", "Hesse")
.values(101, "Alfred", "Döblin")
.execute();
```
###### Dialect support
This example using jOOQ:
```java
insertInto(AUTHOR).columns(AUTHOR.ID, AUTHOR.LAST_NAME).values(100, "Hesse")
```
Translates to the following dialect specific expressions:
###### All dialects
```sql
INSERT INTO AUTHOR (ID, LAST_NAME)
VALUES (
100,
'Hesse'
)
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
, Select>)` method in the `UPDATE` DSL API, for convenience (see the section about [scalar subqueries](#scalar-subqueries) for more details):
```sql
UPDATE AUTHOR
SET FIRST_NAME = (
SELECT FIRST_NAME
FROM PERSON
WHERE PERSON.ID = AUTHOR.ID
),
WHERE ID = 3;
```
```java
create.update(AUTHOR)
.set(AUTHOR.FIRST_NAME,
select(PERSON.FIRST_NAME)
.from(PERSON)
.where(PERSON.ID.eq(AUTHOR.ID))
)
.where(AUTHOR.ID.eq(3))
.execute();
```
###### Dialect support
This example using jOOQ:
```java
update(BOOK_TO_BOOK_STORE).set(BOOK_TO_BOOK_STORE.STOCK, 0).from(BOOK).where(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)).and(BOOK.AUTHOR_ID.eq(1))
```
Translates to the following dialect specific expressions:
###### ASE, BigQuery, Oracle, SQLServer, Sybase
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = 0
FROM BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### Aurora MySQL, MariaDB, MemSQL, MySQL, Trino
```sql
UPDATE BOOK_TO_BOOK_STORE
CROSS JOIN BOOK
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### Aurora Postgres, CockroachDB, DuckDB, Postgres, Redshift, SQLite, Snowflake, YugabyteDB
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
FROM BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### ClickHouse
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
WHERE (NAME, BOOK_ID) IN (
SELECT BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
)
```
###### Databricks
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE STRUCT (
coalesce(BOOK_TO_BOOK_STORE.NAME),
coalesce(BOOK_TO_BOOK_STORE.BOOK_ID)
) = STRUCT (
coalesce(alias_1.v0),
coalesce(alias_1.v1)
)
)
```
###### DB2, Exasol, Firebird, H2, HSQLDB
```sql
MERGE INTO BOOK_TO_BOOK_STORE
USING BOOK
ON (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
WHEN MATCHED THEN UPDATE SET
BOOK_TO_BOOK_STORE.STOCK = 0
```
###### Hana
```sql
UPDATE BOOK_TO_BOOK_STORE
FROM BOOK_TO_BOOK_STORE, BOOK
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
```
###### Spanner
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE (BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID) = (alias_1.v0, alias_1.v1)
)
```
###### SQLDataWarehouse
```sql
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE (
BOOK_TO_BOOK_STORE.NAME = alias_1.v0
AND BOOK_TO_BOOK_STORE.BOOK_ID = alias_1.v1
)
)
```
###### Teradata, Vertica
```sql
MERGE INTO BOOK_TO_BOOK_STORE
USING BOOK
ON (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
WHEN MATCHED THEN UPDATE SET
STOCK = 0
```
###### Access, Informix
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var(name("i"), INTEGER);
create.begin(
for_(i).in(1, 10).loop(
insertInto(T).columns(T.COL).values(i)
)
).execute();
```
The entire loop is executed on the server, which may greatly help reduce client server round trips.
Apart from the [block statement](#procedural-block), this feature set is available only in our commercial distributions.
>
LOOP
i := i + 1;
CONTINUE label WHEN i <= 10;
END LOOP;
```
```java
// All dialects
Label label = label("label");
label.label(loop(
i.set(i.plus(1)),
continue(label).when(i.le(10))
))
```
Notice that `continue` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `continue_()`.
##### Dialect support
This example using jOOQ:
```java
loop(i.set(i.plus(1)), continueWhen(i.gt(10)))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Oracle, Postgres, YugabyteDB
```sql
LOOP
i := (i + 1);
CONTINUE WHEN i > 10;
END LOOP
```
###### BigQuery
```sql
LOOP
SET i = (i + 1);
IF i > 10 THEN
CONTINUE;
END IF;
END LOOP
```
###### DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
alias_1:
LOOP
SET i = (i + 1);
IF i > 10 THEN
ITERATE alias_1;
END IF;
END LOOP
```
###### H2
```sql
for (;;) {
i = (i + 1);
if (i > 10) {
continue;
}
}
```
###### Hana
```sql
WHILE 1 = 1 DO
i = (i + 1);
IF i > 10 THEN
CONTINUE;
END IF;
END WHILE
```
###### Informix
```sql
LOOP
LET i = (i + 1);
IF i > 10 THEN
CONTINUE;
END IF;
END LOOP
```
###### Snowflake
```sql
LOOP
i := (:i + 1);
IF (:i > 10) THEN
CONTINUE;
END IF;
END LOOP
```
###### SQLDataWarehouse, SQLServer
```sql
WHILE 1 = 1 BEGIN
SET @i = (@i + 1);
IF @i > 10
CONTINUE;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, Exasol, Firebird, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
>
LOOP
i := i + 1;
EXIT label WHEN i > 10;
END LOOP;
```
```java
// All dialects
Label label = label("label");
label.label(loop(
i.set(i.plus(1)),
exit(label).when(i.gt(10))
))
```
##### Dialect support
This example using jOOQ:
```java
loop(i.set(i.plus(1)), exitWhen(i.gt(10)))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Oracle, Postgres, YugabyteDB
```sql
LOOP
i := (i + 1);
EXIT WHEN i > 10;
END LOOP
```
###### BigQuery
```sql
LOOP
SET i = (i + 1);
IF i > 10 THEN
BREAK;
END IF;
END LOOP
```
###### DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
alias_1:
LOOP
SET i = (i + 1);
IF i > 10 THEN
LEAVE alias_1;
END IF;
END LOOP
```
###### Firebird
```sql
WHILE (1 = 1) DO BEGIN
:i = (:i + 1);
IF (:i > 10) THEN
LEAVE;
END
```
###### H2
```sql
for (;;) {
i = (i + 1);
if (i > 10) {
break;
}
}
```
###### Hana
```sql
WHILE 1 = 1 DO
i = (i + 1);
IF i > 10 THEN
BREAK;
END IF;
END WHILE
```
###### Informix
```sql
LOOP
LET i = (i + 1);
EXIT WHEN i > 10;
END LOOP
```
###### Snowflake
```sql
LOOP
i := (:i + 1);
IF (:i > 10) THEN
BREAK;
END IF;
END LOOP
```
###### SQLDataWarehouse, SQLServer
```sql
WHILE 1 = 1 BEGIN
SET @i = (@i + 1);
IF @i > 10
BREAK;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, Exasol, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
for_(i).in(1, 10).loop(
insertInto(T).columns(T.COL).values(i)
)
```
In addition to simplifying the most common case, there are also options of traversing the arguments in a reversed way, and using an additional optional step variable, for example:
```sql
-- pgplsql
FOR i IN REVERSE 10 .. 1 BY 2 LOOP
INSERT INTO t (col) VALUES (i);
END LOOP;
```
```java
// All dialects
Variable i = var("i", INTEGER);
for_(i).inReverse(10, 1).by(2).loop(
insertInto(T).columns(T.COL).values(i)
)
```
Not all dialects support the entirety of this syntax, but luckily it is easy for jOOQ to emulate in all dialects using [WHILE](#procedural-while):
```sql
-- PL/SQL
WHILE i >= 1 LOOP
INSERT INTO t (col) VALUES (i);
i := i - 2;
END LOOP;
```
Notice that `for` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `for_()`.
##### Dialect support
This example using jOOQ:
```java
for_(i).in(1, 10).loop(insertInto(BOOK).columns(BOOK.ID).values(i))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Exasol, Oracle, Postgres, YugabyteDB
```sql
FOR i IN 1 .. 10 LOOP
INSERT INTO BOOK (ID)
VALUES (i);
END LOOP
```
###### BigQuery
```sql
BEGIN
DECLARE i int64 DEFAULT 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### DB2
```sql
BEGIN
DECLARE i integer;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### Firebird
```sql
DECLARE i integer DEFAULT 1;
WHILE (:i <= 10) DO BEGIN
INSERT INTO BOOK (ID)
VALUES (:i);
:i = (:i + 1);
END
```
###### H2
```sql
for (Integer i = 1; i <= 10; i++) {
try (PreparedStatement s = c.prepareStatement(
"INSERT INTO BOOK (ID)\n" +
"VALUES (?)"
)) {
s.setObject(1, i);
s.execute();
}
}
```
###### Hana
```sql
BEGIN
DECLARE i integer;
FOR i IN 1 .. 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
END FOR;
END;
```
###### HSQLDB
```sql
BEGIN ATOMIC
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### Informix
```sql
BEGIN
DEFINE i integer;
LET i = 1;
FOR i IN (1 TO 10) LOOP
INSERT INTO BOOK (ID)
VALUES (i);
END LOOP;
END;
```
###### MariaDB
```sql
FOR i IN 1 .. 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
END FOR
```
###### MySQL
```sql
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
```
###### Snowflake
```sql
FOR i IN 1 TO 10 DO
INSERT INTO BOOK (ID)
SELECT :i;
END FOR
```
###### SQLDataWarehouse
```sql
BEGIN
DECLARE @i int DEFAULT 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
SELECT @i;
SET @i = (@i + 1);
END;
END;
```
###### SQLServer
```sql
BEGIN
DECLARE @i int = 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
VALUES (@i);
SET @i = (@i + 1);
END;
END;
```
###### Trino
```sql
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
>
INSERT INTO t (col) VALUES (3);
END;
```
```java
// All dialects
Label l1 = label("l1");
begin(
insertInto(T).columns(T.COL).values(1),
goto_(l1),
insertInto(T).columns(T.COL).values(2),
l1.label(insertInto(T).columns(T.COL).values(3))
)
```
Some dialects (e.g. T-SQL) may implement "full GOTO" in the ways that are generally not really helping readability or maintainability of code, namely the idea that `GOTO` can be used to jump into any arbitrary scope that should not be reachable through ordinary control flow. This is not possible in other languages, like PL/SQL, and currently cannot be emulated by jOOQ.
Notice that `goto` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `goto_()`.
##### Dialect support
This example using jOOQ:
```java
begin(l.label(insertInto(BOOK).columns(BOOK.ID).values(1)), goto_(l))
```
Translates to the following dialect specific expressions:
###### DB2
```sql
BEGIN
l:
BEGIN
INSERT INTO BOOK (ID)
VALUES (1);
END;
GOTO l;
END
```
###### Informix
```sql
BEGIN
<>
INSERT INTO BOOK (ID)
VALUES (1);
GOTO l;
END;
```
###### Oracle
```sql
BEGIN
<>
WHILE 1 = 1 LOOP
INSERT INTO BOOK (ID)
VALUES (1);
EXIT l;
END LOOP;
GOTO l;
END;
```
###### SQLDataWarehouse, SQLServer
```sql
BEGIN
l:
INSERT INTO BOOK (ID)
VALUES (1);
GOTO l;
END;
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLite, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
>
BEGIN NULL; END;
```
```java
// All dialects
Label label = label("label");
label.label(begin())
```
That's a lot of labels.
The main usages of these labels will be discussed in the following sections about, [EXIT](#procedural-exit), and [CONTINUE](#procedural-continue)
##### Dialect support
This example using jOOQ:
```java
l.label(loop())
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Informix, Postgres, YugabyteDB
```sql
<>
LOOP
END LOOP
```
###### DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
l:
LOOP
END LOOP
```
###### Firebird
```sql
l:
WHILE (1 = 1) DO BEGIN
END
```
###### H2
```sql
l:
for (;;) {
}
```
###### Oracle
```sql
<>
LOOP
NULL;
END LOOP
```
###### Snowflake
```sql
LOOP
NULL;
END LOOP l
```
###### SQLDataWarehouse, SQLServer
```sql
l:
WHILE 1 = 1 BEGIN
IF 1 = 0 SELECT 1;
END
```
###### ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Databricks, DuckDB, Exasol, Hana, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
repeat(
insertInto(T).columns(T.COL).values(i),
i.set(i.plus(1))
).until(i.gt(10))
```
##### Dialect support
This example using jOOQ:
```java
repeat(deleteFrom(BOOK).where(BOOK.ID.eq(i)), i.set(i.plus(1))).until(i.gt(10))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Oracle, Postgres, YugabyteDB
```sql
<>
LOOP
DELETE FROM BOOK
WHERE BOOK.ID = i;
i := (i + 1);
EXIT alias_2 WHEN i > 10;
END LOOP
```
###### BigQuery, DB2, HSQLDB, MariaDB, MySQL, Trino
```sql
REPEAT
DELETE FROM BOOK
WHERE BOOK.ID = i;
SET i = (i + 1);
UNTIL i > 10
END REPEAT
```
###### Exasol
```sql
REPEAT
DELETE FROM BOOK
WHERE BOOK.ID = i;
i := (i + 1);
UNTIL i > 10
END REPEAT
```
###### Firebird
```sql
alias_2:
WHILE (1 = 1) DO BEGIN
DELETE FROM BOOK
WHERE BOOK.ID = :i;
:i = (:i + 1);
IF (:i > 10) THEN
LEAVE alias_2;
END
```
###### H2
```sql
do {
try (PreparedStatement s = c.prepareStatement(
"DELETE FROM BOOK\n" +
"WHERE BOOK.ID = ?"
)) {
s.setObject(1, i);
s.execute();
}
i = (i + 1);
}
while (!(i > 10))
```
###### Hana
```sql
WHILE 1 = 1 DO
DELETE FROM BOOK
WHERE BOOK.ID = i;
i = (i + 1);
IF i > 10 THEN
BREAK;
END IF;
END WHILE
```
###### Informix
```sql
<>
LOOP
DELETE FROM BOOK
WHERE BOOK.ID = i;
LET i = (i + 1);
EXIT alias_2 WHEN i > 10;
END LOOP
```
###### Snowflake
```sql
REPEAT
DELETE FROM BOOK
WHERE BOOK.ID = :i;
i := (:i + 1);
UNTIL (:i > 10)
END REPEAT
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
```
This variable doesn't do anything on its own yet. But like many things in jOOQ, it has to be declared first, outside of an actual jOOQ expression, in order to be usable in jOOQ expressions.
We can now reference the variable in a declaration statement as follows:
```sql
-- MySQL syntax
DECLARE i INTEGER;
```
```java
// All dialects
declare(i)
```
Notice that there are many different ways to declare a local variable in different dialects. There is
**The Oracle PL/SQL, PostgreSQL pgplsql style**
In these languages, the `DECLARE` statement is actually not an independent statement that can be used anywhere. It is part of a [procedural block](#procedural-block), prepended to `BEGIN .. END`:
```sql
-- PL/SQL syntax
DECLARE
i INT;
BEGIN
NULL;
END;
```
When using jOOQ, you can safely ignore this fact, and prepend that there is a `DECLARE` statement also in these dialects. jOOQ will add additional `BEGIN .. END` blocks to your surrounding block, to make sure the whole block becomes syntactically and semantically correct.
**The T-SQL, MySQL style**
In these languages, the `DECLARE` statement is really an independent statement that can be used anywhere. Just like in the Java language, variables can be declared at any position and used only "further down", lexically. Ignoring T-SQL's JavaScript-esque understanding of scope for a moment.
```sql
-- T-SQL syntax
DECLARE @i INTEGER;
```
Notice that you can safely ignore the `@` sign that is required in some dialects, such as T-SQL. jOOQ will generate it for you.
##### Assignment
A local variable needs a way to have a value assigned to it. Assignments are possible both on [`org.jooq.Variable`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Variable.html), or on [`org.jooq.Declaration`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Declaration.html), directly. For example
```sql
-- T-SQL syntax
DECLARE @i INTEGER = 1;
```
```java
// All dialects
declare(i).set(1)
```
Alternatively, you can split declaration and assignment, or re-assign new values to variables:
```sql
-- T-SQL syntax
DECLARE @i INTEGER;
SET @i = 1;
SET @i = 2;
```
```java
// All dialects
declare(i),
i.set(1),
i.set(2)
```
Some dialects also support using subqueries in assignment expressions, and other expresions in their procedural languages. For example:
```sql
-- T-SQL syntax
SET @i = (SELECT MAX(col) FROM t);
```
```java
// All dialects
i.set(select(max(T.COL)).from(T))
```
In PL/SQL, the above can be emulated as follows:
```sql
-- PL/SQL syntax
SELECT MAX(col)
INTO i
FROM t;
```
##### Referencing
Obviously, once we've assigned a value to a local variable, we want to reference it as well in arbitrary expressions, and queries.
For this purpose, [`org.jooq.Variable`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Variable.html) extends [`org.jooq.Field`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Field.html), and as such, can be used in arbitrary places where any other [column expression](#column-expressions) can be used. Within the procedural language, a simple example would be to increment a local variable:
```sql
-- PL/SQL syntax
i := i + 1;
```
```java
// All dialects
i.set(i.plus(1))
```
Or in a more complete example, use it in a SQL statement:
```sql
-- PL/SQL syntax
DECLARE
i INT;
BEGIN
i := 1;
INSERT INTO t (col) VALUES (i);
END;
```
```java
// All dialects
Variable i = var("i", INTEGER);
create.begin(
declare(i),
i.set(1),
insertInto(T).columns(T.COL).values(i)
).execute();
```
##### Dialect support
This example using jOOQ:
```java
begin(declare(i), i.set(1))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, Postgres, YugabyteDB
```sql
DO $$
DECLARE
i int;
BEGIN
i := 1;
END;
$$
```
###### BigQuery
```sql
BEGIN
DECLARE i int64;
SET i = 1;
END;
```
###### CockroachDB
```sql
DO $$
DECLARE
i int4;
BEGIN
i := 1;
END;
$$
```
###### DB2
```sql
BEGIN
DECLARE i integer;
SET i = 1;
END
```
###### Exasol
```sql
BEGIN
i int;
i := 1;
END;
```
###### Firebird
```sql
EXECUTE BLOCK AS
DECLARE i integer;
BEGIN
:i = 1;
END
```
###### H2
```sql
CREATE ALIAS block_1794083324747_9642749 AS $$
void x(Connection c) throws SQLException {
Integer i = null;
i = 1;
}
$$;
CALL block_1794083324747_9642749();
DROP ALIAS block_1794083324747_9642749;
```
###### Hana
```sql
DO BEGIN
DECLARE i integer;
i = 1;
END;
```
###### HSQLDB
```sql
BEGIN ATOMIC
DECLARE i int;
SET i = 1;
END;
```
###### Informix
```sql
BEGIN
DEFINE i integer;
LET i = 1;
END;
```
###### MariaDB
```sql
BEGIN NOT ATOMIC
DECLARE i int;
SET i = 1;
END;
```
###### MySQL
```sql
CREATE PROCEDURE block_1794083325381_7381783()
MODIFIES SQL DATA
BEGIN
DECLARE i int;
SET i = 1;
END;
CALL block_1794083325381_7381783();
DROP PROCEDURE block_1794083325381_7381783;
```
###### Oracle
```sql
DECLARE
i number(10);
BEGIN
i := 1;
END;
```
###### Snowflake
```sql
BEGIN
LET i number(10);
i := 1;
END;
```
###### SQLDataWarehouse, SQLServer
```sql
BEGIN
DECLARE @i int;
SET @i = 1;
END;
```
###### Trino
```sql
BEGIN
DECLARE i int;
SET i = 1;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
i = var("i", INTEGER);
while_(i.le(10)).loop(
insertInto(T).columns(T.COL).values(i)
)
```
Notice that `while` is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: `while_()`.
##### Dialect support
This example using jOOQ:
```java
while_(i.le(10)).loop(deleteFrom(BOOK).where(BOOK.ID.eq(i)))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, Exasol, Informix, Oracle, Postgres, YugabyteDB
```sql
WHILE i <= 10 LOOP
DELETE FROM BOOK
WHERE BOOK.ID = i;
END LOOP
```
###### BigQuery, DB2, HSQLDB, Hana, MariaDB, MySQL, Trino
```sql
WHILE i <= 10 DO
DELETE FROM BOOK
WHERE BOOK.ID = i;
END WHILE
```
###### Firebird
```sql
WHILE (:i <= 10) DO BEGIN
DELETE FROM BOOK
WHERE BOOK.ID = :i;
END
```
###### H2
```sql
while (i <= 10) {
try (PreparedStatement s = c.prepareStatement(
"DELETE FROM BOOK\n" +
"WHERE BOOK.ID = ?"
)) {
s.setObject(1, i);
s.execute();
}
}
```
###### Snowflake
```sql
WHILE (:i <= 10) DO
DELETE FROM BOOK
WHERE BOOK.ID = :i;
END WHILE
```
###### SQLDataWarehouse, SQLServer
```sql
WHILE @i <= 10 BEGIN
DELETE FROM BOOK
WHERE BOOK.ID = @i;
END
```
###### ASE, Access, Aurora MySQL, ClickHouse, Databricks, DuckDB, MemSQL, Redshift, SQLite, Spanner, Sybase, Teradata, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
.ID reference
```
Note that if you *know* that the `ID` column is of the same type as the `AUTHOR.ID` column, you can use that again to dereference the column as is explained again in the section [dereferencing table columns](#dereferenced-table-columns).
```java
// Now with inferred Integer type
Field id = named.field(AUTHOR.ID); // Produces a t.ID reference
Field id = unnamed.field(AUTHOR.ID); // Produces a .ID reference
```
> t1 =
name("t1").fields("f1", "f2").as(select(val(1), val("a")));
CommonTableExpression> 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`](https://www.jooq.org/javadoc/3.13.x/org/jooq/CommonTableExpression.html) type extends the commonly used [`org.jooq.Table`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Table.html) type, and can thus be used wherever a table can be used.
##### Inlined common table expressions
If you're just operating on [plain SQL](#plain-sql), you may not need to keep intermediate references to such common table expressions. An example of such usage would be this:
```sql
WITH "a" AS (SELECT
1 AS "x",
'a' AS "y"
)
SELECT
FROM "a"
;
```
```java
create.with("a").as(select(
val(1).as("x"),
val("a").as("y")
))
.select()
.from(table(name("a")))
.fetch();
```
r1 = create.selectFrom(fBooks(null)).fetch();
// Lateral joining the table-valued function to another table using CROSS APPLY:
create.select(BOOK.ID, F_BOOKS.TITLE)
.from(BOOK.crossApply(fBooks(BOOK.ID)))
.fetch();
```
> r = create.selectFrom(generateSeries(1, 10)).fetch();
```
##### Dialect support
This example using jOOQ:
```java
selectFrom(generateSeries(1, 10))
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, DuckDB, Postgres, YugabyteDB
```sql
SELECT generate_series.generate_series
FROM generate_series(1, 10)
```
###### BigQuery, Spanner
```sql
SELECT generate_series.generate_series
FROM (
SELECT null generate_series
FROM UNNEST([STRUCT(1 AS dual)]) AS dual
WHERE FALSE
UNION ALL
SELECT *
FROM unnest(generate_array(1, 10)) generate_series
) generate_series
```
###### ClickHouse
```sql
SELECT generate_series.generate_series
FROM (
SELECT null generate_series
WHERE FALSE
UNION ALL
SELECT *
FROM (
SELECT cast(number AS Nullable(integer)) generate_series
FROM numbers(1, (10 + 1))
) generate_series
) generate_series
```
###### CockroachDB
```sql
SELECT generate_series.generate_series
FROM generate_series(1, 10) generate_series (generate_series)
```
###### Databricks
```sql
SELECT generate_series.generate_series
FROM explode(sequence(1, 10)) generate_series (generate_series)
```
###### DB2
```sql
SELECT generate_series.generate_series
FROM (
WITH
generate_series(generate_series) AS (
SELECT 1
FROM SYSIBM.DUAL
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Exasol, Oracle
```sql
SELECT generate_series.generate_series
FROM (
SELECT (level + (1 - 1)) generate_series
FROM DUAL
CONNECT BY level <= ((10 + 1) - 1)
) generate_series
```
###### Firebird
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM RDB$DATABASE
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### H2
```sql
SELECT generate_series.generate_series
FROM system_range(1, 10) generate_series (generate_series)
```
###### Hana
```sql
SELECT generate_series.generate_series
FROM (
SELECT cast(
null
AS integer
) generate_series
FROM SYS.DUMMY
WHERE 1 = 0
UNION ALL
SELECT *
FROM (
SELECT generated_period_end generate_series
FROM series_generate_integer(1, (1 - 1), 10)
) generate_series
) generate_series
```
###### HSQLDB
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM (VALUES (1)) AS dual (dual)
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Informix
```sql
SELECT generate_series.generate_series
FROM (
SELECT (level + (1 - 1)) generate_series
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
CONNECT BY level <= ((10 + 1) - 1)
) generate_series
```
###### MariaDB, MySQL, SQLite, Trino
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Snowflake
```sql
SELECT generate_series.generate_series
FROM (
SELECT ((seq4() + 1) + (1 - 1)) generate_series
FROM TABLE(generator(rowcount => (10 - (1 - 1))))
) generate_series (generate_series)
```
###### SQLDataWarehouse
```sql
WITH
generate_series(generate_series) AS (
SELECT 1
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series.generate_series
FROM (
SELECT generate_series
FROM generate_series
) generate_series
```
###### SQLServer
```sql
SELECT generate_series.generate_series
FROM (
SELECT *
FROM generate_series(1, 10)
) generate_series (generate_series)
```
###### Sybase
```sql
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM SYS.DUMMY
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series
```
###### Teradata
```sql
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM (
SELECT 1 AS "dual"
) AS "dual"
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series.generate_series
FROM (
SELECT generate_series
FROM generate_series
) generate_series
```
###### ASE, Access, Aurora MySQL, MemSQL, Redshift, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
field1 = BOOK.TITLE;
// A function created from the DSL
Field field2 = trim(BOOK.TITLE);
// More complex function with advanced DSL syntax
Field field4 = listAgg(BOOK.TITLE)
.withinGroupOrderBy(BOOK.ID.asc())
.over().partitionBy(AUTHOR.ID);
```
` record type, and provides access to the container [`org.jooq.Table`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Table.html) instance.
See the manual's section about [generated tables](#codegen-tables) for more information about what is really generated by the [code generator](#code-generation)
firstName = field(name("AUTHOR", "FIRST_NAME"), INTEGER);
```
Note that by default, these names are quoted (among other reasons to prevent [SQL injection](#sql-injection)), and thus case sensitive. For more details, please refer to [the section about names and identifiers](#names).
{
// Cast this field to the type of another field
Field cast(Field field);
// Cast this field to a given DataType
Field cast(DataType type);
// Cast this field to the default DataType for a given Class
Field cast(Class type);
}
// And additional convenience methods in the DSL:
public class DSL {
Field cast(Object object, Field field);
Field cast(Object object, DataType type);
Field cast(Object object, Class type);
Field castNull(Field field);
Field castNull(DataType type);
Field castNull(Class type);
}
```
> [!TIP]
> The `CAST` expression converts between data types directly in SQL. If you wish to change a data type without any effect on rendered SQL, you may prefer to [coerce](#datatype-coercions) your expressions.
##### Dialect support
This example using jOOQ:
```java
cast("1", VARCHAR(10))
```
Translates to the following dialect specific expressions:
###### Access
```sql
cstr('1')
```
###### ASE, Aurora Postgres, DB2, Databricks, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
cast('1' AS varchar(10))
```
###### Aurora MySQL, MariaDB, MemSQL, MySQL
```sql
cast('1' AS char(10))
```
###### BigQuery, Spanner
```sql
cast('1' AS string)
```
###### ClickHouse
```sql
cast('1' AS String(10))
```
###### CockroachDB
```sql
cast('1' AS string(10))
```
###### Informix
```sql
cast('1' AS lvarchar(10))
```
###### Oracle
```sql
cast('1' AS varchar2(10))
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
field1 = val(1).coerce(String.class);
Field field2 = val("1").coerce(Integer.class);
```
In the above example, `field1` will be treated by jOOQ as a `Field`, binding the numeric literal `1` as a `VARCHAR` value. The same applies to `field2`, whose string literal `"1"` will be bound as an `INTEGER` value.
This technique is better than performing unsafe or rawtype casting in Java, if you cannot access the "right" field type from any given expression.
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
create.select(
child,
parent,
sysConnectByPath(child, "/"),
connectByIsCycle())
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2),
row(2, 3)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+--------+--------------------+
| child | parent | path | connect_by_iscycle |
+-------+--------+--------+--------------------+
| 1 | | /1 | 0 |
| 2 | 1 | /1/2 | 0 |
| 3 | 2 | /1/2/3 | 1 |
+-------+--------+--------+--------------------+
```
###### Dialect support
This example using jOOQ:
```java
connectByIsCycle()
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle
```sql
connect_by_iscycle
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"),
connectByIsLeaf())
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2),
row(4, 1)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+-------------------+
| child | parent | sys_connect_by_path | connect_by_isleaf |
+-------+--------+---------------------+-------------------+
| 1 | {null} | /1 | false |
| 2 | 1 | /1/2 | false |
| 3 | 2 | /1/2/3 | true |
| 4 | 1 | /1/4 | true |
+-------+--------+---------------------+-------------------+
```
###### Dialect support
This example using jOOQ:
```java
connectByIsLeaf()
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle
```sql
connect_by_isleaf
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
create.select(
child,
parent,
sysConnectByPath(child, "/"),
connectByRoot(child))
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(val(3), val(null, INTEGER)),
row(4, 3)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+-----------------+
| child | parent | sys_connect_by_path | connect_by_root |
+-------+--------+---------------------+-----------------+
| 1 | {null} | /1 | 1 |
| 2 | 1 | /1/2 | 1 |
| 3 | {null} | /3 | 3 |
| 4 | 3 | /3/4 | 3 |
+-------+--------+---------------------+-----------------+
```
###### Dialect support
This example using jOOQ:
```java
connectByRoot(AUTHOR.ID)
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle, Snowflake
```sql
connect_by_root AUTHOR.ID
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"),
level())
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2),
row(4, 1)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+-------+
| child | parent | sys_connect_by_path | level |
+-------+--------+---------------------+-------+
| 1 | {null} | /1 | 1 |
| 2 | 1 | /1/2 | 2 |
| 3 | 2 | /1/2/3 | 3 |
| 4 | 1 | /1/4 | 2 |
+-------+--------+---------------------+-------+
```
###### Dialect support
This example using jOOQ:
```java
level()
```
Translates to the following dialect specific expressions:
###### Aurora Postgres, CockroachDB, DB2, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, YugabyteDB
```sql
level
```
###### ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Databricks, DuckDB, Hana, MemSQL, Spanner, Vertica
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"))
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+
| child | parent | sys_connect_by_path |
+-------+--------+---------------------+
| 1 | {null} | /1 |
| 2 | 1 | /1/2 |
| 3 | 2 | /1/2/3 |
+-------+--------+---------------------+
```
###### Dialect support
This example using jOOQ:
```java
prior(AUTHOR.ID)
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle, Redshift, Snowflake
```sql
PRIOR AUTHOR.ID
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
child = field("child", INTEGER);
Field parent = field("parent", INTEGER);
ctx.select(
child,
parent,
sysConnectByPath(child, "/"))
.from(values(
row(val(1), val(null, INTEGER)),
row(2, 1),
row(3, 2)).as(table("t"), child, parent))
.startWith(parent.isNull())
.connectByNoCycle(prior(child).eq(parent))
.fetch();
```
The result being, for example
```text
+-------+--------+---------------------+
| child | parent | sys_connect_by_path |
+-------+--------+---------------------+
| 1 | {null} | /1 |
| 2 | 1 | /1/2 |
| 3 | 2 | /1/2/3 |
+-------+--------+---------------------+
```
###### Dialect support
This example using jOOQ:
```java
sysConnectByPath(AUTHOR.ID, "/")
```
Translates to the following dialect specific expressions:
###### Exasol, Informix, Oracle, Snowflake
```sql
sys_connect_by_path(AUTHOR.ID, '/')
```
###### ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
```sql
/* UNSUPPORTED */
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
currval();
// Get a field for the NEXTVAL sequence property
Field nextval();
```
So if you have a sequence like this in Oracle:
```sql
CREATE SEQUENCE s_author_id
```
You can then use your [generated sequence](#codegen-sequences) object directly in a SQL statement as such:
```java
// Reference the sequence in a SELECT statement:
Field s = S_AUTHOR_ID.nextval();
BigInteger nextID = create.select(s).fetchOne(s);
// Reference the sequence in an INSERT statement:
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.values(S_AUTHOR_ID.nextval(), val("William"), val("Shakespeare"))
.execute();
```
- For more information about generated sequences, refer to the manual's section about [generated sequences](#codegen-sequences)
- For more information about executing standalone calls to sequences, refer to the manual's section about [sequence execution](#sequence-execution)
>` type qualifies as a scalar subquery, irrespective of content and whether it is "correlated".
There are mostly 3 ways of creating scalar subqueries in jOOQ
- Type safe wrapping using [`DSL.field(Select)`](https://www.jooq.org/javadoc/3.13.x/org/jooq/impl/DSL.html#field(org.jooq.Select))
- Type unsafe wrapping using [`Select.asField()`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Select.html#asField())
- Through convenience methods, such as [`Field.eq(Select)`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Field.html#eq(org.jooq.Select))
For example:
```sql
SELECT
AUTHOR.ID, (
SELECT count(*) FROM AUTHOR
) AS authors
FROM AUTHOR
```
```java
create.select(
AUTHOR.ID,
field(selectCount().from(AUTHOR)).as("authors"))
.from(AUTHOR)
.fetch();
```
##### Correlated subqueries
A "correlated" subquery is a subquery (scalar or not) whose execution depends on the query that it is embedded in. It acts as a function taking the current row as an input argument.
```sql
SELECT
AUTHOR.ID, (
SELECT count(*)
FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) AS books
FROM AUTHOR
```
```java
create.select(
AUTHOR.ID,
field(selectCount()
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
.from(AUTHOR)
.fetch();
```
In the above example, the subquery counts the number of books for each author from the outer query.
Row1 row(T1 t1) { ... }
public static Row2 row(T1 t1, T2 t2) { ... }
public static Row3 row(T1 t1, T2 t2, T3 t3) { ... }
public static Row4 row(T1 t1, T2 t2, T3 t3, T4 t4) { ... }
// [ ... idem for Row5, Row6, Row7, ..., Row22 ]
// Degrees of more than 22 are supported without type-safety
public static RowN row(Object... values) { ... }
```
##### Using row value expressions in predicates
Row value expressions are incompatible with most other [QueryParts](#queryparts), but they can be used as a basis for constructing various [conditional expressions](#conditional-expressions), such as:
- [comparison predicates](#comparison-predicate-degree-n)
- [NULL predicates](#null-predicate-degree-n)
- [BETWEEN predicates](#between-predicate-degree-n)
- [IN predicates](#in-predicate-degree-n)
- [OVERLAPS predicate](#overlaps-predicate) (for degree 2 row value expressions only)
See the relevant sections for more details about how to use row value expressions in predicates.
##### Using row value expressions in UPDATE statements
The [UPDATE statement](#update-statement) also supports a variant where row value expressions are updated, rather than single columns. See the relevant section for more details
##### Higher-degree row value expressions
jOOQ chose to explicitly support degrees up to 22 to match Scala 2's typesafe tuple, function and product support. Just like in Scala, 22 is an arbitrary choice of number as good as any other (though, in the case of jOOQ, reasonably high for most SQL queries). Unlike Scala 2, however, jOOQ also supports higher degrees without the additional typesafety.
list = List.of(1, 7);
Condition condition = list
.stream()
.map(BOOK.ID::eq)
.reduce(falseCondition(), Condition::or)
```
If a dialect does not support [boolean column types](#boolean-column), jOOQ will simply generate `1 = 0`.
##### NO condition
If you think that the "left-over" identity that is generated from the above reductions is ugly, you can just use the auxiliary [`DSL.noCondition()`](https://www.jooq.org/javadoc/3.13.x/org/jooq/impl/DSL.html#noCondition()), which acts as a pseudo-identity for both `AND` and `OR`, not generating any SQL, except if the reduction produces nothing (from an empty set), in case of which it will behave like `TRUE`, which is what you usually want when placing a dynamic condition in a `WHERE` clause.
If used with actual data:
```sql
ID = 1
OR
ID = 7
```
```java
List list = List.of(1, 7);
Condition condition = list.stream().map(BOOK.ID::eq)
.reduce(noCondition(), Condition::or)
```
If used with empty sets:
```sql
TRUE
```
```java
List list = List.of(1, 7);
Condition condition = list.stream().map(BOOK.ID::eq)
.reduce(noCondition(), Condition::or)
```
Some additional information about the `noCondition()` and related topics can be found in the section about [dynamic SQL](#dynamic-sql).
` column types in jOOQ's [code generator](#code-generation). But even if your dialect doesn't support the `BOOLEAN` type out of the box, you may have applied a [data type rewrite](#codegen-database-forced-types-rewrite) to force a `TINYINT(1)` or `CHAR(1)` or `NUMBER(1)` column to act as a `BOOLEAN`.
When you have such a column, you will want to use it as a condition, and vice-versa. A [`org.jooq.Field`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Field.html) can be turned into a [`org.jooq.Condition`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Condition.html) using [`DSL.condition(Field)`](https://www.jooq.org/javadoc/3.13.x/org/jooq/impl/DSL.html#condition(Field)). The inverse operation can be achieved using [`DSL.field(Condition)`](https://www.jooq.org/javadoc/3.13.x/org/jooq/impl/DSL.html#field(Condition)):
```java
Condition condition = BOOK.TITLE.eq("Animal Farm");
Field field = field(condition);
// Fetch boolean values from a table
create.select(field).from(BOOK).fetch();
// Use a boolean field as a condition
create.selectFrom(BOOK).where(field).fetch();
```
); // = (some column expression)
eq or equal(Select>); // = (some scalar SELECT statement)
ne or notEqual(T); // <> (some bind value)
ne or notEqual(Field); // <> (some column expression)
ne or notEqual(Select>); // <> (some scalar SELECT statement)
lt or lessThan(T); // < (some bind value)
lt or lessThan(Field); // < (some column expression)
lt or lessThan(Select>); // < (some scalar SELECT statement)
le or lessOrEqual(T); // <= (some bind value)
le or lessOrEqual(Field); // <= (some column expression)
le or lessOrEqual(Select>); // <= (some scalar SELECT statement)
gt or greaterThan(T); // > (some bind value)
gt or greaterThan(Field); // > (some column expression)
gt or greaterThan(Select>); // > (some scalar SELECT statement)
ge or greaterOrEqual(T); // >= (some bind value)
ge or greaterOrEqual(Field); // >= (some column expression)
ge or greaterOrEqual(Select>); // >= (some scalar SELECT statement)
```
Note that every operator is represented by two methods. A verbose one (such as `equal()`) and a two-character one (such as `eq()`). Both methods are the same. You may choose either one, depending on your taste. The manual will always use the more verbose one.
##### jOOQ's convenience methods using comparison operators
In addition to the above, jOOQ provides a few convenience methods for common operations performed on strings using comparison predicates:
```sql
LOWER(TITLE) = LOWER('animal farm')
```
```java
BOOK.TITLE.equalIgnoreCase("animal farm")
```
t1, Field t2);
Condition overlaps(Row2 row);
```
This allows for expressing the above predicates as such:
```java
// The date range tuples version
row(Date.valueOf('2010-01-01'), Date.valueOf('2010-01-03')).overlaps(Date.valueOf('2010-01-02'), Date.valueOf('2010-01-04'))
// The INTERVAL tuples version
row(Date.valueOf('2010-01-01'), new DayToSecond(2)).overlaps(Date.valueOf('2010-01-02'), new DayToSecond(2))
```
##### jOOQ's extensions to the standard
Unlike the standard (or any database implementing the standard), jOOQ also supports the `OVERLAPS` predicate for comparing arbitrary [row vlaue expressions of degree 2](#row-value-expressions). For instance, `(1, 3) OVERLAPS (2, 4)` will yield true in jOOQ. This is emulated as such
```sql
-- This predicate
(A, B) OVERLAPS (C, D)
-- can be emulated as such
(C <= B) AND (A <= D)
```
##### Dialect support
This example using jOOQ:
```java
row(1, 3).overlaps(row(2, 4))
```
Translates to the following dialect specific expressions:
###### All dialects
```sql
(
2 <= 3
AND 1 <= 4
)
```
> [!TIP]
> Generated with jOOQ 3.22. Support in older jOOQ versions may differ. [Translate your own SQL on our website](https://www.jooq.org/translate)
books1 =
DSL.using(configuration)
.selectFrom(BOOK)
.where(condition(book))
.fetch();
// Using the convenience API on DSLContext
Result books2 = DSL.using(configuration).fetchByExample(book);
```
The latter API call makes use of the convenience API [`DSLContext.fetchByExample(TableRecord)`](https://www.jooq.org/javadoc/3.13.x/org/jooq/DSLContext.html#fetchByExample(org.jooq.TableRecord)).
`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Field.html) type, where the `T` type variable is derived from its [`Field.getDataType()`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Typed.html#getDataType()) property. The [`org.jooq.DataType`](https://www.jooq.org/javadoc/3.13.x/org/jooq/DataType.html) API models the rich world of SQL data types. A `DataType` models:
- A database type reference, such as [VARCHAR](#data-type-varchar) or [INTEGER](#data-type-integer)
- A variety of flags modifying the type, such as [precision](#data-type-length), [precision](#data-type-precision), [scale](#data-type-scale), and many more.
- A Java representation of the database type reference, usually denoted as the `T` type variable (for "type"), e.g. [`java.lang.String`](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/lang/String.html) or [`java.lang.Integer`](https://docs.oracle.com/en/java/javase/25/docs/api/java.base/java/lang/Integer.html)
- A Java representation of the user type reference, usually denoted as the `U` type variable (for "user type"), e.g. `Email` if it's a [custom data type](#converted-data-types). By default, this is the same as `T`.
Data types include:
- [Built-in data types](#built-in-data-types): These are types that are supported by the underlying RDBMS directly, such as [VARCHAR](#data-type-varchar) or [INTEGER](#data-type-integer). jOOQ's core library offers support for these out of the box, and can emulate some of them where not supported, e.g. [UUID](#data-type-uuid) is just a `VARCHAR(36)`
- Extended data types: These are also built-in data types, but very dialect specific, such that jOOQ's core library doesn't offer them out of the box.
- [Enum data types](#enum-data-types): Some dialects support a special kind of constraint on [VARCHAR](#data-type-varchar) style types, which allows only for certain enumerated values in the type.
- Domain data types: The SQL standard specifies `DOMAIN` types to be a set of re-usable constraints on an existing data type, which is supported in numerous dialects.
- [User-defined data types](#user-defined-data-types): The SQL standard specifies user-defined types to be a number of refinements on existing types, or the creation of a object type. The latter is what a few ORDBMS, JBDC, and jOOQ support.
- [Converted data types](#converted-data-types): Any data type `T` of the above list can be converted to a user type `U` using a [Converter](#data-type-converter) and/or [Binding](#data-type-binding), in order to change the user representation in Java code.
dayOfWeekType = VARCHAR.asEnumDataType(DayOfWeek.class);
```
And then, the type can be attached to any expression, e.g. if generated code is not available:
```java
// Plain SQL template based
Field f1 = DSL.field("my_table.day_of_week", emailType);
// Name based
Field f2 = DSL.field(name("my_table", "day_of_week"), emailType);
```
` is available for use in expressions of type `PointRecord`, wherever this UDT is referenced.
` converts from some [Built-in data types](#built-in-data-types) to a custom data type. The simplest way to get a `DataType {
// Your conversion logic goes into these two methods, that can convert
// between the database type T and the user type U in both directions:
U from(T databaseObject);
T to(U userObject);
// You need to provide Class instances for each type, too:
Class fromType();
Class toType();
}
```
Or, using a specific example:
```java
record Email(String email) {}
// Using a simpler, functional API:
Converter emailConverter = Converter.of(
String.class,
Email.class,
// Converting String -> Email
Email::new,
// Converting Email -> String
Email::email
);
DataType emailType = VARCHAR.asConvertedDataType(emailConverter);
```
In most practical cases, the `DataType f1 = DSL.field("my_table.my_email_column", emailType);
// Name based
Field f2 = DSL.field(name("my_table", "my_email_column"), emailType);
```
> [!TIP]
> In many cases, converters are single use and single direction only (e.g. fetching only). In those cases, there exists convenience API that is much less verbose to use, see ad-hoc converters for details.
extends Serializable {
// A converter that does the conversion between the database type T
// and the user type U (see previous examples)
Converter converter();
// A callback that generates the SQL string for bind values of this
// binding type. Typically, just ?, but also ?::json, etc.
void sql(BindingSQLContext ctx) throws SQLException;
// Callbacks that implement all interaction with JDBC types, such as
// PreparedStatement, CallableStatement, SQLOutput, SQLinput, ResultSet
void register(BindingRegisterContext ctx) throws SQLException;
void set(BindingSetStatementContext ctx) throws SQLException;
void set(BindingSetSQLOutputContext ctx) throws SQLException;
void get(BindingGetResultSetContext ctx) throws SQLException;
void get(BindingGetStatementContext ctx) throws SQLException;
void get(BindingGetSQLInputContext ctx) throws SQLException;
}
```
Below is full fledged example implementation that uses Google Gson to model JSON documents in Java
```java
// We're binding = JSON (or JSONB), and = JsonElement (user type)
// Alternatively, extend org.jooq.impl.AbstractBinding to implement fewer methods.
public class PostgresJSONGsonBinding implements Binding {
private final Gson gson = new Gson();
// The converter does all the work
@Override
public Converter converter() {
return new Converter() {
@Override
public JsonElement from(JSON t) {
return t == null ? JsonNull.INSTANCE : gson.fromJson(t.data(), JsonElement.class);
}
@Override
public JSON to(JsonElement u) {
return u == null || u == JsonNull.INSTANCE ? null : JSON.json(gson.toJson(u));
}
@Override
public Class fromType() {
return JSON.class;
}
@Override
public Class toType() {
return JsonElement.class;
}
};
}
// Rending a bind variable for the binding context's value and casting it to the json type
@Override
public void sql(BindingSQLContext ctx) throws SQLException {
// Depending on how you generate your SQL, you may need to explicitly distinguish
// between jOOQ generating bind variables or inlined literals.
if (ctx.render().paramType() == ParamType.INLINED)
ctx.render().visit(DSL.inline(ctx.convert(converter()).value())).sql("::json");
else
ctx.render().sql(ctx.variable()).sql("::json");
}
// Registering VARCHAR types for JDBC CallableStatement OUT parameters
@Override
public void register(BindingRegisterContext ctx) throws SQLException {
ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
}
// Converting the JsonElement to a String value and setting that on a JDBC PreparedStatement
@Override
public void set(BindingSetStatementContext ctx) throws SQLException {
JSON json = ctx.convert(converter()).value();
ctx.statement().setString(ctx.index(), json == null ? null : json.data());
}
// Getting a String value from a JDBC ResultSet and converting that to a JsonElement
@Override
public void get(BindingGetResultSetContext ctx) throws SQLException {
ctx.convert(converter()).value(JSON.json(ctx.resultSet().getString(ctx.index())));
}
// Getting a String value from a JDBC CallableStatement and converting that to a JsonElement
@Override
public void get(BindingGetStatementContext ctx) throws SQLException {
ctx.convert(converter()).value(JSON.json(ctx.statement().getString(ctx.index())));
}
// Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
@Override
public void set(BindingSetSQLOutputContext ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
// Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
@Override
public void get(BindingGetSQLInputContext ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
}
```
Just like with simple [Converters](#data-type-converter), a [`org.jooq.Binding`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Binding.html) can be attached to a [column expression](#column-expressions) either directly in the code generator using [forced types](#codegen-database-forced-types) configuration, or explictly, e.g. when constructing [plain SQL](#plain-sql) expressions:
```java
// Construct the DataType:
DataType jsonElementType = JSON.asConvertedDataType(new PostgresJSONGsonBinding());
// Plain SQL template based
Field f1 = DSL.field("my_table.my_json_column", jsonElementType);
// Name based
Field f2 = DSL.field(name("my_table", "my_json_column"), jsonElementType);
```
field(String sql);
Field field(String sql, Object... bindings);
Field field(String sql, QueryPart... parts);
// A field with a known data type
Field field(String sql, Class type);
Field field(String sql, Class type, Object... bindings);
Field field(String sql, Class type, QueryPart... parts);
Field field(String sql, DataType type);
Field field(String sql, DataType type, Object... bindings);
Field field(String sql, DataType type, QueryPart... parts);
// A field with a known name (properly escaped)
Field field(Name name);
Field field(Name name, Class type);
Field field(Name name, DataType type);
// A function
Field function(String name, Class type, Field... arguments);
Field function(String name, DataType type, Field... arguments);
// A table
Table table(String sql);
Table table(String sql, Object... bindings);
Table table(String sql, QueryPart... parts);
// A table with a known name (properly escaped)
Table table(Name name);
// A query without results (update, insert, etc)
Query query(String sql);
Query query(String sql, Object... bindings);
Query query(String sql, QueryPart... parts);
// A query with results
ResultQuery resultQuery(String sql);
ResultQuery resultQuery(String sql, Object... bindings);
ResultQuery resultQuery(String sql, QueryPart... parts);
// A query with results. This is the same as resultQuery(...).fetch();
Result fetch(String sql);
Result fetch(String sql, Object... bindings);
Result fetch(String sql, QueryPart... parts);
```
Apart from the general factory methods, plain SQL is also available in various other contexts for convenience. For instance, when adding a `.where("a = b")` clause to a query. Hence, there exist several convenience methods where plain SQL can be inserted usefully. This is an example displaying all various use-cases in one single query:
```java
// You can use your table aliases in plain SQL fields
// As long as that will produce syntactically correct SQL
Field LAST_NAME = field("a.LAST_NAME");
// You can alias your plain SQL fields
Field COUNT1 = field("count(*) x");
// If you know a reasonable Java type for your field, you
// can also provide jOOQ with that type
Field COUNT2 = field("count(*) y", Integer.class);
// Use plain SQL as select fields
create.select(LAST_NAME, COUNT1, COUNT2)
// Use plain SQL as aliased tables (be aware of syntax!)
.from("author a")
.join("book b")
// Use plain SQL for conditions both in JOIN and WHERE clauses
.on("a.id = b.author_id")
// Bind a variable in plain SQL
.where("b.title != ?", "Brida")
// Use plain SQL again as fields in GROUP BY and ORDER BY clauses
.groupBy(LAST_NAME)
.orderBy(LAST_NAME)
.fetch();
```
##### Important things to note about plain SQL!
There are some important things to keep in mind when using plain SQL:
- You have to provide something that will be syntactically correct. If it's not, then jOOQ won't know. Only your JDBC driver or your RDBMS will detect the syntax error.
- You have to provide consistency when you use variable binding. The number of ? must match the number of variables
- Your SQL is inserted into jOOQ queries without further checks. Hence, jOOQ can't prevent SQL injection or transform your SQL string in any way.
count = val(3);
Field string = val("abc");
field("replace(substr(quote(zeroblob(({0} + 1) / 2)), 3, {0}), '0', {1})", String.class, count, string);
// ^ ^ ^ ^^^^^ ^^^^^^
// | | | | |
// argument "count" is repeated twice: \------------------+----------|---------------------/ |
// argument "string" is used only once: \-----------------------------/
```
For convenience, there is also a [`DSL.list(QueryPart...)`](https://www.jooq.org/javadoc/3.13.x/org/jooq/impl/DSL.html#list(org.jooq.QueryPart...)) API that allows for wrapping a comma-separated list of query parts in a single template argument:
```java
Field a = val("a");
Field b = val("b");
Field c = val("c");
// These two produce the same result:
condition("my_column IN ({0}, {1}, {2})", a, b, c); // Using distinct template arguments
condition("my_column IN ({0})", list(a, b, c)); // Using a single template argument
```
##### Parsing rules
When processing these plain SQL templates, a mini parser is run that handles things like
- String literals like `'abc'` or `$$abc$$`
- Quoted names like `"name"`, `` `name` ``, or `[name]`
- Comments like `/* block */` or `-- line`
- JDBC escape sequences like `{d '2000-01-01'}`
- Indexed (`?`) or named (`:identifier`) bind variable placeholders
The above are recognised by the templating engine and contents inside of them are ignored when replacing numbered placeholders and/or bind variables. For instance:
```java
query(
"""
SELECT /* In a comment, this is not a placeholder: {0}. And this is not a bind variable: ? */ title AS `title {1} ?`
-- Another comment without placeholders: {2} nor bind variables: ?
FROM book
WHERE title = 'In a string literal, this is not a placeholder: {3}. And this is not a bind variable: ?'
"""
);
```
The above query does not contain any numbered placeholders nor bind variables, because the tokens that would otherwise be searched for are contained inside of comments, string literals, or quoted names.
Specify the output keyword style (org.jooq.conf.RenderKeywordStyle)
-i / --identifier Specify the output identifier style (org.jooq.conf.RenderNameStyle)
-F / --from-dialect Specify the input dialect (org.jooq.SQLDialect)
-T / --to-dialect Specify the output dialect (org.jooq.SQLDialect)
-s / --sql Specify the input SQL string
-I / --interactive Enter interactive mode
$ java -cp jooq-3.13.6.jar:reactive-streams-1.0.2.jar org.jooq.ParserCLI -T ORACLE -s "SELECT substring('abcde', 2, 3)"
select substr('abcde', 2, 3) from dual;
```
**Windows users:** Please replace `:` by `;` in the above examples.
Another way to use this API is the [https://www.jooq.org/translate](https://www.jooq.org/translate) website.
Specify the output keyword style (org.jooq.conf.RenderKeywordStyle)
-i / --identifier Specify the output identifier style (org.jooq.conf.RenderNameStyle)
-F / --from-dialect Specify the input dialect (org.jooq.SQLDialect)
-T / --to-dialect Specify the output dialect (org.jooq.SQLDialect)
-1 / --sql1 Specify the input SQL string 1 (from SQL)
-2 / --sql2 Specify the input SQL string 2 (to SQL)
$ java -cp jooq-3.13.6.jar:reactive-streams-1.0.2.jar org.jooq.DiffCLI -T POSTGRES \
-1 "create table t (i int);" \
-2 "create table t (i int, j int)"
alter table t add j int null;
```
**Windows users:** Please replace `:` by `;` in the above examples.
Another way to use this API is the [https://www.jooq.org/diff](https://www.jooq.org/diff) website.
values = select.getBindValues();
assertEquals(2, values.size());
assertEquals(5, values.get(0));
assertEquals("Animal Farm", values.get(1));
```
For more details about jOOQ's internals, see the manual's section about [QueryParts](#queryparts).
type information:
Param param2 = param("lastName", "Poe");
Query query2 = create.select().from(AUTHOR).where(LAST_NAME.eq(param2));
```
The [`org.jooq.Query`](https://www.jooq.org/javadoc/3.13.x/org/jooq/Query.html) interface also allows for setting new bind values directly, without accessing the Param type:
```java
Query query1 = create.select().from(AUTHOR).where(LAST_NAME.eq("Poe"));
query1.bind(1, "Orwell");
// Or, with named parameters
Query query2 = create.select().from(AUTHOR).where(LAST_NAME.eq(param("lastName", "Poe")));
query2.bind("lastName", "Orwell");
```
In order to actually render named parameter names in generated SQL, use the [`DSLContext.renderNamedParams()`](https://www.jooq.org/javadoc/3.13.x/org/jooq/DSLContext.html#renderNamedParams(org.jooq.QueryPart)) method:
```sql
-- The named bind variable can be rendered
SELECT *
FROM AUTHOR
WHERE LAST_NAME = :lastName
```
```java
create.renderNamedParams(
create.select()
.from(AUTHOR)
.where(LAST_NAME.eq(
param("lastName", "Poe"))));
```
implements Field {}
public abstract class CustomCondition implements Condition {}
public abstract class CustomTable> implements Table {}
public abstract class CustomRecord> implements TableRecord {}
```
##### An example for implementing a custom table and its record.
Here's an example [`org.jooq.impl.CustomTable`](https://www.jooq.org/javadoc/3.13.x/org/jooq/impl/CustomTable.html) showing how to create a custom table with its field definitions, similar to what the code generator is doing.
```java
public class BookTable extends CustomTable {
public static final BookTable BOOK = new BookTable();
public final TableField FIRST_NAME = createField(name("FIRST_NAME"), VARCHAR);
public final TableField