SQLServer2005Dialect extends Dialect { @Override public String getLimitString(String querySqlString, int offset,String offsetPlaceholder, int limit, String limitPlaceholder) { StringBuffer pagingBuilder = new StringBuffer(); String orderby = getOrderByPart(querySqlString); String distinctStr = ""; String loweredString = querySqlString.toLowerCase(); String sqlPartString = querySqlString; if (loweredString.trim().startsWith("select")) { int index = 6; if (loweredString.startsWith("select distinct")) { distinctStr = "DISTINCT "; index = 15; } sqlPartString = sqlPartString.substring(index); } pagingBuilder.append(sqlPartString); if (orderby == null || orderby.length() == 0) { orderby = "ORDER BY CURRENT_TIMESTAMP"; } StringBuffer result = new StringBuffer(); result.append("WITH query AS (SELECT ") .append(distinctStr) .append("TOP 100 PERCENT ") .append(" ROW_NUMBER() OVER (") .append(orderby) .append(") as __row_number__, ") .append(pagingBuilder) .append(") SELECT * FROM query WHERE __row_number__ BETWEEN ") .append(offset).append(" AND ").append(offset+limit) .append(" ORDER BY __row_number__"); return result.toString(); }  @Override boolean supportsLimit(); @Override boolean supportsLimitOffset(); @Override String getLimitString(String querySqlString, int offset,String offsetPlaceholder, int limit, String limitPlaceholder);  }
@Test public void testDialect() { assertEquals("WITH query AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, * from user) SELECT * FROM query WHERE __row_number__ BETWEEN 0 AND 0 ORDER BY __row_number__", dialect.getLimitString("select * from user", 0, 0)); assertEquals("WITH query AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, * from user) SELECT * FROM query WHERE __row_number__ BETWEEN 0 AND 12 ORDER BY __row_number__", dialect.getLimitString("select * from user", 0, 12)); assertEquals("WITH query AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, * from user) SELECT * FROM query WHERE __row_number__ BETWEEN 12 AND 12 ORDER BY __row_number__", dialect.getLimitString("select * from user", 12, 0)); assertEquals("WITH query AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, * from user) SELECT * FROM query WHERE __row_number__ BETWEEN 12 AND 46 ORDER BY __row_number__", dialect.getLimitString("select * from user", 12, 34)); }