com.avaje.ebean.config.dbplatform.RownumSqlLimiter Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of ebean Show documentation

Ebean

There is a newer version: 2.8.1

package com.avaje.ebean.config.dbplatform;

/**
 * Add ROWNUM column etc around SQL query to limit results.
 */
public class RownumSqlLimiter implements SqlLimiter {

	private final String rnum;
	
	private final boolean useFirstRowsHint;
	
	/**
	 * Create with default inner rownum column alias and used FIRST_ROWS hint.
	 */
	public RownumSqlLimiter() {
		this("rn_", true);
	}
	
	/**
	 * Specify the inner rownum column alias and whether to include 
	 * the FIRST_ROWS hint.
	 */
	public RownumSqlLimiter(String rnum, boolean useFirstRowsHint) {
		this.rnum = rnum;
		this.useFirstRowsHint = useFirstRowsHint;
	}
	
	public SqlLimitResponse limit(SqlLimitRequest request) {
		
//		select * 
//		  from ( select /*+ FIRST_ROWS(n) */ ROWNUM rnum, a.*
//		      from ( your_query_goes_here, 
//		      with order by ) a 
//		      where ROWNUM <= 
//		      :MAX_ROW_TO_FETCH ) 
//		where rnum  >= :MIN_ROW_TO_FETCH;
		
		StringBuilder sb = new StringBuilder(500);
		
		int firstRow = request.getFirstRow();

		int lastRow = request.getMaxRows();
		if (lastRow > 0) {
			lastRow = lastRow + firstRow + 1;
		}

		sb.append("select * ").append(NEW_LINE).append("from ( ");

		sb.append("select ");
		if (useFirstRowsHint && request.getMaxRows() > 0){
			sb.append("/*+ FIRST_ROWS(").append(request.getMaxRows()+1).append(") */ ");
		}

		sb.append("rownum ").append(rnum).append(", a.* ").append(NEW_LINE);
		sb.append("       from (");//

		sb.append(" select ");
		if (request.isDistinct()) {
			sb.append("distinct ");
		}
		sb.append(request.getDbSql());
		
		sb.append(NEW_LINE).append("            ) a ");
		if (lastRow > 0) {
			sb.append(NEW_LINE).append("       where rownum <= ").append(lastRow);
		}
		sb.append(NEW_LINE).append("     ) ");
		if (firstRow > 0) {
			sb.append(NEW_LINE).append("where ");
			sb.append(rnum).append(" > ").append(firstRow);
		}
	
		
		return new SqlLimitResponse(sb.toString(), true);
	}

}