org.hibernate.engine.spi.RowSelection Java Examples

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

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

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

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

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

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

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

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

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

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

@Test
public void testLimit() {
    final RowSelection rowSelection = new RowSelection();
    rowSelection.setMaxRows(getMaxRows());
    LimitHandler limitHandler = ((SessionFactoryImpl) sessionFactory()).getDialect().getLimitHandler();
    String limitStatement = limitHandler.processSql(SELECT_POST_COMMENT, rowSelection);
    long startNanos = System.nanoTime();
    doInJDBC(connection -> {
        try (PreparedStatement statement = connection.prepareStatement(limitStatement)) {
            limitHandler.bindLimitParametersAtEndOfQuery(rowSelection, statement, 1);
            statement.setInt(1, getMaxRows());
            statement.execute();
            int count = 0;
            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                resultSet.getLong(1);
                count++;
            }
            assertEquals(getMaxRows(), count);
        } catch (SQLException e) {
            fail(e.getMessage());
        }

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

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

/**
 * 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) {
	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) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	String sqlOffset = hasOffset ? " SKIP " + selection.getFirstRow() : "";
	String sqlLimit = " FIRST " + getMaxOrLimit( selection );
	String sqlOffsetLimit = sqlOffset + sqlLimit;
	String result = new StringBuilder( sql.length() + 10 )
			.append( sql )
			.insert( sql.toLowerCase( Locale.ROOT ).indexOf( "select" ) + 6, sqlOffsetLimit ).toString();
	return result;
}
 

@Override
public void setMaxRows(RowSelection selection, PreparedStatement statement) throws SQLException {
	if ( LimitHelper.hasMaxRows( selection ) ) {
		int maxRows = selection.getMaxRows() + convertToFirstRowValue( LimitHelper.getFirstRow( selection ) );
		// Use Integer.MAX_VALUE on overflow
		if ( maxRows < 0 ) {
			statement.setMaxRows( Integer.MAX_VALUE );
		}
		else {
			statement.setMaxRows( maxRows );
		}
	}
}
 

@Override
public int bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index)
throws SQLException {
	if ( usedOffsetFetch && !LimitHelper.hasFirstRow( selection ) ) {
		// apply just the max value when offset fetch applied
		statement.setInt( index, getMaxOrLimit( selection ) );
		return 1;
	}
	return super.bindLimitParametersAtEndOfQuery( selection, statement, index );
}
 

@Override
public int bindLimitParametersAtStartOfQuery(RowSelection selection, PreparedStatement statement, int index) throws SQLException {
	if ( topAdded ) {
		// Binding TOP(?)
		statement.setInt( index, getMaxOrLimit( selection ) - 1 );
		return 1;
	}
	return 0;
}
 

@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 )) {
		return sql + (LimitHelper.hasFirstRow( selection ) ?
				" offset ? rows fetch next ? rows only" : " fetch first ? rows only");
	}
	else {
		// or return unaltered SQL
		return sql;
	}
}
 

@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) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	if ( hasOffset ) {
		throw new UnsupportedOperationException( "query result offset is not supported" );
	}
	return super.processSql( sql, 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;
	}
}
 

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