Implement stable keyset pagination with Spring Data JPA

• Use the exact same fields and directions in ordering and cursor predicate.
• Require both cursor values together or neither.
• Clamp page size to 1 through 100.
• Return a projection rather than managed entities.
• Encode and validate cursor values before passing them to the repository.

1. Define the lexicographic predicate for descending timestamp and ID.
2. Use one query for the first page and one for subsequent pages.
3. Request one extra row beyond the client-visible size.
4. If an extra row exists, remove it and build the next cursor from the last returned row.
5. Keep filtering predicates fixed across the paging sequence.

public record OrderSlice(java.util.List<OrderItem> items, OrderCursor next) {}
public record OrderCursor(java.time.Instant createdAt, long id) {}

interface OrderPageRepository extends org.springframework.data.jpa.repository.JpaRepository<Order, Long> {
    @org.springframework.data.jpa.repository.Query("""
        select new com.example.OrderItem(o.id, o.createdAt, o.total)
        from Order o
        where (:cursorTime is null
          or o.createdAt < :cursorTime
          or (o.createdAt = :cursorTime and o.id < :cursorId))
        order by o.createdAt desc, o.id desc
        """)
    java.util.List<OrderItem> next(
        @org.springframework.data.repository.query.Param("cursorTime") java.time.Instant cursorTime,
        @org.springframework.data.repository.query.Param("cursorId") Long cursorId,
        org.springframework.data.domain.Pageable limit);
}

OrderSlice page(OrderCursor cursor, int requestedSize) {
    int size = Math.max(1, Math.min(requestedSize, 100));
    var rows = repository.next(
        cursor == null ? null : cursor.createdAt(),
        cursor == null ? null : cursor.id(),
        org.springframework.data.domain.PageRequest.of(0, size + 1));

    boolean hasMore = rows.size() > size;
    var visible = java.util.List.copyOf(rows.subList(0, Math.min(size, rows.size())));
    var last = hasMore ? visible.get(visible.size() - 1) : null;
    return new OrderSlice(visible,
        last == null ? null : new OrderCursor(last.createdAt(), last.id()));
}

Application work is O(pageSize). With a matching (created_at DESC, id DESC) index and stable filters, the database can seek near the cursor instead of scanning O(offset) preceding rows.

• Many rows can share a timestamp; the unique ID tie-breaker prevents skips.
• Newer inserts may appear before the cursor and therefore do not disturb later pages.
• Updates to sort fields during paging can move rows; immutable creation time reduces this risk.
• An empty page has no next cursor.

• Create several equal timestamps and verify every ID appears exactly once.
• Insert a newer row between page requests and verify the existing sequence does not shift.
• Test page sizes 0, 1, 100, and 101.
• Compare a multi-page traversal with the fully sorted expected set.

• Using only createdAt skips or repeats ties.
• Using id > cursorId with descending order reverses the cursor boundary.
• Returning the extra row leaks pageSize + 1 items.
• Offset pagination becomes increasingly expensive and can shift under inserts.