org.jooq

Interface SelectSeekStepN<R extends Record>

All Superinterfaces:

Attachable, FieldLike, Query, QueryPart, ResultQuery<R>, Select<R>, SelectFinalStep<R>, SelectForUpdateStep<R>, SelectLimitStep<R>, SelectOptionStep<R>, Serializable, TableLike<R>

@Generated(value="This class was generated using jOOQ-tools")
public interface SelectSeekStepN<R extends Record>
extends SelectLimitStep<R>

This type is used for the Select's DSL API when selecting generic 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

Method Summary

Methods

Modifier and Type	Method and Description
SelectSeekLimitStep<R>	seek(Field<?>... fields) Add a synthetic SEEK AFTER clause to the query.
SelectSeekLimitStep<R>	seek(Object... values) Add a synthetic SEEK AFTER clause to the query.
SelectSeekLimitStep<R>	seekAfter(Field<?>... fields) Add a synthetic SEEK AFTER clause to the query.
SelectSeekLimitStep<R>	seekAfter(Object... values) Add a synthetic SEEK AFTER clause to the query.
SelectSeekLimitStep<R>	seekBefore(Field<?>... fields) Add a synthetic SEEK BEFORE clause to the query.
SelectSeekLimitStep<R>	seekBefore(Object... values) Add a synthetic SEEK BEFORE clause to the query.

Methods inherited from interface org.jooq.SelectLimitStep

limit, limit, limit, limit, limit, limit

Methods inherited from interface org.jooq.SelectForUpdateStep

forShare, forUpdate

Methods inherited from interface org.jooq.SelectOptionStep

option

Methods inherited from interface org.jooq.SelectFinalStep

getQuery

Methods inherited from interface org.jooq.Select

except, fetchCount, getSelect, intersect, union, unionAll

Methods inherited from interface org.jooq.ResultQuery

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, fetchGroups, fetchGroups, fetchInto, fetchInto, fetchInto, fetchLater, fetchLater, fetchLazy, fetchLazy, fetchMany, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMap, fetchMaps, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOne, fetchOneArray, fetchOneInto, fetchOneInto, fetchOneMap, fetchResultSet, fetchSize, getRecordType, getResult, intern, intern, intern, keepStatement, maxRows, queryTimeout, resultSetConcurrency, resultSetHoldability, resultSetType

Methods inherited from interface org.jooq.Query

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

Methods inherited from interface org.jooq.QueryPart

equals, hashCode, toString

Methods inherited from interface org.jooq.Attachable

attach, detach

Methods inherited from interface org.jooq.TableLike

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

Methods inherited from interface org.jooq.FieldLike

asField, asField

Method Detail

seek

SelectSeekLimitStep<R> seek(Object... values)

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:

1. 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.
2. 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 * 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 Also:

http://use-the-index-luke.com/sql/partial-results/fetch-next-page, http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method, seekAfter(Object...)

seek

SelectSeekLimitStep<R> seek(Field<?>... fields)

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:

1. 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.
2. 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 * 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 Also:

http://use-the-index-luke.com/sql/partial-results/fetch-next-page, http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method, seekAfter(Field...)

seekAfter

SelectSeekLimitStep<R> seekAfter(Object... values)

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:

1. 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.
2. 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 * 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 Also:

http://use-the-index-luke.com/sql/partial-results/fetch-next-page, http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method

seekAfter

SelectSeekLimitStep<R> seekAfter(Field<?>... fields)

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:

1. 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.
2. 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 * 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 Also:

http://use-the-index-luke.com/sql/partial-results/fetch-next-page, http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method

seekBefore

SelectSeekLimitStep<R> seekBefore(Object... values)

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:

1. 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.
2. 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 * 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 Also:

http://use-the-index-luke.com/sql/partial-results/fetch-next-page, http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method

seekBefore

SelectSeekLimitStep<R> seekBefore(Field<?>... fields)

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:

1. 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.
2. 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 * 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 Also:

http://use-the-index-luke.com/sql/partial-results/fetch-next-page, http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method