org.jooq

Interface SelectQuery<R extends Record>

All Superinterfaces:

Attachable, AutoCloseable, ConditionProvider, FieldLike, Iterable<R>, Query, QueryPart, ResultQuery<R>, Select<R>, Serializable, TableLike<R>

public interface SelectQuery<R extends Record>
extends Select<R>, ConditionProvider

A query for data selection

Author:

Lukas Eder

Method Summary

Modifier and Type	Method and Description
void	addConditions(Collection<? extends Condition> conditions) Adds new conditions to the query, connecting them to existing conditions with Operator.AND
void	addConditions(Condition... conditions) Adds new conditions to the query, connecting them to existing conditions with Operator.AND
void	addConditions(Operator operator, Collection<? extends Condition> conditions) Adds new conditions to the query, connecting them to existing conditions with the provided operator
void	addConditions(Operator operator, Condition... conditions) Adds new conditions to the query, connecting them to existing conditions with the provided operator
void	addConnectBy(Condition condition) Add an Oracle-specific CONNECT BY clause to the query.
void	addConnectByNoCycle(Condition condition) Add an Oracle-specific CONNECT BY NOCYCLE clause to the query.
void	addDistinctOn(Collection<? extends SelectField<?>> fields) Add a PostgreSQL-specific DISTINCT ON (fields...)
void	addDistinctOn(SelectField<?>... fields) Add a PostgreSQL-specific DISTINCT ON (fields...)
void	addFrom(Collection<? extends TableLike<?>> from) Add tables to the table product.
void	addFrom(TableLike<?>... from) Add tables to the table product.
void	addFrom(TableLike<?> from) Add tables to the table product.
void	addGroupBy(Collection<? extends GroupField> fields) Adds grouping fields.
void	addGroupBy(GroupField... fields) Adds grouping fields.
void	addHaving(Collection<? extends Condition> conditions) Adds new conditions to the having clause of the query, connecting them with each other with Operator.AND.
void	addHaving(Condition... conditions) Adds new conditions to the having clause of the query, connecting them with each other with Operator.AND.
void	addHaving(Operator operator, Collection<? extends Condition> conditions) Adds new conditions to the having clause of query, connecting them with each other with Operator.AND.
void	addHaving(Operator operator, Condition... conditions) Adds new conditions to the having clause of query, connecting them with each other with Operator.AND.
void	addHint(String hint) Add an Oracle-style hint to the select clause.
void	addJoin(TableLike<?> table, Condition... conditions) Joins the existing table product to a new table using a condition, connecting them with each other with Operator.AND.
void	addJoin(TableLike<?> table, JoinType type, Condition... conditions) Joins the existing table product to a new table using a condition, connecting them with each other with Operator.AND.
void	addJoin(TableLike<?> table, JoinType type, Condition[] conditions, Field<?>[] partitionBy) Joins the existing table product to a new table using conditions, connecting them with each other with Operator.AND.
void	addJoinOnKey(TableLike<?> table, JoinType type) Joins the existing table product to a new table using a foreign key.
void	addJoinOnKey(TableLike<?> table, JoinType type, ForeignKey<?,?> key) Joins the existing table product to a new table using a foreign key.
void	addJoinOnKey(TableLike<?> table, JoinType type, TableField<?,?>... keyFields) Joins the existing table product to a new table using a foreign key.
void	addJoinUsing(TableLike<?> table, Collection<? extends Field<?>> fields) Joins the existing table product to a new table with a USING clause.
void	addJoinUsing(TableLike<?> table, JoinType type, Collection<? extends Field<?>> fields) Joins the existing table product to a new table with a USING clause.
void	addLimit(int numberOfRows) Limit the results of this select.
void	addLimit(int offset, int numberOfRows) Limit the results of this select.
void	addLimit(int offset, Param<Integer> numberOfRows) Limit the results of this select using named parameters.
void	addLimit(Param<Integer> numberOfRows) Limit the results of this select using named parameters.
void	addLimit(Param<Integer> offset, int numberOfRows) Limit the results of this select.
void	addLimit(Param<Integer> offset, Param<Integer> numberOfRows) Limit the results of this select using named parameters.
void	addOption(String option) Add a SQL Server-style query hint to the select clause.
void	addOrderBy(Collection<? extends SortField<?>> fields) Adds ordering fields.
void	addOrderBy(Field<?>... fields) Adds ordering fields, ordering by the default sort order.
void	addOrderBy(int... fieldIndexes) Adds ordering fields.
void	addOrderBy(SortField<?>... fields) Adds ordering fields.
void	addSeekAfter(Collection<? extends Field<?>> fields) Adds seeking fields.
void	addSeekAfter(Field<?>... fields) Adds seeking fields.
void	addSeekBefore(Collection<? extends Field<?>> fields) Adds seeking fields.
void	addSeekBefore(Field<?>... fields) Adds seeking fields.
void	addSelect(Collection<? extends SelectField<?>> fields) Add a list of select fields.
void	addSelect(SelectField<?>... fields) Add a list of select fields.
void	addWindow(Collection<? extends WindowDefinition> definitions) Adds new window definitions to the window clause of the query.
void	addWindow(WindowDefinition... definitions) Adds new window definitions to the window clause of the query.
void	setConnectByStartWith(Condition condition) Add an Oracle-specific START WITH clause to the query's CONNECT BY clause.
void	setDistinct(boolean distinct) Add "distinct" keyword to the select clause.
void	setForShare(boolean forShare) Sets the "FOR SHARE" flag onto the query.
void	setForUpdate(boolean forUpdate) Sets the "FOR UPDATE" flag onto the query.
void	setForUpdateNoWait() Some RDBMS allow for specifying the locking mode for the applied FOR UPDATE clause.
void	setForUpdateOf(Collection<? extends Field<?>> fields) Some RDBMS allow for specifying the fields that should be locked by the FOR UPDATE clause, instead of the full row.
void	setForUpdateOf(Field<?>... fields) Some RDBMS allow for specifying the fields that should be locked by the FOR UPDATE clause, instead of the full row.
void	setForUpdateOf(Table<?>... tables) Some RDBMS allow for specifying the tables that should be locked by the FOR UPDATE clause, instead of the full row.
void	setForUpdateSkipLocked() Some RDBMS allow for specifying the locking mode for the applied FOR UPDATE clause.
void	setForUpdateWait(int seconds) Some RDBMS allow for specifying the locking mode for the applied FOR UPDATE clause.
void	setInto(Table<?> table) Add INTO clause to the SELECT statement.
void	setOrderBySiblings(boolean orderBySiblings) Indicate whether the SIBLINGS keyword should be used in an ORDER BY clause to form an ORDER SIBLINGS BY clause.
void	setWithCheckOption() Add a WITH CHECK OPTION clause to the end of the subquery.
void	setWithReadOnly() Add a WITH READ ONLY clause to the end of the subquery.

Methods inherited from interface org.jooq.Select

except, exceptAll, fetchCount, getSelect, intersect, intersectAll, union, unionAll

Methods inherited from interface org.jooq.ResultQuery

bind, bind, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAny, fetchAnyArray, fetchAnyInto, fetchAnyInto, fetchAnyMap, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArrays, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchInto, fetchInto, fetchInto, fetchLater, fetchLater, fetchLazy, fetchLazy, fetchMany, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMaps, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOneArray, fetchOneInto, fetchOneInto, fetchOneMap, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptional, fetchOptionalArray, fetchOptionalInto, fetchOptionalInto, fetchOptionalMap, fetchResultSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSet, fetchSize, getRecordType, getResult, intern, intern, intern, intern, iterator, keepStatement, maxRows, queryTimeout, resultSetConcurrency, resultSetHoldability, resultSetType, stream

Methods inherited from interface org.jooq.Query

cancel, close, execute, getBindValues, getParam, getParams, getSQL, getSQL, getSQL, isExecutable

Methods inherited from interface org.jooq.QueryPart

equals, hashCode, toString

Methods inherited from interface org.jooq.Attachable

attach, detach

Methods inherited from interface java.lang.Iterable

forEach, spliterator

Methods inherited from interface org.jooq.TableLike

asTable, asTable, asTable, field, field, field, field, field, field, field, field, field, field, fields, fields, fields, fields, fields, fieldsRow

Methods inherited from interface org.jooq.FieldLike

asField, asField

Method Detail

addSelect

@Support
void addSelect(SelectField<?>... fields)

Add a list of select fields.

Parameters:

fields -

addSelect

@Support
void addSelect(Collection<? extends SelectField<?>> fields)

Add a list of select fields.

Parameters:

fields -

setDistinct

@Support
void setDistinct(boolean distinct)

Add "distinct" keyword to the select clause.

addDistinctOn

@Support(value=POSTGRES)
void addDistinctOn(SelectField<?>... fields)

Add a PostgreSQL-specific DISTINCT ON (fields...) clause.

This also sets the distinct flag to true

addDistinctOn

@Support(value=POSTGRES)
void addDistinctOn(Collection<? extends SelectField<?>> fields)

Add a PostgreSQL-specific DISTINCT ON (fields...) clause.

This also sets the distinct flag to true

setInto

@Support(value={ACCESS,ASE,CUBRID,DB2,DERBY,H2,HANA,HSQLDB,INFORMIX,INGRES,MARIADB,MYSQL,ORACLE,POSTGRES,SQLSERVER,SQLITE})
void setInto(Table<?> table)

Add INTO clause to the SELECT statement.

addFrom

@Support
void addFrom(TableLike<?> from)

Add tables to the table product.

Parameters:

from - The added tables

addFrom

@Support
void addFrom(TableLike<?>... from)

Add tables to the table product.

Parameters:

from - The added tables

addFrom

@Support
void addFrom(Collection<? extends TableLike<?>> from)

Add tables to the table product.

Parameters:

from - The added tables

addJoin

@Support
void addJoin(TableLike<?> table,
                       Condition... conditions)

Joins the existing table product to a new table using a condition, connecting them with each other with Operator.AND.

Parameters:

table - The joined table

conditions - The joining conditions

addJoin

@Support
void addJoin(TableLike<?> table,
                       JoinType type,
                       Condition... conditions)

Joins the existing table product to a new table using a condition, connecting them with each other with Operator.AND.

Parameters:

table - The joined table

type - The type of join

conditions - The joining conditions

addJoin

@Support(value=ORACLE)
void addJoin(TableLike<?> table,
                                     JoinType type,
                                     Condition[] conditions,
                                     Field<?>[] partitionBy)

Joins the existing table product to a new table using conditions, connecting them with each other with Operator.AND.

This adds a PARTITION BY clause to the right hand side of a OUTER JOIN expression.

Parameters:

table - The joined table

type - The type of join

conditions - The joining conditions

partitionBy - The PARTITION BY expression

See Also:

TablePartitionByStep

addJoinUsing

@Support
void addJoinUsing(TableLike<?> table,
                            Collection<? extends Field<?>> fields)

Joins the existing table product to a new table with a USING clause.

If this is not supported by your RDBMS, then jOOQ will try to emulate this behaviour using the information provided in this query.

Parameters:

table - The joined table

fields - The fields for the USING clause

addJoinUsing

@Support
void addJoinUsing(TableLike<?> table,
                            JoinType type,
                            Collection<? extends Field<?>> fields)

Joins the existing table product to a new table with a USING clause.

If this is not supported by your RDBMS, then jOOQ will try to emulate this behaviour using the information provided in this query.

Parameters:

table - The joined table

type - The type of join

fields - The fields for the USING clause

addJoinOnKey

@Support
void addJoinOnKey(TableLike<?> table,
                            JoinType type)
                     throws DataAccessException

Joins the existing table product to a new table using a foreign key.

Parameters:

table - The joined table

type - The type of join

Throws:

DataAccessException - If there is no non-ambiguous key definition known to jOOQ

See Also:

TableOnStep.onKey(ForeignKey)

addJoinOnKey

@Support
void addJoinOnKey(TableLike<?> table,
                            JoinType type,
                            TableField<?,?>... keyFields)
                     throws DataAccessException

Joins the existing table product to a new table using a foreign key.

Parameters:

table - The joined table

type - The type of join

keyFields - The foreign key fields

Throws:

DataAccessException - If there is no non-ambiguous key definition known to jOOQ

See Also:

TableOnStep.onKey(ForeignKey)

addJoinOnKey

@Support
void addJoinOnKey(TableLike<?> table,
                            JoinType type,
                            ForeignKey<?,?> key)

Joins the existing table product to a new table using a foreign key.

Parameters:

table - The joined table

type - The type of join

key - The foreign key

See Also:

TableOnStep.onKey(ForeignKey)

addGroupBy

@Support
void addGroupBy(GroupField... fields)

Adds grouping fields.

Calling this with an empty argument list will result in an empty GROUP BY () clause being rendered.

Parameters:

fields - The grouping fields

addGroupBy

@Support
void addGroupBy(Collection<? extends GroupField> fields)

Adds grouping fields.

Calling this with an empty argument list will result in an empty GROUP BY () clause being rendered.

Parameters:

fields - The grouping fields

addHaving

@Support
void addHaving(Condition... conditions)

Adds new conditions to the having clause of the query, connecting them with each other with Operator.AND.

Parameters:

conditions - The condition

addHaving

@Support
void addHaving(Collection<? extends Condition> conditions)

Adds new conditions to the having clause of the query, connecting them with each other with Operator.AND.

Parameters:

conditions - The condition

addHaving

@Support
void addHaving(Operator operator,
                         Condition... conditions)

Adds new conditions to the having clause of query, connecting them with each other with Operator.AND.

Parameters:

operator - The operator to use to add the conditions to the existing conditions

conditions - The condition

addHaving

@Support
void addHaving(Operator operator,
                         Collection<? extends Condition> conditions)

Adds new conditions to the having clause of query, connecting them with each other with Operator.AND.

Parameters:

operator - The operator to use to add the conditions to the existing conditions

conditions - The condition

addWindow

@Support(value={CUBRID,DB2,HANA,INFORMIX,POSTGRES,ORACLE,SQLSERVER,SYBASE})
void addWindow(WindowDefinition... definitions)

Adds new window definitions to the window clause of the query.

Parameters:

definitions - The definitions

addWindow

@Support(value={CUBRID,DB2,HANA,INFORMIX,POSTGRES,ORACLE,SQLSERVER,SYBASE})
void addWindow(Collection<? extends WindowDefinition> definitions)

Adds new window definitions to the window clause of the query.

Parameters:

definitions - The definitions

addHint

@Support
void addHint(String hint)

Add an Oracle-style hint to the select clause.

Example:

 DSLContext create = DSL.using(configuration);

 create.select(field1, field2)
       .hint("/*+ALL_ROWS*/")
       .from(table1)
       .execute();
 

You can also use this clause for any other database, that accepts hints or options at the same syntactic location, e.g. for MySQL's SQL_CALC_FOUND_ROWS option:

 create.select(field1, field2)
       .hint("SQL_CALC_FOUND_ROWS")
       .from(table1)
       .fetch();
 

The outcome of such a query is this:

 SELECT [hint] field1, field2 FROM table1
 

For SQL Server style table hints, see Table.with(String)

See Also:

Table.with(String)

addOption

@Support
void addOption(String option)

Add a SQL Server-style query hint to the select clause.

Example:

 DSLContext create = DSL.using(configuration);

 create.select(field1, field2)
       .from(table1)
       .option("OPTION (OPTIMIZE FOR UNKNOWN)")
       .execute();
 

You can also use this clause for any other database, that accepts hints or options at the same syntactic location, e.g. for DB2's isolation clause:

 create.select(field1, field2)
       .from(table1)
       .option("WITH RR USE AND KEEP EXCLUSIVE LOCKS")
       .execute();
 

The outcome of such a query is this:

 SELECT field1, field2 FROM table1 [option]
 

For SQL Server style table hints, see Table.with(String)

See Also:

Table.with(String)

addConnectBy

@Support(value={CUBRID,INFORMIX,ORACLE})
void addConnectBy(Condition condition)

Add an Oracle-specific CONNECT BY clause to the query.

addConnectByNoCycle

@Support(value={CUBRID,INFORMIX,ORACLE})
void addConnectByNoCycle(Condition condition)

Add an Oracle-specific CONNECT BY NOCYCLE clause to the query.

setConnectByStartWith

@Support(value={CUBRID,INFORMIX,ORACLE})
void setConnectByStartWith(Condition condition)

Add an Oracle-specific START WITH clause to the query's CONNECT BY clause.

addConditions

@Support
void addConditions(Condition... conditions)

Adds new conditions to the query, connecting them to existing conditions with Operator.AND

Specified by:

addConditions in interface ConditionProvider

Parameters:

conditions - The condition

addConditions

@Support
void addConditions(Collection<? extends Condition> conditions)

Adds new conditions to the query, connecting them to existing conditions with Operator.AND

Specified by:

addConditions in interface ConditionProvider

Parameters:

conditions - The condition

addConditions

@Support
void addConditions(Operator operator,
                             Condition... conditions)

Adds new conditions to the query, connecting them to existing conditions with the provided operator

Specified by:

addConditions in interface ConditionProvider

conditions - The condition

addConditions

@Support
void addConditions(Operator operator,
                             Collection<? extends Condition> conditions)

Adds new conditions to the query, connecting them to existing conditions with the provided operator

Specified by:

addConditions in interface ConditionProvider

conditions - The condition

addOrderBy

@Support
void addOrderBy(Field<?>... fields)

Adds ordering fields, ordering by the default sort order.

Parameters:

fields - The ordering fields

addOrderBy

@Support
void addOrderBy(SortField<?>... fields)

Adds ordering fields.

Parameters:

fields - The ordering fields

addOrderBy

@Support
void addOrderBy(Collection<? extends SortField<?>> fields)

Adds ordering fields.

Parameters:

fields - The ordering fields

addOrderBy

@Support
void addOrderBy(int... fieldIndexes)

Adds ordering fields.

Indexes start at 1 in SQL!

Note, you can use addOrderBy(DSL.val(1).desc()) or addOrderBy(DSL.literal(1).desc()) to apply descending ordering

Parameters:

fieldIndexes - The ordering fields

setOrderBySiblings

@Support(value={CUBRID,INFORMIX,ORACLE})
void setOrderBySiblings(boolean orderBySiblings)

Indicate whether the SIBLINGS keyword should be used in an ORDER BY clause to form an ORDER SIBLINGS BY clause.

This clause can be used only along with Oracle's CONNECT BY clause, to indicate that the hierarchical ordering should be preserved and elements of each hierarchy should be ordered among themselves.

Parameters:

orderBySiblings -

addSeekAfter

@Support
void addSeekAfter(Field<?>... fields)

Adds seeking fields.

Parameters:

fields - The seeking fields

addSeekAfter

@Support
void addSeekAfter(Collection<? extends Field<?>> fields)

Adds seeking fields.

Parameters:

fields - The seeking fields

addSeekBefore

@Support
void addSeekBefore(Field<?>... fields)

Adds seeking fields.

Parameters:

fields - The seeking fields

addSeekBefore

@Support
void addSeekBefore(Collection<? extends Field<?>> fields)

Adds seeking fields.

Parameters:

fields - The seeking fields

addLimit

@Support
void addLimit(int numberOfRows)

Limit the results of this select.

This is the same as calling addLimit(int, int) with offset = 0

Parameters:

numberOfRows - The number of rows to return

addLimit

@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE,VERTICA})
void addLimit(Param<Integer> numberOfRows)

Limit the results of this select using named parameters.

Note that some dialects do not support bind values at all in LIMIT or TOP clauses!

If there is no LIMIT or TOP clause in your RDBMS, or the LIMIT or TOP clause does not support bind values, this may be emulated with a ROW_NUMBER() window function and nested SELECT statements.

This is the same as calling addLimit(int, int) with offset = 0

Parameters:

numberOfRows - The number of rows to return

addLimit

@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,MARIADB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE,VERTICA})
void addLimit(int offset,
                                                                                                                                                        int numberOfRows)

Limit the results of this select.

Note that some dialects do not support bind values at all in LIMIT or TOP clauses!

If there is no LIMIT or TOP clause in your RDBMS, or if your RDBMS does not natively support offsets, this is emulated with a ROW_NUMBER() window function and nested SELECT statements.

Parameters:

offset - The lowest offset starting at 0

numberOfRows - The number of rows to return

addLimit

@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE,VERTICA})
void addLimit(Param<Integer> offset,
                                                                                                                                                 int numberOfRows)

Limit the results of this select.

Note that some dialects do not support bind values at all in LIMIT or TOP clauses!

If there is no LIMIT or TOP clause in your RDBMS, or the LIMIT or TOP clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with a ROW_NUMBER() window function and nested SELECT statements.

Parameters:

offset - The lowest offset starting at 0

numberOfRows - The number of rows to return

addLimit

@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE,VERTICA})
void addLimit(int offset,
                                                                                                                                                 Param<Integer> numberOfRows)

Limit the results of this select using named parameters.

Note that some dialects do not support bind values at all in LIMIT or TOP clauses!

If there is no LIMIT or TOP clause in your RDBMS, or the LIMIT or TOP clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with a ROW_NUMBER() window function and nested SELECT statements.

Parameters:

offset - The lowest offset starting at 0

numberOfRows - The number of rows to return

addLimit

@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE,VERTICA})
void addLimit(Param<Integer> offset,
                                                                                                                                                 Param<Integer> numberOfRows)

Limit the results of this select using named parameters.

Note that some dialects do not support bind values at all in LIMIT or TOP clauses!

If there is no LIMIT or TOP clause in your RDBMS, or the LIMIT or TOP clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with a ROW_NUMBER() window function and nested SELECT statements.

Parameters:

offset - The lowest offset starting at 0

numberOfRows - The number of rows to return

setForUpdate

@Support(value={ASE,CUBRID,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,MARIADB,MYSQL,ORACLE,POSTGRES,SQLSERVER,SYBASE})
void setForUpdate(boolean forUpdate)

Sets the "FOR UPDATE" flag onto the query.

Native implementation

This has been observed to be supported by any of these dialects:

• DB2 FOR UPDATE and similar clauses
• Derby's FOR UPDATE clause
• H2's FOR UPDATE clause
• HSQLDB's FOR UPDATE clause
• MySQL's InnoDB locking reads
• Oracle's PL/SQL FOR UPDATE clause
• Postgres FOR UPDATE / FOR SHARE

emulation

These dialects can emulate the FOR UPDATE clause using a cursor. The cursor is handled by the JDBC driver, at PreparedStatement construction time, when calling Connection.prepareStatement(String, int, int) with ResultSet.CONCUR_UPDATABLE. jOOQ handles emulation of a FOR UPDATE clause using CONCUR_UPDATABLE for these dialects:

• SQLDialect.CUBRID
• SQLDialect.SQLSERVER

Note: This emulation may not be efficient for large result sets!

Not supported

These dialects are known not to support the FOR UPDATE clause in regular SQL:

• SQLDialect.SQLITE

If your dialect does not support this clause, jOOQ will still render it, if you apply it to your query. This might then cause syntax errors reported either by your database or your JDBC driver.

You shouldn't combine this with setForShare(boolean)

Parameters:

forUpdate - The flag's value

setForUpdateOf

@Support(value={DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,ORACLE,SYBASE})
void setForUpdateOf(Field<?>... fields)

Some RDBMS allow for specifying the fields that should be locked by the FOR UPDATE clause, instead of the full row.

This automatically sets the setForUpdate(boolean) flag, and unsets the setForShare(boolean) flag, if it was previously set.

This has been observed to be natively supported by any of these dialects:

• DB2
• Derby
• H2
• HSQLDB
• Ingres
• Oracle
• Sybase

Note, that SQLDialect.DB2 has some stricter requirements regarding the updatability of fields. Refer to the DB2 documentation for further details

Parameters:

fields - The fields that should be locked

setForUpdateOf

@Support(value={DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,ORACLE,SYBASE})
void setForUpdateOf(Collection<? extends Field<?>> fields)

Some RDBMS allow for specifying the fields that should be locked by the FOR UPDATE clause, instead of the full row.

See Also:

setForUpdateOf(Field...)

setForUpdateOf

@Support(value={DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,POSTGRES,ORACLE,SYBASE})
void setForUpdateOf(Table<?>... tables)

Some RDBMS allow for specifying the tables that should be locked by the FOR UPDATE clause, instead of the full row.

This automatically sets the setForUpdate(boolean) flag, and unsets the setForShare(boolean) flag, if it was previously set.

This has been observed to be natively supported by any of these dialects:

• Postgres
• H2
• HSQLDB
• Sybase

jOOQ emulates this by locking all known fields of [tables] for any of these dialects:

• DB2
• Derby
• Ingres
• Oracle

Parameters:

tables - The tables that should be locked

setForUpdateWait

@Support(value=ORACLE)
void setForUpdateWait(int seconds)

Some RDBMS allow for specifying the locking mode for the applied FOR UPDATE clause. In this case, the session will wait for some seconds, before aborting the lock acquirement if the lock is not available.

This automatically sets the setForUpdate(boolean) flag, and unsets the setForShare(boolean) flag, if it was previously set.

This has been observed to be supported by any of these dialects:

• Oracle

Parameters:

seconds - The number of seconds to wait for a lock

setForUpdateNoWait

@Support(value={ORACLE,POSTGRES})
void setForUpdateNoWait()

Some RDBMS allow for specifying the locking mode for the applied FOR UPDATE clause. In this case, the session will not wait before aborting the lock acquirement if the lock is not available.

This automatically sets the setForUpdate(boolean) flag, and unsets the setForShare(boolean) flag, if it was previously set.

This has been observed to be supported by any of these dialects:

• Oracle

setForUpdateSkipLocked

@Support(value={ORACLE,POSTGRES_9_5})
void setForUpdateSkipLocked()

Some RDBMS allow for specifying the locking mode for the applied FOR UPDATE clause. In this case, the session will skip all locked rows from the select statement, whose lock is not available.

This automatically sets the setForUpdate(boolean) flag, and unsets the setForShare(boolean) flag, if it was previously set.

This has been observed to be supported by any of these dialects:

• Oracle

setForShare

@Support(value={MARIADB,MYSQL,POSTGRES})
void setForShare(boolean forShare)

Sets the "FOR SHARE" flag onto the query.

This has been observed to be supported by any of these dialects:

• MySQL's InnoDB locking reads
• Postgres FOR UPDATE / FOR SHARE

If your dialect does not support this clause, jOOQ will still render it, if you apply it to your query. This might then cause syntax errors reported either by your database or your JDBC driver.

You shouldn't combine this with setForUpdate(boolean)

Parameters:

forShare - The flag's value

setWithCheckOption

@Support(value=ORACLE)
void setWithCheckOption()

Add a WITH CHECK OPTION clause to the end of the subquery.

setWithReadOnly

@Support(value=ORACLE)
void setWithReadOnly()

Add a WITH READ ONLY clause to the end of the subquery.