org.hibernate.dialect.pagination
Class SQLServer2005LimitHandler

java.lang.Object
  extended by org.hibernate.dialect.pagination.AbstractLimitHandler
      extended by org.hibernate.dialect.pagination.SQLServer2005LimitHandler
All Implemented Interfaces:
LimitHandler

public class SQLServer2005LimitHandler
extends AbstractLimitHandler

LIMIT clause handler compatible with SQL Server 2005 and later.


Field Summary
 
Fields inherited from class org.hibernate.dialect.pagination.AbstractLimitHandler
selection, sql
 
Constructor Summary
SQLServer2005LimitHandler(String sql, RowSelection selection)
           
 
Method Summary
protected  void addTopExpression(StringBuilder sql)
          Adds TOP expression.
 int bindLimitParametersAtEndOfQuery(PreparedStatement statement, int index)
          Bind parameter values needed by the LIMIT clause after original SELECT statement.
 int bindLimitParametersAtStartOfQuery(PreparedStatement statement, int index)
          Bind parameter values needed by the LIMIT clause before original SELECT statement.
 int convertToFirstRowValue(int zeroBasedFirstResult)
          Hibernate APIs explicitly state that setFirstResult() should be a zero-based offset.
protected  void encloseWithOuterQuery(StringBuilder sql)
          Encloses original SQL statement with outer query that provides __hibernate_row_nr__ column.
protected  String fillAliasInSelectClause(StringBuilder sb)
          Adds missing aliases in provided SELECT clause and returns coma-separated list of them.
 String getProcessedSql()
          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 supportsLimitOffset()
          Does this handler's LIMIT support (if any) additionally support specifying an offset?
 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 org.hibernate.dialect.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

SQLServer2005LimitHandler

public SQLServer2005LimitHandler(String sql,
                                 RowSelection selection)
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 AbstractLimitHandler
Returns:
True if this handler supports some form of LIMIT.

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 AbstractLimitHandler
Returns:
True if limit is relative from offset; false otherwise.

supportsLimitOffset

public boolean supportsLimitOffset()
Description copied from interface: LimitHandler
Does this handler's LIMIT support (if any) additionally support specifying an offset?

Specified by:
supportsLimitOffset in interface LimitHandler
Overrides:
supportsLimitOffset in class AbstractLimitHandler
Returns:
True if the handler supports an offset within the limit support.

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 AbstractLimitHandler
Returns:
True if bind variables can be used; 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.AbstractLimitHandler(String, 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 AbstractLimitHandler
Parameters:
zeroBasedFirstResult - The user-supplied, zero-based first row offset.
Returns:
The corresponding db/dialect specific offset.
See Also:
Query.setFirstResult(int), Criteria.setFirstResult(int)

getProcessedSql

public String getProcessedSql()
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:
getProcessedSql in interface LimitHandler
Overrides:
getProcessedSql in class AbstractLimitHandler
Returns:
A new SQL statement with the LIMIT clause applied.

bindLimitParametersAtStartOfQuery

public int bindLimitParametersAtStartOfQuery(PreparedStatement statement,
                                             int index)
                                      throws SQLException
Description copied from interface: LimitHandler
Bind parameter values needed by the LIMIT clause before original SELECT statement.

Specified by:
bindLimitParametersAtStartOfQuery in interface LimitHandler
Overrides:
bindLimitParametersAtStartOfQuery in class AbstractLimitHandler
Parameters:
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.

bindLimitParametersAtEndOfQuery

public int bindLimitParametersAtEndOfQuery(PreparedStatement statement,
                                           int index)
                                    throws SQLException
Description copied from interface: LimitHandler
Bind parameter values needed by the LIMIT clause after original SELECT statement.

Specified by:
bindLimitParametersAtEndOfQuery in interface LimitHandler
Overrides:
bindLimitParametersAtEndOfQuery in class AbstractLimitHandler
Parameters:
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.

fillAliasInSelectClause

protected String fillAliasInSelectClause(StringBuilder sb)
Adds missing aliases in provided SELECT clause and returns coma-separated list of them. If query takes advantage of expressions like * or {table}.* inside SELECT clause, method returns *.

Parameters:
sb - SQL query.
Returns:
List of aliases separated with comas or *.

encloseWithOuterQuery

protected void encloseWithOuterQuery(StringBuilder sql)
Encloses original SQL statement with outer query that provides __hibernate_row_nr__ column.

Parameters:
sql - SQL query.

addTopExpression

protected void addTopExpression(StringBuilder sql)
Adds TOP expression. Parameter value is bind in bindLimitParametersAtStartOfQuery(PreparedStatement, int) method.

Parameters:
sql - SQL query.


Copyright © 2001-2013 Red Hat, Inc. All Rights Reserved.