org.jooq.SelectSeekStep2 Maven / Gradle / Ivy
/*
* 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.
*
* Other licenses:
* -----------------------------------------------------------------------------
* Commercial licenses for this work are available. These replace the above
* Apache-2.0 license and offer limited warranties, support, maintenance, and
* commercial database integrations.
*
* For more information, please visit: http://www.jooq.org/licenses
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*/
package org.jooq;
import org.jetbrains.annotations.NotNull;
/**
* This type is used for the {@link Select}'s DSL API when selecting generic
* {@link Record} types.
*
* Example:
* -- get all authors' first and last names, and the number
* -- of books they've written in German, if they have written
* -- more than five books in German in the last three years
* -- (from 2011), and sort those authors by last names
* -- limiting results to the second and third row
*
* SELECT T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME, COUNT(*)
* FROM T_AUTHOR
* JOIN T_BOOK ON T_AUTHOR.ID = T_BOOK.AUTHOR_ID
* WHERE T_BOOK.LANGUAGE = 'DE'
* AND T_BOOK.PUBLISHED > '2008-01-01'
* GROUP BY T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME
* HAVING COUNT(*) > 5
* ORDER BY T_AUTHOR.LAST_NAME ASC NULLS FIRST
* LIMIT 2
* OFFSET 1
* FOR UPDATE
* OF FIRST_NAME, LAST_NAME
* NO WAIT
*
Its equivalent in jOOQ
* create.select(TAuthor.FIRST_NAME, TAuthor.LAST_NAME, create.count())
* .from(T_AUTHOR)
* .join(T_BOOK).on(TBook.AUTHOR_ID.equal(TAuthor.ID))
* .where(TBook.LANGUAGE.equal("DE"))
* .and(TBook.PUBLISHED.greaterThan(parseDate('2008-01-01')))
* .groupBy(TAuthor.FIRST_NAME, TAuthor.LAST_NAME)
* .having(create.count().greaterThan(5))
* .orderBy(TAuthor.LAST_NAME.asc().nullsFirst())
* .limit(2)
* .offset(1)
* .forUpdate()
* .of(TAuthor.FIRST_NAME, TAuthor.LAST_NAME)
* .noWait();
*
Refer to the manual for more details
*
* @author Lukas Eder
*/
public interface SelectSeekStep2 extends SelectLimitStep {
/**
* Add a synthetic SEEK AFTER clause to the query.
*
* The synthetic SEEK AFTER clause is an alternative way to specify
* an OFFSET, and thus to perform paging in a SQL query. This
* can be advantageous for two reasons:
*
*
* - The SQL generated from the
SEEK AFTER clause is a regular
* predicate, which can be used by query plan optimisers to choose an
* appropriate index. The SQL standard OFFSET clause will need
* to skip N rows in memory.
* - The
SEEK AFTER clause is stable with respect to new data being
* inserted or data being deleted while paging through pages.
*
*
* Example:
* DSL.using(configuration)
* .selectFrom(TABLE)
* .orderBy(ID, CODE)
* .seek(3, "abc")
* .fetch();
*
*
* The above query will render the following SQL statement:
*
*
* SELECT table.col1, table.col2, ... FROM table
* WHERE (id, code) > (3, 'abc')
* ORDER BY id ASC, code ASC
*
*
* The actual row value expression predicate may be expanded into this
* equivalent predicate:
*
*
* WHERE (id > 3) OR (id = 3 AND code > 'abc')
*
*
* The SEEK AFTER method currently does not support seeking
* NULL values, or operating with NULLS FIRST,
* NULLS LAST clauses in the ORDER BY clause.
*
* @see http://use-the-index-luke.com/sql/partial-results/fetch-next-page
* @see https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method
* @see #seekAfter(Object, Object)
*/
@NotNull @CheckReturnValue
@Support
SelectSeekLimitStep seek(T1 t1, T2 t2);
/**
* Add a synthetic SEEK AFTER clause to the query.
*
* The synthetic SEEK AFTER clause is an alternative way to specify
* an OFFSET, and thus to perform paging in a SQL query. This
* can be advantageous for two reasons:
*
*
* - The SQL generated from the
SEEK AFTER clause is a regular
* predicate, which can be used by query plan optimisers to choose an
* appropriate index. The SQL standard OFFSET clause will need
* to skip N rows in memory.
* - The
SEEK AFTER clause is stable with respect to new data being
* inserted or data being deleted while paging through pages.
*
*
* Example:
* DSL.using(configuration)
* .selectFrom(TABLE)
* .orderBy(ID, CODE)
* .seek(3, "abc")
* .fetch();
*
*
* The above query will render the following SQL statement:
*
*
* SELECT table.col1, table.col2, ... FROM table
* WHERE (id, code) > (3, 'abc')
* ORDER BY id ASC, code ASC
*
*
* The actual row value expression predicate may be expanded into this
* equivalent predicate:
*
*
* WHERE (id > 3) OR (id = 3 AND code > 'abc')
*
*
* The SEEK AFTER method currently does not support seeking
* NULL values, or operating with NULLS FIRST,
* NULLS LAST clauses in the ORDER BY clause.
*
* @see http://use-the-index-luke.com/sql/partial-results/fetch-next-page
* @see https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method
* @see #seekAfter(Field, Field)
*/
@NotNull @CheckReturnValue
@Support
SelectSeekLimitStep seek(Field field1, Field field2);
/**
* Add a synthetic SEEK AFTER clause to the query.
*
* The synthetic SEEK AFTER clause is an alternative way to specify
* an OFFSET, and thus to perform paging in a SQL query. This
* can be advantageous for two reasons:
*
*
* - The SQL generated from the
SEEK AFTER clause is a regular
* predicate, which can be used by query plan optimisers to choose an
* appropriate index. The SQL standard OFFSET clause will need
* to skip N rows in memory.
* - The
SEEK AFTER clause is stable with respect to new data being
* inserted or data being deleted while paging through pages.
*
*
* Example:
* DSL.using(configuration)
* .selectFrom(TABLE)
* .orderBy(ID, CODE)
* .seekAfter(3, "abc")
* .fetch();
*
*
* The above query will render the following SQL statement:
*
*
* SELECT table.col1, table.col2, ... FROM table
* WHERE (id, code) > (3, 'abc')
* ORDER BY id ASC, code ASC
*
*
* The actual row value expression predicate may be expanded into this
* equivalent predicate:
*
*
* WHERE (id > 3) OR (id = 3 AND code > 'abc')
*
*
* The SEEK AFTER method currently does not support seeking
* NULL values, or operating with NULLS FIRST,
* NULLS LAST clauses in the ORDER BY clause.
*
* @see http://use-the-index-luke.com/sql/partial-results/fetch-next-page
* @see https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method
*/
@NotNull @CheckReturnValue
@Support
SelectSeekLimitStep seekAfter(T1 t1, T2 t2);
/**
* Add a synthetic SEEK AFTER clause to the query.
*
* The synthetic SEEK AFTER clause is an alternative way to specify
* an OFFSET, and thus to perform paging in a SQL query. This
* can be advantageous for two reasons:
*
*
* - The SQL generated from the
SEEK AFTER clause is a regular
* predicate, which can be used by query plan optimisers to choose an
* appropriate index. The SQL standard OFFSET clause will need
* to skip N rows in memory.
* - The
SEEK AFTER clause is stable with respect to new data being
* inserted or data being deleted while paging through pages.
*
*
* Example:
* DSL.using(configuration)
* .selectFrom(TABLE)
* .orderBy(ID, CODE)
* .seekAfter(3, "abc")
* .fetch();
*
*
* The above query will render the following SQL statement:
*
*
* SELECT table.col1, table.col2, ... FROM table
* WHERE (id, code) > (3, 'abc')
* ORDER BY id ASC, code ASC
*
*
* The actual row value expression predicate may be expanded into this
* equivalent predicate:
*
*
* WHERE (id > 3) OR (id = 3 AND code > 'abc')
*
*
* The SEEK AFTER method currently does not support seeking
* NULL values, or operating with NULLS FIRST,
* NULLS LAST clauses in the ORDER BY clause.
*
* @see http://use-the-index-luke.com/sql/partial-results/fetch-next-page
* @see https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method
*/
@NotNull @CheckReturnValue
@Support
SelectSeekLimitStep seekAfter(Field field1, Field field2);
/**
* Add a synthetic SEEK BEFORE clause to the query.
*
* The synthetic SEEK BEFORE clause is an alternative way to specify
* an OFFSET, and thus to perform paging in a SQL query. This
* can be advantageous for two reasons:
*
*
* - The SQL generated from the
SEEK BEFORE clause is a regular
* predicate, which can be used by query plan optimisers to choose an
* appropriate index. The SQL standard OFFSET clause will need
* to skip N rows in memory.
* - The
SEEK BEFORE clause is stable with respect to new data being
* inserted or data being deleted while paging through pages.
*
*
* Example:
* DSL.using(configuration)
* .selectFrom(TABLE)
* .orderBy(ID, CODE)
* .seekBefore(3, "abc")
* .fetch();
*
*
* The above query will render the following SQL statement:
*
*
* SELECT table.col1, table.col2, ... FROM table
* WHERE (id, code) < (3, 'abc')
* ORDER BY id ASC, code ASC
*
*
* The actual row value expression predicate may be expanded into this
* equivalent predicate:
*
*
* WHERE (id < 3) OR (id = 3 AND code < 'abc')
*
*
* The SEEK BEFORE method currently does not support seeking
* NULL values, or operating with NULLS FIRST,
* NULLS LAST clauses in the ORDER BY clause.
*
* @see http://use-the-index-luke.com/sql/partial-results/fetch-next-page
* @see https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method
* @deprecated - [#7461] - SEEK BEFORE is not implemented correctly
*/
@Deprecated
@NotNull @CheckReturnValue
@Support
SelectSeekLimitStep seekBefore(T1 t1, T2 t2);
/**
* Add a synthetic SEEK BEFORE clause to the query.
*
* The synthetic SEEK BEFORE clause is an alternative way to specify
* an OFFSET, and thus to perform paging in a SQL query. This
* can be advantageous for two reasons:
*
*
* - The SQL generated from the
SEEK BEFORE clause is a regular
* predicate, which can be used by query plan optimisers to choose an
* appropriate index. The SQL standard OFFSET clause will need
* to skip N rows in memory.
* - The
SEEK BEFORE clause is stable with respect to new data being
* inserted or data being deleted while paging through pages.
*
*
* Example:
* DSL.using(configuration)
* .selectFrom(TABLE)
* .orderBy(ID, CODE)
* .seekBefore(3, "abc")
* .fetch();
*
*
* The above query will render the following SQL statement:
*
*
* SELECT table.col1, table.col2, ... FROM table
* WHERE (id, code) < (3, 'abc')
* ORDER BY id ASC, code ASC
*
*
* The actual row value expression predicate may be expanded into this
* equivalent predicate:
*
*
* WHERE (id < 3) OR (id = 3 AND code < 'abc')
*
*
* The SEEK BEFORE method currently does not support seeking
* NULL values, or operating with NULLS FIRST,
* NULLS LAST clauses in the ORDER BY clause.
*
* @see http://use-the-index-luke.com/sql/partial-results/fetch-next-page
* @see https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method
* @deprecated - [#7461] - SEEK BEFORE is not implemented correctly
*/
@Deprecated
@NotNull @CheckReturnValue
@Support
SelectSeekLimitStep seekBefore(Field field1, Field field2);
}