各种数据库分页

sqlserver分页

辅助类

package com.utils;

import java.util.Collections;
import java.util.List;

/**
 * 分页辅助类
 * @author ZhaoYihao
 *
 */
public class Page {

	/**
	 * 当前页码
	 */
	private int pageNum;
	/**
	 * 开始条数
	 */
	private int startNum;
	/**
	 * 结束条数
	 */
	private int endNum;
	/**
	 * 每页条数
	 */
	private int recordPerPage = 10;
	
	/**
	 * 总页数
	 */
	private int pageCount;
	
	 /**
     * 查询数据列表
     */
    private List records = Collections.emptyList();
	
	
	public Page() {}
	
	public Page(int pageNum) {
		init(pageNum);
	}
	
	private void init(int pageNum2) {
		this.startNum = ((pageNum2-1)*recordPerPage)+1;
		this.endNum = startNum + recordPerPage;
	}
	
	public int getPageNum() {
		return pageNum;
	}
	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}
	public int getStartNum() {
		return startNum;
	}
	public void setStartNum(int startNum) {
		this.startNum = startNum;
	}
	public int getEndNum() {
		return endNum;
	}
	public void setEndNum(int endNum) {
		this.endNum = endNum;
	}
	public int getRecordPerPage() {
		return recordPerPage;
	}
	public void setRecordPerPage(int recordPerPage) {
		this.recordPerPage = recordPerPage;
	}
	public int getPageCount() {
		return pageCount;
	}
	public void setPageCount(int pageCount) {
		this.pageCount = pageCount;
	}

	public List getRecords() {
		return records;
	}

	public void setRecords(List records) {
		this.records = records;
	}
	
}

需要从前台传过来当前所在页面。后台初始化Page对象,将当前页面set进实体类,自动初始化其他信息。

sqlserver

SELECT * FROM ( SELECT row_number() over(order by create_time) rownum,A.*  FROM (
	select * from bdip_chat_point where tree_id = '624' and user_name like '%杨%'
	)A
)B where rownum between 1 AND 2;

oracle

方案一:

select u_name,u_sal
from (select users.*,rownum rn from users where rownum <= end)
where rn > start

方案二:

select u_name,u_sal
from (select users.*,rownum rn from users)
where rn between start and end

方案一效率更高!!!

mysql

使用limit 完成分页