DB2Dialect extends Dialect { public String getLimitString(String sql, int offset,String offsetPlaceholder, int limit, String limitPlaceholder) { int startOfSelect = sql.toLowerCase().indexOf("select"); StringBuffer pagingSelect = new StringBuffer( sql.length()+100 ) .append( sql.substring(0, startOfSelect) ) .append("select * from ( select ") .append( getRowNumber(sql) ); if ( hasDistinct(sql) ) { pagingSelect.append(" row_.* from ( ") .append( sql.substring(startOfSelect) ) .append(" ) as row_"); } else { pagingSelect.append( sql.substring( startOfSelect + 6 ) ); } pagingSelect.append(" ) as temp_ where rownumber_ "); if (offset > 0) { String endString = offsetPlaceholder+"+"+limitPlaceholder; pagingSelect.append("between "+offsetPlaceholder+"+1 and "+endString); } else { pagingSelect.append("<= "+limitPlaceholder); } return pagingSelect.toString(); }  boolean supportsLimit(); boolean supportsLimitOffset(); String getLimitString(String sql, int offset,String offsetPlaceholder, int limit, String limitPlaceholder);  }
@Test public void getLimitString() { Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ <= 0", dialect.getLimitString("select * from user", 0, 0)); Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ <= 12", dialect.getLimitString("select * from user", 0, 12)); Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ between 12+1 and 12+0", dialect.getLimitString("select * from user", 12, 0)); Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ between 12+1 and 12+34", dialect.getLimitString("select * from user", 12, 34)); }
@Test public void getLimitStringWithPlaceHolader() { String OFFSET = ":offset"; String LIMIT = ":limit"; Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ <= :limit", dialect.getLimitString("select * from user", 0,OFFSET, 0,LIMIT)); Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ <= :limit", dialect.getLimitString("select * from user", 0,OFFSET,12,LIMIT)); Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ between :offset+1 and :offset+:limit", dialect.getLimitString("select * from user", 12, OFFSET,0,LIMIT)); Assert.assertEquals("select * from ( select rownumber() over() as rownumber_, * from user ) as temp_ where rownumber_ between :offset+1 and :offset+:limit", dialect.getLimitString("select * from user", 12,OFFSET, 34,LIMIT)); }