resultMap,resultType在xml中多表联查写法

  • 为了方便区分这里直接创建了UserVoToResultType，UserVoToResultMap两个返回的vo类。先看下ResultType的写法
  1. vo类 UserVoToResultType
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    @Data
    public class UserVoToResultType {
        private Integer id;
        private String name;
        private String mobile;
        private Integer age;
        private Integer fileId;
        private String domainType;
        private String levelType;
        private String filesName;
        private String filesUrl;
    }
  2. Controller 
    1
    2
    3
    4
    5
    6
    7
    8
    @GetMapping("listAllToResultType")
    public R listAllToResultType(@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                 @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                 @RequestParam Integer fileId) {
        Page<UserVoToResultType> pageParam = new Page<>(pageNo, pageSize);
        IPage<UserVoToResultType> listPage = userInfoService.listAllToResultType(pageParam,fileId);
        return R.ok().data("listPage", listPage);
    }
  3. 直接跳过Service和Impl来到Mapper层
    1
    IPage<UserVoToResultType> listAllToResultType(Page<UserVoToResultType> pageParam, Integer fileId);
  4. xml文件,直接将查询的结果映射到指定的vo中
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    <select id="listAllToResultType" parameterType="com.baomidou.mybatisplus.core.metadata.IPage" resultType="com.fu99999.note.pojo.vo.UserVoToResultType">
       select
           tui.id,
           tui.name,
           tui.mobile,
           tui.age,
           tui.file_id,
           tfi.domain_type,
           tfi.level_type,
           tfi.files_name,
           tfi.files_url
       from tab_user_info tui left join tab_file_info tfi on tui.file_id = tfi.id where tui.file_id = #{fileId}
    </select>
  • ResultMap的写法
  1. vo类 UserVoToResultMap
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    @Data
    public class UserVoToResultMap {
        private Integer id;
        private String name;
        private String mobile;
        private Integer age;
        private Integer fileId;
        // TabFileInfo是file表的实体类
        private List<TabFileInfo> fileInfoList;
    }
  2. Controller
    1
    2
    3
    4
    5
    6
    7
    8
    @GetMapping("listAllToResultMap")
    public R listAllToResultMap(@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                @RequestParam Integer fileId) {
        Page<UserVoToResultMap> pageParam = new Page<>(pageNo, pageSize);
        IPage<UserVoToResultMap> listPage = userInfoService.listAllToResultMap(pageParam,fileId);
        return R.ok().data("listPage", listPage);
    }
  3. 直接跳过Service和Impl来到Mapper层
    1
    IPage<UserVoToResultMap> listAllToResultMap(Page<UserVoToResultMap> pageParam, Integer fileId);
  4. xml文件
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    <!-- TabUserInfo表默认映射 -->
    <resultMap id="baseResultMap" type="com.fu99999.note.pojo.vo.UserVoToResultMap">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <result property="mobile" column="mobile" />
        <result property="age" column="age" />
        <result property="fileId" column="file_id" />
        <!-- 多表联查 映射 property="fileInfoList" 为映射vo的对应表字段名字 ofType属性指定了集合中元素的类型 -->
        <collection property="fileInfoList" ofType="com.fu99999.note.pojo.entity.TabFileInfo">
            <result property="domainType" column="domain_type" />
            <result property="levelType" column="level_type" />
            <result property="filesName" column="files_name" />
            <result property="filesUrl" column="files_url" />
        </collection>
    </resultMap>

    <select id="listAllToResultMap" parameterType="com.baomidou.mybatisplus.core.metadata.IPage" resultMap="baseResultMap">
        select
            tui.id,
            tui.name,
            tui.mobile,
            tui.age,
            tui.file_id,
            tfi.domain_type,
            tfi.level_type,
            tfi.files_name,
            tfi.files_url
        from tab_user_info tui left join tab_file_info tfi on tui.file_id = tfi.id where tui.file_id = #{fileId}
    </select>
  • 总结：在做多表联查的时候ResultMap需要在xml中添加collection标签来映射需要返回的字段，需要注意的是property为实体字段名column是对应的数据库中的名字。而ResultType需要注意的是SQL查询的结果要和映射的对象一一对应。