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

To replace offset-based pagination with cursor-based retrieval, you must shift from using setFirstResult and setMaxResults to a manual WHERE clause that filters by a monotonic column (typically the primary key).

Offset-based paging requires the database to scan and discard all rows prior to the offset, resulting in O(n) complexity. Cursor-based paging uses indexed comparisons to jump directly to the next set of results, resulting in O(log n) or O(1) performance depending on the index structure.

Implementation via JPQL and Criteria

In Hibernate 6.x, instead of calling setFirstResult(offset), define a predicate based on the ID of the last item from the previous page.

// Example JPQL approach
SELECT e FROM Entity e WHERE e.id > :lastId ORDER BY e.id ASC

// Implementation
List<Entity> results = entityManager.createQuery(
    "SELECT e FROM Entity e WHERE e.id > :lastId ORDER BY e.id ASC", Entity.class)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(pageSize)
    .getResultList();

If using the Criteria API, use the CriteriaBuilder to add the comparison:

CriteriaQuery<Entity> query = cb.createQuery(Entity.class);
Root<Entity> root = query.from(Entity.class);
query.select(root).where(cb.gt(root.get("id"), lastSeenId))
     .orderBy(cb.asc(root.get("id")));
List<Entity> results = entityManager.createQuery(query).setMaxResults(pageSize).getResultList();

FetchMode and Memory Impact

Switching between fetch modes significantly affects how Hibernate materializes entity graphs during cursor paging:

• FetchMode.SELECT: Triggers a separate query for each entity's collection. While this keeps the memory footprint per query low, it leads to the N+1 problem if the page size is large.
• FetchMode.SUBSELECT: Executes one additional query to fetch collections for all entities retrieved in the first query. This is generally more efficient for large batches but can increase memory usage as Hibernate must keep all primary entities in the persistence context to resolve the subselect.

Consistency and Ordering

Cursor paging preserves consistent ordering across concurrent inserts or deletes because it references a specific point value rather than a relative position. If a row is inserted with an ID lower than the current cursor, it does not shift the result set. If the current row is deleted, the cursor remains valid for the next request because it looks for the "next greater" value. To guarantee this, the ORDER BY clause must include a unique column. If sorting by a non-unique column (like "status"), you must include the primary key as a tie-breaker (e.g., ORDER BY status ASC, id ASC) to ensure deterministic cursor behavior.

Note: Ensure that the column used for the cursor is indexed to avoid the performance degradation you seek to escape.