com.github.drinkjava2.hibernate.pagination

Class SQLServer2012LimitHandler

java.lang.Object

com.github.drinkjava2.hibernate.pagination.AbstractLimitHandler

com.github.drinkjava2.hibernate.pagination.SQLServer2005LimitHandler

com.github.drinkjava2.hibernate.pagination.SQLServer2012LimitHandler

All Implemented Interfaces:

LimitHandler

public class SQLServer2012LimitHandler
extends SQLServer2005LimitHandler

LIMIT clause handler compatible with SQL Server 2012 and later.

Author:

Chris Cranford

Field Summary

Fields inherited from class com.github.drinkjava2.hibernate.pagination.SQLServer2005LimitHandler

topAdded

Constructor Summary

Constructors

Constructor and Description
SQLServer2012LimitHandler()

Method Summary

Methods

Modifier and Type	Method and Description
int	bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index) Bind parameter values needed by the LIMIT clause afterQuery original SELECT statement.
int	convertToFirstRowValue(int zeroBasedFirstResult) Hibernate APIs explicitly state that setFirstResult() should be a zero-based offset.
String	processSql(String sql, RowSelection selection) Add a LIMIT clause to the given SQL SELECT (HHH-2655: ROW_NUMBER for Paging) The LIMIT SQL will look like:
boolean	supportsLimit() Does this handler support some form of limiting query results via a SQL clause?
boolean	supportsVariableLimit() Does this handler support bind variables (i.e., prepared statement parameters) for its limit/offset?
boolean	useMaxForLimit() Does the LIMIT clause take a "maximum" row number instead of a total number of returned rows? This is easiest understood via an example.

Methods inherited from class com.github.drinkjava2.hibernate.pagination.SQLServer2005LimitHandler

addTopExpression, bindLimitParametersAtStartOfQuery, encloseWithOuterQuery, fillAliasInSelectClause, supportsLimitOffset

Methods inherited from class com.github.drinkjava2.hibernate.pagination.AbstractLimitHandler

bindLimitParameters, bindLimitParametersFirst, bindLimitParametersInReverseOrder, forceLimitUsage, getMaxOrLimit, setMaxRows

Methods inherited from class java.lang.Object

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

Constructor Detail

SQLServer2012LimitHandler

public SQLServer2012LimitHandler()

Method Detail

supportsLimit

public boolean supportsLimit()

Description copied from interface: LimitHandler

Does this handler support some form of limiting query results via a SQL clause?

Specified by:

supportsLimit in interface LimitHandler

Overrides:

supportsLimit in class SQLServer2005LimitHandler

Returns:

True if this handler supports some form of LIMIT.

supportsVariableLimit

public boolean supportsVariableLimit()

Description copied from class: AbstractLimitHandler

Does this handler support bind variables (i.e., prepared statement parameters) for its limit/offset?

Overrides:

supportsVariableLimit in class SQLServer2005LimitHandler

Returns:

True if bind variables can be used; false otherwise.

processSql

public String processSql(String sql,
                RowSelection selection)

Description copied from class: SQLServer2005LimitHandler

Add a LIMIT clause to the given SQL SELECT (HHH-2655: ROW_NUMBER for Paging) The LIMIT SQL will look like:

 WITH query AS (
   SELECT inner_query.*
        , ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__
     FROM ( original_query_with_top_if_order_by_present_and_all_aliased_columns ) inner_query
 )
 SELECT alias_list FROM query WHERE __hibernate_row_nr__ >= offset AND __hibernate_row_nr__ < offset + last
 

When offset equals 0, only TOP(?) expression is added to the original query.

Specified by:

processSql in interface LimitHandler

Overrides:

processSql in class SQLServer2005LimitHandler

Parameters:

sql - the SQL query to process.

selection - the selection criteria for rows.

Returns:

A new SQL statement with the LIMIT clause applied.

useMaxForLimit

public boolean useMaxForLimit()

Description copied from class: AbstractLimitHandler

Does the LIMIT clause take a "maximum" row number instead of a total number of returned rows?

This is easiest understood via an example. Consider you have a table with 20 rows, but you only want to retrieve rows number 11 through 20. Generally, a limit with offset would say that the offset = 11 and the limit = 10 (we only want 10 rows at a time); this is specifying the total number of returned rows. Some dialects require that we instead specify offset = 11 and limit = 20, where 20 is the "last" row we want relative to offset (i.e. total number of rows = 20 - 11 = 9)

So essentially, is limit relative from offset? Or is limit absolute?

Overrides:

useMaxForLimit in class SQLServer2005LimitHandler

Returns:

True if limit is relative from offset; false otherwise.

convertToFirstRowValue

public int convertToFirstRowValue(int zeroBasedFirstResult)

Description copied from class: AbstractLimitHandler

Hibernate APIs explicitly state that setFirstResult() should be a zero-based offset. Here we allow the Dialect a chance to convert that value based on what the underlying db or driver will expect.

NOTE: what gets passed into AbstractLimitHandler#processSql(String, org.hibernate.engine.spi.RowSelection) is the zero-based offset. Dialects 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 SQLServer2005LimitHandler

Parameters:

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

Returns:

The corresponding db/dialect specific offset.

See Also:

org.hibernate.Query#setFirstResult, org.hibernate.Criteria#setFirstResult

bindLimitParametersAtEndOfQuery

public int bindLimitParametersAtEndOfQuery(RowSelection selection,
                                  PreparedStatement statement,
                                  int index)
                                    throws SQLException

Description copied from interface: LimitHandler

Bind parameter values needed by the LIMIT clause afterQuery original SELECT statement.

Specified by:

bindLimitParametersAtEndOfQuery in interface LimitHandler

Overrides:

bindLimitParametersAtEndOfQuery in class SQLServer2005LimitHandler

Parameters:

selection - the selection criteria for rows.

statement - Statement to which to bind limit parameter values.

index - Index from which to start binding.

Returns:

The number of parameter values bound.

Throws:

SQLException - Indicates problems binding parameter values.