分页技术

UsersDAO

oracle实现

public class UsersDAO {
	
	private JDBCTemplate template;
	
	public UsersDAO() {
		template = new JDBCTemplate() {
			
			@Override
			protected RowMapper getRowMapper() {
				// TODO Auto-generated method stub
				return new UsersMapper();
			}
		};
	}
	//分页查询
	public PageModel<UserBean> getScrollData(int currentPage, int pageSize ){
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select * ")
		.append("from (")
			.append("select i.*, rownum rn from (")
			.append("select u.id, u.username, u.password, u.age ")
			.append("from users u")
			.append(" order by u.id")
			.append(") i where rownum<=? ")
			.append(") ")
			.append("where rn >? ");
		System.out.println("sql=" + sbSql.toString());
		Object[] params = new Object[2];
		PageModel<UserBean> pageModel= null;
		params[0]=currentPage * pageSize;
		params[1]=(currentPage - 1) * pageSize;
		List userList =template.query(sbSql.toString(), params);
		pageModel = new PageModel<UserBean>();
		pageModel.setList(userList);
		pageModel.setTotalRecords(getTotalRecords());
		pageModel.setPageNo(currentPage);
		pageModel.setPageSize(pageSize);
	    return pageModel;
	}
	//获得用户总记录数
	public int getTotalRecords() 
	{
		String sql = "select count(*) from users";
		Object[] params = new Object[0];
		int result = template.selectCount(sql, params);		
		return result;		
	}

}

select * from  (select p.*,rownum rn from T_CHK_LOOK_PLAN p where  rownum <= sizePage*nowPage)
           where rn >=(sizePage*(nowPage-1))+1;

mysql实现

public class UsersDAO {
	
	private JDBCTemplate template;
	
	public UsersDAO() {
		template = new JDBCTemplate() {
			
			@Override
			protected RowMapper getRowMapper() {
				// TODO Auto-generated method stub
				return new UsersMapper();
			}
		};
	}
	//分页查询
	public PageModel<UserBean> getScrollData(int currentPage, int pageSize ){
		String sql = "select * from users limit ?,?";
		Object[] params = new Object[2];
		PageModel<UserBean> pageModel = null;
		params[0]=(currentPage - 1) * pageSize;
		params[1]=pageSize;
		List userList =template.query(sql, params);
		pageModel = new PageModel<UserBean>();
		pageModel.setList(userList);
		pageModel.setTotalRecords(getTotalRecords());
		pageModel.setPageNo(currentPage);
		pageModel.setPageSize(pageSize);
	    return pageModel;
	}
	//获得用户总记录数
	public int getTotalRecords() {
		String sql = "select count(*) from users";
		Object[] params = new Object[0];
		int result = template.selectCount(sql, params);		
		return result;		
	}

}

/**
 * 封装分页逻辑
 * @author Administrator
 *
 */

public class PageModel<T> {
	
	//结果集
	private List<T> list;
	
	//记录数
	private int totalRecords;
	
	//每页多少条数据
	private int pageSize;
	
	//第几页
	private int pageNo;

	/**
	 * 返回总页数
	 * @return
	 */
	public int getTotalPages() {
		return (totalRecords + pageSize - 1) / pageSize;
	}
	
	/**
	 * 首页
	 * @return
	 */
	public int getTopPageNo() {
		return 1;
	}
	
	/**
	 * 上一页 
	 * @return
	 */
	public int getPreviousPageNo() {
		if (this.pageNo <= 1) {
			return 1;
		}
		return this.pageNo - 1;
	}
	
	/**
	 * 下一页
	 * @return
	 */
	public int getNextPageNo() {
		if (this.pageNo >= getButtomPageNo()) {
			return getButtomPageNo();
		}
		return this.pageNo + 1;
	}
	
	/**
	 * 尾页
	 * @return
	 */
	public int getButtomPageNo() {
		return getTotalPages();
	}
	
	public List<T> getList() {
		return list;
	}

	public void setList(List<T> list) {
		this.list = list;
	}

	public int getTotalRecords() {
		return totalRecords;
	}

	public void setTotalRecords(int totalRecords) {
		this.totalRecords = totalRecords;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getPageNo() {
		return pageNo;
	}

	public void setPageNo(int pageNo) {
		this.pageNo = pageNo;
	}
}

/**
 * 用户管理类
 * @author qiutao
 */

public class UserManager {
 /** 用户管理 DAO */
 private UsersDAO userDAO = new UsersDAO(); 
    /**
     * 得到用户总数
     * @return 用户记录总数
     */
 public int getUserTotalCount(){
  return userDAO.getTotalRecords();
 }
 /**
  * 获取总页面数.
  * 
  * @param pageSize
  *            一页显示数据量
  * @return 页面总数
  */
 public int getTotalPage(int pageSize) {
  int totalCount = userDAO.getTotalRecords();
  // 得到页面总数
  int totalPageCount = ((totalCount + pageSize) - 1) / pageSize;
  return totalPageCount;
 } 
 /**
  * 分页显示用户数据.
  * @param currentPage 当前页码, 从 1 开始
  * @param pageSize 每页显示数据量
  * @return 用户数据
  */
 public PageModel<UserBean> findPagedAll(int currentPage, int pageSize) {
  return userDAO.getScrollData(currentPage, pageSize);
 }
 public static void main(String[] args) {
  PageModel<UserBean> pageModel=new UserManager().findPagedAll(1, 3);
  for (UserBean user : pageModel.getList()) {
   System.out.println(user.getUsername());
  }
 } 
}