分页查询生成工具

能够将普通SQL转化为分页SQL的工具类,支持ORACLE

public class SQLTools {
	
	/**
	 * 分页SQL 查询生成器
	 * 原查询语句必须有条件
	 * @param query   原查询语句
	 * @param pageSize 每页大小
	 * @param curPage  第N页
	 * @return
	 */
	public static String separateSQL(String query, int pageSize, int curPage){
 		String _query = query;
 		_query = _query.replaceFirst("select", "select rownum as t_rownum ,");
 		StringBuilder newQuery = new StringBuilder();
		newQuery.append("select * from (");
		newQuery.append(_query);
		newQuery.append(" and rownum <=  (");
		newQuery.append( getEndIndexSQL(_query, pageSize, curPage));
		newQuery.append(" ) )");
		newQuery.append("where t_rownum >= (").append( getBeginIndexSQL(_query, pageSize, curPage)).append(")");
		return newQuery.toString();
	}
	
	
	
	private static String getBeginIndexSQL(String o_query, int pageSize, int toPage){
		StringBuilder newQuery = new StringBuilder();
		String temp_str = "";
		newQuery.append(" ( select count(*) items from (");
		newQuery.append(o_query).append(" )  its ");
		
		temp_str = " ( select (case when MOD(items,{pageSize})=0 then trunc(items/{pageSize}) else trunc(items/{pageSize}+1) end) pages from ";
		newQuery.insert(0, temp_str);
		newQuery.append(" ) pages");
		
		temp_str = " ( select (case when {toPage} > pages then pages when {toPage}<1 then 1 else {toPage} end) to_page from ";
		newQuery.insert(0, temp_str);
		newQuery.append(" ) topage");
		
		temp_str = "select ((to_page - 1) * {pageSize} + 1) as beginindex from ";
		newQuery.insert(0, temp_str);
		newQuery.append(" ) index1 ");
		
		temp_str = newQuery.toString();
		temp_str = temp_str.replaceAll("\\{pageSize\\}", String.valueOf(pageSize));
		temp_str = temp_str.replaceAll("\\{toPage\\}", String.valueOf(toPage));
		return temp_str;
	}
	
	
	private static String getEndIndexSQL(String o_query, int pageSize, int toPage){
		StringBuilder newQuery = new StringBuilder();
		String temp_str = "";
		newQuery.append(" ( select count(*) items from (");
		newQuery.append(o_query).append(" )  its ");
		
		temp_str = "( select (case when MOD(items,{pageSize})=0 then trunc(items/{pageSize}) else trunc(items/{pageSize}+1) end) pages,items from ";
		newQuery.insert(0, temp_str);
		newQuery.append(" )  ");
		
		temp_str = " ( select (case when {toPage} > pages then pages when {toPage}<1 then 1 else {toPage} end) to_page, pages,items from ";
		newQuery.insert(0, temp_str);
		newQuery.append(" ) pages ");
		
		temp_str = "select  case when to_page != pages then to_page*{pageSize} else items end as endindex from ";
		newQuery.insert(0, temp_str);
		newQuery.append(" ) index2 ");
		temp_str = newQuery.toString();
		temp_str = temp_str.replaceAll("\\{pageSize\\}", String.valueOf(pageSize));
		temp_str = temp_str.replaceAll("\\{toPage\\}", String.valueOf(toPage));
		return temp_str;
	}

}