JavaWeb---2、分页查询

优点：数据清晰直观

​            不受数据量限制

​            页面不再冗长

一、步骤

1、计算显示数据的总数量

2、确定每页显示的数据量

3、计算总页数

判断：总数量%每页显示的数量 是否等于0

        ①整除（等于0）：总页数=总记录数/每页显示记录数）

        ②不能整除（不等于0）：总页数=总记录数/每页显示记录数+1）

4、计算偏移量

        偏移量 =（总页数-1）*每页显示的数量

4、编写分页查询SQL语句

MySQL的分页查询要用到 limit关键字，方式就是 limit m,n m表示第几条数据 n表示从m+1开始取多少条数据，比如

　　　select * from table limit m,n 其中m是指记录开始的index，从0开始，表示第一条记录

　　　具体：select * from tableName where 条件 limit 当前页码*页面容量-1,页面容量

二、编写代码

Dao实现层

public class ProviderDaoImpl extends BaseDao implements ProviderDao {
    private ResultSet rs = null;
    /**
     * 展示供应商信息
     * @param pyl 偏移量
     * @param maxCount 每页显示最大数量
     * @param beginDate 起始时间
     * @param endDate 结束时间
     * @param seachDesc 产品描述全文检索
     * @return
     * @throws Exception
     */
    @Override
    public List<Provider> showProviderInfo(Provider provider,Integer pyl,Integer maxCount,String beginDate ,String endDate,String seachDesc) throws Exception {
        List<Provider> providerList = new ArrayList<>();
        if (getConnection()){
            String sql = "SELECT id, proCode,proName,proDesc,creationDate  FROM smbms_provider smp where 1=1";
            if (provider.getProName()!=null){
                sql += " and proName LIKE '%"+provider.getProName()+"%'";
            }
            if (beginDate != null){
                sql += " and DATE_FORMAT(creationDate,'%Y%m%d')>DATE_FORMAT('"+beginDate+"','%Y%m%d')";
            }
            if (endDate != null){
                sql += " and DATE_FORMAT(creationDate,'%Y%m%d')<DATE_FORMAT('"+endDate+"','%Y%m%d')";
            }
            if (seachDesc !=null){
                sql += " and MATCH(proDesc) AGAINST ('*"+seachDesc+"*' IN boolean MODE)";
            }
            sql+=" order by creationDate DESC limit ?,? ";
            System.out.println(sql);
            rs = exSelect(sql, new Object[]{pyl,maxCount});
            Provider provider1 = null;
            while (rs.next()){
                provider1 =new Provider();
                provider1.setId(rs.getInt("id"));
                provider1.setProCode(rs.getString("proCode"));
                provider1.setProDesc(rs.getString("proDesc"));
                provider1.setProName(rs.getString("proName"));
                provider1.setCreationDate(rs.getDate("creationDate"));
                providerList.add(provider1);
            }
        }
        return providerList;
    }

 /**
     * 统计供应商信息的总数量
     * @return
     * @throws Exception
     */
    @Override
    public int countPrividerNum(String proName,String beginDate ,String endDate,String seachDesc) throws Exception {
        if (getConnection()){
            String sql ="SELECT COUNT(1) countnum FROM smbms_provider where 1=1 ";
            if (proName != null){
                sql += "and proName like '%"+proName+"%'";
            }
            if (beginDate != null){
                sql += "and DATE_FORMAT(creationDate,'%Y%m%d')<DATE_FORMAT('"+beginDate+"','%y%m%d')";
            }
            if (endDate != null){
                sql += "and DATE_FORMAT(creationDate,'%Y%m%d')<DATE_FORMAT('"+endDate+"','%y%m%d')";
            }
            if (seachDesc !=null){
                sql += " and MATCH(proDesc) AGAINST ('*"+seachDesc+"*' IN boolean MODE)";
            }

            rs = exSelect(sql,null);
            int count = 0;
            while (rs.next()){
                count = rs.getInt("countnum");
            }
            return count;
        }
        return 0;
    }

Servlet

package com.hz.servlet;

import com.hz.pojo.Provider;
import com.hz.service.Impl.ProviderServiceImpl;
import com.hz.service.ProviderService;

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 javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.List;

@WebServlet("/api/indexServlet")
public class IndexServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            HttpSession session = request.getSession();
            ProviderService providerService = new ProviderServiceImpl();

            //设置初始页码为1
            int pageNo = 1;
            //判断获得到的页码是为空，若不为空则拿到当前页码
            if (request.getParameter("pageNo") != null){
                pageNo = Integer.parseInt(request.getParameter("pageNo"));
            }

            //设置初始页码为1
            int pageSize = 5;
            //判断获得到的页码是为空，若不为空则拿到当前页码
            if (request.getParameter("pageSize") != null){
                pageSize = Integer.parseInt(request.getParameter("pageSize"));
            }
            //获得起始和结束时间
            String beginDate = (request.getParameter("beginDate")=="")?null:request.getParameter("beginDate");
            String endDate = (request.getParameter("endDate")=="")?null:request.getParameter("endDate");
            System.out.println(beginDate+"----------"+endDate);
            //获得搜索公司名称
            String searchInfo = request.getParameter("searchInfo");
            //获得产品描述的搜索词
            String seachDesc = (request.getParameter("seachDesc")=="")?null:request.getParameter("seachDesc");
            System.out.println(seachDesc);
            //调用service，获得供应商总数量
            int countPrivderNum = providerService.countPrividerNum(searchInfo,beginDate,endDate,seachDesc);

            //计算总页数
            // 整除：总页数=总数/5      不整除：总页数= 总数/5+1
            int maxPages = (countPrivderNum%pageSize == 0)?countPrivderNum/pageSize:(countPrivderNum/pageSize)+1;

            List<Provider> providerList = null;
            Provider provider = new Provider();
            provider.setProName(searchInfo);
            providerList = providerService.showProviderInfo(provider,pageNo,pageSize,beginDate,endDate,seachDesc);




            // 将providerList存储在 session 中以便首页访问
            session.setAttribute("providerList", providerList);
            // 将maxPages总页数存储在 session 中以便首页访问
            session.setAttribute("maxPages",maxPages);
            // 将pageNo页码存储在 session 中以便首页访问
            session.setAttribute("pageNo",pageNo);
            // 将pageSize每页显示多少行数据存储在 session 中以便首页访问
            session.setAttribute("pageSize",pageSize);
            session.setAttribute("searchInfo",searchInfo);
            session.setAttribute("beginDate",beginDate);
            session.setAttribute("endDate",endDate);
            session.setAttribute("seachDesc",seachDesc);

            // 重定向回首页
            response.sendRedirect("/pages/index.jsp?pageNo="+pageNo+"&pageSize="+pageSize);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
}

index.jsp

<a href="/api/indexServlet?pageNo=1">首页</a>
    <a <c:if test="${pageNo>1}">href="/api/indexServlet?pageNo=${pageNo-1}&pageSize=${pageSize}"</c:if>>上一页</a>
    <a <c:if test="${pageNo<maxPages}">href="/api/indexServlet?pageNo=${pageNo+1}&pageSize=${pageSize}"</c:if>>下一页</a>
    <a href="/api/indexServlet?pageNo=${maxPages}">尾页</a>&nbsp;&nbsp;&nbsp;
    当前是第${pageNo}页
    共${maxPages}页