Top Description Fields Constructors Methods
org.hibernate.dialect.pagination

public Class SQLServer2012LimitHandler

extends SQLServer2005LimitHandler
Class Inheritance
Imports
java.sql.PreparedStatement, .SQLException, org.hibernate.engine.spi.RowSelection
LIMIT clause handler compatible with SQL Server 2012 and later.
Author
Chris Cranford

Field Summary

Modifier and TypeField and Description
private boolean
usedOffsetFetch

Constructor Summary

AccessConstructor and Description
public
SQLServer2012LimitHandler()

Method Summary

Modifier and TypeMethod and Description
private String
applyOffsetFetch(RowSelection selection, String sql, int position)

public int
bindLimitParametersAtEndOfQuery(RowSelection
the selection criteria for rows.
selection, PreparedStatement
Statement to which to bind limit parameter values.
statement, int
Index from which to start binding.
index)

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.bindLimitParametersAtEndOfQuery.

Implements org.hibernate.dialect.pagination.LimitHandler.bindLimitParametersAtEndOfQuery.

Bind parameter values needed by the LIMIT clause after original SELECT statement.
public int
convertToFirstRowValue(int
The user-supplied, zero-based first row offset.
zeroBasedFirstResult)

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.convertToFirstRowValue.

Hibernate APIs explicitly state that setFirstResult() should be a zero-based offset.
private int
getInsertPosition(String sql)

private String
getOffsetFetch(RowSelection selection)

private boolean
hasOrderBy(String sql)

public String
processSql(String
the SQL query to process.
sql, RowSelection
the selection criteria for rows.
selection)

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.processSql.

Implements org.hibernate.dialect.pagination.LimitHandler.processSql.

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 __row__
    FROM ( original_query_with_top_if_order_by_present_and_all_aliased_columns ) inner_query
)
SELECT alias_list FROM query WHERE __row__ >= offset AND __row__ < offset + last
public boolean
supportsLimit()

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.supportsLimit.

Implements org.hibernate.dialect.pagination.LimitHandler.supportsLimit.

Does this handler support some form of limiting query results via a SQL clause?
public boolean
supportsVariableLimit()

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.supportsVariableLimit.

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

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.useMaxForLimit.

Does the LIMIT clause take a "maximum" row number instead of a total number of returned rows?
Inherited from org.hibernate.dialect.pagination.SQLServer2005LimitHandler:
addTopExpressionbindLimitParametersAtStartOfQueryencloseWithOuterQueryfillAliasInSelectClausesupportsLimitOffset

Field Detail

usedOffsetFetchback to summary
private boolean usedOffsetFetch

Constructor Detail

SQLServer2012LimitHandlerback to summary
public SQLServer2012LimitHandler()

Method Detail

applyOffsetFetchback to summary
private String applyOffsetFetch(RowSelection selection, String sql, int position)
bindLimitParametersAtEndOfQueryback to summary
public int bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index) throws SQLException

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.bindLimitParametersAtEndOfQuery.

Implements org.hibernate.dialect.pagination.LimitHandler.bindLimitParametersAtEndOfQuery.

Doc from org.hibernate.dialect.pagination.LimitHandler.bindLimitParametersAtEndOfQuery.

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

Parameters
selection:RowSelection

the selection criteria for rows.

statement:PreparedStatement

Statement to which to bind limit parameter values.

index:int

Index from which to start binding.

Returns:int

The number of parameter values bound.

Annotations
@Override
Exceptions
SQLException:
Indicates problems binding parameter values.
convertToFirstRowValueback to summary
public int convertToFirstRowValue(int zeroBasedFirstResult)

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.convertToFirstRowValue.

Doc from org.hibernate.dialect.pagination.AbstractLimitHandler.convertToFirstRowValue.

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 supportsVariableLimit should take care to perform any needed first-row-conversion calls prior to injecting the limit values into the SQL string.

Parameters
zeroBasedFirstResult:int

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

Returns:int

The corresponding db/dialect specific offset.

Annotations
@Override
getInsertPositionback to summary
private int getInsertPosition(String sql)
getOffsetFetchback to summary
private String getOffsetFetch(RowSelection selection)
hasOrderByback to summary
private boolean hasOrderBy(String sql)
processSqlback to summary
public String processSql(String sql, RowSelection selection)

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.processSql.

Implements org.hibernate.dialect.pagination.LimitHandler.processSql.

Doc from org.hibernate.dialect.pagination.SQLServer2005LimitHandler.processSql.

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 __row__
    FROM ( original_query_with_top_if_order_by_present_and_all_aliased_columns ) inner_query
)
SELECT alias_list FROM query WHERE __row__ >= offset AND __row__ < offset + last
When offset equals 0, only TOP(?) expression is added to the original query.
Parameters
sql:String

Doc from org.hibernate.dialect.pagination.LimitHandler.processSql.

the SQL query to process.

selection:RowSelection

Doc from org.hibernate.dialect.pagination.LimitHandler.processSql.

the selection criteria for rows.

Returns:String

A new SQL statement with the LIMIT clause applied.

Annotations
@Override
supportsLimitback to summary
public boolean supportsLimit()

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.supportsLimit.

Implements org.hibernate.dialect.pagination.LimitHandler.supportsLimit.

Doc from org.hibernate.dialect.pagination.LimitHandler.supportsLimit.

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

Returns:boolean

True if this handler supports some form of LIMIT.

Annotations
@Override
supportsVariableLimitback to summary
public boolean supportsVariableLimit()

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.supportsVariableLimit.

Doc from org.hibernate.dialect.pagination.AbstractLimitHandler.supportsVariableLimit.

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

Returns:boolean

True if bind variables can be used; false otherwise.

Annotations
@Override
useMaxForLimitback to summary
public boolean useMaxForLimit()

Overrides org.hibernate.dialect.pagination.SQLServer2005LimitHandler.useMaxForLimit.

Doc from org.hibernate.dialect.pagination.AbstractLimitHandler.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. 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?

Returns:boolean

True if limit is relative from offset; false otherwise.

Annotations
@Override