- All Superinterfaces:
Attachable
,AttachableQueryPart
,AutoCloseable
,ConditionProvider
,FieldLike
,Fields
,Flow.Publisher<R>
,Iterable<R>
,Publisher<R>
,org.reactivestreams.Publisher<R>
,Query
,QueryPart
,ResultQuery<R>
,Select<R>
,Serializable
,Statement
,TableLike<R>
SELECT
statement (model API).
This type is the model API representation of a Select
statement,
which can be mutated after creation. The advantage of this API compared to
the DSL API is a more simple approach to writing dynamic SQL.
Instances can be created using DSLContext.selectQuery()
and overloads.
- Author:
- Lukas Eder
-
Method Summary
Modifier and TypeMethodDescriptionvoid
addConditions(Collection<? extends Condition> conditions)
Adds new conditions to the query, connecting them to existing conditions withOperator.AND
.void
addConditions(Condition condition)
Adds a new condition to the query, connecting them to existing conditions withOperator.AND
.void
addConditions(Condition... conditions)
Adds new conditions to the query, connecting them to existing conditions withOperator.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 condition)
Adds a new condition 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-specificCONNECT BY
clause to the query.void
addConnectByNoCycle(Condition condition)
Add an Oracle-specificCONNECT BY NOCYCLE
clause to the query.void
addDistinctOn(Collection<? extends SelectFieldOrAsterisk> fields)
Add a PostgreSQL-specificDISTINCT ON (fields...)
clause.void
addDistinctOn(SelectFieldOrAsterisk... fields)
Add a PostgreSQL-specificDISTINCT ON (fields...)
clause.void
addFrom(Collection<? extends TableLike<?>> from)
Add tables to the table product.void
Add tables to the table product.void
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 withOperator.AND
.void
Adds a new condition to the having clause of the query, connecting it with each other withOperator.AND
.void
Adds new conditions to the having clause of the query, connecting them with each other withOperator.AND
.void
addHaving(Operator operator, Collection<? extends Condition> conditions)
Adds new conditions to the having clause of query, connecting them with each other withoperator
.void
Adds a new condition to the having clause of query, connecting it with each other withoperator
.void
Adds new conditions to the having clause of query, connecting them with each other withoperator
.void
Add an Oracle-style hint to the select clause.void
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.void
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.void
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.void
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.void
Joins the existing table product to a new table using conditions, connecting them with each other withOperator.AND
.void
Joins the existing table product to a new table using conditions, connecting them with each other withOperator.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 aUSING
clause.void
addJoinUsing(TableLike<?> table, JoinType type, Collection<? extends Field<?>> fields)
Joins the existing table product to a new table with aUSING
clause.void
addLimit(int numberOfRows)
Limit the results of this select.void
addLimit(int offset, int numberOfRows)
Limit the results of this select.void
Limit the results of this select using named parameters.void
Limit the results of this select.void
Limit the results of this select.void
Limit the results of this select using named parameters.void
Limit the results of this select using named parameters.void
Limit the results of this select.void
Limit the results of this select using named parameters.void
Limit the results of this select.void
addOffset(int offset)
Add anOFFSET
clause to the query.void
Add anOFFSET
clause to the query.void
Add anOFFSET
clause to the query using a named parameter.void
Add a SQL Server-style query hint to the select clause.void
addOrderBy(int... fieldIndexes)
Adds ordering fields.void
addOrderBy(Collection<? extends OrderField<?>> fields)
Adds ordering fields.void
addOrderBy(OrderField<?>... fields)
Adds ordering fields.void
addQualify(Collection<? extends Condition> conditions)
Adds new conditions to the qualify clause of the query, connecting them with each other withOperator.AND
.void
addQualify(Condition condition)
Adds a new condition to the qualify clause of the query, connecting it with each other withOperator.AND
.void
addQualify(Condition... conditions)
Adds new conditions to the qualify clause of the query, connecting them with each other withOperator.AND
.void
addQualify(Operator operator, Collection<? extends Condition> conditions)
Adds new conditions to the qualify clause of query, connecting them with each other withoperator
.void
addQualify(Operator operator, Condition condition)
Adds a new condition to the qualify clause of query, connecting it with each other withoperator
.void
addQualify(Operator operator, Condition... conditions)
Adds new conditions to the qualify clause of query, connecting them with each other withoperator
.void
addSeekAfter(Collection<? extends Field<?>> fields)
Adds seeking fields.void
addSeekAfter(Field<?>... fields)
Adds seeking fields.void
addSeekBefore(Collection<? extends Field<?>> fields)
Deprecated.- [#7461] - SEEK BEFORE is not implemented correctlyvoid
addSeekBefore(Field<?>... fields)
Deprecated.- [#7461] - SEEK BEFORE is not implemented correctlyvoid
addSelect(Collection<? extends SelectFieldOrAsterisk> fields)
Add a list of select fields.void
addSelect(SelectFieldOrAsterisk... 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-specificSTART WITH
clause to the query'sCONNECT BY
clause.void
setDistinct(boolean distinct)
Add "distinct" keyword to the select clause.void
setForJSONAuto(boolean forJSONAuto)
Add aFOR JSON AUTO
clause to the end of the query.void
setForJSONB(boolean forJSONB)
void
setForJSONIncludeNullValues(boolean forJSONIncludeNullValues)
Add aFOR JSON ROOT
clause to the end of the query.void
setForJSONPath(boolean forJSONPath)
Add aFOR JSON PATH
clause to the end of the query.void
setForJSONRoot(boolean forJSONRoot)
Add aFOR JSON ROOT
clause to the end of the query.void
setForJSONRoot(boolean forJSONRoot, String rootName)
Add aFOR JSON ROOT
clause to the end of the query.void
setForJSONWithoutArrayWrapper(boolean forJSONWithoutArrayWrapper)
Add aFOR JSON ROOT
clause to the end of the query.void
setForKeyShare(boolean forKeyShare)
Sets the "FOR KEY SHARE" lock mode onto the query.void
Some RDBMS allow for specifying the locking mode for the appliedFOR <lock_mode>
clause.void
setForLockModeOf(Collection<? extends Field<?>> fields)
Some RDBMS allow for specifying the fields that should be locked by theFOR <lock_mode>
clause, instead of the full row.void
setForLockModeOf(Field<?>... fields)
Some RDBMS allow for specifying the fields that should be locked by theFOR <lock_mode>
clause, instead of the full row.void
setForLockModeOf(Table<?>... tables)
Some RDBMS allow for specifying the tables that should be locked by theFOR <lock_mode>
clause, instead of the full row.void
Some RDBMS allow for specifying the locking mode for the appliedFOR <lock_mode>
clause.void
setForLockModeWait(int seconds)
Some RDBMS allow for specifying the locking mode for the appliedFOR <lock_mode>
clause.void
setForNoKeyUpdate(boolean forNoKeyUpdate)
Sets the "FOR NO KEY UPDATE" lock mode onto the query.void
setForShare(boolean forShare)
Sets the "FOR SHARE" lock mode onto the query.void
setForUpdate(boolean forUpdate)
Sets the "FOR UPDATE" lock mode onto the query.void
Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeNoWait()
void
setForUpdateOf(Collection<? extends Field<?>> fields)
Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeOf(Collection)
void
setForUpdateOf(Field<?>... fields)
Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeOf(Field...)
void
setForUpdateOf(Table<?>... tables)
Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeOf(Table...)
void
Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeSkipLocked()
void
setForUpdateWait(int seconds)
Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeWait(int)
void
setForXMLAuto(boolean forXMLAuto)
Add aFOR XML AUTO
clause to the end of the query.void
setForXMLElements(boolean forXMLElements)
Add aFOR XML ELEMENTS
clause to the end of the query.void
setForXMLElementsAbsent(boolean forXMLElementsAbsent)
Add aFOR XML ELEMENTS ABSENT
clause to the end of the query.void
setForXMLElementsXsinil(boolean forXMLElementsXsinil)
Add aFOR XML ELEMENTS XSINIL
clause to the end of the query.void
setForXMLPath(boolean forXMLPath)
Add aFOR XML PATH
clause to the end of the query.void
setForXMLPath(boolean forXMLPath, String elementName)
Add aFOR XML PATH
clause to the end of the query.void
setForXMLRaw(boolean forXMLRaw)
Add aFOR XML RAW
clause to the end of the query.void
setForXMLRaw(boolean forXMLRaw, String elementName)
Add aFOR XML RAW
clause to the end of the query.void
setForXMLRoot(boolean forXMLRoot)
Add aFOR XML ROOT
clause to the end of the query.void
setForXMLRoot(boolean forXMLRoot, String rootName)
Add aFOR XML ROOT
clause to the end of the query.void
setForXMLType(boolean forXMLRoot)
Add aFOR XML TYPE
clause to the end of the query.void
setInto(Collection<? extends Variable<?>> variables)
Add a PL/SQL styleINTO
clause to theSELECT
statement to assign the projection of a single rowSELECT
statement to local variables in a procedural context.void
Add a T-SQL styleINTO
clause to theSELECT
statement to create a new table from aSELECT
statement.void
setLimitPercent(boolean percent)
Add thePERCENT
clause to aLIMIT
clause.void
setOrderBySiblings(boolean orderBySiblings)
Indicate whether theSIBLINGS
keyword should be used in anORDER BY
clause to form anORDER SIBLINGS BY
clause.void
Add aWITH CHECK OPTION
clause to the end of the subquery.void
Add aWITH READ ONLY
clause to the end of the subquery.void
setWithTies(boolean withTies)
Add theWITH TIES
clause to aLIMIT
clause.Methods inherited from interface org.jooq.Attachable
attach, configuration, detach
Methods inherited from interface org.jooq.AttachableQueryPart
getBindValues, getParam, getParams, getSQL, getSQL
Methods inherited from interface org.jooq.Fields
dataType, dataType, dataType, dataTypes, field, field, field, field, field, field, field, field, field, field, fields, fields, fields, fields, fields, fieldsRow, fieldStream, indexOf, indexOf, indexOf, type, type, type, types
Methods inherited from interface org.reactivestreams.Publisher
subscribe
Methods inherited from interface org.jooq.Query
cancel, close, execute, executeAsync, executeAsync, isExecutable
Methods inherited from interface org.jooq.ResultQuery
bind, bind, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, coerce, collect, 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, fetchAsync, fetchAsync, 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, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchInto, fetchInto, fetchInto, 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, 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, fetchSet, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingle, fetchSingleArray, fetchSingleInto, fetchSingleInto, fetchSingleMap, fetchSize, fetchStream, fetchStreamInto, fetchStreamInto, getRecordType, getResult, intern, intern, intern, intern, iterator, keepStatement, maxRows, poolable, queryTimeout, resultSetConcurrency, resultSetHoldability, resultSetType, spliterator, stream
-
Method Details
-
addSelect
Add a list of select fields.- Parameters:
fields
-
-
addSelect
Add a list of select fields.- Parameters:
fields
-
-
setDistinct
Add "distinct" keyword to the select clause. -
addDistinctOn
@Support({AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addDistinctOn(SelectFieldOrAsterisk... fields)Add a PostgreSQL-specificDISTINCT ON (fields...)
clause.This also sets the
distinct
flag totrue
-
addDistinctOn
@Support({AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addDistinctOn(Collection<? extends SelectFieldOrAsterisk> fields)Add a PostgreSQL-specificDISTINCT ON (fields...)
clause.This also sets the
distinct
flag totrue
-
setInto
@Support({ACCESS,ASE,AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,H2,HANA,HSQLDB,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,TERADATA,VERTICA}) void setInto(Table<?> table)Add a T-SQL styleINTO
clause to theSELECT
statement to create a new table from aSELECT
statement. -
setInto
@Pro @Support({AURORA_POSTGRES,DB2,HANA,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES,SQLDATAWAREHOUSE,SQLSERVER}) void setInto(Collection<? extends Variable<?>> variables)Add a PL/SQL styleINTO
clause to theSELECT
statement to assign the projection of a single rowSELECT
statement to local variables in a procedural context. -
addFrom
Add tables to the table product.- Parameters:
from
- The added tables
-
addFrom
Add tables to the table product.- Parameters:
from
- The added tables
-
addFrom
Add tables to the table product.- Parameters:
from
- The added tables
-
addJoin
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.- Parameters:
table
- The joined tablecondition
- The joining condition
-
addJoin
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.- Parameters:
table
- The joined tableconditions
- The joining conditions
-
addJoin
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.- Parameters:
table
- The joined tabletype
- The type of joincondition
- The joining condition
-
addJoin
Joins the existing table product to a new table using a condition, connecting them with each other withOperator.AND
.- Parameters:
table
- The joined tabletype
- The type of joinconditions
- The joining conditions
-
addJoin
@Support(ORACLE) @Pro 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 withOperator.AND
.This adds a
PARTITION BY
clause to the right hand side of aOUTER JOIN
expression.- Parameters:
table
- The joined tabletype
- The type of joinconditions
- The joining conditionspartitionBy
- ThePARTITION BY
expression- See Also:
TablePartitionByStep
-
addJoin
@Support(ORACLE) @Pro 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 withOperator.AND
.This adds a
PARTITION BY
clause to the right hand side of aOUTER JOIN
expression.- Parameters:
table
- The joined tabletype
- The type of joinconditions
- The joining conditionspartitionBy
- ThePARTITION BY
expression- See Also:
TablePartitionByStep
-
addJoinUsing
Joins the existing table product to a new table with aUSING
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 tablefields
- The fields for theUSING
clause
-
addJoinUsing
@Support void addJoinUsing(TableLike<?> table, JoinType type, Collection<? extends Field<?>> fields)Joins the existing table product to a new table with aUSING
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 tabletype
- The type of joinfields
- The fields for theUSING
clause
-
addJoinOnKey
Joins the existing table product to a new table using a foreign key.- Parameters:
table
- The joined tabletype
- 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 DataAccessExceptionJoins the existing table product to a new table using a foreign key.- Parameters:
table
- The joined tabletype
- The type of joinkeyFields
- The foreign key fields- Throws:
DataAccessException
- If there is no non-ambiguous key definition known to jOOQ- See Also:
TableOnStep.onKey(ForeignKey)
-
addJoinOnKey
Joins the existing table product to a new table using a foreign key.- Parameters:
table
- The joined tabletype
- The type of joinkey
- The foreign key- See Also:
TableOnStep.onKey(ForeignKey)
-
addGroupBy
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
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
Adds a new condition to the having clause of the query, connecting it with each other withOperator.AND
.- Parameters:
condition
- The condition
-
addHaving
Adds new conditions to the having clause of the query, connecting them with each other withOperator.AND
.- Parameters:
conditions
- The condition
-
addHaving
Adds new conditions to the having clause of the query, connecting them with each other withOperator.AND
.- Parameters:
conditions
- The condition
-
addHaving
Adds a new condition to the having clause of query, connecting it with each other withoperator
.- Parameters:
operator
- The operator to use to add the conditions to the existing conditionscondition
- The condition
-
addHaving
Adds new conditions to the having clause of query, connecting them with each other withoperator
.- Parameters:
operator
- The operator to use to add the conditions to the existing conditionsconditions
- The condition
-
addHaving
Adds new conditions to the having clause of query, connecting them with each other withoperator
.- Parameters:
operator
- The operator to use to add the conditions to the existing conditionsconditions
- The condition
-
addWindow
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addWindow(WindowDefinition... definitions)Adds new window definitions to the window clause of the query.- Parameters:
definitions
- The definitions
-
addWindow
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addWindow(Collection<? extends WindowDefinition> definitions)Adds new window definitions to the window clause of the query.- Parameters:
definitions
- The definitions
-
addQualify
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addQualify(Condition condition)Adds a new condition to the qualify clause of the query, connecting it with each other withOperator.AND
.- Parameters:
condition
- The condition
-
addQualify
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addQualify(Condition... conditions)Adds new conditions to the qualify clause of the query, connecting them with each other withOperator.AND
.- Parameters:
conditions
- The condition
-
addQualify
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addQualify(Collection<? extends Condition> conditions)Adds new conditions to the qualify clause of the query, connecting them with each other withOperator.AND
.- Parameters:
conditions
- The condition
-
addQualify
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addQualify(Operator operator, Condition condition)Adds a new condition to the qualify clause of query, connecting it with each other withoperator
.- Parameters:
operator
- The operator to use to add the conditions to the existing conditionscondition
- The condition
-
addQualify
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addQualify(Operator operator, Condition... conditions)Adds new conditions to the qualify clause of query, connecting them with each other withoperator
.- Parameters:
operator
- The operator to use to add the conditions to the existing conditionsconditions
- The condition
-
addQualify
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addQualify(Operator operator, Collection<? extends Condition> conditions)Adds new conditions to the qualify clause of query, connecting them with each other withoperator
.- Parameters:
operator
- The operator to use to add the conditions to the existing conditionsconditions
- The condition
-
addHint
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
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({AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD,H2,HSQLDB,INFORMIX,MARIADB,MYSQL_8_0,ORACLE,POSTGRES,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA}) @Pro void addConnectBy(Condition condition)Add an Oracle-specificCONNECT BY
clause to the query.Simple versions of this clause can be emulated using
DSL.generateSeries(int, int)
. -
addConnectByNoCycle
Add an Oracle-specificCONNECT BY NOCYCLE
clause to the query. -
setConnectByStartWith
@Support({CUBRID,EXASOL,INFORMIX,ORACLE,SNOWFLAKE}) @Pro void setConnectByStartWith(Condition condition)Add an Oracle-specificSTART WITH
clause to the query'sCONNECT BY
clause. -
addConditions
Description copied from interface:ConditionProvider
Adds a new condition to the query, connecting them to existing conditions withOperator.AND
.- Specified by:
addConditions
in interfaceConditionProvider
- Parameters:
condition
- The condition
-
addConditions
Description copied from interface:ConditionProvider
Adds new conditions to the query, connecting them to existing conditions withOperator.AND
.- Specified by:
addConditions
in interfaceConditionProvider
- Parameters:
conditions
- The condition
-
addConditions
Description copied from interface:ConditionProvider
Adds new conditions to the query, connecting them to existing conditions withOperator.AND
.- Specified by:
addConditions
in interfaceConditionProvider
- Parameters:
conditions
- The condition
-
addConditions
Description copied from interface:ConditionProvider
Adds a new condition to the query, connecting them to existing conditions with the provided operator.- Specified by:
addConditions
in interfaceConditionProvider
condition
- The condition
-
addConditions
Description copied from interface:ConditionProvider
Adds new conditions to the query, connecting them to existing conditions with the provided operator.- Specified by:
addConditions
in interfaceConditionProvider
conditions
- The condition
-
addConditions
Description copied from interface:ConditionProvider
Adds new conditions to the query, connecting them to existing conditions with the provided operator.- Specified by:
addConditions
in interfaceConditionProvider
conditions
- The condition
-
addOrderBy
Adds ordering fields.- Parameters:
fields
- The ordering fields
-
addOrderBy
Adds ordering fields.- Parameters:
fields
- The ordering fields
-
addOrderBy
Adds ordering fields.Indexes start at
1
in SQL!Note, you can use
addOrderBy(DSL.val(1).desc())
oraddOrderBy(DSL.literal(1).desc())
to apply descending ordering- Parameters:
fieldIndexes
- The ordering fields
-
setOrderBySiblings
Indicate whether theSIBLINGS
keyword should be used in anORDER BY
clause to form anORDER 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
Adds seeking fields.- Parameters:
fields
- The seeking fields
-
addSeekAfter
Adds seeking fields.- Parameters:
fields
- The seeking fields
-
addSeekBefore
Deprecated.- [#7461] - SEEK BEFORE is not implemented correctlyAdds seeking fields.- Parameters:
fields
- The seeking fields
-
addSeekBefore
Deprecated.- [#7461] - SEEK BEFORE is not implemented correctlyAdds seeking fields.- Parameters:
fields
- The seeking fields
-
addOffset
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addOffset(int offset)Add anOFFSET
clause to the query.If there is no
LIMIT .. OFFSET
orTOP
clause in your RDBMS, or if your RDBMS does not natively support offsets, this is emulated with aROW_NUMBER()
window function and nestedSELECT
statements. -
addOffset
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addOffset(Number offset)Add anOFFSET
clause to the query.If there is no
LIMIT .. OFFSET
orTOP
clause in your RDBMS, or if your RDBMS does not natively support offsets, this is emulated with aROW_NUMBER()
window function and nestedSELECT
statements. -
addOffset
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addOffset(Param<? extends Number> offset)Add anOFFSET
clause to the query using a named parameter.If there is no
LIMIT .. OFFSET
orTOP
clause in your RDBMS, or if your RDBMS does not natively support offsets, this is emulated with aROW_NUMBER()
window function and nestedSELECT
statements. -
addLimit
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
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({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addLimit(Param<? extends Number> numberOfRows)Limit the results of this select using named parameters.Note that some dialects do not support bind values at all in
LIMIT
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or theLIMIT
orTOP
clause does not support bind values, this may be emulated with aROW_NUMBER()
window function and nestedSELECT
statements.This is the same as calling
addLimit(int, int)
with offset = 0- Parameters:
numberOfRows
- The number of rows to return
-
addLimit
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,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
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or if your RDBMS does not natively support offsets, this is emulated with aROW_NUMBER()
window function and nestedSELECT
statements.- Parameters:
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return
-
addLimit
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addLimit(Number offset, Number numberOfRows)Limit the results of this select.Note that some dialects do not support bind values at all in
LIMIT
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or if your RDBMS does not natively support offsets, this is emulated with aROW_NUMBER()
window function and nestedSELECT
statements.- Parameters:
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return
-
addLimit
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,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
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or theLIMIT
orTOP
clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with aROW_NUMBER()
window function and nestedSELECT
statements.- Parameters:
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return
-
addLimit
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addLimit(Param<? extends Number> offset, Number numberOfRows)Limit the results of this select.Note that some dialects do not support bind values at all in
LIMIT
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or theLIMIT
orTOP
clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with aROW_NUMBER()
window function and nestedSELECT
statements.- Parameters:
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return
-
addLimit
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,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
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or theLIMIT
orTOP
clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with aROW_NUMBER()
window function and nestedSELECT
statements.- Parameters:
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return
-
addLimit
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addLimit(Number offset, Param<? extends Number> numberOfRows)Limit the results of this select using named parameters.Note that some dialects do not support bind values at all in
LIMIT
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or theLIMIT
orTOP
clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with aROW_NUMBER()
window function and nestedSELECT
statements.- Parameters:
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return
-
addLimit
@Support({AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void addLimit(Param<? extends Number> offset, Param<? extends Number> numberOfRows)Limit the results of this select using named parameters.Note that some dialects do not support bind values at all in
LIMIT
orTOP
clauses!If there is no
LIMIT
orTOP
clause in your RDBMS, or theLIMIT
orTOP
clause does not support bind values, or if your RDBMS does not natively support offsets, this may be emulated with aROW_NUMBER()
window function and nestedSELECT
statements.- Parameters:
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return
-
setLimitPercent
Add thePERCENT
clause to aLIMIT
clause. -
setWithTies
@Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,CUBRID,DB2,EXASOL,FIREBIRD_3_0,H2,HANA,INFORMIX,MARIADB,MYSQL_8_0,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLSERVER,SYBASE,TERADATA,VERTICA}) void setWithTies(boolean withTies)Add theWITH TIES
clause to aLIMIT
clause. -
setForUpdate
@Support({ASE,AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,SQLDATAWAREHOUSE,SQLSERVER,SYBASE,TERADATA}) void setForUpdate(boolean forUpdate)Sets the "FOR UPDATE" lock mode 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
SQLDialect.SQLSERVER
: jOOQ will try to lock the database record usingWITH (ROWLOCK, UPDLOCK)
hints.Not supported
These dialects are known not to support the
FOR UPDATE
clause in regular SQL: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
-
setForNoKeyUpdate
Sets the "FOR NO KEY UPDATE" lock mode onto the query. -
setForUpdateOf
@Deprecated(forRemoval=true, since="3.14") @Support({COCKROACHDB,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,ORACLE,SYBASE}) void setForUpdateOf(Field<?>... fields)Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeOf(Field...)
Some RDBMS allow for specifying the fields that should be locked by theFOR UPDATE
clause, instead of the full row.This automatically sets the
setForUpdate(boolean)
flag, and unsets thesetForShare(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
@Deprecated(forRemoval=true, since="3.14") @Support({COCKROACHDB,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,ORACLE,SYBASE}) void setForUpdateOf(Collection<? extends Field<?>> fields)Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeOf(Collection)
Some RDBMS allow for specifying the fields that should be locked by theFOR UPDATE
clause, instead of the full row.- See Also:
setForUpdateOf(Field...)
-
setForUpdateOf
@Deprecated(forRemoval=true, since="3.14") @Support({AURORA_POSTGRES,COCKROACHDB,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,MYSQL_8_0,ORACLE,POSTGRES,SQLSERVER,SYBASE}) void setForUpdateOf(Table<?>... tables)Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeOf(Table...)
Some RDBMS allow for specifying the tables that should be locked by theFOR UPDATE
clause, instead of the full row.This automatically sets the
setForUpdate(boolean)
flag, and unsets thesetForShare(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
@Deprecated(forRemoval=true, since="3.14") @Support({MARIADB_10_3,ORACLE}) void setForUpdateWait(int seconds)Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeWait(int)
Some RDBMS allow for specifying the locking mode for the appliedFOR UPDATE
clause. In this case, the session will wait for someseconds
, before aborting the lock acquirement if the lock is not available.This automatically sets the
setForUpdate(boolean)
flag, and unsets thesetForShare(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
@Deprecated(forRemoval=true, since="3.14") @Support({AURORA_POSTGRES,MARIADB_10_3,MYSQL_8_0,ORACLE,POSTGRES,SQLSERVER}) void setForUpdateNoWait()Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeNoWait()
Some RDBMS allow for specifying the locking mode for the appliedFOR 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 thesetForShare(boolean)
flag, if it was previously set.This has been observed to be supported by any of these dialects:
- Oracle
-
setForUpdateSkipLocked
@Deprecated(forRemoval=true, since="3.14") @Support({AURORA_POSTGRES,MYSQL_8_0,ORACLE,POSTGRES_9_5,SQLSERVER}) void setForUpdateSkipLocked()Deprecated, for removal: This API element is subject to removal in a future version.[#5218] - 3.14.0 - UsesetForLockModeSkipLocked()
Some RDBMS allow for specifying the locking mode for the appliedFOR 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 thesetForShare(boolean)
flag, if it was previously set.This has been observed to be supported by any of these dialects:
- Oracle
-
setForLockModeOf
@Support({DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,ORACLE,SYBASE}) void setForLockModeOf(Field<?>... fields)Some RDBMS allow for specifying the fields that should be locked by theFOR <lock_mode>
clause, instead of the full row.In case no lock mode has been set yet, it will implicitly be set to
UPDATE
(i.e.setForUpdate(boolean)
).Depending on the dialect and lock mode this flag may or may not be supported.
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
-
setForLockModeOf
@Support({DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,ORACLE,SYBASE}) void setForLockModeOf(Collection<? extends Field<?>> fields)Some RDBMS allow for specifying the fields that should be locked by theFOR <lock_mode>
clause, instead of the full row.- See Also:
setForLockModeOf(Field...)
-
setForLockModeOf
@Support({AURORA_POSTGRES,COCKROACHDB,DB2,DERBY,FIREBIRD,H2,HANA,HSQLDB,INFORMIX,INGRES,MYSQL_8_0,ORACLE,POSTGRES,SQLSERVER,SYBASE}) void setForLockModeOf(Table<?>... tables)Some RDBMS allow for specifying the tables that should be locked by theFOR <lock_mode>
clause, instead of the full row.In case no lock mode has been set yet, it will implicitly be set to
UPDATE
(i.e.setForUpdate(boolean)
).Depending on the dialect and lock mode this flag may or may not be supported.
- Parameters:
tables
- The tables that should be locked
-
setForLockModeWait
@Support({AURORA_MYSQL,AURORA_POSTGRES,MARIADB,MYSQL,ORACLE,POSTGRES}) void setForLockModeWait(int seconds)Some RDBMS allow for specifying the locking mode for the appliedFOR <lock_mode>
clause. In this case, the session will wait for someseconds
, before aborting the lock acquirement if the lock is not available.In case no lock mode has been set yet, it will implicitly be set to
UPDATE
(i.e.setForUpdate(boolean)
).Depending on the dialect and lock mode this flag may or may not be supported.
- Parameters:
seconds
- The number of seconds to wait for a lock
-
setForLockModeNoWait
@Support({AURORA_POSTGRES,MARIADB_10_3,MYSQL_8_0,ORACLE,POSTGRES,SQLSERVER}) void setForLockModeNoWait()Some RDBMS allow for specifying the locking mode for the appliedFOR <lock_mode>
clause. In this case, the session will not wait before aborting the lock acquirement if the lock is not available.In case no lock mode has been set yet, it will implicitly be set to
UPDATE
(i.e.setForUpdate(boolean)
).Depending on the dialect and lock mode this flag may or may not be supported.
-
setForLockModeSkipLocked
Some RDBMS allow for specifying the locking mode for the appliedFOR <lock_mode>
clause. In this case, the session will skip all locked rows from the select statement, whose lock is not available.In case no lock mode has been set yet, it will implicitly be set to
UPDATE
(i.e.setForUpdate(boolean)
).Depending on the dialect and lock mode this flag may or may not be supported.
-
setForXMLRaw
Add aFOR XML RAW
clause to the end of the query. -
setForXMLRaw
@Support({DB2,ORACLE,POSTGRES,SQLSERVER,TERADATA}) @Pro void setForXMLRaw(boolean forXMLRaw, String elementName)Add aFOR XML RAW
clause to the end of the query. -
setForXMLAuto
Add aFOR XML AUTO
clause to the end of the query. -
setForXMLPath
Add aFOR XML PATH
clause to the end of the query. -
setForXMLPath
@Support({DB2,ORACLE,POSTGRES,SQLSERVER,TERADATA}) @Pro void setForXMLPath(boolean forXMLPath, String elementName)Add aFOR XML PATH
clause to the end of the query. -
setForXMLType
Add aFOR XML TYPE
clause to the end of the query. -
setForXMLRoot
Add aFOR XML ROOT
clause to the end of the query. -
setForXMLRoot
@Support({DB2,ORACLE,POSTGRES,SQLSERVER,TERADATA}) @Pro void setForXMLRoot(boolean forXMLRoot, String rootName)Add aFOR XML ROOT
clause to the end of the query. -
setForXMLElements
@Support({DB2,ORACLE,POSTGRES,SQLSERVER,TERADATA}) @Pro void setForXMLElements(boolean forXMLElements)Add aFOR XML ELEMENTS
clause to the end of the query. -
setForXMLElementsXsinil
Add aFOR XML ELEMENTS XSINIL
clause to the end of the query. -
setForXMLElementsAbsent
Add aFOR XML ELEMENTS ABSENT
clause to the end of the query. -
setForJSONB
@Support({AURORA_POSTGRES,COCKROACHDB,DB2_11,H2,MARIADB_10_5,MYSQL_5_7,ORACLE12C,POSTGRES,SQLSERVER}) @Pro void setForJSONB(boolean forJSONB) -
setForJSONAuto
@Support({AURORA_POSTGRES,COCKROACHDB,DB2_11,H2,MARIADB_10_5,MYSQL_5_7,ORACLE12C,POSTGRES,SQLSERVER}) @Pro void setForJSONAuto(boolean forJSONAuto)Add aFOR JSON AUTO
clause to the end of the query. -
setForJSONPath
@Support({AURORA_POSTGRES,COCKROACHDB,DB2_11,H2,MARIADB_10_5,MYSQL_5_7,ORACLE12C,POSTGRES,SQLSERVER}) @Pro void setForJSONPath(boolean forJSONPath)Add aFOR JSON PATH
clause to the end of the query. -
setForJSONRoot
@Support({AURORA_POSTGRES,COCKROACHDB,DB2_11,H2,MARIADB_10_5,MYSQL_5_7,ORACLE12C,POSTGRES,SQLSERVER}) @Pro void setForJSONRoot(boolean forJSONRoot)Add aFOR JSON ROOT
clause to the end of the query. -
setForJSONRoot
@Support({AURORA_POSTGRES,COCKROACHDB,DB2_11,H2,MARIADB_10_5,MYSQL_5_7,ORACLE12C,POSTGRES,SQLSERVER}) @Pro void setForJSONRoot(boolean forJSONRoot, String rootName)Add aFOR JSON ROOT
clause to the end of the query. -
setForJSONIncludeNullValues
@Support({AURORA_POSTGRES,COCKROACHDB,DB2_11,H2,MARIADB_10_5,MYSQL_5_7,ORACLE12C,POSTGRES,SQLSERVER}) @Pro void setForJSONIncludeNullValues(boolean forJSONIncludeNullValues)Add aFOR JSON ROOT
clause to the end of the query. -
setForJSONWithoutArrayWrapper
@Support({AURORA_POSTGRES,COCKROACHDB,DB2_11,H2,MARIADB_10_5,MYSQL_5_7,ORACLE12C,POSTGRES,SQLSERVER}) @Pro void setForJSONWithoutArrayWrapper(boolean forJSONWithoutArrayWrapper)Add aFOR JSON ROOT
clause to the end of the query. -
setWithCheckOption
Add aWITH CHECK OPTION
clause to the end of the subquery. -
setWithReadOnly
Add aWITH READ ONLY
clause to the end of the subquery.
-