OracleDialect extends Dialect { public String getLimitString(String sql, int offset,String offsetPlaceholder, int limit, String limitPlaceholder) { sql = sql.trim(); boolean isForUpdate = false; if ( sql.toLowerCase().endsWith(" for update") ) { sql = sql.substring( 0, sql.length()-11 ); isForUpdate = true; } StringBuffer pagingSelect = new StringBuffer( sql.length()+100 ); if (offset > 0) { pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( "); } else { pagingSelect.append("select * from ( "); } pagingSelect.append(sql); if (offset > 0) { String endString = offsetPlaceholder+"+"+limitPlaceholder; pagingSelect.append(" ) row_ ) where rownum_ <= " + endString + " and rownum_ > " + offsetPlaceholder); } else { pagingSelect.append(" ) where rownum <= " + limitPlaceholder); } if ( isForUpdate ) { pagingSelect.append( " for update" ); } return pagingSelect.toString(); }  boolean supportsLimit(); boolean supportsLimitOffset(); String getLimitString(String sql, int offset,String offsetPlaceholder, int limit, String limitPlaceholder);  }
@Test public void test() { String sql = dialect.getLimitString("select * from user", 10, 100); assertEquals("select * from ( select row_.*, rownum rownum_ from ( select * from user ) row_ ) where rownum_ <= 10+100 and rownum_ > 10",sql); System.out.println(sql); String sql0limit = dialect.getLimitString("select * from user", 0, 100); assertEquals("select * from ( select * from user ) where rownum <= 100",sql0limit); System.out.println(sql0limit); String forUpdateSql = dialect.getLimitString("select * from user for update", 10, 100); assertEquals("select * from ( select row_.*, rownum rownum_ from ( select * from user ) row_ ) where rownum_ <= 10+100 and rownum_ > 10 for update",forUpdateSql); System.out.println(forUpdateSql); }
@Test public void getLimitStringWithPlaceHolader() { String OFFSET = ":offset"; String LIMIT = ":limit"; Assert.assertEquals("select * from ( select * from user ) where rownum <= :limit", dialect.getLimitString("select * from user", 0,OFFSET, 0,LIMIT)); Assert.assertEquals("select * from ( select * from user ) where rownum <= :limit", dialect.getLimitString("select * from user", 0,OFFSET,12,LIMIT)); Assert.assertEquals("select * from ( select row_.*, rownum rownum_ from ( select * from user ) row_ ) where rownum_ <= :offset+:limit and rownum_ > :offset", dialect.getLimitString("select * from user", 12, OFFSET,0,LIMIT)); Assert.assertEquals("select * from ( select row_.*, rownum rownum_ from ( select * from user ) row_ ) where rownum_ <= :offset+:limit and rownum_ > :offset", dialect.getLimitString("select * from user", 12, OFFSET, 34,LIMIT)); }
@Test public void getLimitString() { String sql = new OracleDialect().getLimitString("select * from user", 10, 100); assertEquals("select * from ( select row_.*, rownum rownum_ from ( select * from user ) row_ ) where rownum_ <= 10+100 and rownum_ > 10",sql); }