org.hibernate.dialect.pagination.LimitHelper Java Examples

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();
			}
		}
	}
}
 

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 ?");
	}
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	sql = sql.trim();
	String forUpdateClause = null;
	boolean isForUpdate = false;
	final int forUpdateIndex = sql.toLowerCase(Locale.ROOT).lastIndexOf( "for update" );
	if (forUpdateIndex > -1) {
		// save 'for update ...' and then remove it
		forUpdateClause = sql.substring( forUpdateIndex );
		sql = sql.substring( 0, forUpdateIndex - 1 );
		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 <= ?) where rownum_ > ?" );
	}
	else {
		pagingSelect.append( " ) where rownum <= ?" );
	}

	if (isForUpdate) {
		pagingSelect.append( " " );
		pagingSelect.append( forUpdateClause );
	}

	return pagingSelect.toString();
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	if (hasOffset) {
		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) {
	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 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();
}
 

/**
 * {@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 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();
}
 

@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";
}
 

/**
 * 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;
	}
}
 

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();
		}

	}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return hasOffset ? sql + " rows ? to ?" : sql + " rows ?";
}
 

/**
 * Obtain a <tt>PreparedStatement</tt> with all parameters pre-bound.
 * Bind JDBC-style <tt>?</tt> parameters, named parameters, and
 * limit parameters.
 */
protected final PreparedStatement prepareQueryStatement(
		final String sql,
		final QueryParameters queryParameters,
		final LimitHandler limitHandler,
		final boolean scroll,
		final SharedSessionContractImplementor session) throws SQLException, HibernateException {
	final Dialect dialect = session.getJdbcServices().getJdbcEnvironment().getDialect();
	final RowSelection selection = queryParameters.getRowSelection();
	final boolean useLimit = LimitHelper.useLimit( limitHandler, selection );
	final boolean hasFirstRow = LimitHelper.hasFirstRow( selection );
	final boolean useLimitOffset = hasFirstRow && useLimit && limitHandler.supportsLimitOffset();
	final boolean callable = queryParameters.isCallable();
	final ScrollMode scrollMode = getScrollMode( scroll, hasFirstRow, useLimitOffset, queryParameters );

	final PreparedStatement st = session.getJdbcCoordinator()
			.getStatementPreparer().prepareQueryStatement( sql, callable, scrollMode );

	try {

		int col = 1;
		//TODO: can we limit stored procedures ?!
		col += limitHandler.bindLimitParametersAtStartOfQuery( selection, st, col );

		if (callable) {
			col = dialect.registerResultSetOutParameter( (CallableStatement)st, col );
		}

		col += bindParameterValues( st, queryParameters, col, session );

		col += limitHandler.bindLimitParametersAtEndOfQuery( selection, st, col );

		limitHandler.setMaxRows( selection, st );

		if ( selection != null ) {
			if ( selection.getTimeout() != null ) {
				st.setQueryTimeout( selection.getTimeout() );
			}
			if ( selection.getFetchSize() != null ) {
				st.setFetchSize( selection.getFetchSize() );
			}
		}

		// handle lock timeout...
		final LockOptions lockOptions = queryParameters.getLockOptions();
		if ( lockOptions != null ) {
			if ( lockOptions.getTimeOut() != LockOptions.WAIT_FOREVER ) {
				if ( !dialect.supportsLockTimeouts() ) {
					if ( log.isDebugEnabled() ) {
						log.debugf(
								"Lock timeout [%s] requested but dialect reported to not support lock timeouts",
								lockOptions.getTimeOut()
						);
					}
				}
				else if ( dialect.isLockTimeoutParameterized() ) {
					st.setInt( col++, lockOptions.getTimeOut() );
				}
			}
		}

		if ( log.isTraceEnabled() ) {
			log.tracev( "Bound [{0}] parameters total", col );
		}
	}
	catch ( SQLException sqle ) {
		session.getJdbcCoordinator().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
		throw sqle;
	}
	catch ( HibernateException he ) {
		session.getJdbcCoordinator().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
		throw he;
	}

	return st;
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + (hasOffset ? " limit ?, ?" : " limit ?");
}
 

@Override
public List extractResults(
		ResultSet resultSet,
		final SharedSessionContractImplementor session,
		QueryParameters queryParameters,
		NamedParameterContext namedParameterContext,
		boolean returnProxies,
		boolean readOnly,
		ResultTransformer forcedResultTransformer,
		List<AfterLoadAction> afterLoadActionList) throws SQLException {

	handlePotentiallyEmptyCollectionRootReturns( loadPlan, queryParameters.getCollectionKeys(), resultSet, session );

	final int maxRows;
	final RowSelection selection = queryParameters.getRowSelection();
	if ( LimitHelper.hasMaxRows( selection ) ) {
		maxRows = selection.getMaxRows();
		LOG.tracef( "Limiting ResultSet processing to just %s rows", maxRows );
	}
	else {
		maxRows = Integer.MAX_VALUE;
	}

	// Handles the "FETCH ALL PROPERTIES" directive in HQL
	final boolean forceFetchLazyAttributes = false;

	final ResultSetProcessingContextImpl context = new ResultSetProcessingContextImpl(
			resultSet,
			session,
			loadPlan,
			aliasResolutionContext,
			readOnly,
			shouldUseOptionalEntityInstance,
			forceFetchLazyAttributes,
			returnProxies,
			queryParameters,
			namedParameterContext,
			hadSubselectFetches
	);

	final List loadResults = new ArrayList();

	LOG.trace( "Processing result set" );
	int count;
	for ( count = 0; count < maxRows && resultSet.next(); count++ ) {
		LOG.debugf( "Starting ResultSet row #%s", count );

		Object logicalRow = rowReader.readRow( resultSet, context );

		// todo : apply transformers here?

		loadResults.add( logicalRow );

		context.finishUpRow();
	}

	LOG.tracev( "Done processing result set ({0} rows)", count );

	rowReader.finishUp( context, afterLoadActionList );
	context.wrapUp();

	session.getPersistenceContext().initializeNonLazyCollections();

	return loadResults;
}
 

/**
 * Obtain a <tt>PreparedStatement</tt> with all parameters pre-bound.
 * Bind JDBC-style <tt>?</tt> parameters, named parameters, and
 * limit parameters.
 */
protected final PreparedStatement prepareQueryStatement(
		String sql,
		final QueryParameters queryParameters,
		final LimitHandler limitHandler,
		final boolean scroll,
		final SharedSessionContractImplementor session) throws SQLException, HibernateException {
	final Dialect dialect = getFactory().getDialect();
	final RowSelection selection = queryParameters.getRowSelection();
	final boolean useLimit = LimitHelper.useLimit( limitHandler, selection );
	final boolean hasFirstRow = LimitHelper.hasFirstRow( selection );
	final boolean useLimitOffset = hasFirstRow && useLimit && limitHandler.supportsLimitOffset();
	final boolean callable = queryParameters.isCallable();
	final ScrollMode scrollMode = getScrollMode( scroll, hasFirstRow, useLimitOffset, queryParameters );

	PreparedStatement st = session.getJdbcCoordinator().getStatementPreparer().prepareQueryStatement(
			sql,
			callable,
			scrollMode
	);

	try {

		int col = 1;
		//TODO: can we limit stored procedures ?!
		col += limitHandler.bindLimitParametersAtStartOfQuery( selection, st, col );

		if ( callable ) {
			col = dialect.registerResultSetOutParameter( (CallableStatement) st, col );
		}

		col += bindParameterValues( st, queryParameters, col, session );

		col += limitHandler.bindLimitParametersAtEndOfQuery( selection, st, col );

		limitHandler.setMaxRows( selection, st );

		if ( selection != null ) {
			if ( selection.getTimeout() != null ) {
				st.setQueryTimeout( selection.getTimeout() );
			}
			if ( selection.getFetchSize() != null ) {
				st.setFetchSize( selection.getFetchSize() );
			}
		}

		// handle lock timeout...
		LockOptions lockOptions = queryParameters.getLockOptions();
		if ( lockOptions != null ) {
			if ( lockOptions.getTimeOut() != LockOptions.WAIT_FOREVER ) {
				if ( !dialect.supportsLockTimeouts() ) {
					if ( LOG.isDebugEnabled() ) {
						LOG.debugf(
								"Lock timeout [%s] requested but dialect reported to not support lock timeouts",
								lockOptions.getTimeOut()
						);
					}
				}
				else if ( dialect.isLockTimeoutParameterized() ) {
					st.setInt( col++, lockOptions.getTimeOut() );
				}
			}
		}

		if ( LOG.isTraceEnabled() ) {
			LOG.tracev( "Bound [{0}] parameters total", col );
		}
	}
	catch (SQLException | HibernateException e) {
		session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( st );
		session.getJdbcCoordinator().afterStatementExecution();
		throw e;
	}

	return st;
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + ( hasOffset ? " limit ? offset ?" : " limit ?" );
}
 

@Override
public String processSql(String sql, RowSelection selection) {
    final boolean hasOffset = LimitHelper.hasFirstRow(selection);
    return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
}
 

@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
}
 

/**
 * This method is based on {@link Loader#processResultSet}
 */
@Override
@SuppressWarnings("unchecked")
public CompletionStage<List<Object>> reactiveExtractResults(
		ResultSet rs,
		SharedSessionContractImplementor session,
		QueryParameters queryParameters,
		NamedParameterContext namedParameterContext,
		boolean returnProxies,
		boolean readOnly,
		ResultTransformer forcedResultTransformer,
		List<AfterLoadAction> afterLoadActionList) throws SQLException {
	final int entitySpan = loader.getEntityPersisters().length;
	final RowSelection rowSelection = queryParameters.getRowSelection();
	final int maxRows = LimitHelper.hasMaxRows( rowSelection ) ? rowSelection.getMaxRows() : Integer.MAX_VALUE;
	final boolean createSubselects = loader.isSubselectLoadingEnabled();
	final List<EntityKey[]> subselectResultKeys = createSubselects ? new ArrayList<>() : null;
	final List<Object> hydratedObjects = entitySpan == 0 ? null : new ArrayList<>(entitySpan * 10);

	final List results = loader.getRowsFromResultSet(
			rs,
			queryParameters,
			session,
			returnProxies,
			forcedResultTransformer,
			maxRows,
			hydratedObjects,
			subselectResultKeys );

	return CompletionStages.nullFuture()
			.thenCompose(v -> reactiveInitializeEntitiesAndCollections(
					hydratedObjects,
					rs,
					session,
					queryParameters.isReadOnly(session),
					afterLoadActionList))
			.thenAccept(v -> {
				if (createSubselects) {
					loader.createSubselects(subselectResultKeys, queryParameters, session);
				}
			})
			.thenApply(v -> results);
}
 

/**
 * Build LIMIT clause handler applicable for given selection criteria. Returns {@link org.hibernate.dialect.pagination.NoopLimitHandler} delegate
 * if dialect does not support LIMIT expression or processed query does not use pagination.
 *
 * @param selection Selection criteria.
 * @return LIMIT clause delegate.
 */
protected LimitHandler getLimitHandler(RowSelection selection) {
	final LimitHandler limitHandler = getFactory().getDialect().getLimitHandler();
	return LimitHelper.useLimit( limitHandler, selection ) ? limitHandler : NoopLimitHandler.INSTANCE;
}
 

/**
 * Build LIMIT clause handler applicable for given selection criteria. Returns {@link NoopLimitHandler} delegate
 * if dialect does not support LIMIT expression or processed query does not use pagination.
 *
 * @param selection Selection criteria.
 *
 * @return LIMIT clause delegate.
 */
protected LimitHandler getLimitHandler(RowSelection selection) {
	final LimitHandler limitHandler = getFactory().getDialect().getLimitHandler();
	return LimitHelper.useLimit( limitHandler, selection ) ? limitHandler : NoopLimitHandler.INSTANCE;
}