jOOQ keyset pagination
Keyset (or seek) pagination doesn't have a default implementation in Spring Boot, but this shouldn't stop you from using it. Simply start by choosing a table's column that should act as the latest visited record/row (for instance, the id column), and use this column in the WHERE and ORDER BY clauses. The idioms relying on the ID column are as follows (sorting by multiple columns follows this same idea):
SELECT ... FROM ...
WHERE id < {last_seen_id}
ORDER BY id DESC
LIMIT {how_many_rows_to_fetch}
SELECT ... FROM ...
WHERE id > {last_seen_id}
ORDER BY id ASC
LIMIT {how_many_rows_to_fetch}
Or, like this:
SELECT ... FROM ...
WHERE ... AND id < {last_seen_id}
ORDER BY id DESC
LIMIT {how_many_rows_to_fetch}
SELECT ... FROM ...
WHERE ... AND id > {last_seen_id}
ORDER BY id ASC
LIMIT {how_many_rows_to_fetch}
Based on the experience gained so far, expressing these queries in jOOQ should be a piece of cake. For instance, let&apos...
