数据分页

数据分页功能

    protected void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //获取入参
        String queryName = req.getParameter("queryname");
        String temp = req.getParameter("queryUserRole");
        String pageIndex = req.getParameter("pageIndex");
        //角色
        int queryUserRole = 0;
        //每页最大容量可以前端传入，也可以写成配置项，这里暂时写死
        int pageSize = 5;
        int currentPageNo = 1;
        //参数处理
        if (temp != null && temp.length() > 0) {
            queryUserRole = Integer.parseInt(temp);
        }
        if (pageIndex != null && pageIndex.length() > 0) {
            currentPageNo = Integer.parseInt(pageIndex);
        }

        UserService userService = new UserServiceImpl();
        //获取用户总数(上一页，下一页)
        int totalCount = userService.getUserCount(queryName, queryUserRole);
        //总页数支持
        PageSupport pageSupport = new PageSupport();
        pageSupport.setCurrentPageNo(currentPageNo);
        pageSupport.setPageSize(pageSize);
        pageSupport.setTotalCount(totalCount);

        int totalPageCount = pageSupport.getTotalPageCount();
        //控制首页和尾页
        if (currentPageNo < 0) {
            currentPageNo = 0;
        } else if (currentPageNo > totalPageCount) {
            currentPageNo = totalPageCount;
        }
        //查询用户列表
        List<User> userList = userService.getUserList(queryName, queryUserRole, currentPageNo, pageSize);
        //获取角色列表
        RoleService roleService = new RoleServiceImpl();
        List<Role> roleList = roleService.getRoleList();
        req.setAttribute("userList", userList);
        req.setAttribute("roleList", roleList);
        req.setAttribute("totalCount", totalCount);
        req.setAttribute("currentPageNo", currentPageNo);
        req.setAttribute("totalPageCount", totalPageCount);
        req.setAttribute("queryUserName", queryName);
        req.setAttribute("queryUserRole", queryUserRole);
        req.getRequestDispatcher("userlist.jsp").forward(req, resp);
    }

Dao层方法

public List<User> getUserList(Connection con, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        if (con != null) {
            StringBuffer sql = new StringBuffer("select u.*,r.roleName as `userRoleName` from smbms_user u,smbms_role r where u.userRole=r.id");
            List<Object> list = new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0) {
                sql.append(" and r.id = ?");
                list.add(userRole);
            }
            //mysql 分页使用limit startIndex, pageSize
            //比如现在一共13条数据，每页最大容量是5
            //0,5 01234 第一页
            //5,5 56789 第二页
            //10,3 10,11,12 第三页
            sql.append(" order by u.creationDate desc limit ?,?");
			//公式：开始的索引（该页第一个数据的起始位置）=（当前页码-1）*每页显示的条数
            currentPageNo = (currentPageNo - 1) * pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setUserRole(rs.getInt("userRole"));
                user.setUserRoleName(rs.getString("userRoleName"));
                userList.add(user);
            }
            BaseDao.closeResource(ps, rs);

        }
        return userList;
    }

PageSupport

public class PageSupport {
    //当前页码-来自于用户输入
    private int currentPageNo = 1;

    //总数量（表）
    private int totalCount = 0;

    //页面容量
    private int pageSize = 0;

    //总页数-totalCount/pageSize（+1）
    private int totalPageCount = 1;

    public int getCurrentPageNo() {
        return currentPageNo;
    }

    public void setCurrentPageNo(int currentPageNo) {
        if(currentPageNo > 0){
            this.currentPageNo = currentPageNo;
        }
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        if(totalCount > 0){
            this.totalCount = totalCount;
            //设置总页数
            this.setTotalPageCountByRs();
        }
    }
    public int getPageSize() {
        return pageSize;
    }

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

    public int getTotalPageCount() {
        return totalPageCount;
    }

    public void setTotalPageCount(int totalPageCount) {
        this.totalPageCount = totalPageCount;
    }

    public void setTotalPageCountByRs(){
        if(this.totalCount % this.pageSize == 0){
            this.totalPageCount = this.totalCount / this.pageSize;
        }else if(this.totalCount % this.pageSize > 0){
            this.totalPageCount = this.totalCount / this.pageSize + 1;
        }else{
            this.totalPageCount = 0;
        }
    }
}