Hibernate internals – how does pagination work?

Hibernate supports pagination for all query types – HQL, Criteria and SQL, via the query setMaxResults() and setFirstResult() methods, but how does this work under the covers? Well, assuming your database supports it, it will add a row_number() to your query, then wrap this inside another query to pick out the desired rows. You can see the SQL for yourself if you turn on Hibernate SQL logging (by setting the org.hibernate.SQL log level to DEBUG):

2021-05-17 16:22:00,452 http-nio-8081-exec-5 DEBUG [org.hibernate.SQL] 
WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) 
as __hibernate_row_nr__ 
FROM ( select
  clm.claim_id claimId, 
  clm.claim_number claimNumber,
  clmst.code claimStatusCode, 
  clmst.description claimStatusDescription,
  ppb.companyname policyholderCompanyName

You can see what is going on if you look inside the Hibernate Loader class:

protected SqlStatementWrapper executeQueryStatement(
      String sqlStatement,
      QueryParameters queryParameters,
      boolean scroll,
      List<AfterLoadAction> afterLoadActions,
      SharedSessionContractImplementor session) throws SQLException {
   // Processing query filters.
   queryParameters.processFilters( sqlStatement, session );
   // Applying LIMIT clause.
   final LimitHandler limitHandler = getLimitHandler(
         queryParameters.getRowSelection()
   );
   String sql = limitHandler.processSql( queryParameters.getFilteredSQL(), queryParameters.getRowSelection() );

In my case I’m running on SQL Server and the limit handler is the class SQLServer2005LimitHandler. If you look inside this class you can see the code that parses the SQL statement, adds a row_number() and wraps it in an outer query:

/**
 * Add a LIMIT clause to the given SQL SELECT (HHH-2655: ROW_NUMBER for Paging)
 *
 * The LIMIT SQL will look like:
 *
 * <pre>
 * WITH query AS (
 *   SELECT inner_query.*
 *        , ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__
 *     FROM ( original_query_with_top_if_order_by_present_and_all_aliased_columns ) inner_query
 * )
 * SELECT alias_list FROM query WHERE __hibernate_row_nr__ >= offset AND __hibernate_row_nr__ < offset + last
 * </pre>
 *
 * When offset equals {@literal 0}, only <code>TOP(?)</code> expression is added to the original query.
 *
 * @return A new SQL statement with the LIMIT clause applied.
 */
@Override
public String processSql(String sql, RowSelection selection) {
   final StringBuilder sb = new StringBuilder( sql );
   if ( sb.charAt( sb.length() - 1 ) == ';' ) {
      sb.setLength( sb.length() - 1 );
   }
   if ( LimitHelper.hasFirstRow( selection ) ) {
      final String selectClause = fillAliasInSelectClause( sb );
      final int orderByIndex = shallowIndexOfPattern( sb, ORDER_BY_PATTERN, 0 );
      if ( orderByIndex > 0 ) {
         // ORDER BY requires using TOP.
         addTopExpression( sb );
      }
      encloseWithOuterQuery( sb );
      // Wrap the query within a with statement:
      sb.insert( 0, "WITH query AS (" ).append( ") SELECT " ).append( selectClause ).append( " FROM query " );
      sb.append( "WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?" );
   }
   else {
      addTopExpression( sb );
   }
   return sb.toString();
}