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}