Mybatisplus中如何优雅地使用单表分页和多表分页

MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变。

一、配置

添加依赖如下，需要注意的是，不要再次配置其他分页插件，否则可能导致分页返回结果total=0：

  	<mybatisplus.spring.boot.version>1.0.5</mybatisplus.spring.boot.version>
       <mybatisplus.version>2.2.0</mybatisplus.version>

     		 <!--mybatis-plus -->
       <dependency>
           <groupId>com.baomidou</groupId>
           <artifactId>mybatisplus-spring-boot-starter</artifactId>
           <version>${mybatisplus.spring.boot.version}</version>
       </dependency>
       <dependency>
           <groupId>com.baomidou</groupId>
           <artifactId>mybatis-plus</artifactId>
           <version>${mybatisplus.version}</version>
       </dependency>

根据自己的需求，在 application.properties 配置相关信息：

mybatis-plus.mapper-locations=classpath:mapper/*.xml		#配置xml文件扫描
mybatis-plus.typeAliasesPackage=com.df.dsell.mobile.pojo 	#配置实体扫描
mybatis-plus.global-config.db-column-underline=true
mybatis-plus.global-config.sql-injector=com.baomidou.mybatisplus.mapper.LogicSqlInjector
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.cache-enabled=false
mybatis-plus.configuration.call-setters-on-nulls=true

代码也配置一把：

import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.df.dsell.mobile.dao") //配置dao扫描
public class MapperScanConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor() { //分页插件
        PaginationInterceptor page = new PaginationInterceptor();
        page.setDialectType("mysql"); //指定数据库类型
        return page;

    }
}

二、单表分页

单表分页很简单，这里只写核心部分。

• dao继承BaseMapper

import com.baomidou.mybatisplus.mapper.BaseMapper;

@Mapper
public interface BkXcOrderDao extends BaseMapper<BkXcOrder> { //dao接口继承BaseMapper，BkXcOrder为pojo
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.df.dsell.mobile.dao.DsellOrderFormMapper">
</mapper>

• service实现类继承ServiceImpl

public interface BkXcOrderService { 
    /**
     * @描叙： 分页查询 历史记录
     * 传递分页参数 如下：
     * page ：当前页，默认是1
     * limit： 每页的数量 默认10
     * sidx： 排序字段
     * order： ASC || DESC 即按排序字段 升序或降序
     */
    PageUtils queryPage(Map<String, Object> params);   
}

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
@Service("BkXcOrderServiceImpl")
public class BkXcOrderServiceImpl extends ServiceImpl<BkXcOrderDao, BkXcOrder> implements BkXcOrderService {//service 实现类继承ServiceImpl，BkXcOrderService 为service 基类

  	@Override
    public PageUtils queryPage(Map<String, Object> params) {//param为前端传过来的查询参数

        String staffGid = (String) params.get("staffGid");

	    //单表分页
        Page<BkXcOrder> page = this.selectPage(
                new Query<BkXcOrder>(params).getPage(), //Query为自定义参数提取类
                new EntityWrapper<BkXcOrder>()
                        .eq(StringUtils.isNotBlank(staffGid), "STAFF_GID", staffGid)
        );

        return new PageUtils(page);	//PageUtils自定义page结果封装类
    }
}

三、多表分页

多表分页有两种，第一种是将多表分页分两次查询，第一次利用单表在主表中查询出分页信息（如total等字段）以及关联的id列表，然后根据这些id列表，使用in条件查询，做关联查询。第二种是一次性操作，只有一条关联查询加上Pagination来实现分页。个人觉得第二种更简单。

3.1 第一种分页

下面先看第一种分页查询，在上面已经做了单表分页的基础上，再进一步来关联查询。
逻辑如下：

	 //先查分页数据
        PageUtils page = bkXcOrderService.queryPage(params);


        List<BkXcOrder> bkXcOrderList = (List<BkXcOrder>) page.getList();
        List<String> ids = new ArrayList<>();
        //拼接参数
        bkXcOrderList.forEach(item -> {
            ids.add(item.getGid());
        });

        //查询分页service调用
        page.setList(bkXcOrderService.queryListByPage(ids, staffGid));

• 在BkXcOrderServiceImpl 中继续添加一个方法queryListByPage

public interface BkXcOrderService {
	...
	
    /**
     * @描叙： 根据分页参数查列表
     */
    List<BookHistoryVo> queryListByPage(List ids, String staffGid);
}

@Service("BkXcOrderServiceImpl")
public class BkXcOrderServiceImpl extends ServiceImpl<BkXcOrderDao, BkXcOrder> implements BkXcOrderService {

    @Override
    public List<BookHistoryVo> queryListByPage(List ids, String staffGid) {

        return baseMapper.queryListByPage(ids, staffGid);//BkXcOrderDao 中自定义的方法
    }
}

• BkXcOrderDao 中添加queryListByPage

import com.baomidou.mybatisplus.mapper.BaseMapper;

@Mapper
public interface BkXcOrderDao extends BaseMapper<BkXcOrder> { //dao接口继承BaseMapper，BkXcOrder为pojo
    /**
     * @描叙： 根据分页参数查列表
     */
    List<BookHistoryVo> queryListByPage(@Param("ids") List ids, @Param("staffGid") String staffGid);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.df.dsell.mobile.dao.DsellOrderFormMapper">
 <select id="queryListByPage" resultType="com.sx.purchase.VO.BookHistoryVo">
        SELECT bk_passport.GID customerGid ,
        bk_passport.REAL_NAME customerName,
        bk_book.GID bookGid,
        bk_book.BOOKNAME bookname,
        bk_book.AUTHOR author,
        bk_book.ISBN isbn ,
        bk_book.PRICE price ,
        bk_passport.POSITION position,
        bk_passport.DEPARTMENT department
        FROM BK_XC_ORDER bk_order
        INNER JOIN BK_XC_PASSPORT_INFO bk_passport ON bk_order.CUSTOMER_GID = bk_passport.GID
        INNER JOIN BK_XC_BOOKS bk_book ON bk_order.BIBGID = bk_book.GID
        <where>
            <if test="staffGid != null and staffGid != ''">
                AND bk_order.STAFF_GID=#{staffGid}
            </if>

            <if test=" ids != null and ids.size != 0">
                AND bk_order.GID in
                <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

3.2 第二种分页

也就是一次数据查询解决问题，这种很简单

• dao层先做声明

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.plugins.pagination.Pagination;
import com.df.dsell.mobile.dao.vo.OrderQueryVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;


/**
 * @ClassName： 中文名：【订单表】
 * @description： 订单表 CRUD 操作
 */
@Mapper
public interface DsellOrderFormMapper extends BaseMapper<DsellOrderForm> {

    /**
     * 根据分页插件来实现分页。OrderQueryVo自定义VO
     *
     * @param page        Pagination
     * @param userId      用户id
     * @param orderStatus 订单状态
     * @return
     */
    List<OrderQueryVo> queryListByPage(Pagination page, @Param("userId") String userId, @Param("orderStatus") Integer orderStatus);
}

xml文件为很简单的关联查询操作

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.df.dsell.mobile.dao.DsellOrderFormMapper">

    <select id="queryListByPage" resultType="com.df.dsell.mobile.dao.vo.OrderQueryVo">
        SELECT order_status.ORDER_STATUS orderStutas,
        order_form.GID orderId,
        order_form.ORDER_AMOUNT orderAmount,
        order_form.LOGISTICS_PAY logisticsPay,
        order_formlist.BOOK_NAME bookName,
        order_formlist.PRICE price,
        order_formlist.PRICE price,
        order_formlist.BOOK_NUM bookNum,
        order_formlist.DISCOUNT discount
        FROM DSELL_ORDER_STATUS order_status
        INNER JOIN DSELL_ORDER_FORM order_form ON order_form.GID = order_status.ORDER_ID AND order_form.LOGIC_DELETE=0
        INNER JOIN DSELL_ORDER_FORMLIST order_formlist ON order_form.GID = order_formlist.ORDER_GID AND
        order_formlist.LOGIC_DELETE=0
        <where>
            <if test="userId != null and userId != ''">
                AND order_status.PASSPORT_GID=#{userId}
            </if>
            <if test="orderStatus != null">
                AND order_status.ORDER_STATUS=#{orderStatus}
            </if>
            <if test=" 1 > 0">
                AND order_status.LOGIC_DELETE=0
            </if>
        </where>
        ORDER BY order_form.CREATE_DATE
    </select>
</mapper>

• DsellOrderFormServiceImpl 依然要实现 ServiceImpl类

public interface DsellOrderFormService {
    /**
     * 根据分页插件来实现分页
     * @param page Pagination
     * @param userId 用户id
     * @param orderStatus 订单状态
     * @return
     */
    List<OrderQueryVo> queryListByPage(Pagination page, String userId, Integer orderStatus);
}

import com.baomidou.mybatisplus.plugins.pagination.Pagination;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.df.dsell.mobile.dao.DsellOrderFormMapper;
import com.df.dsell.mobile.dao.vo.OrderQueryVo;
import com.df.dsell.mobile.pojo.DsellOrderForm;
import com.df.dsell.order.service.DsellOrderFormService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;



@Service
public class DsellOrderFormServiceImpl extends ServiceImpl<DsellOrderFormMapper, DsellOrderForm> implements DsellOrderFormService {

    @Override
    public List<OrderQueryVo> queryListByPage(Pagination page, String userId, Integer orderStatus) {
        return baseMapper.queryListByPage(page,userId,orderStatus);
    }
}

• 调用逻辑如下：

	//构建分页参数
	Page<OrderQueryVo> page = new Query<OrderQueryVo>(params).getPage();
	//调用分页查询
	List<OrderQueryVo> orderQueryVoList = dsellOrderFormService.queryListByPage(page, userId, orderStatus);
	 //设置结果
	page.setRecords(orderQueryVoList);
	//结果转换为自己公司规范的分页字段
	return new PageUtils(page);

辅助类

上面用到了两个自定义的辅助类Query和PageUtils

• Query

package com.df.dsell.common.utils;

import com.baomidou.mybatisplus.plugins.Page;
import com.df.dsell.common.xss.SQLFilter;
import org.apache.commons.lang.StringUtils;

import java.util.LinkedHashMap;
import java.util.Map;

/**
 * @ClassName： 中文名：【分页查询参数】
 */
public class Query<T> extends LinkedHashMap<String, Object> {
    private static final long serialVersionUID = 1L;
    /**
     * mybatis-plus分页参数
     */
    private Page<T> page;
    /**
     * 当前页码
     */
    private int pageNum = 1;
    /**
     * 每页条数
     */
    private int pageSize = 10;

    public Query(Map<String, Object> params) {
        this.putAll(params);

        //分页参数
        if (params.get("pageNum") != null) {
            pageNum = (Integer) params.get("pageNum");
        }
        if (params.get("pageSize") != null) {
            pageSize = (Integer) params.get("pageSize");
        }

        this.put("offset", (pageNum - 1) * pageSize);
        this.put("page", pageNum);
        this.put("limit", pageSize);

        //防止SQL注入（因为sidx、order是通过拼接SQL实现排序的，会有SQL注入风险）
        String sidx = SQLFilter.sqlInject((String) params.get("sidx"));  //排序字段
        String order = SQLFilter.sqlInject((String) params.get("order"));   //ASC | DESC 两个值中的一种
        this.put("sidx", sidx);
        this.put("order", order);

        //mybatis-plus分页
        this.page = new Page<>(pageNum, pageSize);

        //排序
        if (StringUtils.isNotBlank(sidx) && StringUtils.isNotBlank(order)) {
            this.page.setOrderByField(sidx);
            this.page.setAsc("ASC".equalsIgnoreCase(order));
        }

    }

    public Page<T> getPage() {
        return page;
    }

    public int getPageNum() {
        return pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }
}

• PageUtils

package com.df.dsell.common.utils;

import com.baomidou.mybatisplus.plugins.Page;
import lombok.Data;

import java.io.Serializable;
import java.util.Collection;
import java.util.List;

/**
 * @description： mybatis-plus专用分页工具
 */
@Data
public class PageUtils implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 当前页数
     */
    private Long pageNum = 1L;
    /**
     * 每页记录数
     */
    private Integer pageSize = 10;
    /**
     * 列表数据
     */
    private List<?> entities;
    /**
     * 总页数
     */
    private Long count = 1L;
    /**
     * 总记录数
     */
    private Long total = 1L;
    /**
     * 第一页
     */
    private boolean isFirstPage = false;
    /**
     * 最后一页
     */
    private boolean isLastPage = false;


    /**
     * 分页
     *
     * @param entities 列表数据
     * @param total    总记录数
     * @param pageSize 每页记录数
     * @param pageNum  当前页数
     */
    public PageUtils(List<?> entities, Long total, int pageSize, Long pageNum) {
        this.entities = entities;
        this.total = total;
        this.pageSize = pageSize;
        this.pageNum = pageNum;
        this.count = (long) Math.ceil((double) total / pageSize);

        if (entities instanceof Collection) {
            this.judgePageBoudary();
        }
    }

    /**
     * 分页
     */
    public PageUtils(Page<?> page) {
        this.entities = page.getRecords();
        this.total = page.getTotal();
        this.pageSize = page.getSize();
        this.pageNum = (long) page.getCurrent();
        this.count = page.getPages();

        this.judgePageBoudary();
    }

    /**
     * 参数
     */
    private void judgePageBoudary() {
        this.isFirstPage = this.pageNum == 1L;
        this.isLastPage = this.pageNum == this.count;
    }
}

• SQLFilter

package com.df.dsell.common.xss;

import com.df.dsell.common.exception.ParameterException;
import com.df.framework.core.vo.ResponseConstant;
import org.apache.commons.lang.StringUtils;

/**
 * @description： SQL过滤
 */
public class SQLFilter {

    /**
     * SQL注入过滤
     *
     * @param str 待验证的字符串
     */
    public static String sqlInject(String str) {
        if (StringUtils.isBlank(str)) {
            return null;
        }
        //去掉'|"|;|\字符
        str = StringUtils.replace(str, "'", "");
        str = StringUtils.replace(str, "\"", "");
        str = StringUtils.replace(str, ";", "");
        str = StringUtils.replace(str, "\\", "");

        //转换成小写
        str = str.toLowerCase();

        //非法字符
        String[] keywords = {"master", "truncate", "insert", "select", "delete", "update", "declare", "alert", "drop"};

        //判断是否包含非法字符
        for (String keyword : keywords) {
            if (str.indexOf(keyword) != -1) { //ParameterException为继承RuntimeException的自定义异常
                throw new ParameterException(ResponseConstant.VALIDATE_EXCEPTION,"包含非法字符");
            }
        }

        return str;
    }
}

文章比较长，需要耐心看才能看明白，尤其是那多表分页两种分页的第一种。不过个人还是推荐使用第二种，因为一个查询能解决的问题，为何要用两个查询呢！