分页查询

古城微笑少年丶 2022-09-25 13:18 298阅读 0赞

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;
    	}
    }