Mybatis的resultMap标签

resultMap标签只和select标签配合使用，用于设置自定义映射规则，处理字段名和属性名不一致问题，处理多对一映射和一对多映射关系。

一 基础数据准备

1.1 数据库表模型

CREATE TABLE `sys_role` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `code` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sys_depart` (
  `id` varchar(32) NOT NULL COMMENT '部门主键id',
  `name` varchar(32) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sys_user` (
  `id` varchar(32) NOT NULL COMMENT '主键id',
  `username` varchar(32) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `depart_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1.2 实体类代码

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private String id;
    private String username;
    private Integer age;
    private String departId;
    private Role role;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role {
    private String id;
    private String name;
    private String code;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Depart {
    private String id;
    private String name;
    private List<User> userList;
}

二 resultMap处理字段和属性的映射关系

若字段名和实体类中的属性名不一致，可以通过别名的方式，也可以通过resultMap标签设置自定义的映射关系。

2.1 字段别名

@Repository
@Mapper
public interface UserMapper {
    List<User> queryUserList();
}

<mapper namespace="com.mango.mapper.UserMapper">
    <select id="queryUserList" resultType="com.mango.domain.User">
        select id, username, age, depart_id as departId from sys_user
    </select>
</mapper>

2.2 resultMap标签

@Repository
@Mapper
public interface UserMapper {
    List<User> queryUserList();
}

<mapper namespace="com.mango.mapper.UserMapper">
    <resultMap id="userMap" type="com.mango.domain.User">
        <id property="id" column="id" />
        <result property="username" column="username" />
        <result property="age" column="age" />
        <result property="departId" column="depart_id" />
        <result property="role.id" column="r_id" />
        <result property="role.name" column="r_name" />
        <result property="role.code" column="code" />
    </resultMap>
    <select id="queryUserList" resultMap="userMap">
        select u.id, u.username, u.age, u.depart_id, 
        r.id as r_id, r.name as r_name, r.code
        from sys_user u
        left join sys_role r on u.role_id = r.id
    </select>
</mapper>

三 resultMap处理一对多映射

Depart类有个泛型是User类型的集合类型属性，对应着部门表和用户表是一对多的关系，在一个部门下面有好多用户，查询部门列表，嵌套查询每个部门下的所有用户信息。

3.1 collection单步方式

@Repository
@Mapper
public interface DepartMapper {
    List<Depart> queryDepartList();
}

<mapper namespace="com.mango.mapper.DepartMapper">
    <resultMap id="departMap" type="com.mango.domain.Depart">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="userList" ofType="com.mango.domain.User">
            <id property="id" column="u_id"/>
            <result property="username" column="username"/>
            <result property="age" column="age"/>
            <result property="departId" column="depart_id"/>
        </collection>
    </resultMap>
    <select id="queryDepartList" resultMap="departMap">
        select d.id, d.name, u.id as u_id, u.username, u.age, u.depart_id
        from sys_depart d
        left join sys_user u on d.id = u.depart_id
    </select>
</mapper>

3.2 collection分步方式

@Repository
@Mapper
public interface UserMapper {
    List<User> listUserByDepartId(String id);
}

<mapper namespace="com.mango.mapper.UserMapper">
    <select id="listUserByDepartId" resultType="com.mango.domain.User">
        select id, username, age, depart_id from sys_user
        where depart_id = #{id}
    </select>
</mapper>

@Repository
@Mapper
public interface DepartMapper {
    List<Depart> queryDepartList();
}

<mapper namespace="com.mango.mapper.DepartMapper">
    <resultMap id="departMap" type="com.mango.domain.Depart">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="userList"
                    column="id"
                    ofType="com.mango.domain.User"
                    fetchType="eager"
                    select="com.mango.mapper.UserMapper.listUserByDepartId"/>
    </resultMap>
    <select id="queryDepartList" resultMap="departMap">
        select id, name from sys_depart
    </select>
</mapper>

collection标签，用于处理父对象和子集合的一对多的映射关系；
collection标签的property属性，对应实体类中集合类型的属性，该属性在数据库中表不存在；
collection标签的column属性，对应数据库表中的字段，和其他数据库表有关联关系的字段；
collection标签的ofType属性，对应实体类中子集合存储的数据类型；
collection标签的fetchType属性，用于设置延迟加载模式，fetchType属性有两个属性值，lazy(延迟加载)和eager(立即加载)，lazy(延迟加载)是指在访问集合数据之前，不会立即从数据库中加载集合数据，eager(立即加载)是指在加载父对象的时候，会立即从数据库中加载集合数据；
collection标签的select属性，对应集合类型数据的mapper接口全路径方法名；

四 resultMap处理多对一映射

User类有个Role类型的属性，对应着用户表和角色表是多对一的关系，多个用户可以是相同的角色，查询每个用户信息，并嵌套查询用户的角色信息。

4.1 association单步方式

@Repository
@Mapper
public interface UserMapper {
    List<User> queryAllUser();
}

<mapper namespace="com.mango.mapper.UserMapper">
    <resultMap id="userMap" type="com.mango.domain.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="age" column="age"/>
        <result property="departId" column="depart_id"/>
        <association property="role"
                     javaType="com.mango.domain.Role">
            <id property="id" column="r_id" />
            <result property="name" column="r_name" />
            <result property="code" column="code" />
        </association>
    </resultMap>
    <select id="queryAllUser" resultMap="userMap">
        select
            u.id, u.username, age, depart_id, role_id,
            r.id as r_id, r.name as r_name, r.code
        from sys_user u
        left join sys_role r on u.role_id = r.id
    </select>
</mapper>

4.2 association多步方式

@Repository
@Mapper
public interface RoleMapper {
    Role queryById(String id);
}

<mapper namespace="com.mango.mapper.RoleMapper">
    <select id="queryById" resultType="com.mango.domain.Role">
        select id, name, code from sys_role where id = #{id}
    </select>
</mapper>

@Repository
@Mapper
public interface UserMapper {
    List<User> queryAllUser();
}

<mapper namespace="com.mango.mapper.UserMapper">
    <resultMap id="userMap" type="com.mango.domain.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="age" column="age"/>
        <result property="departId" column="depart_id"/>
        <association property="role"
                     column="role_id"
                     fetchType="eager"
                     javaType="com.mango.domain.Role"
                     select="com.mango.mapper.RoleMapper.queryById" />
    </resultMap>
    <select id="queryAllUser" resultMap="userMap">
        select id, username, age, depart_id, role_id
        from sys_user
    </select>
</mapper>

association标签用于处理类型之间多对一的映射关系，association标签的属性和collection标签的属性意义是一样的，用来设置嵌套查询的信息。