mybatisPlus+多表联查+多条件分页查询（一对一，一对多分页）

为什么会写这篇文章？

分页插件在分页时处理一对多的结构的情况时，总条数与实际条数不匹配。这时候需要自定义sql分页查询语句来实现一对多分页条件查询。希望能帮到你！

自定义mapper

package com.minglei.hotnews.mapper;


import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam;
import javafx.scene.control.Pagination;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ClientuserCustomMapper {
    /**
     * 根据工单ID获取详情
     * @param workOrderId
     * @return
     */
    WorkOrderDetailDto  loadWorkOrderDetailDto(Long workOrderId);
//    List<WorkOrderDetailDto> loadWorkOrderDetails(WorkOrderLoadParam para);

    /**
     * 多条件分页显示工单
     * @param para
     * @return
     */
    List<WorkOrderDetailDto> loadWorkOrderDetails(WorkOrderLoadParam para);

    /**
     * 多条件分页显示工单的工单数量
     * @param param
     * @return
     */
    Integer loadWorkOrderDetailsCount(WorkOrderLoadParam param);

    /**
     * 多条件分页显示工单
     *
     * @param page
     * @param param
     * @return
     */
    List<WorkOrderLoadDto> loadWorkOrder(Page<WorkOrderLoadDto> page, @Param("param") WorkOrderLoadParam param);
}

自定义mapper映射文件

<?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.minglei.hotnews.mapper.ClientuserCustomMapper">
    <resultMap id="BaseResultMap" type="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="ordernum" jdbcType="VARCHAR"  property="ordernum" ></result>
        <result column="addtime" jdbcType="TIMESTAMP"  property="addtime" ></result>
        <result column="shop_Id" jdbcType="BIGINT"  property="shop_Id" ></result>
        <association property="shopInfoDto"  javaType="com.minglei.hotnews.mapper.ClientUserCustomDto.ShopInfoDto">
            <id column="shopId" jdbcType="BIGINT" property="shopId" />
            <result column="shopname" jdbcType="VARCHAR" property="shopname"></result>
            <result column="address" jdbcType="VARCHAR" property="address"></result>
        </association>
        <collection property="workOrderLogInfos" ofType="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLogInfo" >
            <id column="logId" jdbcType="BIGINT" property="logId"></id>
            <result column="memo" jdbcType="VARCHAR"  property="memo" ></result>
            <result column="logaddtime" jdbcType="TIMESTAMP"  property="logaddtime" ></result>
        </collection>
    </resultMap>

    <resultMap id="LoadWorkBaseResultMap" type="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="ordernum" jdbcType="VARCHAR"  property="ordernum" ></result>
        <result column="addtime" jdbcType="TIMESTAMP"  property="addtime" ></result>
        <result column="shop_Id" jdbcType="BIGINT"  property="shop_Id" ></result>
        <association property="shopInfoDto"  javaType="com.minglei.hotnews.mapper.ClientUserCustomDto.ShopInfoDto">
            <id column="shopId" jdbcType="BIGINT" property="shopId" />
            <result column="shopname" jdbcType="VARCHAR" property="shopname"></result>
            <result column="address" jdbcType="VARCHAR" property="address"></result>
        </association>
        <collection property="workOrderLogInfos" ofType="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLogInfo" >
            <id column="logId" jdbcType="BIGINT" property="logId"></id>
            <result column="memo" jdbcType="VARCHAR"  property="memo" ></result>
            <result column="logaddtime" jdbcType="TIMESTAMP"  property="logaddtime" ></result>
        </collection>
    </resultMap>
    <resultMap id="LoadWorkCustomResultMap" type="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadDto">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="ordernum" jdbcType="VARCHAR"  property="ordernum" ></result>
        <result column="addtime" jdbcType="TIMESTAMP"  property="addtime" ></result>
        <result column="shop_Id" jdbcType="BIGINT"  property="shop_Id" ></result>
        <association property="shopInfoDto"  javaType="com.minglei.hotnews.mapper.ClientUserCustomDto.ShopInfoDto">
            <id column="shopId" jdbcType="BIGINT" property="shopId" />
            <result column="shopname" jdbcType="VARCHAR" property="shopname"></result>
            <result column="address" jdbcType="VARCHAR" property="address"></result>
        </association>
    </resultMap>
<!--    工单详情-->
    <select id="loadWorkOrderDetailDto" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
        w.id as id,
        w.addtime as addtime,
        w.ordernum as ordernum,
        w.shop_id as shop_Id,
        s.id as shopId,
        s.shopname as shopname,
        s.address as address,
        wlog.Id as logId,
        wlog.memo as memo,
        wlog.addtime as logaddtime from workorder as w
        left JOIN shop as s ON w.shop_id=s.id
        LEFT JOIN workoderlog as wlog ON w.id=wlog.workorder_id
        WHERE w.id=#{workOrderId,jdbcType=BIGINT}
    </select>
<!-- 多条件查询工单工单 -->
    <select id="loadWorkOrderDetails" resultMap="LoadWorkBaseResultMap" parameterType="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam">
        select
        w.id as id,
        w.addtime as addtime,
        w.ordernum as ordernum,
        w.shop_id as shop_Id,
        s.id as shopId,
        s.shopname as shopname,
        s.address as address,
        wlog.Id as logId,
        wlog.memo as memo,
        wlog.addtime as logaddtime from workorder as w
        left JOIN shop as s ON w.shop_id=s.id
        LEFT JOIN workoderlog as wlog ON w.id=wlog.workorder_id
        <where>
            <if test="OrderNum!=null and OrderNum !=''">
            and w.ordernum like concat('%',#{OrderNum},'%')
            </if>
            <if test="ShopName!=null and ShopName!=''">
            and s.shopname  like concat('%',#{ShopName},'%')
            </if>
            <if test="PageSize>0">
                and w.id in(select temp.id from( select distinct childw.id id from workorder childw LEFT JOIN shop childs ON childw.shop_id=childs.id <where>  <if test="OrderNum!=null and OrderNum !=''">
                and childw.ordernum like concat('%',#{OrderNum},'%')
            </if>
                <if test="ShopName!=null and ShopName!=''">
                    and childs.shopname  like concat('%',#{ShopName},'%')
                </if></where>  limit #{PageNum},#{PageSize}) as temp)

            </if>
        </where>

    </select>
<!--  多条件查询工单工单总数  -->
    <select id="loadWorkOrderDetailsCount" resultType="java.lang.Integer"  parameterType="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam">
        select count(temp.id) from( select distinct childw.id id from workorder childw LEFT JOIN shop childs ON childw.shop_id=childs.id <where>  <if test="OrderNum!=null and OrderNum !=''">
        and childw.ordernum like concat('%',#{OrderNum},'%')
    </if>
        <if test="ShopName!=null and ShopName!=''">
            and childs.shopname  like concat('%',#{ShopName},'%')
        </if></where>) as temp
    </select>
<!--    多条件查询工单（自带分页插件）-->
    <select id="loadWorkOrder" resultMap="LoadWorkCustomResultMap" parameterType="com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam" >
        select
        w.id as id,
        w.addtime as addtime,
        w.ordernum as ordernum,
        w.shop_id as shop_Id,
        s.id as shopId,
        s.shopname as shopname,
        s.address as address
        from workorder as w
        left JOIN shop as s ON w.shop_id=s.id

        <where>
        <if test="param.OrderNum!=null and param.OrderNum !=''">
            and w.ordernum like concat('%',#{param.OrderNum},'%')
        </if>
        <if test="param.ShopName!=null and param.ShopName!=''">
            and s.shopname  like concat('%',#{param.ShopName},'%')
        </if>
        </where>
        ORDER BY w.id
    </select>
</mapper>

定义接口service

package com.minglei.hotnews.service.WorkOrderService;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam;

import java.util.List;
import java.util.Map;

/**
 * 工单service
 */
public interface IWorkOrderLoadService {
    /**
     * 多条件分页显示工单
     * @param param
     * @return
     */
//    List<WorkOrderDetailDto> loadWorkOrder(WorkOrderLoadParam param);
    Map loadWorkOrder(WorkOrderLoadParam param);

    /**
     * 使用mp自带分页查询工单
     * @param param
     * @return
     */
    Page<WorkOrderLoadDto>  loadWorkOrderCustom(WorkOrderLoadParam param);
}

定义service接口实现类

package com.minglei.hotnews.service.WorkOrderService.Impl;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderDetailDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadDto;
import com.minglei.hotnews.mapper.ClientUserCustomDto.WorkOrderLoadParam;
import com.minglei.hotnews.mapper.ClientuserCustomMapper;
import com.minglei.hotnews.service.WorkOrderService.IWorkOrderLoadService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class WorkOrderLoadService implements IWorkOrderLoadService {
    @Autowired
    ClientuserCustomMapper clientuserCustomMapper;
    /**
     * 多条件分页显示工单
     *
     * @param param
     * @return
     */
    @Override
    public Map loadWorkOrder(WorkOrderLoadParam param) {
        Map<String,Object> workOrderRes=new HashMap<>();
        Integer aLong = clientuserCustomMapper.loadWorkOrderDetailsCount(param);
        workOrderRes.put("total",aLong);
        List<WorkOrderDetailDto> workOrderDetailDtos = clientuserCustomMapper.loadWorkOrderDetails( param);
        workOrderRes.put("list",workOrderDetailDtos);
        return workOrderRes;
    }

    /**
     * 使用mp自带分页查询工单
     *
     * @param param
     * @return
     */
    @Override
    public Page<WorkOrderLoadDto> loadWorkOrderCustom(WorkOrderLoadParam param) {
        Page<WorkOrderLoadDto> page=new Page<>(param.PageNum,param.PageSize);
        List<WorkOrderLoadDto> workOrderLoadDtos = clientuserCustomMapper.loadWorkOrder(page, param);
        return page.setRecords(workOrderLoadDtos);
    }
}