Hibernate ORM and JDBC pagination boundary: OFFSET/LIMIT versus cursor-based row retrieval

Hibernate translates setFirstResult and setMaxResults into database-specific LIMIT and OFFSET clauses, while cursor-based pagination relies on WHERE filtering against a stable unique column. Applications processing large datasets must choose between these interoperable approaches without introducing duplicate or skipped rows.

The uncertainty centers on how Hibernate session management, entity materialization, and index strategy interact with each pagination method at increasing offset depths. Projecting partial attributes may reduce transfer volume but can alter lazy-loading and second-level-cache behavior, while cursor pagination requires monotonic unique identifiers that may not exist in all schemas.

What performance trade-offs distinguish partial projection from full entity materialization across increasing offsets? Under which index and primary-key conditions does cursor pagination remain consistent with Hibernate session state? What safeguards prevent row duplication or loss when switching between OFFSET/LIMIT and cursor-based retrieval within the same transaction?