Java Code Examples for org.hibernate.dialect.pagination.LimitHelper#hasFirstRow()

The following examples show how to use org.hibernate.dialect.pagination.LimitHelper#hasFirstRow() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: HSQLDialect.java    From lams with GNU General Public License v2.0 6 votes vote down vote up 
@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 ?");
	}
}
Example 2
Source File: Oracle9iDialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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();
}
Example 3
Source File: DB2390Dialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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";
}
Example 4
Source File: RDMSOS2200Dialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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 ";
}
Example 5
Source File: Ingres9Dialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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();
}
Example 6
Source File: DerbyDialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
/**
 * {@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();
}
Example 7
Source File: DB2Dialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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";
}
Example 8
Source File: Oracle8iDialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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();
}
Example 9
Source File: DB2400Dialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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";
}
Example 10
Source File: FirebirdDialect.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
@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();
}
Example 11
Source File: MySQLDialect.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + (hasOffset ? " limit ?, ?" : " limit ?");
}
Example 12
Source File: PostgreSQL81Dialect.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
}
Example 13
Source File: AbstractHANADialect.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + ( hasOffset ? " limit ? offset ?" : " limit ?" );
}
Example 14
Source File: SQLiteDialect.java    From md_blockchain with Apache License 2.0 4 votes vote down vote up 
@Override
public String processSql(String sql, RowSelection selection) {
    final boolean hasOffset = LimitHelper.hasFirstRow(selection);
    return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
}
Example 15
Source File: H2Dialect.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
}
Example 16
Source File: InterbaseDialect.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
@Override
public String processSql(String sql, RowSelection selection) {
	final boolean hasOffset = LimitHelper.hasFirstRow( selection );
	return hasOffset ? sql + " rows ? to ?" : sql + " rows ?";
}
Example 17
Source File: AbstractLoadPlanBasedLoader.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
/**
 * 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;
}
Example 18
Source File: Loader.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
/**
 * 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;
}