Hibernate 6.x: Converting setFirstResult Pagination to Cursor-Based Retrieval

Goal

We need to replace the traditional offset‑based paging that uses setFirstResult and setMaxResults in Hibernate 6.x with a cursor‑based approach that references a monotonic primary key. The target is a table that contains millions of rows where large offsets cause full table scans and inconsistent results if rows are inserted or deleted between requests.

Constraints

Offset paging triggers a LIMIT/OFFSET clause that the database must evaluate sequentially, leading to linear time and memory consumption. Cursor paging can avoid this but requires a different query construction pattern and may interact with fetch‑mode settings that affect entity materialization.

Unresolved Questions

• What is the most portable way to express cursor‑based limits in a Hibernate Criteria or JPQL query that replaces setFirstResult?
• How does switching from FetchMode.SELECT to FetchMode.SUBSELECT influence memory usage when cursor paging large entity graphs?
• Will cursor paging preserve consistent ordering across concurrent inserts or deletes, and how should the query be written to guarantee that?