mybatis和pagehelper分页查询

（一）maven

<pagehelper-starter.version>1.3.0</pagehelper-starter.version>
<pagehelper.version>5.2.0</pagehelper.version>

<!--MyBatis分页插件starter-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>${pagehelper-starter.version}</version>
</dependency>
<!--MyBatis分页插件-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>${pagehelper.version}</version>
</dependency>

统一response的模型

package com.macro.mall.common.api;

import com.github.pagehelper.PageInfo;
import org.springframework.data.domain.Page;

import java.util.List;

/**
 * 分页数据封装类
 * Created by macro on 2019/4/19.
 */
public class CommonPage<T> {
    /**
     * 当前页码
     */
    private Integer pageNum;
    /**
     * 每页数量
     */
    private Integer pageSize;
    /**
     * 总页数
     */
    private Integer totalPage;
    /**
     * 总条数
     */
    private Long total;
    /**
     * 分页数据
     */
    private List<T> list;

    /**
     * 将PageHelper分页后的list转为分页信息
     */
    public static <T> CommonPage<T> restPage(List<T> list) {
        CommonPage<T> result = new CommonPage<T>();
        PageInfo<T> pageInfo = new PageInfo<T>(list);
        result.setTotalPage(pageInfo.getPages());
        result.setPageNum(pageInfo.getPageNum());
        result.setPageSize(pageInfo.getPageSize());
        result.setTotal(pageInfo.getTotal());
        result.setList(pageInfo.getList());
        return result;
    }

    /**
     * 将SpringData分页后的list转为分页信息
     */
    public static <T> CommonPage<T> restPage(Page<T> pageInfo) {
        CommonPage<T> result = new CommonPage<T>();
        result.setTotalPage(pageInfo.getTotalPages());
        result.setPageNum(pageInfo.getNumber());
        result.setPageSize(pageInfo.getSize());
        result.setTotal(pageInfo.getTotalElements());
        result.setList(pageInfo.getContent());
        return result;
    }

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

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

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

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

    public Long getTotal() {
        return total;
    }

    public void setTotal(Long total) {
        this.total = total;
    }
}

数据库层

package bdemo.common.dal.datainterface;
 
import bdemo.common.dal.dataobject.OperateLogDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
 
import java.util.List;
import java.util.Map;
 
public interface OperateLogDAO {
 
    int batchInsert(List<OperateLogDO> operateLogDOList);
 
    List<OperateLogDO> optLogQuery(@Param("params") Map<String,String> params);
 
    int pageQueryCount(@Param("params") Map<String,String> params);
}

<?xml version="1.0" encoding="UTF-8" ?>
 
<mapper namespace="bdemo.common.dal.datainterface.OperateLogDAO">
 
    <resultMap id="resultMap" type="bdemo.common.dal.dataobject.OperateLogDO">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <id column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate"/>
        <id column="gmt_modify" jdbcType="TIMESTAMP" property="gmtModify"/>
        <id column="biz_id" jdbcType="VARCHAR" property="bizId"/>
        <id column="biz_type" jdbcType="VARCHAR" property="bizType"/>
    </resultMap>
 
    <sql id="BASE_COLUMN_LIST">
        id,
        gmt_create,
        gmt_modify,
        biz_id,
        biz_type
    </sql>
    <sql id="BASE_TABLE_NAME">
        operate_log
    </sql>
    <insert id="batchInsert">
        insert into
        <include refid="BASE_TABLE_NAME" />
         (
        gmt_create,
        gmt_modify,
        biz_id,
        biz_type
        )values
        <foreach collection="list" item="item" index="index" open="" close="" separator=",">
            (
            now().
            now(),
            #{item.bizId},
            #{item.bizType}
            )
        </foreach>
    </insert>
 
    <select id="optLogQuery" resultMap="resultMap">
        select
        <include refid="BASE_COLUMN_LIST" />
        from
        <include refid="BASE_TABLE_NAME" />
        where 1=1
        <if test="params.bizId !=null and params.bizId !=''">
            and biz_id=#{params.bizId}
        </if>
        order by gmt_create desc
        LIMIT #{params.offset},#{params.limit}
    </select>
 
    <select id="pageQueryCount" resultMap="java.lang.Integer">
    select
    count(1)
    from
    <include refid="BASE_TABLE_NAME" />
    where 1=1
    <if test="params.bizId !=null and params.bizId !=''">
        and biz_id=#{params.bizId}
    </if>
    </select>
 
 
</mapper>

中间业务层

@Override
    public PageData<OperateLogDomain> optLogQuerypage(Map<String, Object> parmas, int pageNo, int pageSize) {
        PageData<OperateLogDO> pageData=null;
        try{
            pageData= PageQueryUtils.pageQuery(pageNo,pageSize,parmas,map -> operateLogDAO.pageQueryCount(map),map -> operateLogDAO.optLogQuery(map));
 
        }catch (Exception e){
        }
        if(Objects.nonNull(pageData)&& !CollectionUtils.isEmpty(pageData.getList())){
            List<OperateLogDomain> operateLogDomainList=pageData.getList().stream().map(operateLogDO->{
                OperateLogDomain operateLogDomain=new OperateLogDomain();
                operateLogDomain.setBizId(operateLogDO.getBizId());
                operateLogDomain.setBizType(operateLogDO.getBizType());
                operateLogDomain.setExeMethod(operateLogDO.getExeMethod());
                operateLogDomain.setExrStatus(operateLogDO.getExeStatus());
                return operateLogDomain;
            }).collect(Collectors.toList());
            if(!CollectionUtils.isEmpty(operateLogDomainList)){
                PageData<OperateLogDomain> operateLogDomainPageData=new PageData<>(pageNo,pageSize,pageData.getTotalCount(),operateLogDomainList);
                return operateLogDomainPageData;
            }
        }
        return null;
    }

package bdemo.core.service.util;
 
import bdemo.common.service.facade.base.PageData;
 
import java.util.List;
import java.util.Map;
import java.util.function.Function;
 
public class PageQueryUtils {
 
    public static PageData pageQuery(int curPage, int pageSize, Map<String,Object> params, Function<Map,Integer> countFun, Function<Map, List> dataFun){
 
 
 
        int totalCount=countFun.apply(params);
        PageData pageData=new PageData(curPage,pageSize,totalCount);
        pageData.setPageNo(curPage);
        if(totalCount>0){
            params.put("limit",pageData.getLength());
            params.put("offset",pageData.getOffset());
            List list=dataFun.apply(params);
            pageData.setList(list);
        }
        return pageData;
    }
}