001package io.ebean.config.dbplatform; 002 003/** 004 * Add ROWNUM column etc around SQL query to limit results. 005 */ 006public class RownumSqlLimiter implements SqlLimiter { 007 008 private final String rnum; 009 010 private final boolean useFirstRowsHint; 011 012 /** 013 * Create with default inner rownum column alias and used FIRST_ROWS hint. 014 */ 015 public RownumSqlLimiter() { 016 this("rn_", true); 017 } 018 019 /** 020 * Specify the inner rownum column alias and whether to include the FIRST_ROWS 021 * hint. 022 */ 023 public RownumSqlLimiter(String rnum, boolean useFirstRowsHint) { 024 this.rnum = rnum; 025 this.useFirstRowsHint = useFirstRowsHint; 026 } 027 028 @Override 029 public SqlLimitResponse limit(SqlLimitRequest request) { 030 031 // select * 032 // from ( select /*+ FIRST_ROWS(n) */ ROWNUM rnum, a.* 033 // from ( your_query_goes_here, 034 // with order by ) a 035 // where ROWNUM <= 036 // :MAX_ROW_TO_FETCH ) 037 // where rnum >= :MIN_ROW_TO_FETCH; 038 039 String dbSql = request.getDbSql(); 040 041 StringBuilder sb = new StringBuilder(60 + dbSql.length()); 042 043 int firstRow = request.getFirstRow(); 044 045 int lastRow = request.getMaxRows(); 046 if (lastRow > 0) { 047 lastRow += firstRow; 048 } 049 050 sb.append("select * from ( "); 051 052 sb.append("select "); 053 if (useFirstRowsHint && request.getMaxRows() > 0) { 054 sb.append("/*+ FIRST_ROWS(").append(request.getMaxRows()).append(") */ "); 055 } 056 057 sb.append("rownum ").append(rnum).append(", a.* "); 058 sb.append(" from ("); 059 060 sb.append(" select "); 061 if (request.isDistinct()) { 062 sb.append("distinct "); 063 } 064 sb.append(dbSql); 065 066 sb.append(NEW_LINE).append(" ) a "); 067 if (lastRow > 0) { 068 sb.append(" where rownum <= ").append(lastRow); 069 } 070 sb.append(" ) "); 071 if (firstRow > 0) { 072 sb.append(" where "); 073 sb.append(rnum).append(" > ").append(firstRow); 074 } 075 076 String sql = request.getDbPlatform().completeSql(sb.toString(), request.getOrmQuery()); 077 078 return new SqlLimitResponse(sql, true); 079 } 080 081}