下面列出了怎么用org.hibernate.engine.spi.RowSelection的API类实例代码及写法,或者点击链接到github查看源代码。
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;
}
}