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}