/* * Hibernate, Relational Persistence for Idiomatic Java * * License: GNU Lesser General Public License (LGPL), version 2.1 or later. * See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>. */ package com.github.drinkjava2.jdialects.hibernatesrc.pagination; import java.sql.PreparedStatement; import java.sql.SQLException; /** * LIMIT clause handler compatible with SQL Server 2012 and later. * * @author Chris Cranford */ public class SQLServer2012LimitHandler extends SQLServer2005LimitHandler { // determines whether the limit handler used offset/fetch or 2005 behavior. private boolean usedOffsetFetch; public SQLServer2012LimitHandler() { // empty } @Override public boolean supportsLimit() { return true; } @Override public boolean supportsVariableLimit() { return true; } @Override public String processSql(String sql, RowSelection selection) { // SQLServer mandates the following rules to use OFFSET/LIMIT // * An 'ORDER BY' is required // * The 'OFFSET ...' clause is mandatory, cannot use 'FETCH ...' by itself. // * The 'TOP' clause isn't permitted with LIMIT/OFFSET. if (hasOrderBy(sql)) { if (!LimitHelper.useLimit(this, selection)) { return sql; } return applyOffsetFetch(selection, sql, getInsertPosition(sql)); } return super.processSql(sql, selection); } @Override public boolean useMaxForLimit() { // when using the offset fetch clause, the max value is passed as-is. // SQLServer2005LimitHandler uses start + max values. return usedOffsetFetch ? false : super.useMaxForLimit(); } @Override public int convertToFirstRowValue(int zeroBasedFirstResult) { // When using the offset/fetch clause, the first row is passed as-is // SQLServer2005LimitHandler uses zeroBasedFirstResult + 1 if (usedOffsetFetch) { return zeroBasedFirstResult; } return super.convertToFirstRowValue(zeroBasedFirstResult); } @Override public int bindLimitParametersAtEndOfQuery(RowSelection selection, PreparedStatement statement, int index) throws SQLException { if (usedOffsetFetch && !LimitHelper.hasFirstRow(selection)) { // apply just the max value when offset fetch applied statement.setInt(index, getMaxOrLimit(selection)); return 1; } return super.bindLimitParametersAtEndOfQuery(selection, statement, index); } private String getOffsetFetch(RowSelection selection) { if (!LimitHelper.hasFirstRow(selection)) { return " offset 0 rows fetch next ? rows only"; } return " offset ? rows fetch next ? rows only"; } private int getInsertPosition(String sql) { int position = sql.length() - 1; for (; position > 0; --position) { char ch = sql.charAt(position); if (ch != ';' && ch != ' ' && ch != ' ' && ch != ' ') { break; } } return position + 1; } private String applyOffsetFetch(RowSelection selection, String sql, int position) { usedOffsetFetch = true; StringBuilder sb = new StringBuilder(); sb.append(sql.substring(0, position)); sb.append(getOffsetFetch(selection)); if (position > sql.length()) { sb.append(sql.substring(position - 1)); } return sb.toString(); } private boolean hasOrderBy(String sql) { int depth = 0; for (int i = 0; i < sql.length(); ++i) { char ch = sql.charAt(i); if (ch == '(') { depth++; } else if (ch == ')') { depth--; } if (depth == 0 && sql.substring(i).toLowerCase().startsWith("order by ")) { return true; } } return false; } }