Module org.jooq
Package org.jooq

Interface SelectSeekStepN<R extends Record>

All Superinterfaces:
Attachable, AttachableQueryPart, FieldLike, FieldOrRowOrSelect, Fields, Flow.Publisher<R>, Iterable<R>, Publisher<R>, org.reactivestreams.Publisher<R>, Query, QueryPart, ResultQuery<R>, Select<R>, SelectCorrelatedSubqueryStep<R>, SelectFinalStep<R>, SelectForStep<R>, SelectForUpdateStep<R>, SelectLimitStep<R>, SelectOptionStep<R>, SelectUnionStep<R>, Serializable, Statement, TableLike<R>
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 Details

    • seek

      @NotNull @CheckReturnValue @Support @NotNull 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 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 column list length must match the ORDER BY expression list length.

      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:

    • seek

      @NotNull @CheckReturnValue @Support @NotNull 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 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 column list length must match the ORDER BY expression list length.

      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:

    • seekAfter

      @NotNull @CheckReturnValue @Support @NotNull 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 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 column list length must match the ORDER BY expression list length.

      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:

    • seekAfter

      @NotNull @CheckReturnValue @Support @NotNull 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 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 column list length must match the ORDER BY expression list length.

      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:

    • seekBefore

      @NotNull @CheckReturnValue @Support @Deprecated @NotNull SelectSeekLimitStep<R> seekBefore(Object... values)
      Deprecated.
      - [#7461] - SEEK BEFORE is not implemented correctly
      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 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 column list length must match the ORDER BY expression list length.

      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:

    • seekBefore

      @NotNull @CheckReturnValue @Support @Deprecated @NotNull SelectSeekLimitStep<R> seekBefore(Field<?>... fields)
      Deprecated.
      - [#7461] - SEEK BEFORE is not implemented correctly
      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 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 column list length must match the ORDER BY expression list length.

      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: