怎么实现列表分页功能，并且给前端返回总条数和总页数

重点：

1、数据库:Mysql     ==>      MySQL用的是limit进行分页查询

代码示例:
 
语句1：select * from student limit 0,10
 
// 语句1和2均返回表student的前10行 ，语句1 ：第一个参数表示从该参数的下一条数据开始查询，第二个参数表示每次返回的数据条数。
 
 
语句2：select * from student limit 10 offset 0
//语句2 ：第二个参数表示从该参数的下一条数据开始查询，第一个参数表示每次返回的数据条数。

2、计算分页页数：

首先通过sql查询出满足条件的总记录数（totalCount）：

select  count(1) from tablename 

之后计算总页数（totalPage）：

Double totalPage = Math.ceil(totalCount/ 10)        (这里每页记录数使用固定值10，可换为变量)

使用Math.ceil(）函数==>向上取整   

即：Math.ceil(  23.0 /10 ）==> 3.0   （拓展：向下取整：Math.floor()）

totalPage的类型将影响结果：

如果totalPage使用Int类型==> 如：  23/10  ==>2    ,如取Double类型 ==>如： 23.0/10 = 2.3

所以这里需采用Double。

下面为具体案例：

SQL：

工单列表：

  <select id="selectByCompanyCode" parameterType="WorkOrderMo" resultType="WorkVO">
        SELECT
        two.cust_id as custId

        FROM tb_work_order two
        join tc_work_type twt on two.work_type = twt.work_type
        join tc_work_class twcs on two.work_class = twcs.work_class
        <if test="status != '043000'">
            and two.status=#{status}
        </if>
        order by two.urgency desc,two.create_date desc limit #{startIndex},10;
    </select>

工单列表总条数：（注意是返回是Double类型，前面已提到作用）

<select id="selectCount" resultType="java.lang.Double">
        select count(1) from tb_work_order two
        join tc_work_type twt on two.work_type = twt.work_type
        join tc_work_class twcs on two.work_class = twcs.work_class
        <if test="status != '043000'">
            and two.status=#{status}
        </if>
   </select>

DAO层：

//工单列表
List<WorkVO> selectByCompanyCode(@Param("companyCode") String companyCode, @Param("status") String status,@Param("startIndex") Integer startIndex);

  //工单列表Count
    Double selectCount(@Param("companyCode") String companyCode, @Param("status") String status);

Service：

列表：

 @Override
    public List<WorkVO> selectByCompanyCode(String status,String companyCode,Integer page) {
        List<WorkVO> list = new ArrayList<>();
        int startIndex = (page-1)*10;
        list = tbWorkOrderDao.selectByCompanyCode(companyCode,status,startIndex);
        return list;
    }

列表Count：

    @Override
    public Double selectCount(String status,String companyCode) {
        Double totalCount = 0.0;
        totalCount = tbWorkOrderDao.selectCount(companyCode,status);
        return totalCount;
    }

Controller:

  //工单列表
    @ResponseBody
    @GetMapping("/selectByCompanyCode")
    public WebApiPage<List<WorkVO>> selectByCompanyCode(HttpServletRequest request, HttpServletResponse response) {
        WebApiPage<List<WorkVO>> webApi = new WebApiPage<List<WorkVO>>();
        String status = request.getParameter("status");
        String companyCode = request.getParameter("companyCode");
    
        if(request.getParameter("page").equals("")){
           Integer page = 1;
            List<WorkVO>  list = orderService.selectByCompanyCode(status,companyCode,page);
            webApi.setResult(list);
        }else {
            Integer page = Integer.valueOf(request.getParameter("page"));
            List<WorkVO>  list = orderService.selectByCompanyCode(status,companyCode,page);
            webApi.setResult(list);
        }

        Double totalCount1 = orderService.selectCount(status,companyCode);
        Double totalPage1 = Math.ceil(totalCount1/10);//向上取整
        
        Integer totalPage = totalPage1.intValue();
        Integer totalCount = totalCount1.intValue();
        
        //返回字段
        webApi.setTotalCount(totalCount);//总记录数
        webApi.setTotalPage(totalPage);//总页数
        webApi.setReturnCode(0);
        webApi.setMessage("成功");
        return webApi;
    }

WebApiPage:

/**
 * @作者:ll
 * @创建时间:10:19 2020/9/9
 */


public class WebApiPage<T> {
    public WebApiPage() {
        init();
    }

    public WebApiPage(int totalCount,int totalPage,int returnCode, String message, T result) {
        totalCount = totalCount;
        totalPage = totalPage;
        returnCode = returnCode;
        message = message;
        result = result;
    }
    private int totalCount;
    private int totalPage;
    private int returncode;
    private String message="";
    private T result;

    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 int getReturncode() {
        return returncode;
    }

    public void setReturnCode(int returnCode) {
        this.returncode = returnCode;
    }

    private void init() {
        this.returncode = 0;
        this.message = "成功";
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public T getResult() {
        return result;
    }

    public void setResult(T result) {
        this.result = result;
    }

    /**
     * 重新设置返回值
     */
    public void reset(int totalCount,int totalPage,int returncode, String message) {
        this.totalCount = totalCount;
        this.totalPage = totalPage;
        this.returncode = returncode;
        this.message = message;
    }

    /**
     * 重新设置返回值
     */
    public void reset(int totalCount,int totalPage,int returncode, String message, T result) {
        this.totalCount = totalCount;
        this.totalPage = totalPage;
        this.returncode = returncode;
        this.message = message;
        this.result = result;
    }
}