private Query createQuery() {
    Query jpaQuery = createBaseQuery();

    if (range != null) {
        jpaQuery.setFirstResult(range.getStartIndex());
        // range is 0 based, so we add 1
        jpaQuery.setMaxResults(range.getLastIndex() - range.getStartIndex() + 1);
    } else if (page != null) {
        jpaQuery.setFirstResult(page.index * page.size);
        jpaQuery.setMaxResults(page.size);
    } else {
        // Use deprecated API in org.hibernate.Query that will be moved to org.hibernate.query.Query on Hibernate 6.0
        @SuppressWarnings("deprecation")
        RowSelection options = jpaQuery.unwrap(org.hibernate.query.Query.class).getQueryOptions();
        options.setFirstRow(null);
        options.setMaxRows(null);
    }

    return jpaQuery;
}
 

private Query createQuery(int maxResults) {
    Query jpaQuery = createBaseQuery();

    if (range != null) {
        jpaQuery.setFirstResult(range.getStartIndex());
    } else if (page != null) {
        jpaQuery.setFirstResult(page.index * page.size);
    } else {
        // Use deprecated API in org.hibernate.Query that will be moved to org.hibernate.query.Query on Hibernate 6.0
        @SuppressWarnings("deprecation")
        RowSelection options = jpaQuery.unwrap(org.hibernate.query.Query.class).getQueryOptions();
        options.setFirstRow(null);
    }
    jpaQuery.setMaxResults(maxResults);

    return jpaQuery;
}
 

private List doTheLoad(String sql, QueryParameters queryParameters, SharedSessionContractImplementor session) throws SQLException {
	final RowSelection selection = queryParameters.getRowSelection();
	final int maxRows = LimitHelper.hasMaxRows( selection ) ?
			selection.getMaxRows() :
			Integer.MAX_VALUE;

	final List<AfterLoadAction> afterLoadActions = new ArrayList<>();
	final SqlStatementWrapper wrapper = executeQueryStatement( sql, queryParameters, false, afterLoadActions, session );
	final ResultSet rs = wrapper.getResultSet();
	final Statement st = wrapper.getStatement();
	try {
		return processResultSet( rs, queryParameters, session, false, null, maxRows, afterLoadActions );
	}
	finally {
		session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
	}
}
 

/**
 * Advance the cursor to the first required row of the <tt>ResultSet</tt>
 */
private void advance(final ResultSet rs, final RowSelection selection) throws SQLException {

	final int firstRow = LimitHelper.getFirstRow( selection );
	if ( firstRow != 0 ) {
		if ( getFactory().getSessionFactoryOptions().isScrollableResultSetsEnabled() ) {
			// we can go straight to the first required row
			rs.absolute( firstRow );
		}
		else {
			// we need to step through the rows one row at a time (slow)
			for ( int m = 0; m < firstRow; m++ ) {
				rs.next();
			}
		}
	}
}
 

/**
 * Execute given <tt>PreparedStatement</tt>, advance to the first result and return SQL <tt>ResultSet</tt>.
 */
protected final ResultSet getResultSet(
		final PreparedStatement st,
		final RowSelection selection,
		final LimitHandler limitHandler,
		final boolean autodiscovertypes,
		final SharedSessionContractImplementor session) throws SQLException, HibernateException {
	try {
		ResultSet rs = session.getJdbcCoordinator().getResultSetReturn().extract( st );

		return processResultSet(rs, selection, limitHandler, autodiscovertypes, session);
	}
	catch (SQLException | HibernateException e) {
		session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
		throw e;
	}
}
 

/**
 * Execute given <tt>CallableStatement</tt>, advance to the first result and return SQL <tt>ResultSet</tt>.
 */
protected final ResultSet getResultSet(
		final CallableStatement st,
		final RowSelection selection,
		final LimitHandler limitHandler,
		final boolean autodiscovertypes,
		final SharedSessionContractImplementor session) throws SQLException, HibernateException {
	try {
		ResultSet rs = session.getJdbcCoordinator().getResultSetReturn().extract( st );

		return processResultSet(rs, selection, limitHandler, autodiscovertypes, session);
	}
	catch (SQLException | HibernateException e) {
		session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
		throw e;
	}
}
 

private ResultSet processResultSet(
		ResultSet rs,
		final RowSelection selection,
		final LimitHandler limitHandler,
		final boolean autodiscovertypes,
		final SharedSessionContractImplementor session
) throws SQLException, HibernateException {
	rs = wrapResultSetIfEnabled( rs, session );

	if ( !limitHandler.supportsLimitOffset() || !LimitHelper.useLimit( limitHandler, selection ) ) {
		advance( rs, selection );
	}

	if ( autodiscovertypes ) {
		autoDiscoverTypes( rs );
	}
	return rs;
}
 

private void doTheLoad(String sql, QueryParameters queryParameters, SharedSessionContractImplementor session) throws SQLException {
	final RowSelection selection = queryParameters.getRowSelection();
	final int maxRows = LimitHelper.hasMaxRows( selection ) ?
			selection.getMaxRows() :
			Integer.MAX_VALUE;

	final List<AfterLoadAction> afterLoadActions = Collections.emptyList();
	final SqlStatementWrapper wrapper = executeQueryStatement( sql, queryParameters, false, afterLoadActions, session );
	final ResultSet rs = wrapper.getResultSet();
	final Statement st = wrapper.getStatement();
	try {
		processResultSet( rs, queryParameters, session, true, null, maxRows, afterLoadActions );
	}
	finally {
		session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
	}
}
 

/**
 * Advance the cursor to the first required row of the <tt>ResultSet</tt>
 */
protected void advance(final ResultSet rs, final RowSelection selection) throws SQLException {
	final int firstRow = LimitHelper.getFirstRow( selection );
	if ( firstRow != 0 ) {
		if ( getFactory().getSettings().isScrollableResultSetsEnabled() ) {
			// we can go straight to the first required row
			rs.absolute( firstRow );
		}
		else {
			// we need to step through the rows one row at a time (slow)
			for ( int m = 0; m < firstRow; m++ ) {
				rs.next();
			}
		}
	}
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	if ( hsqldbVersion < 200 ) {
		return new StringBuilder( sql.length() + 10 )
				.append( sql )
				.insert(
						sql.toLowerCase(Locale.ROOT).indexOf( "select" ) + 6,
						hasOffset ? " limit ? ?" : " top ?"
				)
				.toString();
	}
	else {
		return sql + (hasOffset ? " offset ? limit ?" : " limit ?");
	}
}
 

/**
 * Default implementation of binding parameter values needed by the LIMIT clause.
 *
    * @param selection the selection criteria for rows.
 * @param statement Statement to which to bind limit parameter values.
 * @param index Index from which to start binding.
 * @return The number of parameter values bound.
 * @throws SQLException Indicates problems binding parameter values.
 */
protected final int bindLimitParameters(RowSelection selection, PreparedStatement statement, int index)
		throws SQLException {
	if ( !supportsVariableLimit() || !LimitHelper.hasMaxRows( selection ) ) {
		return 0;
	}
	final int firstRow = convertToFirstRowValue( LimitHelper.getFirstRow( selection ) );
	final int lastRow = getMaxOrLimit( selection );
	final boolean hasFirstRow = supportsLimitOffset() && ( firstRow > 0 || forceLimitUsage() );
	final boolean reverse = bindLimitParametersInReverseOrder();
	if ( hasFirstRow ) {
		statement.setInt( index + ( reverse ? 1 : 0 ), firstRow );
	}
	statement.setInt( index + ( reverse || !hasFirstRow ? 0 : 1 ), lastRow );
	return hasFirstRow ? 2 : 1;
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	if ( selection.getMaxRows() == null ) {
		return sql;
	}

	int top = getMaxOrLimit( selection );
	if ( top == Integer.MAX_VALUE ) {
		return sql;
	}

	Matcher selectDistinctMatcher = SELECT_DISTINCT_PATTERN.matcher( sql );
	if ( selectDistinctMatcher.matches() ) {
		return insertTop( selectDistinctMatcher, sql, top );
	}

	Matcher selectMatcher = SELECT_PATTERN.matcher( sql );
	if ( selectMatcher.matches() ) {
		return insertTop( selectMatcher, sql, top );
	}

	return sql;
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	if (LimitHelper.hasFirstRow( selection )) {
		throw new UnsupportedOperationException( "query result offset is not supported" );
	}

	final int selectIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select" );
	final int selectDistinctIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select distinct" );
	final int insertionPoint = selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);

	StringBuilder sb = new StringBuilder( sql.length() + 8 )
			.append( sql );

	if ( supportsVariableLimit ) {
		sb.insert( insertionPoint, " TOP ? " );
	}
	else {
		sb.insert( insertionPoint, " TOP " + getMaxOrLimit( selection ) + " " );
	}

	return sb.toString();
}
 

@Test
public void testLimit() {
    RowSelection rowSelection = new RowSelection();
    rowSelection.setMaxRows(getMaxRows());
    long startNanos = System.nanoTime();
    doInJDBC(connection -> {
        try (PreparedStatement statement = connection.prepareStatement(SELECT_POST)
        ) {
            statement.setMaxRows(getMaxRows());
            assertEquals(getMaxRows(), processResultSet(statement));
        } catch (SQLException e) {
            fail(e.getMessage());
        }

    });
    LOGGER.info("{} Result Set with limit took {} millis",
            dataSourceProvider().database(),
            TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - startNanos));
}
 

@Test
public void testLimit() {
    RowSelection rowSelection = new RowSelection();
    rowSelection.setMaxRows(getMaxRows());
    long startNanos = System.nanoTime();
    doInJDBC(connection -> {
        try (PreparedStatement statement1 = connection.prepareStatement(SELECT_POST_COMMENT_1);
             PreparedStatement statement11 = connection.prepareStatement(SELECT_POST_COMMENT_1);
             PreparedStatement statement2 = connection.prepareStatement(SELECT_POST_COMMENT_2);
        ) {
            statement1.setMaxRows(getMaxRows());
            assertEquals(getMaxRows(), processResultSet(statement1));
            assertEquals(getPostCommentCount() * getPostCount(), processResultSet(statement11));
            assertEquals(getPostCommentCount() * getPostCount(), processResultSet(statement2));
        } catch (SQLException e) {
            fail(e.getMessage());
        }

    });
    LOGGER.info("{} Result Set with limit took {} millis",
            dataSourceProvider().database(),
            TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - startNanos));
}
 

private List doQuery(
			final SharedSessionContractImplementor session,
			final QueryParameters queryParameters,
			final boolean returnProxies,
			final ResultTransformer forcedResultTransformer) throws SQLException, HibernateException {

		final RowSelection selection = queryParameters.getRowSelection();
		final int maxRows = LimitHelper.hasMaxRows( selection ) ?
				selection.getMaxRows() :
				Integer.MAX_VALUE;

		final List<AfterLoadAction> afterLoadActions = new ArrayList<AfterLoadAction>();

		final SqlStatementWrapper wrapper = executeQueryStatement( queryParameters, false, afterLoadActions, session );
		final ResultSet rs = wrapper.getResultSet();
		final Statement st = wrapper.getStatement();

// would be great to move all this below here into another method that could also be used
// from the new scrolling stuff.
//
// Would need to change the way the max-row stuff is handled (i.e. behind an interface) so
// that I could do the control breaking at the means to know when to stop

		try {
			return processResultSet(
					rs,
					queryParameters,
					session,
					returnProxies,
					forcedResultTransformer,
					maxRows,
					afterLoadActions
			);
		}
		finally {
			session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( st );
			session.getJdbcCoordinator().afterStatementExecution();
		}

	}
 

/**
 * Execute given <tt>PreparedStatement</tt>, advance to the first result and return SQL <tt>ResultSet</tt>.
 */
protected final ResultSet getResultSet(
		final PreparedStatement st,
		final RowSelection selection,
		final LimitHandler limitHandler,
		final boolean autodiscovertypes,
		final SharedSessionContractImplementor session)
		throws SQLException, HibernateException {

	try {
		ResultSet rs = session.getJdbcCoordinator().getResultSetReturn().extract( st );
		rs = wrapResultSetIfEnabled( rs , session );

		if ( !limitHandler.supportsLimitOffset() || !LimitHelper.useLimit( limitHandler, selection ) ) {
			advance( rs, selection );
		}

		if ( autodiscovertypes ) {
			autoDiscoverTypes( rs );
		}
		return rs;
	}
	catch (SQLException | HibernateException ex) {
		session.getJdbcCoordinator().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
		throw ex;
	}
}
 

/**
 * If we're able to guess a likely size of the results we can optimize allocation
 * of our datastructures.
 * Essentially if we detect the user is not using pagination, we attempt to use the FetchSize
 * as a reasonable hint. If fetch size is not being set either, it is reasonable to expect
 * that we're going to have a single hit. In such a case it would be tempting to return a constant
 * of value one, but that's dangerous as it doesn't scale up appropriately for example
 * with an ArrayList if the guess is wrong.
 *
 * @param rowSelection
 * @return a reasonable size to use for allocation
 */
@SuppressWarnings("UnnecessaryUnboxing")
private int guessResultSize(RowSelection rowSelection) {
	if ( rowSelection != null ) {
		final int maxReasonableAllocation = rowSelection.getFetchSize() != null ? rowSelection.getFetchSize().intValue() : 100;
		if ( rowSelection.getMaxRows() != null && rowSelection.getMaxRows().intValue() > 0 ) {
			return Math.min( maxReasonableAllocation, rowSelection.getMaxRows().intValue() );
		}
		else if ( rowSelection.getFetchSize() != null && rowSelection.getFetchSize().intValue() > 0 ) {
			return rowSelection.getFetchSize().intValue();
		}
	}
	return 7;//magic number guessed as a reasonable default.
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	if (LimitHelper.hasFirstRow( selection )) {
		throw new UnsupportedOperationException( "query result offset is not supported" );
	}
	return sql + " fetch first " + getMaxOrLimit( selection ) + " rows only";
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final String soff = " offset " + selection.getFirstRow();
	final String slim = " fetch first " + getMaxOrLimit( selection ) + " rows only";
	final StringBuilder sb = new StringBuilder( sql.length() + soff.length() + slim.length() )
			.append( sql );
	if (LimitHelper.hasFirstRow( selection )) {
		sb.append( soff );
	}
	if (LimitHelper.hasMaxRows( selection )) {
		sb.append( slim );
	}
	return sb.toString();
}
 

/**
 * {@inheritDoc}
 * <p/>
 * From Derby 10.5 Docs:
 * <pre>
 * Query
 * [ORDER BY clause]
 * [result offset clause]
 * [fetch first clause]
 * [FOR UPDATE clause]
 * [WITH {RR|RS|CS|UR}]
 * </pre>
 */
@Override
public String processSql(String sql, RowSelection selection) {
	final StringBuilder sb = new StringBuilder( sql.length() + 50 );
	final String normalizedSelect = sql.toLowerCase(Locale.ROOT).trim();
	final int forUpdateIndex = normalizedSelect.lastIndexOf( "for update" );

	if (hasForUpdateClause( forUpdateIndex )) {
		sb.append( sql.substring( 0, forUpdateIndex - 1 ) );
	}
	else if (hasWithClause( normalizedSelect )) {
		sb.append( sql.substring( 0, getWithIndex( sql ) - 1 ) );
	}
	else {
		sb.append( sql );
	}

	if (LimitHelper.hasFirstRow( selection )) {
		sb.append( " offset " ).append( selection.getFirstRow() ).append( " rows fetch next " );
	}
	else {
		sb.append( " fetch first " );
	}

	sb.append( getMaxOrLimit( selection ) ).append(" rows only" );

	if (hasForUpdateClause( forUpdateIndex )) {
		sb.append( ' ' );
		sb.append( sql.substring( forUpdateIndex ) );
	}
	else if (hasWithClause( normalizedSelect )) {
		sb.append( ' ' ).append( sql.substring( getWithIndex( sql ) ) );
	}
	return sb.toString();
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	sql = sql.trim();
	boolean isForUpdate = false;
	if (sql.toLowerCase(Locale.ROOT).endsWith( " for update" )) {
		sql = sql.substring( 0, sql.length() - 11 );
		isForUpdate = true;
	}

	final StringBuilder pagingSelect = new StringBuilder( sql.length() + 100 );
	if (hasOffset) {
		pagingSelect.append( "select * from ( select row_.*, rownum rownum_ from ( " );
	}
	else {
		pagingSelect.append( "select * from ( " );
	}
	pagingSelect.append( sql );
	if (hasOffset) {
		pagingSelect.append( " ) row_ ) where rownum_ <= ? and rownum_ > ?" );
	}
	else {
		pagingSelect.append( " ) where rownum <= ?" );
	}

	if (isForUpdate) {
		pagingSelect.append( " for update" );
	}

	return pagingSelect.toString();
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	if ( LimitHelper.hasFirstRow( selection ) ) {
		//nest the main query in an outer select
		return "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( "
				+ sql + " fetch first " + getMaxOrLimit( selection ) + " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
				+ selection.getFirstRow() + " order by rownumber_";
	}
	return sql + " fetch first " + getMaxOrLimit( selection ) + " rows only";
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return new StringBuilder( sql.length() + 20 )
			.append( sql )
			.insert( 6, hasOffset ? " first ? skip ?" : " first ?" )
			.toString();
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	if (LimitHelper.hasFirstRow( selection )) {
		//nest the main query in an outer select
		return "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( "
				+ sql + " fetch first " + getMaxOrLimit( selection ) + " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
				+ selection.getFirstRow() + " order by rownumber_";
	}
	return sql + " fetch first " + getMaxOrLimit( selection ) +  " rows only";
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean useLimitOffset = supportsLimit()
			&& supportsLimitOffset()
			&& LimitHelper.hasFirstRow( selection )
			&& LimitHelper.hasMaxRows( selection );
	return dialect.getLimitString(
			sql,
			useLimitOffset ? LimitHelper.getFirstRow( selection ) : 0,
			getMaxOrLimit( selection )
	);
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	if ( LimitHelper.useLimit( this, selection ) ) {
		// useLimitOffset: whether "offset" is set or not;
		// if set, use "LIMIT offset, row_count" syntax;
		// if not, use "LIMIT row_count"
		final boolean useLimitOffset = LimitHelper.hasFirstRow( selection );
		return sql + (useLimitOffset ? " limit ?, ?" : " limit ?");
	}
	else {
		// or return unaltered SQL
		return sql;
	}
}
 

/**
 * Some dialect-specific LIMIT clauses require the maximum last row number
 * (aka, first_row_number + total_row_count), while others require the maximum
 * returned row count (the total maximum number of rows to return).
 *
 * @param selection the selection criteria for rows.
 *
 * @return The appropriate value to bind into the limit clause.
 */
protected final int getMaxOrLimit(RowSelection selection) {
	final int firstRow = convertToFirstRowValue( LimitHelper.getFirstRow( selection ) );
	final int lastRow = selection.getMaxRows();
	final int maxRows = useMaxForLimit() ? lastRow + firstRow : lastRow;
	// Use Integer.MAX_VALUE on overflow
	if ( maxRows < 0 ) {
		return Integer.MAX_VALUE;
	}
	else {
		return maxRows;
	}
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	return new StringBuilder( sql.length() + 16 )
			.append( sql )
			.insert( sql.toLowerCase( Locale.ROOT).indexOf( "select" ) + 6, " first " + getMaxOrLimit( selection ) )
			.toString();
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	if (LimitHelper.useLimit( this, selection )) {
		return sql + (LimitHelper.hasFirstRow( selection ) ?
				" offset ? rows fetch next ? rows only" : " fetch first ? rows only");
	}
	else {
		// or return unaltered SQL
		return sql;
	}
}