分页技术

UsersDAO

oracle实现

public class UsersDAO {private JDBCTemplate template;public UsersDAO() {template = new JDBCTemplate() {@Overrideprotected RowMapper getRowMapper() {// TODO Auto-generated method stubreturn 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() {@Overrideprotected RowMapper getRowMapper() {// TODO Auto-generated method stubreturn 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());  } } }