Pagination

Pages are selected by row-number range. Page numbers start at 0; with 20 rows per page, the first page is rows 1–20 and the second is rows 21–40.

Page query

WITH selectTemp AS (
    SELECT TOP 100 PERCENT
           ROW_NUMBER() OVER (ORDER BY id) AS __row_number__,
           id, name
    FROM user_info
)
SELECT * FROM selectTemp
WHERE __row_number__ BETWEEN 1 AND 20
ORDER BY __row_number__

The row-number bounds are calculated from the page number and page size and written into the SQL.

Total-count query

When a total row count is required, dbVisitor removes the original ORDER BY and runs a separate query:

SELECT COUNT(*) FROM (
    SELECT id, name FROM user_info
) AS TEMP_T

dbVisitor Usage

List<UserInfo> rows = lambda.query(UserInfo.class)
        .orderBy(UserInfo::getId)
        .initPage(20, 0)
        .queryForList();

initPage(pageSize, pageNumber) uses zero-based page numbers. To reuse pagination state, use usePage(PageObject.of(pageNumber, pageSize)); Mapper methods can also accept a Page parameter. See 9.5 Pagination for pagination objects, total counts, and API entry points.

Notes

• The original ORDER BY is used by ROW_NUMBER(); include a unique tie-breaker.
• Without business ordering, the dialect uses CURRENT_TIMESTAMP, which does not produce stable pages.
• The result includes __row_number__; counting and fetching are also separate queries.

For changing sort direction and combining fields, see Order By.