526互联

mybatis-plus使用连表查询分页

发布时间 2023-12-12 10:33:00作者: CallMeEureka
  • mybatis-plus在进行单表查询时确实很方便，但是一旦我们需要连表查询时，就不得不引用其他插件来进行操作。但是复杂的查询必然会造成我们的代码量往上涨。
    当然mybatis-plus也不会脱离mybatis原本的特性，比如xml文件来操作数据库。本篇文章记录一下使用mybatis-plus来进行连表查询分页功能。
    接口：
    /**
     * TODO: 分页查询操作
     * @param param
     * @return
     * @throws Exception
     */
    InPageResponse getInpageJobOperation(JobTmpOperateInpageParam param) throws Exception;

实现类：

    @Override
    public InPageResponse getInPagejobTmpForms(JobTmpFormInpageParam param) throws Exception {
        Page<JobFormsVo> page = new Page<>(param.getCurpage(), param.getPagesize());
        QueryWrapper<JobFormsVo> wrapper = new QueryWrapper<>();
        IPage<JobFormsVo> jobFormsVos = this.jobTmpFormMp.getInPagejobTmpForms(param,page,wrapper);
        InPageResponse inPageResponse = new InPageResponse();
        inPageResponse.setItems(jobFormsVos.getRecords());
        inPageResponse.setCount(jobFormsVos.getTotal());
        inPageResponse.setPages(jobFormsVos.getPages());
        return inPageResponse;
    }

mapper:

    /**
     * Author: Eureka
     * Date: 2023/12/5
     * TODO:
     */
    public interface JobTmpFormMp extends BaseMapper<JobTmpForm> {
        IPage<JobFormsVo> getInPagejobTmpForms(@Param("param") JobTmpFormInpageParam param, @Param("page") Page<JobFormsVo> page, @Param(Constants.WRAPPER) 
        QueryWrapper<JobFormsVo> wrapper) throws Exception;
    }

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.console.flow.mapper.mp.JobOperationMp">
    <resultMap id="JobOperationVo" type="com.console.flow.classes.job.vo.JobOperationVo">
        <id property="operateid" column="operateid"/>
        <result property="name" column="name"/>
        <result property="joname" column="joname"/>
        <result property="selected" column="selected"/>
    </resultMap>
    <select id="getInpageJobOperation" parameterType="map" resultMap="JobOperationVo">
        select a.operateid,
           a.name,
           b.joname,
           ifnull(b.selected,'A003002')  as selected
        from gz_operation a
        left join(
           select b.name as joname,
              b.operateid,
              'A003001' as selected
           from gz_jobtmp_operation b
           where b.jobtmpid=#{param.jobtmpid}
        ) b on b.operateid = a.operateid
        order by a.operateid
        ${ew.customSqlSegment}
    </select>
</mapper>

如上就是在我们的原始查询sql最下面加上${ew.customSqlSegment}就可以实现如单表查询的丝滑操作了。