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.

      NULL handling

      NULL handling in the SEEK clause is enabled only for ORDER BY expressions when providing explicit SortField.nullsFirst() or SortField.nullsLast() clauses, in case of which the SEEK predicate is always composed of multiple predicates containing Field.isNull() or Field.isNotNull(), respectively, never of ROW predicates.

      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.

      NULL handling

      NULL handling in the SEEK clause is enabled only for ORDER BY expressions when providing explicit SortField.nullsFirst() or SortField.nullsLast() clauses, in case of which the SEEK predicate is always composed of multiple predicates containing Field.isNull() or Field.isNotNull(), respectively, never of ROW predicates.

      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.

      NULL handling

      NULL handling in the SEEK clause is enabled only for ORDER BY expressions when providing explicit SortField.nullsFirst() or SortField.nullsLast() clauses, in case of which the SEEK predicate is always composed of multiple predicates containing Field.isNull() or Field.isNotNull(), respectively, never of ROW predicates.

      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.

      NULL handling

      NULL handling in the SEEK clause is enabled only for ORDER BY expressions when providing explicit SortField.nullsFirst() or SortField.nullsLast() clauses, in case of which the SEEK predicate is always composed of multiple predicates containing Field.isNull() or Field.isNotNull(), respectively, never of ROW predicates.

      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.

      NULL handling

      NULL handling in the SEEK clause is enabled only for ORDER BY expressions when providing explicit SortField.nullsFirst() or SortField.nullsLast() clauses, in case of which the SEEK predicate is always composed of multiple predicates containing Field.isNull() or Field.isNotNull(), respectively, never of ROW predicates.

      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.

      NULL handling

      NULL handling in the SEEK clause is enabled only for ORDER BY expressions when providing explicit SortField.nullsFirst() or SortField.nullsLast() clauses, in case of which the SEEK predicate is always composed of multiple predicates containing Field.isNull() or Field.isNotNull(), respectively, never of ROW predicates.

      See Also: