分页查询

1.mysql分页查询sql

SELECT * FROM TABLE_NAME LIMIT 20 OFFSET 20;

2.oracle分页查询sql

第一种

SELECT * FROM ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21

第二种

SELECT * FROM ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40

第一种分页查询语句大多数拥有较高的效率,这是由于CBO优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率.也就是说第二层的查询条件ROWNUM <= 40 可以被Oracle推入到内层查询中,这样一旦超过ROWNUM的查询条件,就终止查询,将结果返回了.

第二种分页查询,由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

3.使用java代码封装oracle分页查询

	/**
	 * 获得分页SQL语句
	 */
	public static String getPagingSql(String rawSql, PagingParameter paging) {
		if(paging == null || paging.isInvalid()) {
			log.debug(rawSql);
			return rawSql;
		}
		Map<String,Integer> map = paging.getStartAndEndRow();
		int start = map.get("start");
		int end = map.get("end");
		String pagingSql = "SELECT T.*, ROWNUM AS ROW_NUM FROM (" + rawSql + ") T WHERE ROWNUM <= " + end;
		if(start == 0) {
			log.debug(pagingSql);
			return pagingSql;
		}
		pagingSql = "SELECT * FROM (" + pagingSql + ") T_O WHERE ROW_NUM > " + start;
		log.debug(pagingSql);
		return pagingSql;
	}

PagingParameter.java这个类是分页参数封装.

package com.sunshine.monitor.comm.dao.page;

import java.util.HashMap;
import java.util.Map;

import com.sunshine.monitor.comm.bean.Entity;

/**
 * 封装分页参数
 */
public class PagingParameter extends Entity {
	
	private static final long serialVersionUID = -5871263750693828476L;

	/** 分页起始行，默认为-1，表示不分页，查询全部记录 */
	private int curPage = -1;
	/** 每页显示行数，默认为0，表示不分页，查询全部记录 */
	private int pageSize = 0;
	/** 总记录*/
	private int totalRows = 0;
	
	/**
	 * 构造方法，不指定分页起始行和每页显示行数，默认不分页，查询全部记录
	 */
	public PagingParameter(){
	}
	
	/**
	 * 构造方法
	 * @param start
	 * @param pageSize
	 */
	public PagingParameter(int curPage,int pageSize){
		this.curPage = curPage;
		this.pageSize = pageSize;
	}
	
	/**
	 * 构造方法
	 * @param start
	 * @param pageSize
	 */
	public PagingParameter(int curPage,int pageSize, int totalRows){
		this.curPage = curPage;
		this.pageSize = pageSize;
		this.totalRows = totalRows;
	}
	
	public int getTotalRows() {
		return totalRows;
	}

	public int getCurPage() {
		return curPage;
	}
	
	public void setCurPage(int curPage) {
		this.curPage = curPage;
	}
	
	public int getPageSize() {
		return pageSize;
	}
	
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public void setTotalRows(int totalRows) {
		this.totalRows = totalRows;
	}

	/**
	 * 判断分页参数是否无效，如果返回true(表示分页参数无效)则不分页，查询全部的记录
	 *
	 * @return
	 */
	public boolean isInvalid() {
		return curPage < 0 || pageSize <= 0;
	}

	/**
	 * 构造开始行与结束行
	 * @return
	 */
	public Map<String,Integer> getStartAndEndRow(){
		// 总页数
		int totalPages = totalPage();
		Map<String,Integer> map = null;
		// 起始行数
		int start = (curPage - 1) * pageSize;
		// 结束行数
		int end = 0;
		if(totalRows==0){
			end = curPage * pageSize;
		}
		if(totalRows!=0){
			if (totalRows < pageSize) {
				end = totalRows;
			} else if ((totalRows % pageSize == 0)
					|| (totalRows % pageSize != 0 && curPage < totalPages)) {
				end = curPage * pageSize;
			} else if (totalRows % pageSize != 0 && curPage == totalPages) {// 最后一页
				end = totalRows;
			}
		}
		map = new HashMap<String,Integer>();
		map.put("start", start);
		map.put("end", end);
		return map ;
	}
	
	/**
	 * 总页数
	 * @return
	 */
	public int totalPage(){
		int totalPages = 0;
		if (pageSize != -1) {
			if (totalRows % pageSize == 0) {
				totalPages = totalRows / pageSize;
			} else {
				totalPages = (totalRows / pageSize) + 1;
			}
		} else {
			totalPages = 1;
		}
		return totalPages;
	}
}