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

public Class SQLServer2005LimitHandler

extends AbstractLimitHandler
Class Inheritance
Imports
java.sql.PreparedStatement, .SQLException, java.util.LinkedList, .List, java.util.regex.Matcher, .Pattern, org.hibernate.internal.util.StringHelper, org.hibernate.query.spi.Limit
A LimitHandler compatible with SQL Server 2005 and later that uses top() and rownumber().
Authors
Lukasz Antoniak, Chris Cranford, Gavin King

Nested and Inner Type Summary

Modifier and TypeClass and Description
pack-priv static enum
SQLServer2005LimitHandler.Keyword

Field Summary

Modifier and TypeField and Description
private boolean
topAdded

Inherited from org.hibernate.dialect.pagination.AbstractLimitHandler:
NO_LIMIT

Constructor Summary

AccessConstructor and Description
public
SQLServer2005LimitHandler()

Method Summary

Modifier and TypeMethod and Description
public int
bindLimitParametersAtEndOfQuery(Limit limit, PreparedStatement statement, int index)

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParametersAtEndOfQuery.

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

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

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParametersAtStartOfQuery.

Implements org.hibernate.dialect.pagination.LimitHandler.bindLimitParametersAtStartOfQuery.

public int
convertToFirstRowValue(int
The user-supplied, zero-based first row offset.
zeroBasedFirstResult)

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.convertToFirstRowValue.

The API method Query#setFirstResult(int) accepts a zero-based offset.
private int
getAliasIndex(String sql)

private int

Returns:

the ending offset of the current element
nextElement(String
A SQL fragment containing a comma-separated list
sql, int
the starting offset of the current element
startOffset, int
the end of the list
endOffset)

Return the position just before the start of the next element of a comma-separated list, that is, the position of the next comma, or the position of the end of the list.

public String

Returns:

A new SQL statement
processSql(String sql, Limit limit)

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.processSql.

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

When the offset of the current row is 0, add a top(?) clause to the given SQL query.
private String

Returns:

a comma-separated list of aliases, or *
selectAliases(String
the whole query
sql, int
the starting offset of the select list
afterSelectOffset, int
the ending offset of the select list
fromOffset, StringBuilder
a string builder for inserting missing aliases
result)

Add any missing aliases to the given select list, and return comma-separated list of all aliases in the list, unless the given list contains * or {table.*}, in which case * is returned.

public final boolean
supportsLimit()

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.supportsLimit.

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

Does this handler support limiting query results?
public final boolean
supportsVariableLimit()

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.supportsVariableLimit.

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

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.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?
Inherited from org.hibernate.dialect.pagination.AbstractLimitHandler:
bindLimitParametersbindLimitParametersFirstbindLimitParametersInReverseOrderforceLimitUsagegetFirstRowgetForUpdatePatterngetMaxOrLimithasFirstRowhasMaxRowsinsertAfterDistinctinsertAfterSelectinsertAtEndinsertBeforeForUpdatesetMaxRowssupportsLimitOffsetsupportsOffset

Field Detail

topAddedback to summary
private boolean topAdded

Constructor Detail

SQLServer2005LimitHandlerback to summary
public SQLServer2005LimitHandler()

Method Detail

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

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParametersAtEndOfQuery.

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

Annotations
@Override
bindLimitParametersAtStartOfQueryback to summary
public int bindLimitParametersAtStartOfQuery(Limit limit, PreparedStatement statement, int index) throws SQLException

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParametersAtStartOfQuery.

Implements org.hibernate.dialect.pagination.LimitHandler.bindLimitParametersAtStartOfQuery.

Annotations
@Override
convertToFirstRowValueback to summary
public int convertToFirstRowValue(int zeroBasedFirstResult)

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.convertToFirstRowValue.

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

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

Parameters
zeroBasedFirstResult:int

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

Returns:int

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

Annotations
@Override
getAliasIndexback to summary
private int getAliasIndex(String sql)
nextElementback to summary
private int nextElement(String sql, int startOffset, int endOffset)

Return the position just before the start of the next element of a comma-separated list, that is, the position of the next comma, or the position of the end of the list.

Parameters
sql:String

A SQL fragment containing a comma-separated list

startOffset:int

the starting offset of the current element

endOffset:int

the end of the list

Returns:int

the ending offset of the current element

processSqlback to summary
public String processSql(String sql, Limit limit)

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.processSql.

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

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.

Returns:String

A new SQL statement

Annotations
@Override
selectAliasesback to summary
private String selectAliases(String sql, int afterSelectOffset, int fromOffset, StringBuilder result)

Add any missing aliases to the given select list, and return comma-separated list of all aliases in the list, unless the given list contains * or {table.*}, in which case * is returned.

Parameters
sql:String

the whole query

afterSelectOffset:int

the starting offset of the select list

fromOffset:int

the ending offset of the select list

result:StringBuilder

a string builder for inserting missing aliases

Returns:String

a comma-separated list of aliases, or *

supportsLimitback to summary
public final boolean supportsLimit()

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.supportsLimit.

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

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

Does this handler support limiting query results?

Returns:boolean

True if this handler supports limit alone.

Annotations
@Override
supportsVariableLimitback to summary
public final boolean supportsVariableLimit()

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.supportsVariableLimit.

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

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

Returns:boolean

true if bind variables can be used

Annotations
@Override
useMaxForLimitback to summary
public final boolean useMaxForLimit()

Overrides org.hibernate.dialect.pagination.AbstractLimitHandler.useMaxForLimit.

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

Returns:boolean

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

Annotations
@Override
org.hibernate.dialect.pagination back to summary

pack-priv final Enum SQLServer2005LimitHandler.Keyword

extends Enum<SQLServer2005LimitHandler.Keyword>
Class Inheritance

Field Summary

Modifier and TypeField and Description
public static final SQLServer2005LimitHandler.Keyword
AS

public static final SQLServer2005LimitHandler.Keyword
FROM

public static final SQLServer2005LimitHandler.Keyword
ORDER_BY

pack-priv Pattern
pattern

public static final SQLServer2005LimitHandler.Keyword
SELECT

public static final SQLServer2005LimitHandler.Keyword
WITH

Constructor Summary

AccessConstructor and Description
private
Keyword(String keyword)

Method Summary

Modifier and TypeMethod and Description
pack-priv int
endOffset(String sql, int startOffset)

pack-priv boolean
occursAt(String sql, int offset)

pack-priv int

Returns:

the offset at which the keyword occurs, or 0 if it never occurs outside of quotes or parentheses.
rootOffset(String
a fragment of SQL
sql)

Look for a "root" occurrence of this keyword in the given SQL fragment, that is, an offset where the keyword occurs unquoted and not parenthesized.

public static SQLServer2005LimitHandler.Keyword
valueOf(String name)

public static SQLServer2005LimitHandler.Keyword[]
values()

Inherited from java.lang.Enum:
clonecompareTodescribeConstableequalsfinalizegetDeclaringClasshashCodenameordinaltoStringvalueOf

Field Detail

ASback to summary
public static final SQLServer2005LimitHandler.Keyword AS
FROMback to summary
public static final SQLServer2005LimitHandler.Keyword FROM
ORDER_BYback to summary
public static final SQLServer2005LimitHandler.Keyword ORDER_BY
patternback to summary
pack-priv Pattern pattern
SELECTback to summary
public static final SQLServer2005LimitHandler.Keyword SELECT
WITHback to summary
public static final SQLServer2005LimitHandler.Keyword WITH

Constructor Detail

Keywordback to summary
private Keyword(String keyword)

Method Detail

endOffsetback to summary
pack-priv int endOffset(String sql, int startOffset)
occursAtback to summary
pack-priv boolean occursAt(String sql, int offset)
rootOffsetback to summary
pack-priv int rootOffset(String sql)

Look for a "root" occurrence of this keyword in the given SQL fragment, that is, an offset where the keyword occurs unquoted and not parenthesized.

Parameters
sql:String

a fragment of SQL

Returns:int

the offset at which the keyword occurs, or 0 if it never occurs outside of quotes or parentheses.

valueOfback to summary
public static SQLServer2005LimitHandler.Keyword valueOf(String name)
valuesback to summary
public static SQLServer2005LimitHandler.Keyword[] values()