【MyBatis-plus】- 使用多表关联查询并分页

2022-03-24 10:51:51 浏览数 (1)

1、第一步，创建一个对象VO类，用于展示页面

代码语言：javascript复制
/**
 * @Author: Liu Yue
 * @Descripition:
 * @Date; Create in 2021/4/22 14:16
 **/
@Data
public class HkTaskMasterVO extends HkTaskMaster {

    /**
     * 附表list
     */
    private List<HkTaskAddendumVO> hkTaskAddendumList;

    private String sendName;

}

2、创建XML的mapper

代码语言：javascript复制
<?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.hake.mana.business.mapper.HkTaskMasterRelate1Mapper">
    <resultMap id="hkTaskMasterRelaterMap" type="com.hake.mana.business.vo.HkTaskMasterVO">
        <result column="id" jdbcType="INTEGER"
                property="id"/>
        <result column="task_uuid" jdbcType="VARCHAR"
                property="taskUuid"/>
        <result column="task_name" jdbcType="VARCHAR"
                property="taskName"/>
        <result column="task_content" jdbcType="VARCHAR"
                property="taskContent"/>
        <result column="send_uuid" jdbcType="VARCHAR"
                property="sendUuid"/>
        <result column="send_name" jdbcType="VARCHAR"
                property="sendName"/>
        <result column="create_time" jdbcType="TIMESTAMP"
                property="createTime"/>
        <result column="create_user" jdbcType="VARCHAR"
                property="createUser"/>
        <result column="modify_time" jdbcType="TIMESTAMP"
                property="modifyTime"/>
        <result column="modify_user" jdbcType="VARCHAR"
                property="modifyUser"/>
        <result column="del_mark" jdbcType="INTEGER"
                property="delMark"/>
        <result column="flag" jdbcType="INTEGER"
                property="flag"/>
        <result column="remark" jdbcType="VARCHAR"
                property="remark"/>
        <collection property="hkTaskAddendumList" column="task_uuid"  ofType="map" select="selelctTaskAddendumsByMastUuid"/>
        <!注释，这里使用到了关联查询子查询>
    </resultMap>
    <select id="findByPage" resultMap="hkTaskMasterRelaterMap">
        <!注释，这里是主查询，使用到了关联查询>
        SELECT
          tm.*,
          u.`user_name` AS send_name
        FROM
          `hk_task_master` tm
          LEFT JOIN hk_user u
            ON tm.`send_uuid` = u.`login_no` ${ew.customSqlSegment}
    </select>
    <resultMap id="addenduBaseMap" type="com.hake.mana.business.vo.HkTaskAddendumVO">
        <result column="id" jdbcType="INTEGER"
                property="id"/>
        <result column="master_uuid" jdbcType="VARCHAR"
                property="masterUuid"/>
        <result column="addendum_uuid" jdbcType="VARCHAR"
                property="addendumUuid"/>
        <result column="addendum_content" jdbcType="VARCHAR"
                property="addendumContent"/>
        <result column="addendum_content" jdbcType="VARCHAR"
                property="addendumContent"/>
        <result column="create_time" jdbcType="TIMESTAMP"
                property="createTime"/>
        <result column="create_user" jdbcType="VARCHAR"
                property="createUser"/>
        <result column="modify_time" jdbcType="TIMESTAMP"
                property="modifyTime"/>
        <result column="modify_user" jdbcType="VARCHAR"
                property="modifyUser"/>
        <result column="del_mark" jdbcType="INTEGER"
                property="delMark"/>
        <result column="flag" jdbcType="INTEGER"
                property="flag"/>
        <result column="remark" jdbcType="VARCHAR"
                property="remark"/>
        <collection property="fileUuidList" column="addendum_uuid"  ofType="map" select="selectAddendumfiles"/>
    </resultMap>
    <select id="selelctTaskAddendumsByMastUuid" parameterType="string" resultMap="addenduBaseMap">
        SELECT
          *
        FROM
          `hk_task_addendum`
        WHERE del_mark = 1
          AND master_uuid = #{task_uuid,jdbcType=VARCHAR}
    </select>
    <resultMap id="masterFileMap" type="com.hake.mana.business.vo.HkTaskMasterFileVO">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="id" jdbcType="INTEGER"
                property="id"/>
        <result column="master_uuid" jdbcType="VARCHAR"
                property="masterUuid"/>
        <result column="master_type" jdbcType="VARCHAR"
                property="masterType"/>
        <result column="file_uuid" jdbcType="VARCHAR"
                property="fileUuid"/>
        <result column="create_time" jdbcType="TIMESTAMP"
                property="createTime"/>
        <result column="create_user" jdbcType="VARCHAR"
                property="createUser"/>
        <result column="modify_time" jdbcType="TIMESTAMP"
                property="modifyTime"/>
        <result column="modify_user" jdbcType="VARCHAR"
                property="modifyUser"/>
        <result column="del_mark" jdbcType="INTEGER"
                property="delMark"/>
        <result column="flag" jdbcType="INTEGER"
                property="flag"/>
        <result column="remark" jdbcType="VARCHAR"
                property="remark"/>
        <result column="file_name" jdbcType="VARCHAR"
                property="fileName"/>
    </resultMap>
    <select id="selectAddendumfiles" parameterType="map" resultMap="masterFileMap">
        SELECT
          mf.*,
          CONCAT(
            f.`file_name`,
            f.`file_extension`
          ) AS file_name
        FROM
          `hk_task_master_file` mf
          INNER JOIN hk_file f
            ON f.`file_uuid` = mf.`file_uuid`
        WHERE mf.`master_uuid` = #{addendum_uuid}
        AND mf.`del_mark` = 1
    </select>
</mapper>

3、对应的mapper.java

代码语言：javascript复制
/**
 * @Author: Liu Yue
 * @Descripition:
 * @Date; Create in 2021/4/22 14:24
 **/
public interface HkTaskMasterRelate1Mapper extends BaseMapper<HkTaskMasterVO> {

    重点，分页查询方法
    Page<HkTaskMasterVO> findByPage(IPage<HkTaskMasterVO> page, @Param(Constants.WRAPPER) Wrapper<HkTaskMasterVO> wrapper);

    List<HkTaskAddendumVO> selelctTaskAddendumsByMastUuid(@Param("task_uuid") String masterUuid);
}

4、service层

代码语言：javascript复制
/**
 * @Author: Liu Yue
 * @Descripition:
 * @Date; Create in 2021/4/22 14:41
 **/
@Service
public interface HkTaskMasterRelate1Service extends IService<HkTaskMasterVO> {
    Page<HkTaskMasterVO> selectPage2List(HkTaskMasterDTO hkTaskMasterDTO);

    List<HkTaskAddendumVO> selectTaskAddendumd(HkTaskMasterDTO hkTaskMasterDTO);
}
代码语言：javascript复制
/**
 * @Author: Liu Yue
 * @Descripition:
 * @Date; Create in 2021/4/22 14:43
 **/
@Service
@Slf4j
public class HkTaskMasterRelate1ServiceImpl
        extends ServiceImpl<HkTaskMasterRelate1Mapper, HkTaskMasterVO>
        implements HkTaskMasterRelate1Service {
    /**
     * 包含发送人姓名
     * @param hkTaskMasterDTO
     * @return
     */
    @Override
    public Page<HkTaskMasterVO> selectPage2List(HkTaskMasterDTO hkTaskMasterDTO){
        QueryWrapper<HkTaskMasterVO> wrapper = new QueryWrapper<>();
        wrapper.eq("tm.del_mark",1);
        //重点，这里的查询使用QueryWrapper，条件字段也要使用别名指定，排序也要指定字段
        if (!StringUtils.isEmpty(hkTaskMasterDTO.getTaskName())) {
            wrapper.like("tm.task_name", hkTaskMasterDTO.getTaskName());
        }
        wrapper.orderByDesc("tm.create_time");
        Page<HkTaskMasterVO> page = new Page(hkTaskMasterDTO.getPageNum(),hkTaskMasterDTO.getPageSize());
        return baseMapper.findByPage(page,wrapper);
    }

    /**
     * 包含发送人姓名
     * @param hkTaskMasterDTO
     * @return
     */
    @Override
    public List<HkTaskAddendumVO> selectTaskAddendumd(HkTaskMasterDTO hkTaskMasterDTO){
       return  baseMapper.selelctTaskAddendumsByMastUuid(hkTaskMasterDTO.getTaskUuid());
    }
}

5、controller层调用

代码语言：javascript复制
    /**
     * 下发任务分页查询
     * @param hkTaskMasterDTO
     * @return
     */
    @PostMapping("selectPageList")
    public BaseResponse selectPageList(@RequestBody HkTaskMasterDTO hkTaskMasterDTO){
        try {
            return successToJson(hkTaskMasterRelate1Service.selectPage2List(hkTaskMasterDTO));
        } catch (Exception ex) {
            log.error("###SRM###{}", ex.getMessage());
            return fail("查询失败，请联系管理员");
        }
    }

亲测，可用；

每天进步一点点 ！！！

java xml controller service 对象

0 人点赞