Hibernate Pagination Integration: setFirstResult/setMaxResults with Dialect‑Specific LIMIT/OFFSET and FETCH FIRST/OFFSET

Answer

Yes. In Hibernate 6.2 the setFirstResult/setMaxResults API is translated by the dialect’s LimitHandler into:

• PostgreSQL & MySQL: LIMIT ? OFFSET ?
• Oracle 12c+ and SQL Server 2012+: FETCH FIRST ? ROWS ONLY OFFSET ? ROWS (or the equivalent OFFSET ? ROWS FETCH NEXT ? ROWS ONLY)

This translation is identical in semantics – both express offset‑based pagination – and is unaffected by whether the ORDER BY column list is unique.

Confirmed facts (from research)

• The presence of a non‑unique ORDER BY does not change the generated pagination clause; pagination is applied after the ordering step.
• Hibernate 6.0+ avoids in‑memory pagination for any dialect that provides native LIMIT/OFFSET support; fallback to rownum/TOP or in‑memory paging only occurs for older Oracle (<12c) or SQL Server (<2012) dialects, or when a fetch join forces a DISTINCT that cannot be pushed past the limit.
• To inspect the generated SQL without executing the query, enable Hibernate SQL logging with formatting and comments, or use a JDBC proxy such as P6Spy.

Likely explanation / version threshold

Prior to Hibernate 6.0, some dialects (especially Oracle) lacked a native LIMIT/OFFSET implementation, causing Hibernate to fall back to subquery‑based rownum tricks or to collect the full result set and paginate in memory. Starting with Hibernate 6.0 the LimitHandler abstraction was unified, and all dialects that support LIMIT/OFFSET (PostgreSQL, MySQL, Oracle 12c+, SQL Server 2012+) now use the native clause directly. Therefore, the version threshold where in‑memory pagination is avoided for the given scenario is Hibernate 6.0.

Steps to verify dialect alignment

1. Configure Hibernate to show formatted SQL:

hibernate.show_sql=true
hibernate.format_sql=true
hibernate.use_sql_comments=true
logging.level.org.hibernate.SQL=DEBUG

2. Run the query with setFirstResult and setMaxResults; the DEBUG log will display the exact SQL sent to the database.
3. Alternatively, wrap the DataSource with a JDBC proxy (e.g., P6Spy) and examine its log for the SQL statement.
4. Confirm the active dialect via sessionFactory.getDialect() to ensure the correct LimitHandler (PostgreSQLDialect, MySQLDialect, Oracle12cDialect, SQLServer2012Dialect) is in use.