R
- The record type being returned by this querypublic interface SimpleSelectQuery<R extends Record> extends Select<R>, ConditionProvider, OrderProvider, LockProvider
This is the type of query that is possible with a SimpleSelectQuery
:
SELECT * FROM [table] WHERE [conditions] ORDER BY [ordering] LIMIT [limit clause]
SelectQuery
Modifier and Type | Method and Description | ||
---|---|---|---|
void |
addConditions(Collection<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<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 |
addLimit(int numberOfRows)
Limit the results of this select
This is the same as calling
OrderProvider.addLimit(int, int) with offset = 0 |
||
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! |
||
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! |
||
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! |
||
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! |
||
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! |
||
void |
addOrderBy(Collection<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
Indexes start at
1 in SQL! |
||
void |
addOrderBy(SortField<?>... fields)
Adds ordering fields
|
||
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.
|
||
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:
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 |
setOrderBySiblings(boolean orderBySiblings)
Indicate whether the
SIBLINGS keyword should be used in an
ORDER BY clause to form an ORDER SIBLINGS BY
clause. |
bind, bind, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetch, fetchAny, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArray, fetchArrays, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchGroups, fetchInto, fetchInto, fetchInto, fetchLater, fetchLater, fetchLazy, fetchLazy, fetchMany, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMaps, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOneArray, fetchOneMap, fetchResultSet, getRecordType, getResult, keepStatement, maxRows, queryTimeout
cancel, close, execute, getBindValues, getParam, getParams, getSQL, getSQL, isExecutable
internalAPI
@Support void addConditions(Condition... conditions)
Operator.AND
addConditions
in interface ConditionProvider
conditions
- The condition@Support void addConditions(Collection<Condition> conditions)
Operator.AND
addConditions
in interface ConditionProvider
conditions
- The condition@Support void addConditions(Operator operator, Condition... conditions)
addConditions
in interface ConditionProvider
conditions
- The condition@Support void addConditions(Operator operator, Collection<Condition> conditions)
addConditions
in interface ConditionProvider
conditions
- The condition@Support void addOrderBy(Field<?>... fields)
addOrderBy
in interface OrderProvider
fields
- The ordering fields@Support void addOrderBy(SortField<?>... fields)
addOrderBy
in interface OrderProvider
fields
- The ordering fields@Support void addOrderBy(Collection<SortField<?>> fields)
addOrderBy
in interface OrderProvider
fields
- The ordering fields@Support void addOrderBy(int... fieldIndexes)
Indexes start at 1
in SQL!
Note, you can use addOrderBy(Factory.val(1).desc())
or
addOrderBy(Factory.literal(1).desc())
to apply descending
ordering
addOrderBy
in interface OrderProvider
fieldIndexes
- The ordering fields@Support(value={CUBRID,ORACLE}) void setOrderBySiblings(boolean orderBySiblings)
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.
setOrderBySiblings
in interface OrderProvider
@Support void addLimit(int numberOfRows)
This is the same as calling OrderProvider.addLimit(int, int)
with offset = 0
addLimit
in interface OrderProvider
numberOfRows
- The number of rows to return@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HSQLDB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE}) void addLimit(Param<Integer> numberOfRows)
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 simulated with a
ROW_NUMBER()
window function and nested SELECT
statements.
This is the same as calling OrderProvider.addLimit(int, int)
with offset = 0
addLimit
in interface OrderProvider
numberOfRows
- The number of rows to return@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HSQLDB,INGRES,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE}) void addLimit(int offset, int numberOfRows)
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
simulated with a ROW_NUMBER()
window function and nested
SELECT
statements.
addLimit
in interface OrderProvider
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HSQLDB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE}) void addLimit(Param<Integer> offset, int numberOfRows)
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 simulated with a ROW_NUMBER()
window function
and nested SELECT
statements.
addLimit
in interface OrderProvider
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HSQLDB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE}) void addLimit(int offset, Param<Integer> numberOfRows)
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 simulated with a ROW_NUMBER()
window function
and nested SELECT
statements.
addLimit
in interface OrderProvider
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return@Support(value={CUBRID,DB2,DERBY,FIREBIRD,H2,HSQLDB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE}) void addLimit(Param<Integer> offset, Param<Integer> numberOfRows)
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 simulated with a ROW_NUMBER()
window function
and nested SELECT
statements.
addLimit
in interface OrderProvider
offset
- The lowest offset starting at 0numberOfRows
- The number of rows to return@Support(value={ASE,CUBRID,DB2,DERBY,FIREBIRD,H2,HSQLDB,INGRES,MYSQL,ORACLE,POSTGRES,SQLSERVER,SYBASE}) void setForUpdate(boolean forUpdate)
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 simulation of a
FOR UPDATE
clause using CONCUR_UPDATABLE
for
these dialects:
Note: This simulation may not be efficient for large result sets!
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 LockProvider.setForShare(boolean)
setForUpdate
in interface LockProvider
forUpdate
- The flag's value@Support(value={DB2,DERBY,FIREBIRD,H2,HSQLDB,INGRES,ORACLE,SYBASE}) void setForUpdateOf(Field<?>... fields)
FOR UPDATE
clause, instead of the full row.
This automatically sets the LockProvider.setForUpdate(boolean)
flag, and
unsets the LockProvider.setForShare(boolean)
flag, if it was previously set.
This has been observed to be natively supported by any of these dialects:
Note, that SQLDialect.DB2
has some stricter requirements
regarding the updatability of fields. Refer to the DB2 documentation for
further details
setForUpdateOf
in interface LockProvider
fields
- The fields that should be locked@Support(value={DB2,DERBY,FIREBIRD,H2,HSQLDB,INGRES,ORACLE,SYBASE}) void setForUpdateOf(Collection<? extends Field<?>> fields)
FOR UPDATE
clause, instead of the full row.
setForUpdateOf
in interface LockProvider
LockProvider.setForUpdateOf(Field...)
@Support(value={DB2,DERBY,FIREBIRD,H2,HSQLDB,INGRES,POSTGRES,ORACLE,SYBASE}) void setForUpdateOf(Table<?>... tables)
FOR UPDATE
clause, instead of the full row.
This automatically sets the LockProvider.setForUpdate(boolean)
flag, and
unsets the LockProvider.setForShare(boolean)
flag, if it was previously set.
This has been observed to be natively supported by any of these dialects:
jOOQ simulates this by locking all known fields of [tables
]
for any of these dialects:
setForUpdateOf
in interface LockProvider
tables
- The tables that should be locked@Support(value=ORACLE) void setForUpdateWait(int seconds)
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 LockProvider.setForUpdate(boolean)
flag, and
unsets the LockProvider.setForShare(boolean)
flag, if it was previously set.
This has been observed to be supported by any of these dialects:
setForUpdateWait
in interface LockProvider
seconds
- The number of seconds to wait for a lock@Support(value=ORACLE) void setForUpdateNoWait()
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 LockProvider.setForUpdate(boolean)
flag, and
unsets the LockProvider.setForShare(boolean)
flag, if it was previously set.
This has been observed to be supported by any of these dialects:
setForUpdateNoWait
in interface LockProvider
@Support(value=ORACLE) void setForUpdateSkipLocked()
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 LockProvider.setForUpdate(boolean)
flag, and
unsets the LockProvider.setForShare(boolean)
flag, if it was previously set.
This has been observed to be supported by any of these dialects:
setForUpdateSkipLocked
in interface LockProvider
@Support(value={MYSQL,POSTGRES}) void setForShare(boolean forShare)
This has been observed to be supported by any of these dialects:
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 LockProvider.setForUpdate(boolean)
setForShare
in interface LockProvider
forShare
- The flag's valueCopyright © 2013. All Rights Reserved.