001package io.ebean.config.dbplatform.sqlserver;
002
003import io.ebean.config.dbplatform.SqlLimitRequest;
004import io.ebean.config.dbplatform.SqlLimitResponse;
005import io.ebean.config.dbplatform.SqlLimiter;
006
007/**
008 * Use top and row_number() function to limit sql results.
009 */
010public class SqlServer2005SqlLimiter implements SqlLimiter {
011
012  final String rowNumberWindowAlias;
013
014  /**
015   * Specify the name of the rowNumberWindowAlias.
016   */
017  public SqlServer2005SqlLimiter(String rowNumberWindowAlias) {
018    this.rowNumberWindowAlias = rowNumberWindowAlias;
019  }
020
021  public SqlServer2005SqlLimiter() {
022    this("as limitresult");
023  }
024
025  @Override
026  public SqlLimitResponse limit(SqlLimitRequest request) {
027
028    StringBuilder sb = new StringBuilder(500);
029
030    int firstRow = request.getFirstRow();
031
032    int lastRow = request.getMaxRows();
033    if (lastRow > 0) {
034      lastRow += firstRow;
035    }
036
037    if (firstRow < 1) {
038      // just use top n
039      sb.append("select ");
040      if (request.isDistinct()) {
041        sb.append("distinct ");
042      }
043      sb.append("top ").append(lastRow).append(" ");
044      sb.append(request.getDbSql());
045      return new SqlLimitResponse(sb.toString(), false);
046    }
047
048    /*
049     * SELECT * FROM (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY ...) AS rn, ...)
050     * AS limitresult WHERE rn >= 11 AND rn <= 20
051     */
052
053    sb.append("select * ").append(NEW_LINE).append("from ( ");
054
055    sb.append("select ");
056    if (request.isDistinct()) {
057      sb.append("distinct ");
058    }
059    sb.append("top ").append(lastRow);
060    sb.append(" row_number() over (order by ");
061    sb.append(request.getDbOrderBy());
062    sb.append(") as rn, ");
063    sb.append(request.getDbSql());
064
065    sb.append(NEW_LINE).append(") ");
066    sb.append(rowNumberWindowAlias);
067    sb.append(" where ");
068    if (firstRow > 0) {
069      sb.append(" rn > ").append(firstRow);
070      if (lastRow > 0) {
071        sb.append(" and ");
072      }
073    }
074    if (lastRow > 0) {
075      sb.append(" rn <= ").append(lastRow);
076    }
077
078    String sql = request.getDbPlatform().completeSql(sb.toString(), request.getOrmQuery());
079
080    return new SqlLimitResponse(sql, true);
081  }
082
083}