Package org.hibernate.dialect.pagination

Class SQLServer2005LimitHandler

java.lang.Object

org.hibernate.dialect.pagination.AbstractLimitHandler

org.hibernate.dialect.pagination.SQLServer2005LimitHandler

All Implemented Interfaces:

LimitHandler

public class SQLServer2005LimitHandler
extends AbstractLimitHandler

A LimitHandler compatible with SQL Server 2005 and later that uses top() and rownumber().

Field Summary

Fields inherited from class org.hibernate.dialect.pagination.AbstractLimitHandler

NO_LIMIT

Constructor Summary

Constructors

Constructor	Description
SQLServer2005LimitHandler()

Method Summary

Modifier and Type	Method	Description
int	bindLimitParametersAtEndOfQuery(Limit limit, PreparedStatement statement, int index)
int	bindLimitParametersAtStartOfQuery(Limit limit, PreparedStatement statement, int index)
int	convertToFirstRowValue(int zeroBasedFirstResult)	Hibernate Query.setFirstResult(int) accepts a zero-based offset.
String	processSql(String sql, Limit limit)	When the offset of the current row is 0, add a top(?) clause to the given SQL query.
boolean	supportsLimit()	Does this handler support limiting query results?
boolean	supportsVariableLimit()	Does this handler support bind variables (JDBC prepared statement parameters) for its limit/offset?
boolean	useMaxForLimit()	Does the limit clause expect the number of the last row, or the "page size", the maximum number of rows we want to receive? Hibernate's Query.setMaxResults(int) accepts the page size, so the number of the last row is obtained by adding the number of the first row, which is one greater than Query.setFirstResult(int).

Methods inherited from class org.hibernate.dialect.pagination.AbstractLimitHandler

bindLimitParameters, bindLimitParametersFirst, bindLimitParametersInReverseOrder, forceLimitUsage, getFirstRow, getForUpdatePattern, getMaxOrLimit, hasFirstRow, hasMaxRows, insertAfterDistinct, insertAfterSelect, insertAtEnd, insertBeforeForUpdate, setMaxRows, supportsLimitOffset, supportsOffset

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface org.hibernate.dialect.pagination.LimitHandler

processSql

Constructor Detail

SQLServer2005LimitHandler

public SQLServer2005LimitHandler()

Method Detail

supportsLimit

public final boolean supportsLimit()

Description copied from interface: LimitHandler

Does this handler support limiting query results?

Specified by:

supportsLimit in interface LimitHandler

Overrides:

supportsLimit in class AbstractLimitHandler

Returns:

True if this handler supports limit alone.

useMaxForLimit

public final boolean useMaxForLimit()

Description copied from class: AbstractLimitHandler

Does the limit clause expect the number of the last row, or the "page size", the maximum number of rows we want to receive? Hibernate's Query.setMaxResults(int) accepts the page size, so the number of the last row is obtained by adding the number of the first row, which is one greater than Query.setFirstResult(int).

Overrides:

useMaxForLimit in class AbstractLimitHandler

Returns:

true if the limit clause expects the number of the last row, false if it expects the page size

supportsVariableLimit

public final boolean supportsVariableLimit()

Description copied from class: AbstractLimitHandler

Does this handler support bind variables (JDBC prepared statement parameters) for its limit/offset?

Overrides:

supportsVariableLimit in class AbstractLimitHandler

Returns:

true if bind variables can be used

convertToFirstRowValue

public int convertToFirstRowValue(int zeroBasedFirstResult)

Description copied from class: AbstractLimitHandler

Hibernate Query.setFirstResult(int) accepts a zero-based offset. Does this dialect require a one-based offset to be specified in the offset clause?

NOTE: what gets passed into AbstractLimitHandler.processSql(String, Limit) is the zero-based offset. Handlers which do not AbstractLimitHandler.supportsVariableLimit() should take care to perform any needed first-row-conversion calls prior to injecting the limit values into the SQL string.

Overrides:

convertToFirstRowValue in class AbstractLimitHandler

Parameters:

zeroBasedFirstResult - The user-supplied, zero-based first row offset.

Returns:

The resulting offset, adjusted to one-based if necessary.

processSql

public String processSql(String sql,
                         Limit limit)

When the offset of the current row is 0, add a top(?) clause to the given SQL query. When the offset is non-zero, wrap the given query in an outer query that limits the results using the row_number() window function.

 with query_ as (
     select row_.*, row_number()
         over (order by current_timestamp) AS rownumber_
     from ( [original-query] ) row_
 )
 select [alias-list] from query_
 where rownumber_ >= ? and rownumber_ < ?
 

Where [original-query] is the original SQL query, with a top() clause added iff the query has an order by clause, and with generated aliases added to any elements of the projection list that don't already have aliases, and [alias-list] is a list of aliases in the projection list.

Specified by:

processSql in interface LimitHandler

Overrides:

processSql in class AbstractLimitHandler

Returns:

A new SQL statement

bindLimitParametersAtStartOfQuery

public int bindLimitParametersAtStartOfQuery(Limit limit,
                                             PreparedStatement statement,
                                             int index)
                                      throws SQLException

Specified by:

bindLimitParametersAtStartOfQuery in interface LimitHandler

Overrides:

bindLimitParametersAtStartOfQuery in class AbstractLimitHandler

Throws:

SQLException

bindLimitParametersAtEndOfQuery

public int bindLimitParametersAtEndOfQuery(Limit limit,
                                           PreparedStatement statement,
                                           int index)
                                    throws SQLException

Specified by:

bindLimitParametersAtEndOfQuery in interface LimitHandler

Overrides:

bindLimitParametersAtEndOfQuery in class AbstractLimitHandler

Throws:

SQLException