org.jooq.SelectQuery Maven / Gradle / Ivy
/**
* Copyright (c) 2009-2014, Data Geekery GmbH (http://www.datageekery.com)
* All rights reserved.
*
* This work is dual-licensed
* - under the Apache Software License 2.0 (the "ASL")
* - under the jOOQ License and Maintenance Agreement (the "jOOQ License")
* =============================================================================
* You may choose which license applies to you:
*
* - If you're using this work with Open Source databases, you may choose
* either ASL or jOOQ License.
* - If you're using this work with at least one commercial database, you must
* choose jOOQ License
*
* For more information, please visit http://www.jooq.org/licenses
*
* Apache Software License 2.0:
* -----------------------------------------------------------------------------
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* jOOQ License and Maintenance Agreement:
* -----------------------------------------------------------------------------
* Data Geekery grants the Customer the non-exclusive, timely limited and
* non-transferable license to install and use the Software under the terms of
* the jOOQ License and Maintenance Agreement.
*
* This library is distributed with a LIMITED WARRANTY. See the jOOQ License
* and Maintenance Agreement for more details: http://www.jooq.org/licensing
*/
package org.jooq;
// ...
// ...
import static org.jooq.SQLDialect.CUBRID;
// ...
import static org.jooq.SQLDialect.DERBY;
import static org.jooq.SQLDialect.FIREBIRD;
import static org.jooq.SQLDialect.H2;
import static org.jooq.SQLDialect.HSQLDB;
// ...
// ...
import static org.jooq.SQLDialect.MARIADB;
import static org.jooq.SQLDialect.MYSQL;
// ...
import static org.jooq.SQLDialect.POSTGRES;
import static org.jooq.SQLDialect.SQLITE;
// ...
// ...
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import org.jooq.exception.DataAccessException;
/**
* A query for data selection
*
* @author Lukas Eder
*/
@SuppressWarnings("deprecation")
public interface SelectQuery extends Select, ConditionProvider {
/**
* Add a list of select fields.
*
* @param fields
*/
@Support
void addSelect(Field... fields);
/**
* Add a list of select fields.
*
* @param fields
*/
@Support
void addSelect(Collection> fields);
/**
* Add "distinct" keyword to the select clause.
*/
@Support
void setDistinct(boolean distinct);
/**
* Add a PostgreSQL-specific DISTINCT ON (fields...) clause.
*
* This also sets the distinct flag to true
*/
@Support({ POSTGRES })
void addDistinctOn(Field... fields);
/**
* Add a PostgreSQL-specific DISTINCT ON (fields...) clause.
*
* This also sets the distinct flag to true
*/
@Support({ POSTGRES })
void addDistinctOn(Collection> fields);
/**
* Add INTO clause to the SELECT statement.
*/
@Support({ CUBRID, DERBY, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE })
void setInto(Table table);
/**
* Add tables to the table product.
*
* @param from The added tables
*/
@Support
void addFrom(TableLike from);
/**
* Add tables to the table product.
*
* @param from The added tables
*/
@Support
void addFrom(TableLike... from);
/**
* Add tables to the table product.
*
* @param from The added tables
*/
@Support
void addFrom(Collection> from);
/**
* Joins the existing table product to a new table using a condition.
*
* @param table The joined table
* @param conditions The joining conditions
*/
@Support
void addJoin(TableLike table, Condition... conditions);
/**
* Joins the existing table product to a new table using a condition.
*
* @param table The joined table
* @param type The type of join
* @param conditions The joining conditions
*/
@Support
void addJoin(TableLike table, JoinType type, Condition... conditions);
/* [pro] xx
xxx
x xxxxx xxx xxxxxxxx xxxxx xxxxxxx xx x xxx xxxxx xxxxx x xxxxxxxxxx
x xxx
x xxxx xxxx x xxxxxxxxxxxxxxx xxxxxxxxx xxxxxx xx xxx xxxxx xxxx xxxx xx x
x xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx
x
x xxxxxx xxxxx xxx xxxxxx xxxxx
x xxxxxx xxxx xxx xxxx xx xxxx
x xxxxxx xxxxxxxxxx xxx xxxxxxx xxxxxxxxxx
x xxxxxx xxxxxxxxxxx xxx xxxxxxxxxxxxxxx xxxxxxxxx xxxxxxxxxx
x xxxx xxxxxxxxxxxxxxxxxxxx
xx
xxxxxxxxxxxxxxxx
xxxx xxxxxxxxxxxxxxxxxxxx xxxxxx xxxxxxxx xxxxx xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxx xxxxxxxxxxxxx
xx [/pro] */
/**
* 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 simulate
* this behaviour using the information provided in this query.
*
* @param table The joined table
* @param fields The fields for the USING clause
*/
@Support
void addJoinUsing(TableLike table, Collection> 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 simulate
* this behaviour using the information provided in this query.
*
* @param table The joined table
* @param type The type of join
* @param fields The fields for the USING clause
*/
@Support
void addJoinUsing(TableLike table, JoinType type, Collection> fields);
/**
* Joins the existing table product to a new table using a foreign key.
*
* @param table The joined table
* @param type The type of join
* @see TableOnStep#onKey(ForeignKey)
* @throws DataAccessException If there is no non-ambiguous key definition
* known to jOOQ
*/
@Support
void addJoinOnKey(TableLike table, JoinType type) throws DataAccessException;
/**
* Joins the existing table product to a new table using a foreign key.
*
* @param table The joined table
* @param type The type of join
* @param keyFields The foreign key fields
* @see TableOnStep#onKey(ForeignKey)
* @throws DataAccessException If there is no non-ambiguous key definition
* known to jOOQ
*/
@Support
void addJoinOnKey(TableLike table, JoinType type, TableField... keyFields) throws DataAccessException;
/**
* Joins the existing table product to a new table using a foreign key.
*
* @param table The joined table
* @param type The type of join
* @param key The foreign key
* @see TableOnStep#onKey(ForeignKey)
*/
@Support
void addJoinOnKey(TableLike table, JoinType type, ForeignKey key);
/**
* Adds grouping fields.
*
* Calling this with an empty argument list will result in an empty
* GROUP BY () clause being rendered.
*
* @param fields The grouping fields
*/
@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.
*
* @param fields The grouping fields
*/
@Support
void addGroupBy(Collection fields);
/**
* Adds new conditions to the having clause of the query, connecting it to
* existing conditions with the and operator.
*
* @param conditions The condition
*/
@Support
void addHaving(Condition... conditions);
/**
* Adds new conditions to the having clause of the query, connecting it to
* existing conditions with the and operator.
*
* @param conditions The condition
*/
@Support
void addHaving(Collection conditions);
/**
* Adds new conditions to the having clause of query, connecting them to
* existing conditions with the provided operator.
*
* @param operator The operator to use to add the conditions to the existing
* conditions
* @param conditions The condition
*/
@Support
void addHaving(Operator operator, Condition... conditions);
/**
* Adds new conditions to the having clause of query, connecting them to
* existing conditions with the provided operator.
*
* @param operator The operator to use to add the conditions to the existing
* conditions
* @param conditions The condition
*/
@Support
void addHaving(Operator operator, Collection conditions);
/**
* Adds new window definitions to the window clause of the query.
*
* @param definitions The definitions
*/
@Support({ CUBRID, POSTGRES })
void addWindow(WindowDefinition... definitions);
/**
* Adds new window definitions to the window clause of the query.
*
* @param definitions The definitions
*/
@Support({ CUBRID, POSTGRES })
void addWindow(Collection definitions);
/**
* 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 {@link Table#with(String)}
*
* @see Table#with(String)
*/
@Support
void addHint(String hint);
/**
* 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 {@link Table#with(String)}
*
* @see Table#with(String)
*/
@Support
void addOption(String option);
/**
* Add an Oracle-specific CONNECT BY clause to the query.
*/
@Support({ CUBRID })
void addConnectBy(Condition condition);
/**
* Add an Oracle-specific CONNECT BY NOCYCLE clause to the
* query.
*/
@Support({ CUBRID })
void addConnectByNoCycle(Condition condition);
/**
* Add an Oracle-specific START WITH clause to the query's
* CONNECT BY clause.
*/
@Support({ CUBRID })
void setConnectByStartWith(Condition condition);
// ------------------------------------------------------------------------
// Methods from ConditionProvider, OrderProvider, LockProvider
// ------------------------------------------------------------------------
/**
* {@inheritDoc}
*/
@Override
@Support
void addConditions(Condition... conditions);
/**
* {@inheritDoc}
*/
@Override
@Support
void addConditions(Collection conditions);
/**
* {@inheritDoc}
*/
@Override
@Support
void addConditions(Operator operator, Condition... conditions);
/**
* {@inheritDoc}
*/
@Override
@Support
void addConditions(Operator operator, Collection conditions);
/**
* Adds ordering fields, ordering by the default sort order.
*
* @param fields The ordering fields
*/
@Support
void addOrderBy(Field... fields);
/**
* Adds ordering fields.
*
* @param fields The ordering fields
*/
@Support
void addOrderBy(SortField... fields);
/**
* Adds ordering fields.
*
* @param fields The ordering fields
*/
@Support
void addOrderBy(Collection> fields);
/**
* 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
*
* @param fieldIndexes The ordering fields
*/
@Support
void addOrderBy(int... fieldIndexes);
/**
* 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.
*
* @param orderBySiblings
*/
@Support({ CUBRID })
void setOrderBySiblings(boolean orderBySiblings);
/**
* Adds seeking fields.
*
* @param fields The seeking fields
*/
@Support
void addSeekAfter(Field... fields);
/**
* Adds seeking fields.
*
* @param fields The seeking fields
*/
@Support
void addSeekAfter(Collection> fields);
/**
* Adds seeking fields.
*
* @param fields The seeking fields
*/
@Support
void addSeekBefore(Field... fields);
/**
* Adds seeking fields.
*
* @param fields The seeking fields
*/
@Support
void addSeekBefore(Collection> fields);
/**
* Limit the results of this select.
*
* This is the same as calling {@link #addLimit(int, int)} with offset = 0
*
* @param numberOfRows The number of rows to return
*/
@Support
void addLimit(int 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 simulated with a
* ROW_NUMBER() window function and nested SELECT
* statements.
*
* This is the same as calling {@link #addLimit(int, int)} with offset = 0
*
* @param numberOfRows The number of rows to return
*/
@Support({ CUBRID, DERBY, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE })
void addLimit(Param 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
* simulated with a ROW_NUMBER() window function and nested
* SELECT statements.
*
* @param offset The lowest offset starting at 0
* @param numberOfRows The number of rows to return
*/
@Support({ CUBRID, DERBY, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE })
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 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.
*
* @param offset The lowest offset starting at 0
* @param numberOfRows The number of rows to return
*/
@Support({ CUBRID, DERBY, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE })
void addLimit(Param offset, int 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 simulated with a ROW_NUMBER() window function
* and nested SELECT statements.
*
* @param offset The lowest offset starting at 0
* @param numberOfRows The number of rows to return
*/
@Support({ CUBRID, DERBY, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE })
void addLimit(int offset, Param 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 simulated with a ROW_NUMBER() window function
* and nested SELECT statements.
*
* @param offset The lowest offset starting at 0
* @param numberOfRows The number of rows to return
*/
@Support({ CUBRID, DERBY, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE })
void addLimit(Param offset, Param numberOfRows);
/**
* 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
*
*
*
Simulation
*
* These dialects can simulate the FOR UPDATE clause using a
* cursor. The cursor is handled by the JDBC driver, at
* {@link PreparedStatement} construction time, when calling
* {@link Connection#prepareStatement(String, int, int)} with
* {@link ResultSet#CONCUR_UPDATABLE}. jOOQ handles simulation of a
* FOR UPDATE clause using CONCUR_UPDATABLE for
* these dialects:
*
* - {@link SQLDialect#CUBRID}
* - {@link SQLDialect#SQLSERVER}
*
*
* Note: This simulation may not be efficient for large result sets!
*
*
Not supported
*
* These dialects are known not to support the FOR UPDATE
* clause in regular SQL:
*
* - {@link 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 {@link #setForShare(boolean)}
*
* @param forUpdate The flag's value
*/
@Support({ CUBRID, DERBY, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES })
void setForUpdate(boolean forUpdate);
/**
* 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 {@link #setForUpdate(boolean)} flag, and
* unsets the {@link #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 {@link SQLDialect#DB2} has some stricter requirements
* regarding the updatability of fields. Refer to the DB2 documentation for
* further details
*
* @param fields The fields that should be locked
*/
@Support({ DERBY, FIREBIRD, H2, HSQLDB })
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.
*
*
* @see #setForUpdateOf(Field...)
*/
@Support({ DERBY, FIREBIRD, H2, HSQLDB })
void setForUpdateOf(Collection> fields);
/**
* 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 {@link #setForUpdate(boolean)} flag, and
* unsets the {@link #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 simulates this by locking all known fields of [tables]
* for any of these dialects:
*
* - DB2
* - Derby
* - Ingres
* - Oracle
*
*
* @param tables The tables that should be locked
*/
@Support({ DERBY, FIREBIRD, H2, HSQLDB, POSTGRES })
void setForUpdateOf(Table... tables);
/* [pro] xx
xxx
x xxxx xxxxx xxxxx xxx xxxxxxxxxx xxx xxxxxxx xxxx xxx xxx xxxxxxx
x xxxxxxxxx xxxxxxxxxxxxx xxxxxxx xx xxxx xxxxx xxx xxxxxxx xxxx xxxx xxx
x xxxx xxxxxxxxxxxxxxxxxxxxx xxxxxx xxxxxxxx xxx xxxx xxxxxxxxxxx xx xxx
x xxxx xx xxx xxxxxxxxxx
x xxx
x xxxx xxxxxxxxxxxxx xxxx xxx xxxxxx xxxxxxxxxxxxxxxxxxxxxxx xxxxx xxx
x xxxxxx xxx xxxxxx xxxxxxxxxxxxxxxxxxxxxx xxxxx xx xx xxx xxxxxxxxxx xxxx
x xxx
x xxxx xxx xxxx xxxxxxxx xx xx xxxxxxxxx xx xxx xx xxxxx xxxxxxxxx
x xxxx
x xxxxxxxxxxxxxxx
x xxxxx
x
x xxxxxx xxxxxxx xxx xxxxxx xx xxxxxxx xx xxxx xxx x xxxx
xx
xxxxxxxxxxxxxxxx
xxxx xxxxxxxxxxxxxxxxxxxx xxxxxxxxx
xx [/pro] */
/**
* 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 {@link #setForUpdate(boolean)} flag, and
* unsets the {@link #setForShare(boolean)} flag, if it was previously set.
*
* This has been observed to be supported by any of these dialects:
*
* - Oracle
*
*/
@Support({ POSTGRES })
void setForUpdateNoWait();
/* [pro] xx
xxx
x xxxx xxxxx xxxxx xxx xxxxxxxxxx xxx xxxxxxx xxxx xxx xxx xxxxxxx
x xxxxxxxxx xxxxxxxxxxxxx xxxxxxx xx xxxx xxxxx xxx xxxxxxx xxxx xxxx xxx
x xxxxxx xxxx xxxx xxx xxxxxx xxxxxxxxxx xxxxx xxxx xx xxx xxxxxxxxxx
x xxx
x xxxx xxxxxxxxxxxxx xxxx xxx xxxxxx xxxxxxxxxxxxxxxxxxxxxxx xxxxx xxx
x xxxxxx xxx xxxxxx xxxxxxxxxxxxxxxxxxxxxx xxxxx xx xx xxx xxxxxxxxxx xxxx
x xxx
x xxxx xxx xxxx xxxxxxxx xx xx xxxxxxxxx xx xxx xx xxxxx xxxxxxxxx
x xxxx
x xxxxxxxxxxxxxxx
x xxxxx
xx
xxxxxxxxxxxxxxxx
xxxx xxxxxxxxxxxxxxxxxxxxxxxxx
xx [/pro] */
/**
* Sets the "FOR SHARE" flag onto the query.
*
* 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 {@link #setForUpdate(boolean)}
*
* @param forShare The flag's value
*/
@Support({ MARIADB, MYSQL, POSTGRES })
void setForShare(boolean forShare);
/* [pro] xx
xxx
x xxx x xxxxxxxxxx xxxxx xxxxxxxxxxxxx xxxxxx xx xxx xxx xx xxx xxxxxxxxx
xx
xxxxxxxxxx xxxxxx xx
xxxx xxxxxxxxxxxxxxxxxxxxx
xxx
x xxx x xxxxxxxxxx xxxx xxxxxxxxxxx xxxxxx xx xxx xxx xx xxx xxxxxxxxx
xx
xxxxxxxxxx xxxxxx xx
xxxx xxxxxxxxxxxxxxxxxx
xx [/pro] */
}