CRUD功能增强(Mysql分页查询)

分页查询（Mysql）

* 好处：
    1. 减轻服务器内存的开销
    2. 提升用户体验

* 实现:
    第一种分页：
    用多少取多少，逻辑分页，每次查询数据库只取当前页的数据。问题：多次操作数据库 优点：不占内存
    第二种分页：
    物理分页,全部取出，在内存中存储，然后进行分页显示.问题：数据量太大，容易造成内存溢出   优点：只访问一次数据库
    具体使用哪种的方式,要考虑多种情况.具体情况,具体分析.


* 思路：
    1. 分页查询最好封装成一个bean，传递一个泛型。
    2. 封装pageBean对象，包含：
        private int totalCount;     //总记录数目
        private int totalPage;      //总页码
        private List<T> list;       //每页的数据集合
        private int currentPage;    //当前的页码数
        private int rows;            //每一页显示的行数）
    3. 从客户端发送到服务器的只需传递两个参数，currentPage和rows（用来放在limit后）
    4. 客户端发送带有当前页码与每页记录数的参数到findUserByPageServlet中
    5. servlet接收参数，调用service查询封装并返回pagebean对象
    6. 将pagebean对象保存在request域中，转发到list.jsp中进行显示（el+jstl）

核心代码

*findUserByPageServlet 

package com.qin.web.servlet;
import com.qin.domain.PageBean;
import com.qin.domain.User;
import com.qin.service.UserService;
import com.qin.service.impl.UserServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Map;

@WebServlet("/findUserByPageServlet")
public class FindUserByPageServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //通过传递一个当前的页码，以及每页的记录数，来得到一个pageBean对象
        //首先设置request编码
        request.setCharacterEncoding("utf-8");
        //获取请求的参数
        //当前页码
        String currentPage = request.getParameter("currentPage");
        //每一页的记录数
        String rows = request.getParameter("rows");
        //对传递参数健壮性进行判断
        //设置默认值
        if (currentPage == null ||currentPage ==""){
            currentPage = "1";
        }
        if (rows ==null || rows==""){
            rows = "5";
        }

        //获取查询条件的参数
        Map<String, String[]> map = request.getParameterMap();


        //调用Userservice的方法，返回一个pageBean对象
        UserService service = new UserServiceImpl();
        PageBean<User> pageBean =  service.findUserByPage(currentPage,rows,map);

        //将pagebean输出到jsp页面上
        request.setAttribute("pageBean",pageBean);
        //将参数传递进去
        request.setAttribute("map",map);
        //转发
        request.getRequestDispatcher("/list1.jsp").forward(request,response);



    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

* pagebean类

package com.qin.domain;

import java.util.List;

public class PageBean<T> {
    private int totalCount;     //总记录数目
    private int totalPage;      //总页码
    private List<T> list;       //每页的数据集合
    private int currentPage;    //当前的页码数
    private int rows;            //每一页显示的行数


    //生成构造方法


    public PageBean() {
    }

    public PageBean(int totalCount, int totalPage, List<T> list, int currentPage, int rows) {
        this.totalCount = totalCount;
        this.totalPage = totalPage;
        this.list = list;
        this.currentPage = currentPage;
        this.rows = rows;
    }

    //getter setter


    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public List<T> getList() {
        return list;
    }

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

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }


    //tostring

    @Override
    public String toString() {
        return "PageBean{" +
                "totalCount=" + totalCount +
                ", totalPage=" + totalPage +
                ", list=" + list +
                ", currentPage=" + currentPage +
                ", rows=" + rows +
                '}';
    }
}

* UserServiceImpl
@Override
public PageBean<User> findUserByPage(String currentPage, String rows) {
    //创建Pagebean对象
    PageBean<User> pageBean = new PageBean<>();
    //将参数转换成为int类型的
    int cp = Integer.parseInt(currentPage);//当前页码
    int rs = Integer.parseInt(rows);        //每页的记录数

    //健壮性判断
    if (cp <=0){
        cp = 1;
    }

    pageBean.setCurrentPage(cp);
    pageBean.setRows(rs);
    //调用dao的方法，查询出总记录数以及每页的list<User>集合
    //查询总记录数

    int totalCount = dao.findCount();
    pageBean.setTotalCount(totalCount);
    //查询list
    List<User> users = dao.findUserByPage(cp,rs);
    pageBean.setList(users);

    //计算出总页码
    int totalPage = totalCount % rs == 0 ? totalCount/rs : (totalCount/rs+1);
    pageBean.setTotalPage(totalPage);
    return pageBean;
}

* UserDaoImpl

/**
 * 查询总记录数
 * @return
 * @param map
 */
@Override
public int findCount() {
    String sql = "select count(*) from user";
    // new beanpropertyrowmapper是为了封装自定义类，如果是系统封装好的就可以直接使用
    //int integer = template.queryForObject(sql, new BeanPropertyRowMapper<>(Integer.class));
    //多态来接收

    int integer = template.queryForObject(sql, Integer.class);
    return integer;
}

/**
 * 批量查询数据，并封装成list
 * @param cp
 * @param rs
 * @param map
 * @return
 */
@Override
public List<User> findUserByPage(int cp, int rs) {
    String sql = "select * from user limit ?,? ";
    int index = (cp - 1) * rs;
    List<User> users = template.query(sql, new BeanPropertyRowMapper<>(User.class),index,rs);
    return users;
}

* list.jsp中的核心分页部分代码

<div>
    <nav>
        <ul class="pagination">
            <c:if test="${pageBean.currentPage == 1}">
                <li class="disabled">
                    <a href="#" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>
            </c:if>

            <c:if test="${pageBean.currentPage != 1}">
                <li >
                    <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pageBean.currentPage-1}&rows=${pageBean.rows}" aria-label="Previous">
                        <span aria-hidden="true">&laquo;</span>
                    </a>
                </li>
            </c:if>

            <c:forEach begin="1" end="${pageBean.totalPage}" var="i">
                <c:if test="${pageBean.currentPage == i}">
                    <li class="active"><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=${pageBean.rows}">${i}<span class="sr-only">(current)</span></a></li>
                </c:if>

                <c:if test="${pageBean.currentPage != i}">
                    <li><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=${pageBean.rows}">${i}</a></li>
                </c:if>
            </c:forEach>



            <c:if test="${pageBean.currentPage == pageBean.totalPage}">
                <li class="disabled">
                    <a href="#" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li>
            </c:if>

            <c:if test="${pageBean.currentPage != pageBean.totalPage}">
                <li >
                    <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pageBean.currentPage+1}&rows=${pageBean.rows}" aria-label="Next">
                        <span aria-hidden="true">&raquo;</span>
                    </a>
                </li>
            </c:if>
            <span style="font-size: 20px;margin-left: 5px">共${pageBean.totalCount}条记录，共${pageBean.totalPage}页</span>
        </ul>
    </nav>