jOOQ

https://www.jooq.org/doc/3.19/
Admin
jOOQ is a Java library for building and executing SQL queries through a fluent API and code...
Tokens:1,343,712
Snippets:11,617
Trust Score:10
Update:17 hours ago
Show doc for...
Tokens:
Raw
### SQL example of SUBSTRING()

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

Shows the SQL syntax for SUBSTRING() with two arguments (start position) and three arguments (start position and length).

```sql
SELECT
  substring('hello world', 7),
  substring('hello world', 7, 1);
```

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

### SQL example using CONNECT_BY_ISLEAF with START WITH and CONNECT BY

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

Shows a hierarchical query using CONNECT_BY_ISLEAF to detect leaf nodes. The query starts with rows where parent is null and connects by prior child = parent. The result includes the path and leaf flag.

```sql
SELECT
  child,
  parent,
  sys_connect_by_path(child, '/'),
  connect_by_isleaf
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;
```

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

### SQL example using LEVEL with CONNECT BY

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

Shows how to use the LEVEL pseudo column in a hierarchical query with START WITH and CONNECT BY NOCYCLE. The query selects child, parent, sys_connect_by_path, and level from a VALUES table, starting with rows where parent is null and connecting by prior child = parent.

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

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

### SQL example of POSITION() function

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

Shows the SQL syntax for POSITION() with two arguments and with an optional start position. The first call returns 2, the second returns 4.

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

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

### Result of DATESUB example

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

Shows the output table of the DATESUB example.

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

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

### Result of ARRAY_PREPEND example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/array-functions/array-prepend-function/

Shows the expected output of the ARRAY_PREPEND example.

```text
+--------------.+
| array_prepend |
+---------------+
| [1, 2, 3]     |
+---------------+
```

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

### Result of JSON_SET example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/json-functions/json-set-function/

Shows the expected output of the JSON_SET example.

```text
+----------+---------------+
| json_set | json_set      |
+----------+---------------+
| {"a":2}  | {"a":1,"b":2} |
+----------+---------------+
```

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

### Result of NVL2 example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/general-functions/nvl2-function/

The output table from the previous SQL and jOOQ examples.

```text
+------+------+
| nvl2 | nvl2 |
+------+------+
|    2 |    3 |
+------+------+
```

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

### jOOQ selectFrom crossApply example

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

A compact jOOQ example using selectFrom with crossApply, used as the basis for dialect translation examples.

```java
selectFrom(AUTHOR.crossApply(selectCount().from(BOOK).where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))))
```

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

### SQL translation for CREATE SEQUENCE START WITH

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

The SQL generated for dialects that support the START WITH clause. This is the exact translation of the jOOQ example.

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

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

### Sample result table for CONNECT_BY_ROOT example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/connect-by-functions/connect-by-root-operator/

Shows the expected output of the example query, with columns child, parent, sys_connect_by_path, and connect_by_root.

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

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

### SQL UPDATE .. FROM example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/sql-statements/update-statement/update-from/

SQL example showing the UPDATE .. FROM clause joining BOOK to BOOK_ARCHIVE.

```sql
UPDATE BOOK_ARCHIVE
SET
  BOOK_ARCHIVE.TITLE = BOOK.TITLE
FROM BOOK
WHERE BOOK_ARCHIVE.ID = BOOK.ID
```

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

### jOOQ example of CHR function

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

Example jOOQ query using the chr method to get the character for ASCII code 65.

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

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

### SQL example of CHR function

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

Example SQL query using the CHR function to get the character for ASCII code 64.

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

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

### jOOQ example using connectByRoot

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/connect-by-functions/connect-by-root-operator/

Shows the jOOQ API equivalent of the SQL example, using connectByRoot, sysConnectByPath, startWith, and connectByNoCycle.

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

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

### Using a DAO

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

Example of initializing a DAO with a Configuration and performing CRUD operations.

```APIDOC
## Using a DAO

### Description
Example of using a generated DAO to find, update, and delete a Book POJO.

### Steps
1. Initialize a Configuration with a connection and SQL dialect.
2. Initialize the DAO with the Configuration.
3. Use the DAO to find a Book by id.
4. Modify the POJO and update it.
5. Delete the POJO.

### Example Code
```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);
```

```

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

### jOOQ JSON attribute access example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/json-functions/json-attribute-access/

The jOOQ expression used to access a JSON attribute. It is the source example that gets translated to dialect-specific SQL.

```java
jsonGetAttribute(json("{\"a\":1}"), "a")
```

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

### Result table for SYS_CONNECT_BY_PATH example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/connect-by-functions/sys-connect-by-path-function/

Example output of the SYS_CONNECT_BY_PATH query, showing the generated path for each row.

```text
+-------+--------+---------------------+
| child | parent | sys_connect_by_path |
+-------+--------+---------------------+
|     1 | {null} | /1                  |
|     2 |      1 | /1/2                |
|     3 |      2 | /1/2/3              |
+-------+--------+---------------------+
```

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

### jOOQ example: stNumPoints with stGeomFromText

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

Use stNumPoints to get the number of points in a linestring geometry. The example creates a linestring with four points and fetches the result, which is 4.

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

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

### Example book record in SQL comment format

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

Shows an example record with fields ID, AUTHOR_ID, TITLE, PUBLISHED_IN, and LANGUAGE_ID, where only some fields are set. This is used to illustrate the QBE concept where set attributes become equality predicates.

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

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

### Example of implementing a custom table and its record

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

Shows how to create a custom table with field definitions and its corresponding record, similar to what the code generator does.

```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);
    }
}
```

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

### jOOQ example using stExteriorRing

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

Creates a polygon geometry and applies stExteriorRing to get the exterior ring. The example uses stGeomFromText with a multi-ring polygon and fetches the result.

```java
create.select(stExteriorRing(
  stGeomFromText("""
    POLYGON (
      (-3 -3, 3 -3, 3 3, -3 3, -3 -3),
      (-2 -2, 2 -2, 2 2, -2 2, -2 -2),
      (-1 -1, 1 -1, 1 1, -1 1, -1 -1)
    )
  """)
)).fetch();
```

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

### jOOQ example using connectByIsCycle()

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

Shows the jOOQ equivalent of the SQL example, using the connectByIsCycle() function and the connectByNoCycle() method. It demonstrates how to build the same hierarchical query with jOOQ's DSL.

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

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

### jOOQ createSequence startWith example

Source: https://www.jooq.org/doc/3.19/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. This is supported in many dialects, translating to CREATE SEQUENCE s START WITH 1000.

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

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

### Corresponding SQL query for the example record

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

Shows the SQL query generated from the example record, with equality predicates for each set attribute (author_id, published_in, language_id). This is the expected output of the QBE translation.

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

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

### jOOQ DELETE .. RETURNING example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/sql-statements/delete-statement/delete-returning/

Uses jOOQ's deleteFrom and returningResult to delete a book with ID 1 and return its title. This is the source example that gets translated to dialect-specific SQL.

```java
deleteFrom(BOOK).where(BOOK.ID.eq(1)).returningResult(BOOK.TITLE)
```

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

### SQL example of CHOOSE()

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/general-functions/choose-function/

Shows the SQL syntax for CHOOSE() with three calls, returning 'a', 'b', and null for indices 1, 2, and 3.

```sql
SELECT
  choose(1, 'a', 'b'),
  choose(2, 'a', 'b'),
  choose(3, 'a', 'b');
```

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

### jOOQ ST_Z usage example

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

Extracts the Z coordinate from a point geometry using jOOQ's stZ function. The example uses stGeomFromText to create a point and stZ to get its Z coordinate.

```java
create.select(stZ(stGeomFromText("POINT (0 0 1)"))).fetch();
```

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

### SQL example of CARDINALITY function

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/array-functions/cardinality-function/

SQL query using CARDINALITY to get the size of an array literal.

```sql
SELECT CARDINALITY(ARRAY[1, 2])
```

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

### SQL example: count(*) OVER (PARTITION BY BOOK.AUTHOR_ID)

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

SQL query that fetches all books and the total number of books per author using a window function with PARTITION BY.

```sql
SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  count(*) OVER (PARTITION BY BOOK.AUTHOR_ID)
FROM
  BOOK
```

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

### jOOQ startTransaction() call

Source: https://www.jooq.org/doc/3.19/manual/sql-building/transactional-statements/start-transaction-statement/

The jOOQ API call to start a transaction. This is the source example that translates to dialect-specific SQL.

```java
startTransaction()
```

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

### Using a generated DAO with Configuration

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

Shows how to initialize a DAO with a Configuration and perform CRUD operations: findById, update, and delete. The example uses SQLDialect.ORACLE and a DefaultConfiguration.

```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);
```

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

### Sample result table for CONNECT_BY_ISLEAF example

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

Displays the expected output of the example query, showing child, parent, path, and leaf flag. The leaf flag is true for rows without children.

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

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

### DSL.trueCondition() dynamic SQL example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/conditional-expressions/true-false-no-condition/

Java example using DSL.trueCondition() to start a condition and conditionally add AND clauses based on non-null parameters. If a dialect does not support boolean column types, jOOQ generates '1 = 1'.

```java
Condition condition = trueCondition();
if (id != null)
    condition = condition.and(BOOK.ID.eq(id));
if (title != null)
    condition = condition.and(BOOK.TITLE.eq(title));
```

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

### MockFileDatabase file content example

Source: https://www.jooq.org/doc/3.19/manual/sql-execution/mock-file-database/

Example file content for MockFileDatabase. Lines starting with '#' are comments, SQL comments are parsed, statements end with ';', result sets are prefixed with '> ', and each statement is terminated by '@ rows: N'. Exceptions can be indicated with '@ exception: ...'.

```text
# All lines with a leading hash are ignored. This is the MockFileDatabase comment syntax
-- SQL comments are parsed and passed to the SQL statement
/* The same is true for multi-line SQL comments */
select 'A';
> A
> -
> A
@ rows: 1

select 'A', 'B' union all 'C', 'D';
> A B
> - -
> A B
> C D
@ rows: 2

# Statements without result sets just leave that section empty
update t set x = 1;
@ rows: 3

# Statements producing specific exceptions can indicate them as such
select * from t;
@ exception: ACCESS TO TABLE T FORBIDDEN

```

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

### jOOQ example: product(BOOK.ID)

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

jOOQ fluent API query that translates to the SQL example, using the product() method on the BOOK.ID field.

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

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

### Output of LEAD window function example

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

Resulting table from the LEAD query, showing values for lead, lead with offset 2, and lead with offset 2 and default -1.

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

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

### SQL example: SELECT current_timestamp

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/datetime-functions/current-localdatetime-function/

SQL query to get the current server time as a timestamp. This is the SQL equivalent of the jOOQ function.

```sql
SELECT current_timestamp;
```

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

### SQL Server WITH hint SQL example

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

Shows the SQL syntax for applying the WITH hint with READUNCOMMITTED to a table.

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

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

### SQL Server 3 argument DATEDIFF SQL example

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

SQL example of the 3 argument DATEDIFF function in SQL Server, using MONTH as the date part and dates '2020-02-03' and '2020-04-01'. The dates are truncated to the start of the month before calculating the difference.

```sql
SELECT DATEDIFF(
  MONTH
  DATE '2020-02-03',
  DATE '2020-04-01');
```

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

### SQL example of FLOOR()

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

Shows the SQL syntax for FLOOR() with positive and negative values.

```sql
SELECT
  floor(1.7),
  floor(-1.7);
```

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

### jOOQ example using connectByIsLeaf()

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

Shows the jOOQ API equivalent of the SQL example, using connectByIsLeaf() and sysConnectByPath(). It builds a hierarchical query with startWith and connectByNoCycle.

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

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

### jOOQ example using level() with connectByNoCycle

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

Shows the jOOQ equivalent of the SQL example, using the level() function and connectByNoCycle. It selects child, parent, sysConnectByPath, and level from a values table, starting with parent.isNull() and connecting by prior(child).eq(parent).

```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();
```

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

### Example SQL with named parameters

Source: https://www.jooq.org/doc/3.19/manual/sql-building/dsl-context/custom-settings/settings-named-parameter-prefix/

Shows the default output when using named parameters, with a colon prefix.

```sql
-- NAMED
SELECT FIRST_NAME || :1 FROM AUTHOR WHERE ID = :x
```

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

### jOOQ example of POSITION() function

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

Shows the jOOQ API usage for POSITION() with two and three arguments. The third argument is the start position.

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

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

### jOOQ DOCUMENT predicate on column

Source: https://www.jooq.org/doc/3.19/manual/sql-building/conditional-expressions/document-predicate/

Example of using isDocument() on a column expression AUTHOR.FIRST_NAME. This is the jOOQ expression that gets translated to dialect-specific SQL.

```java
AUTHOR.FIRST_NAME.isDocument()
```

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

### jOOQ example: create.select(atan(1)).fetch()

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

Shows the jOOQ API usage to execute the same query as the SQL example.

```java
create.select(atan(1)).fetch();
```

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

### Configure interpreter settings with Settings class

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

Example configuration showing how to set interpreterDialect, interpreterDelayForeignKeyDeclarations, interpreterLocale, interpreterNameLookupCaseSensitivity, and interpreterSearchPath. Defaults are noted in comments.

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

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

### SQL example of CURRENT_USER

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/system-functions/current-user-function/

Shows the SQL expression to get the current user. The result is a table with a single column 'current_user' and value 'sa'.

```sql
SELECT current_user;
```

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

### SQL RPAD example

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

SQL query using RPAD to pad 'hello' to length 10 with '.'.

```sql
SELECT rpad('hello', 10, '.');
```

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

### Configure listener invocation order with Settings

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

Use this configuration to set the invocation order for start and end events of jOOQ listeners. The example sets start order to DEFAULT and end order to REVERSE for all listener types, overriding the default DEFAULT order for end events.

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

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

### Using DSL.condition(Record) and DSLContext.fetchByExample(TableRecord)

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

Java code demonstrating two ways to perform QBE: using the explicit condition(book) API with selectFrom().where(), and using the convenience fetchByExample(book) method on DSLContext. Both require a BookRecord with set fields.

```java
BookRecord book = new BookRecord();
book.setAuthorId(1);
book.setPublishedIn(1970);
book.setLanguageId(1);

// Using the explicit condition() API
Result<BookRecord> books1 =
DSL.using(configuration)
   .selectFrom(BOOK)
   .where(condition(book))
   .fetch();

// Using the convenience API on DSLContext
Result<BookRecord> books2 = DSL.using(configuration).fetchByExample(book);
```

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

### SQL example of OVERLAY function

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

SQL query using OVERLAY('abcdefg', 'xxx', 2) to overlay 'xxx' starting at position 2, resulting in 'axxxefg'.

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

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

### SQL quantified comparison predicate examples

Source: https://www.jooq.org/doc/3.19/manual/sql-building/conditional-expressions/quantified-comparison-predicate/

Shows SQL syntax for quantified comparison predicates using ANY and ALL with literal lists.

```sql
TITLE = ANY('Animal Farm', '1982')
PUBLISHED_IN > ALL(1920, 1940)
```

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

### Output of PRODUCT example

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

The result table produced by the example query, showing the product value 24.

```text
+---------+
| product |
+---------+
|      24 |
+---------+
```

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

### Output: FILTER without GROUP BY

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/aggregate-functions/aggregate-filter/

The result table produced by the first SQL and jOOQ examples. It shows the counts for all rows, rows starting with 'A', and rows containing 'A'.

```text
+-------+-------+-------+
| count | count | count |
+-------+-------+-------+
|     4 |     1 |     2 |
+-------+-------+-------+
```

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

### Configuring Configuration and implementing MyContextConverter

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

Shows how to store custom data in a Configuration using Configuration::data, and then implement a ContextConverter that reads that data during conversion. The example returns different strings based on whether the data key 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";
    }
}
```

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

### jOOQ count().filterWhere() example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/aggregate-functions/aggregate-filter/

The jOOQ API call that applies a filter to the COUNT aggregate function. This is the source expression that gets translated to dialect-specific SQL.

```java
count().filterWhere(BOOK.TITLE.like("A%"))
```

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

### SQL example using CONNECT_BY_ROOT

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/connect-by-functions/connect-by-root-operator/

Shows a hierarchical query using CONNECT_BY_ROOT to access the root row's child value. Uses START WITH and CONNECT BY NOCYCLE.

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

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

### jOOQ UPDATE .. FROM example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/sql-statements/update-statement/update-from/

jOOQ DSL equivalent of the UPDATE .. FROM example using the from() method.

```java
create.update(BOOK_ARCHIVE)
      .set(BOOK_ARCHIVE.TITLE, BOOK.TITLE)
      .from(BOOK)
      .where(BOOK_ARCHIVE.ID.eq(BOOK.ID))
      .execute();
```

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

### Output: FILTER with GROUP BY

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/aggregate-functions/aggregate-filter/

The result table produced by the second SQL and jOOQ examples. It shows the counts per AUTHOR_ID for all rows, rows starting with 'A', and rows containing 'A'.

```text
+-----------+-------+-------+-------+
| AUTHOR_ID | count | count | count |
+-----------+-------+-------+-------+
|         1 |     2 |     1 |     1 |
|         2 |     2 |     0 |     1 |
+-----------+-------+-------+-------+
```

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

### jOOQ COMMIT statement example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/transactional-statements/commit-statement/

The jOOQ API call to commit the current transaction. Depending on the dialect, this may end the transaction and require a new START TRANSACTION statement.

```java
commit()
```

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

### Configure rendering settings with Settings and RenderFormatting

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

Example configuration showing how to enable formatted SQL output, set newline to \r\n, and indentation to tab. Note that renderFormatted defaults to false, newline defaults to \n, and indentation defaults to two spaces.

```java
Settings settings = new Settings()
    .withRenderFormatted(true)                           // Defaults to false
    .withRenderFormatting(new RenderFormatting()
        .withNewline("\r\n")                           // Defaults to \n
        .withIndentation("\t"))                         // Defaults to "  "
    
```

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

### SQL STARTS_WITH predicate example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/conditional-expressions/starts-with-predicate/

Shows the SQL syntax for the STARTS_WITH predicate, using the column expression TITLE and the prefix 'abc'.

```sql
STARTS_WITH(TITLE, 'abc')
```

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

### jOOQ cast to BOOLEAN

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

Example of using jOOQ's cast method to cast a field to BOOLEAN. This is the source jOOQ code that gets translated to dialect-specific cast expressions.

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

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

### Output of RATIO_TO_REPORT example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/window-functions/window-ratio-to-report/

The result table produced by the example query, showing the ratio values for each ID.

```text
+----+-----------------+
| id | ratio_to_report |
+----+-----------------+
|  1 |             0.1 |
|  2 |             0.2 |
|  3 |             0.3 |
|  4 |             0.4 |
+----+-----------------+
```

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

### Output of PERCENT_RANK example

Source: https://www.jooq.org/doc/3.19/manual/sql-building/column-expressions/window-functions/window-percent-rank/

Result table showing language_id and percent_rank values. Tied rows (language_id 1) are both ranked first with 0, and the highest value gets 1.

```text
+-------------+--------------+
| language_id | percent_rank |
+-------------+--------------+
|           1 |            0 |
|           1 |            0 | <-- Tied rows are both ranked first
|           2 | 0.6666666667 |
|           4 |            1 |
+-------------+--------------+
```

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

### SQL example of LEAD window function

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

SQL query using LEAD with default offset, offset 2, and offset 2 with default value -1, ordered by ID.

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

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

### jOOQ ST_EndPoint usage example

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

Uses jOOQ's stEndPoint and stGeomFromText to get the last point of a linestring. The result is a POINT value, e.g., POINT (3 1).

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

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

### SELECT DISTINCT ip FROM access_log (SQL)

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

Simple SQL example to get distinct IP addresses from a log table. Useful for ad-hoc queries when the schema isn't normalized.

```sql
SELECT DISTINCT ip FROM access_log
```
Tokens:
Raw