jOOQ

https://www.jooq.org/doc/3.21/manual/
Admin
jOOQ is a Java-based SQL builder and executor that enables developers to build type-safe SQL queries...
Tokens:1,282,990
Snippets:10,850
Trust Score:10
Update:1 week ago
Show doc for...
Tokens:
Raw
### POSITION (binary) example with SQL and jOOQ

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/binary-functions/binary-position-function

Shows a SQL example of POSITION with a binary string (casting to bytea) and the equivalent jOOQ API using binaryPosition. The SQL example includes a third argument to start the search at position 4. The jOOQ example fetches the result.

```sql
SELECT
  position(cast('hello' as bytea), cast('e' as bytea)),
  position(cast('hello' as bytea), cast('l' as bytea), 4);
```

```java
create.select(
  binaryPosition("hello".getBytes(), "e".getBytes()),
  binaryPosition("hello".getBytes(), "e".getBytes(), 4)).fetch();
```

--------------------------------

### SQL example using LEVEL with CONNECT BY

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/connect-by-functions/level-function

SQL query that selects child, parent, sys_connect_by_path, and level from a VALUES table, using START WITH and CONNECT BY NOCYCLE to traverse the hierarchy.

```sql
SELECT
  child,
  parent,
  sys_connect_by_path(child, '/'),
  level
FROM (
  VALUES
    (1, null),
    (2, 1),
    (3, 2),
    (4, 1)
) AS t (child, parent)
START WITH
  parent IS NULL
CONNECT BY NOCYCLE
  PRIOR child = parent;
```

--------------------------------

### Result of the example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/datetime-functions/localdatesub-function

Shows the expected output table from the example.

```text
+------------+
| date_sub   |
+------------+
| 2020-02-01 |
+------------+
```

--------------------------------

### SQL example of POSITION()

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/position-function

Shows the SQL syntax for POSITION() with two calls: one finding 'e' in 'hello' and another finding 'l' starting from position 4.

```sql
SELECT
  position('hello', 'e'),
  position('hello', 'l', 4);
```

--------------------------------

### jOOQ useIndex() method example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/hints/hints-mysql/hints-mysql-index

jOOQ DSL example using the useIndex() method on a table to apply the same index hint as the SQL example.

```java
create.selectFrom(BOOK.useIndex("i_book_a"))
      .fetch()
```

--------------------------------

### Result of localDateTimeSub example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/datetime-functions/localdatetimesub-function

Shows the expected output of the example query.

```text
+---------------------+
| timestamp_sub       |
+---------------------+
| 2020-02-01 15:30:45 |
+---------------------+
```

--------------------------------

### jOOQ ORDER BY example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/sql-statements/select-statement/order-by-clause

jOOQ DSL example equivalent to the SQL example, using asc() and desc() methods on column expressions to create SortFields.

```java
create.select(BOOK.AUTHOR_ID, BOOK.TITLE)
      .from(BOOK)
      .orderBy(BOOK.AUTHOR_ID.asc(), BOOK.TITLE.desc())
      .fetch();
```

--------------------------------

### Using a DAO

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/daos

Example showing how to initialize a DAO with a Configuration and perform CRUD operations.

```APIDOC
## Using a DAO

### Description
Example usage of a generated DAO class.

### Steps
1. Initialize a `Configuration` with a connection and SQL dialect.
2. Create a DAO instance with the configuration.
3. Use the DAO methods to find, update, and delete POJOs.

### Example Code
```java
// Initialise a Configuration
Configuration configuration = new DefaultConfiguration().set(connection).set(SQLDialect.ORACLE);

// Initialise the DAO with the Configuration
BookDao bookDao = new BookDao(configuration);

// Start using the DAO
Book book = bookDao.findById(5);

// Modify and update the POJO
book.setTitle("1984");
book.setPublishedIn(1948);
bookDao.update(book);

// Delete it again
bookDao.delete(book);
```
```

--------------------------------

### jOOQ example of LENGTH function

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/length-function

Example jOOQ query using the length function to get the length of the string 'hello'.

```java
create.select(length("hello")).fetch();
```

--------------------------------

### Command-line example for Windows (java -cp with jOOQ jars)

Source: https://www.jooq.org/doc/3.21/manual/code-generation/codegen-configuration

Runs jOOQ code generation from the command line. Requires the XML configuration file, jooq*.jar files, and the JDBC driver in the same directory (e.g. C:\temp\jooq). The classpath uses semicolons on Windows; OSX/Linux will be similar.

```bash
java -cp jooq-3.21.9.jar;jooq-meta-3.21.9.jar;jooq-codegen-3.21.9.jar;reactive-streams-1.0.3.jar;r2dbc-spi-1.0.0.RELEASE.jar;[JDBC-driver].jar org.jooq.codegen.GenerationTool <XML-file>
```

--------------------------------

### SQL example of LENGTH function

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/length-function

Example SQL query using the LENGTH function to get the length of the string 'hello'.

```sql
SELECT length('hello');
```

--------------------------------

### SQL example of PRODUCT()

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/aggregate-functions/product-function

SQL query using the PRODUCT() aggregate function on the BOOK.ID column.

```sql
SELECT product(ID)
FROM BOOK
```

--------------------------------

### SQL example of LEFT function

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/left-function

Example SQL query using LEFT to get the first 5 characters of 'hello world'.

```sql
SELECT left('hello world', 5);
```

--------------------------------

### jOOQ ST_Dimension example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/spatial-functions/st-dimension-function

Example of using the jOOQ ST_Dimension function with ST_GeomFromText to get dimensions of point, linestring, and polygon geometries.

```java
create.select(
  stDimension(stGeomFromText("POINT (0 0)")),
  stDimension(stGeomFromText("LINESTRING (0 0, 1 1)")),
  stDimension(stGeomFromText("POLYGON ((0 0, 1 1, 1 0, 0 0))"))
)).fetch();
```

--------------------------------

### PARTITION BY SQL and jOOQ DSL

Source: https://www.jooq.org/doc/3.21/manual/sql-building/table-expressions/joined-tables/join-mode-partition-by

Examples showing the SQL syntax and the corresponding jOOQ DSL usage for the PARTITION BY clause.

```sql
SELECT *
FROM AUTHOR
LEFT OUTER JOIN BOOK
PARTITION BY (PUBLISHED_IN)
ON BOOK.AUTHOR_ID = AUTHOR.ID
```

```java
create.select()
      .from(AUTHOR)
      .leftOuterJoin(BOOK)
      .partitionBy(BOOK.PUBLISHED_IN)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();
```

--------------------------------

### SQL ORDER BY example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/sql-statements/select-statement/order-by-clause

SQL example showing how to order results by AUTHOR_ID ascending and TITLE descending.

```sql
SELECT AUTHOR_ID, TITLE
FROM BOOK
ORDER BY AUTHOR_ID ASC, TITLE DESC
```

--------------------------------

### jOOQ ST_StartPoint usage example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/spatial-functions/st-startpoint-function

Example of using the jOOQ stStartPoint function with a linestring geometry. The result is a point representing the start of the linestring.

```java
create.select(stStartPoint(stGeomFromText("LINESTRING (0 0, 1 1, 2 0, 3 1)"))).fetch();
```

--------------------------------

### jOOQ example of OVERLAY function

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/overlay-function

Example jOOQ query using the overlay() method to overlay 'xxx' onto 'abcdefg' starting at position 2.

```java
create.select(overlay(val("abcdefg"), "xxx", 2)).fetch();
```

--------------------------------

### SQL example of OVERLAY function

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/overlay-function

Example SQL query using the OVERLAY function to overlay 'xxx' onto 'abcdefg' starting at position 2.

```sql
SELECT overlay('abcdefg', 'xxx', 2);
```

--------------------------------

### jOOQ example of product()

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/aggregate-functions/product-function

jOOQ API call using the product() function on BOOK.ID.

```java
create.select(product(BOOK.ID))
      .from(BOOK)
```

--------------------------------

### Configure SQL Interpreter Settings

Source: https://www.jooq.org/doc/3.21/manual/sql-building/dsl-context/custom-settings/settings-interpreter

Example showing how to instantiate and configure the Settings object for the SQL Interpreter API.

```java
Settings settings = new Settings()
    .withInterpreterDialect(H2)                           // Defaults to DEFAULT
    .withInterpreterDelayForeignKeyDeclarations(true)     // Defaults to false
    .withInterpreterLocale(Locale.forLanguageTag("de"))   // Defaults to Locale.getDefault()
    .withInterpreterQuotedNames(ALWAYS)                   // Defaults to EXPLICIT_DEFAULT_QUOTED
    .withInterpreterNameLookupCaseSensitivity(NEVER)      // Defaults to WHEN_QUOTED
    .withInterpreterSearchPath(...)                       // Defaults to an empty list
    .withInterpreterWithMetaLookups(IGNORE_ON_FAILURE);   // Defaults to THROW_ON_FAILURE
```

--------------------------------

### SQL example of OCTET_LENGTH

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/octet-length-function

SQL query using OCTET_LENGTH to get the byte length of the string 'hello'.

```sql
SELECT octet_length('hello');
```

--------------------------------

### APPLY SQL and jOOQ DSL Example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/table-expressions/joined-tables/join-mode-apply

Demonstrates the SQL syntax for CROSS APPLY and the corresponding jOOQ DSL implementation.

```sql
SELECT *
FROM
  AUTHOR

  -- All previous objects (i.e. AUTHOR)
  -- are now in scope for the following subquery
  CROSS APPLY (
    SELECT count(*)
    FROM BOOK
    WHERE BOOK.AUTHOR_ID = AUTHOR.ID -- AUTHOR is in scope
  );
```

```java
DSL.using(configuration)
   .select()
   .from(
      AUTHOR
      .crossApply(
        select(count()
        .from(BOOK)
        .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
      )
   )
   .fetch();
```

--------------------------------

### Basic jOOQ application in Kotlin

Source: https://www.jooq.org/doc/3.21/manual/getting-started/jooq-and-kotlin

A complete example showing how to initialize a jOOQ context and execute a join query using Kotlin syntax.

```kotlin
package org.jooq.example.kotlin

import java.util.Properties

import org.jooq.*
import org.jooq.impl.DSL
import org.jooq.impl.DSL.*

import org.jooq.example.db.h2.Tables.*

fun main(args: Array<String>) {

    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]}")
           }
    }
}
```

--------------------------------

### jOOQ example of OCTET_LENGTH

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/octet-length-function

jOOQ API call using octetLength to get the byte length of the string 'hello'.

```java
create.select(octetLength("hello")).fetch();
```

--------------------------------

### SQL example of MAX_BY()

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/aggregate-functions/max-by-function

SQL query using MAX_BY() to get the AUTHOR_ID at the row with the maximum ID from the BOOK table.

```sql
SELECT max_by(AUTHOR_ID, ID)
FROM BOOK
```

--------------------------------

### Configure Optimistic Locking Settings

Source: https://www.jooq.org/doc/3.21/manual/sql-building/dsl-context/custom-settings/settings-optimistic-locking

Example showing how to instantiate and configure the Settings object with various optimistic locking flags.

```java
Settings settings = new Settings()
    .withUpdateRecordVersion(true)                              // Defaults to true
    .withUpdateRecordTimestamp(true)                            // Defaults to true
    .withExecuteWithOptimisticLocking(true)                     // Defaults to false
    .withExecuteWithOptimisticLockingExcludeUnversioned(false); // Defaults to false
```

--------------------------------

### SQL example: SELECT chr(64)

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/chr-function

Shows the SQL usage of the CHR() function to get the character for ASCII code 64.

```sql
SELECT chr(64);
```

--------------------------------

### LEAST function example with output

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/numeric-functions/least-function

Shows the SQL query, the jOOQ equivalent, and the resulting output table for the LEAST function with two arguments.

```sql
SELECT least(2, 3);
```

```java
create.select(least(2, 3)).fetch();
```

```text
+-------+
| least |
+-------+
|     2 |
+-------+
```

--------------------------------

### Using jOOQ-generated DAOs

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/fetching/pojos

Demonstrates initializing a DAO with a configuration and performing basic CRUD operations on a POJO.

```java
// Initialise a Configuration
Configuration configuration = new DefaultConfiguration().set(connection).set(SQLDialect.ORACLE);

// Initialise the DAO with the Configuration
BookDao bookDao = new BookDao(configuration);

// Start using the DAO
Book book = bookDao.findById(5);

// Modify and update the POJO
book.setTitle("1984");
book.setPublishedIn(1948);
bookDao.update(book);

// Delete it again
bookDao.delete(book);
```

--------------------------------

### Cast to INTERVAL using jOOQ

Source: https://www.jooq.org/doc/3.21/manual/sql-building/data-types/built-in-data-types/data-type-interval

jOOQ DSL example for casting a field to INTERVAL. This is the source expression that gets translated to dialect-specific cast expressions.

```java
cast(field("c"), INTERVAL)
```

--------------------------------

### Cast field to BOOLEAN using jOOQ

Source: https://www.jooq.org/doc/3.21/manual/sql-building/data-types/built-in-data-types/data-type-boolean

jOOQ example for casting a field to BOOLEAN. This is the source expression that gets translated to dialect-specific cast expressions.

```java
cast(field("c"), BOOLEAN)
```

--------------------------------

### jOOQ API example using mergeJoin()

Source: https://www.jooq.org/doc/3.21/manual/sql-building/table-expressions/joined-tables/join-hints/join-hint-merge

Shows the jOOQ DSL equivalent of the SQL example, using mergeJoin() and on() methods.

```java
create.select()
      .from(AUTHOR)
      .mergeJoin(BOOK)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();
```

--------------------------------

### jOOQ example: create.select(chr(65)).fetch()

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/chr-function

Shows the jOOQ API usage of the chr() function to get the character for ASCII code 65.

```java
create.select(chr(65)).fetch();
```

--------------------------------

### jOOQ example using level() with CONNECT BY

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/connect-by-functions/level-function

jOOQ code that builds the equivalent query using the DSL, with values, startWith, connectByNoCycle, and prior.

```java
Field<Integer> child = field("child", INTEGER);
Field<Integer> 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();
```

--------------------------------

### Configure listener invocation order with Settings

Source: https://www.jooq.org/doc/3.21/manual/sql-building/dsl-context/custom-settings/settings-listener-invocation-order

Use this configuration to override the invocation order of 'start' and 'end' events for each listener type. The example sets start events to DEFAULT and end events to REVERSE for TransactionListener, VisitListener, RecordListener, and ExecuteListener. All settings default to DEFAULT if not specified.

```java
Settings settings = new Settings()
    .withTransactionListenerStartInvocationOrder(DEFAULT) // Defaults to DEFAULT
    .withTransactionListenerEndInvocationOrder(REVERSE)   // Defaults to DEFAULT
    .withVisitListenerStartInvocationOrder(DEFAULT)       // Defaults to DEFAULT
    .withVisitListenerEndInvocationOrder(REVERSE)         // Defaults to DEFAULT
    .withRecordListenerStartInvocationOrder(DEFAULT)      // Defaults to DEFAULT
    .withRecordListenerEndInvocationOrder(REVERSE)        // Defaults to DEFAULT
    .withExecuteListenerStartInvocationOrder(DEFAULT)     // Defaults to DEFAULT
    .withExecuteListenerEndInvocationOrder(REVERSE);      // Defaults to DEFAULT
```

--------------------------------

### jOOQ example using selectFrom() with mergeJoin()

Source: https://www.jooq.org/doc/3.21/manual/sql-building/table-expressions/joined-tables/join-hints/join-hint-merge

Shows a more concise jOOQ example using selectFrom() and mergeJoin().

```java
selectFrom(AUTHOR.mergeJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
```

--------------------------------

### Create table with BOOLEAN column using jOOQ

Source: https://www.jooq.org/doc/3.21/manual/sql-building/data-types/built-in-data-types/data-type-boolean

jOOQ example for creating a table with a BOOLEAN column. This is the source expression that gets translated to dialect-specific DDL.

```java
createTable("t").column("c", BOOLEAN)
```

--------------------------------

### Implementing a custom table and record

Source: https://www.jooq.org/doc/3.21/manual/sql-building/queryparts/custom-queryparts

An example showing how to create a custom table and its associated record by extending CustomTable and CustomRecord.

```java
public class BookTable extends CustomTable<BookRecord> {
    public static final BookTable BOOK = new BookTable();

    public final TableField<BookRecord, String> FIRST_NAME = createField(name("FIRST_NAME"), VARCHAR);
    public final TableField<BookRecord, String> UNMATCHED  = createField(name("UNMATCHED"), VARCHAR);
    public final TableField<BookRecord, String> LAST_NAME  = createField(name("LAST_NAME"), VARCHAR);
    public final TableField<BookRecord, Short>  ID         = createField(name("ID"), SMALLINT);
    public final TableField<BookRecord, String> TITLE      = createField(name("TITLE"), VARCHAR);

    protected BookTable() {
        super(name("BOOK"));
    }

    @Override
    public Class<? extends BookRecord> getRecordType() {
        return BookRecord.class;
    }
}

public class BookRecord extends CustomRecord<BookRecord> {
    protected BookRecord() {
        super(BookTable.BOOK);
    }
}
```

--------------------------------

### Create table with INTERVAL column using jOOQ

Source: https://www.jooq.org/doc/3.21/manual/sql-building/data-types/built-in-data-types/data-type-interval

jOOQ DSL example for creating a table with an INTERVAL column. This is the source expression that gets translated to dialect-specific DDL.

```java
createTable("t").column("c", INTERVAL)
```

--------------------------------

### Example record and corresponding SQL

Source: https://www.jooq.org/doc/3.21/manual/sql-building/conditional-expressions/query-by-example

An illustration of how an example record with specific fields set translates into a SQL WHERE clause.

```sql
-- example book record:
ID          :
AUTHOR_ID   : 1
TITLE       :
PUBLISHED_IN: 1970
LANGUAGE_ID : 1
```

```sql
-- Corresponding query
SELECT *
FROM book
WHERE author_id = 1
AND published_in = 1970
AND language_id = 1
```

--------------------------------

### SQL Server OPTION hint example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/hints/hints-sql-server/hints-sql-server-option

Demonstrates the use of the OPTION hint in a raw SQL query and the corresponding jOOQ DSL usage.

```sql
SELECT field1, field2
FROM table1
OPTION (OPTIMIZE FOR UNKNOWN)
```

```java
create.select(field1, field2)
      .from(table1)
      .option("OPTION (OPTIMIZE FOR UNKNOWN)")
      .fetch()
```

--------------------------------

### SQL: SELECT DISTINCT ip FROM access_log

Source: https://www.jooq.org/doc/3.21/manual/reference/dont-do-this/dont-do-this-sql-select-distinct

Simple example of using SELECT DISTINCT to get distinct IP addresses from a log table. Useful for ad-hoc querying.

```sql
SELECT DISTINCT ip FROM access_log
```

--------------------------------

### Result of SECOND function example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/datetime-functions/second-function

Shows the expected output of the previous SQL and jOOQ examples.

```text
+--------+
| second |
+--------+
|     45 |
+--------+
```

--------------------------------

### jOOQ DSL: alterSequence("s").startWith(10)

Source: https://www.jooq.org/doc/3.21/manual/sql-building/ddl-statements/alter-statement/alter-sequence-statement/alter-sequence-start-with

The jOOQ DSL expression for altering a sequence's start value. This is the API call used in the Java example.

```java
alterSequence("s").startWith(10)
```

--------------------------------

### Configuration example for ParamType

Source: https://www.jooq.org/doc/3.21/manual/sql-building/dsl-context/custom-settings/settings-parameter-type

Shows how to set the parameter type to NAMED using Settings, with a comment noting that the default is INDEXED.

```java
Settings settings = new Settings()
    .withParamType(ParamType.NAMED); // Defaults to INDEXED
```

--------------------------------

### Warning log for discouraged static type registry usage

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/fetching/data-type-lookups

Example warning log output when relying on the static type registry for user-defined data types, which is discouraged starting from jOOQ 3.19.

```text
org.jooq.impl.DefaultDataType$DiscouragedStaticTypeRegistryUsage: null
    at org.jooq.impl.DefaultDataType.check(DefaultDataType.java)
    at org.jooq.impl.Val.accept(Val.java)
    at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java)
    at org.jooq.impl.AbstractBindContext.visit0(AbstractBindContext.java)
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java)
    at ...
```

--------------------------------

### Example result set for LEVEL query

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/connect-by-functions/level-function

Sample output table showing the result of the example query, with columns child, parent, sys_connect_by_path, and level.

```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 |
+-------+--------+---------------------+-------+
```

--------------------------------

### Get fields from AUTHOR dynamically without type safety

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/table-columns/dereferenced-table-columns

Example showing how to dereference table columns using the field() method on a table, retrieving fields by name without type safety.

```java
// Get fields from AUTHOR dynamically, without type safety:
Field<?> id = AUTHOR.field("ID");
Field<?> firstName = AUTHOR.field("FIRST_NAME");
```

--------------------------------

### LEAD example output

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/window-functions/window-lead

Output of the LEAD example query. Shows the values returned for each row with the default offset, offset 2, and offset 2 with default -1.

```text
+----+------+------+------+
| id | lead | lead | lead |
+----+------+------+------+
|  1 |    2 |    3 |    3 |
|  2 |    3 |    4 |    4 |
|  3 |    4 |      |   -1 |
|  4 |      |      |   -1 |
+----+------+------+------+
```

--------------------------------

### jOOQ createSequence startWith example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/ddl-statements/create-statement/create-sequence-statement/create-sequence-start-with

Use createSequence("s").startWith(1000) to define a sequence starting at 1000 instead of the default 1. This is useful when introducing a sequence to a table with existing data.

```java
createSequence("s").startWith(1000)
```

--------------------------------

### SQL example of WIDTH_BUCKET

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/numeric-functions/width-bucket-function

Shows the SQL syntax for WIDTH_BUCKET with three values (0, 15, 99) over the range 0 to 100 with 10 buckets.

```sql
SELECT
  width_bucket(0 , 0, 100, 10),
  width_bucket(15, 0, 100, 10),
  width_bucket(99, 0, 100, 10);
```

--------------------------------

### Dialect-specific SQL translations for CREATE SEQUENCE .. MAXVALUE

Source: https://www.jooq.org/doc/3.21/manual/sql-building/ddl-statements/create-statement/create-sequence-statement/create-sequence-maxvalue

Shows the SQL generated for the jOOQ example across different database dialects. The SQLServer variant includes START WITH 1, while many other dialects are unsupported.

```sql
CREATE SEQUENCE s MAXVALUE 10
```

```sql
CREATE SEQUENCE s START WITH 1 MAXVALUE 10
```

```sql
/* UNSUPPORTED */
```

--------------------------------

### jOOQ WITH hint with multiple options

Source: https://www.jooq.org/doc/3.21/manual/sql-building/hints/hints-sql-server/hints-sql-server-table

Shows a jOOQ example using multiple hint options (ROWLOCK, UPDLOCK) and its dialect-specific translations.

```java
selectFrom(BOOK.with("ROWLOCK, UPDLOCK"))
```

```sql
SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  BOOK.TITLE,
  BOOK.PUBLISHED_IN,
  BOOK.LANGUAGE_ID
FROM BOOK
FOR UPDATE
```

```sql
SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  BOOK.TITLE,
  BOOK.PUBLISHED_IN,
  BOOK.LANGUAGE_ID
FROM BOOK
FOR UPDATE OF BOOK
```

```sql
SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  BOOK.TITLE,
  BOOK.PUBLISHED_IN,
  BOOK.LANGUAGE_ID
FROM BOOK
FOR UPDATE OF ID, AUTHOR_ID, TITLE, PUBLISHED_IN, LANGUAGE_ID
```

```sql
SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  BOOK.TITLE,
  BOOK.PUBLISHED_IN,
  BOOK.LANGUAGE_ID
FROM BOOK
FOR UPDATE OF BOOK WITH LOCK
```

```sql
SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  BOOK.TITLE,
  BOOK.PUBLISHED_IN,
  BOOK.LANGUAGE_ID
FROM BOOK
FOR UPDATE OF BOOK.ID, BOOK.AUTHOR_ID, BOOK.TITLE, BOOK.PUBLISHED_IN, BOOK.LANGUAGE_ID
```

```sql
SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  BOOK.TITLE,
  BOOK.PUBLISHED_IN,
  BOOK.LANGUAGE_ID
FROM BOOK WITH (ROWLOCK, UPDLOCK)
```

```sql
/* UNSUPPORTED */
```

--------------------------------

### SQL example using CONNECT_BY_ISCYCLE

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/connect-by-functions/connect-by-iscycle-function

SQL query that uses CONNECT_BY_ISCYCLE to detect cycles in a hierarchical data set. The query starts with rows where parent is null and uses CONNECT BY NOCYCLE with PRIOR child = parent.

```sql
SELECT
  child,
  parent,
  sys_connect_by_path(child, '/'),
  connect_by_iscycle
FROM (
  VALUES
    (1, null),
    (2, 1),
    (3, 2),
    (2, 3)
) AS t (child, parent)
START WITH
  parent IS NULL
CONNECT BY NOCYCLE
  PRIOR child = parent;
```

--------------------------------

### Using orderSiblingsBy() with CONNECT BY

Source: https://www.jooq.org/doc/3.21/manual/sql-building/sql-statements/select-statement/order-by-clause/order-siblings-by

Example of using jOOQ's orderSiblingsBy() method to order siblings in a hierarchical query. The query selects a path using sysConnectByPath(), starts with a root condition, connects by a prior relationship, and orders siblings by the DIRECTORY.LABEL column.

```java
create.select(sysConnectByPath(DIRECTORY.LABEL, "/").as("dir"))
      .from(DIRECTORY)
      .startWith(DIRECTORY.PARENT_ID.isNull())
      .connectBy(prior(DIRECTORY.ID).eq(DIRECTORY.PARENT_ID))
      .orderSiblingsBy(DIRECTORY.LABEL)
      .fetch();
```

--------------------------------

### MockConnection setup with MockDataProvider

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/mocking-connection

Initializes a MockDataProvider and MockConnection, passes the connection to a DSLContext, and executes a query. The provider is implemented further down in the source.

```java
// Initialise your data provider (implementation further down):
MockDataProvider provider = new MyProvider();
MockConnection connection = new MockConnection(provider);

// Pass the mock connection to a jOOQ DSLContext:
DSLContext create = DSL.using(connection, SQLDialect.ORACLE);

// Execute queries transparently, with the above DSLContext:
Result<BookRecord> result = create.selectFrom(BOOK).where(BOOK.ID.eq(5)).fetch();
```

--------------------------------

### Translated SQL for supported dialects

Source: https://www.jooq.org/doc/3.21/manual/sql-building/ddl-statements/create-statement/create-sequence-statement/create-sequence-start-with

The jOOQ example translates to this SQL for dialects that support START WITH: Aurora Postgres, CockroachDB, DB2, DuckDB, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, Oracle, Postgres, SQLServer, Snowflake, Sybase, Vertica, and YugabyteDB.

```sql
CREATE SEQUENCE s START WITH 1000
```

--------------------------------

### Using a generated DAO

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/daos

Shows how to initialize a Configuration, create a BookDao, and use it to find, modify, update, and delete a Book POJO.

```java
// Initialise an Configuration
Configuration configuration = new DefaultConfiguration().set(connection).set(SQLDialect.ORACLE);

// Initialise the DAO with the Configuration
BookDao bookDao = new BookDao(configuration);

// Start using the DAO
Book book = bookDao.findById(5);

// Modify and update the POJO
book.setTitle("1984");
book.setPublishedIn(1948);
bookDao.update(book);

// Delete it again
bookDao.delete(book);
```

--------------------------------

### Maven plugin configuration for jOOQ code generation

Source: https://www.jooq.org/doc/3.21/manual/code-generation/codegen-configuration

Configure the jOOQ-codegen-maven plugin in your pom.xml to generate sources during the generate phase. The example uses PostgreSQL and specifies the JDBC connection and generator target. Note that only the Open Source Edition is on Maven Central; commercial editions require local installation or access from repo.jooq.org.

```xml
<plugin>

  <!-- Specify the maven code generator plugin -->
  <!-- Use org.jooq                for the Open Source Edition
           org.jooq.pro            for commercial editions with Java 17 support,
           org.jooq.pro-java-11    for commercial editions with Java 11 support,
           org.jooq.pro-java-8     for commercial editions with Java 8 support,
           org.jooq.trial          for the free trial edition with Java 17 support,
           org.jooq.trial-java-11  for the free trial edition with Java 11 support,
           org.jooq.trial-java-8   for the free trial edition with Java 8 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 -->
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>3.21.9</version>

  <!-- The plugin should hook into the generate goal -->
  <executions>
    <execution>
      <goals>
        <goal>generate</goal>
      </goals>
    </execution>
  </executions>

  <!-- Manage the plugin's dependency. In this example, we'll use a PostgreSQL database -->
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.7.3</version>
    </dependency>
  </dependencies>

  <!-- Specify the plugin configuration.
       The configuration format is the same as for the standalone code generator -->
  <configuration>

    <!-- JDBC connection parameters -->
    <jdbc>
      <driver>org.postgresql.Driver</driver>
      <url>jdbc:postgresql:postgres</url>
      <user>postgres</user>
      <password>test</password>
    </jdbc>

    <!-- Generator parameters -->
    <generator>
      <database>
        <name>org.jooq.meta.postgres.PostgresDatabase</name>
        <includes>.*</includes>
        <excludes></excludes>
        <!-- In case your database supports catalogs, e.g. SQL Server:
        <inputCatalog>public</inputCatalog>
          -->
        <inputSchema>public</inputSchema>
      </database>
      <target>
        <packageName>org.jooq.codegen.maven.example</packageName>
        <directory>target/generated-sources/jooq</directory>
      </target>
    </generator>
  </configuration>
</plugin>
```

--------------------------------

### Configuring and implementing a ContextConverter

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/fetching/context-converter

Shows how to configure a Configuration with custom data using Configuration::data, and then implement a ContextConverter that accesses that data via the ConverterContext. The example returns different strings based on whether the configuration data is present.

```java
// Configuring your Configuration, e.g. with Spring:
Configuration configuration = new DefaultConfiguration();
configuration.data("my-configuration-key", myConfigurationValue);

// And then:
public class MyContextConverter implements ContextConverter<Integer, String> {
    @Override
    public String from(Integer databaseObject, ConverterContext ctx) {
        if (ctx.configuration().data("my-configuration-key") != null)
            return "configuration enabled";
        else
            return "configuration disabled";
    }
}
```

--------------------------------

### Implement MockDataProvider with execute method

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/mocking-connection

This snippet shows a complete implementation of MockDataProvider. It uses MockExecuteContext to get the SQL string and batch status, creates a mock result for SELECT statements, throws an exception for DROP statements, and returns an array of MockResult. The example uses SQLDialect.ORACLE and creates a result with AUTHOR.ID and AUTHOR.LAST_NAME fields.

```java
public class MyProvider implements MockDataProvider {

    @Override
    public MockResult[] execute(MockExecuteContext ctx) throws SQLException {

        // You might need a DSLContext to create org.jooq.Result and org.jooq.Record objects
        DSLContext create = DSL.using(SQLDialect.ORACLE);
        MockResult[] mock = new MockResult[1];

        // The execute context contains SQL string(s), bind values, and other meta-data
        String sql = ctx.sql();

        // Exceptions are propagated through the JDBC and jOOQ APIs
        if (sql.toUpperCase().startsWith("DROP")) {
            throw new SQLException("Statement not supported: " + sql);
        }

        // You decide, whether any given statement returns results, and how many
        else if (sql.toUpperCase().startsWith("SELECT")) {

            // Always return one record
            Result<Record2<Integer, String>> result = create.newResult(AUTHOR.ID, AUTHOR.LAST_NAME);
            result.add(create
                .newRecord(AUTHOR.ID, AUTHOR.LAST_NAME)
                .values(1, "Orwell"));
            mock[0] = new MockResult(1, result);
        }

        // You can detect batch statements easily
        else if (ctx.batch()) {
            // [...]
        }

        return mock;
    }
}
```

--------------------------------

### SQL Server WITH hint example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/hints/hints-sql-server/hints-sql-server-table

Shows the SQL Server syntax for a table hint using WITH (READUNCOMMITTED).

```sql
SELECT *
FROM BOOK WITH (READUNCOMMITTED)
```

--------------------------------

### Regenerate example code with jbang codegen

Source: https://www.jooq.org/doc/3.21/manual/getting-started/tutorials/jooq-with-jbang

Run this command to regenerate the example code, for example when your schema changes.

```shell
jbang codegen@jooq db.xml
```

--------------------------------

### Example output of getTables()

Source: https://www.jooq.org/doc/3.21/manual/sql-execution/meta-data/meta-jdbc

The expected output when printing tables from the sample database.

```text
AUTHOR
BOOK
BOOK_STORE
BOOK_TO_BOOK_STORE
LANGUAGE
```

--------------------------------

### Static imports for global artefacts and usage example

Source: https://www.jooq.org/doc/3.21/manual/code-generation/codegen-object-types/codegen-globals

Shows how to static import all global artefacts (Keys, Routines, Sequences, Tables) and then reference them concisely, e.g., MY_TABLE, MY_SEQUENCE.nextval(), myFunction(). The second block shows the fully qualified equivalent using com.example.generated.Tables, Sequences, and Routines.

```java
// Static imports for all global artefacts (if they exist)
import static com.example.generated.Keys.*;
import static com.example.generated.Routines.*;
import static com.example.generated.Sequences.*;
import static com.example.generated.Tables.*;

// You could then reference your artefacts as follows:
create.insertInto(MY_TABLE)
      .values(MY_SEQUENCE.nextval(), myFunction())
```

```java
// as a more concise form of this:
create.insertInto(com.example.generated.Tables.MY_TABLE)
      .values(com.example.generated.Sequences.MY_SEQUENCE.nextval(), com.example.generated.Routines.myFunction())
```

--------------------------------

### LAST_VALUE jOOQ example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/window-functions/window-last-value

jOOQ example using lastValue() with two different window frames, mirroring the SQL example.

```java
create.select(
         BOOK.ID,
         lastValue(BOOK.ID).over(orderBy(BOOK.ID)
           .rowsBetweenUnboundedPreceding().andUnboundedFollowing()),
         lastValue(BOOK.ID).over(orderBy(BOOK.ID)
           .rowsBetweenCurrentRow().andFollowing(1)))
     .from(BOOK)
     .fetch();
```

--------------------------------

### LOG10 SQL example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/numeric-functions/log10-function

SQL example showing the LOG10 function with a numeric argument.

```sql
SELECT log10(100);
```

--------------------------------

### jOOQ example of LEFT function

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/left-function

Example jOOQ query using the left() method to achieve the same result as the SQL example.

```java
create.select(left("hello world", 5)).fetch();
```

--------------------------------

### Result of the example query

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/window-functions/window-partition

The output table produced by the example query, showing the count per author partition.

```text
+----+-----------+-------+
| id | author_id | count |
+----+-----------+-------+
|  1 |         1 |     2 |
|  2 |         1 |     2 |
|  3 |         2 |     2 |
|  4 |         2 |     2 |
+----+-----------+-------+
```

--------------------------------

### jOOQ API example of TRANSLATE

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/string-functions/translate-function

jOOQ DSL example using the translate() method with val() to achieve the same translation as the SQL example.

```java
create.select(translate(val("1 * [2 + 3]"), "[]", "()")).fetch();
```

--------------------------------

### LEAD window function example

Source: https://www.jooq.org/doc/3.21/manual/sql-building/column-expressions/window-functions/window-lead

Shows the LEAD window function with default offset, offset 2, and offset 2 with default value -1. The SQL and jOOQ examples are equivalent and produce the same result set.

```sql
SELECT
  ID,
  lead(ID) OVER (ORDER BY ID),
  lead(ID, 2) OVER (ORDER BY ID),
  lead(ID, 2, -1) OVER (ORDER BY ID)
FROM
  BOOK;
```

```java
create.select(
         BOOK.ID,
         lead(BOOK.ID).over(orderBy(BOOK.ID)),
         lead(BOOK.ID, 2).over(orderBy(BOOK.ID)),
         lead(BOOK.ID, 2, -1).over(orderBy(BOOK.ID)))
     .from(BOOK)
     .fetch();
```
Tokens:
Raw