MyBatis-8 关联映射

1 前言

MyBatis 中主要有这两种方式实现关联查询：

• 嵌套结果查询：使用嵌套映射的方式来处理关联结果的子集。
• 嵌套 select 查询：通过 select 属性来执行另外一个 SQL 映射语句来返回预期的复杂类型。

数据库与文件目录：

2 一对一映射

一个 Employee 对应一个 Department。

// 员工类
@Data
public class Employee {
    private Integer empId;
    private String empName;
    private Integer empAge;
    private Integer empSex;
    private String empEmail;
    private String empAddress;
    private Department department;
}
//部门类
@Data
public class Department {
    private Integer deptId;
    private String deptName;
}
//员工Mapper接口
public interface EmployeeMapper {
    List<Employee> selectAll();
    Employee selectEmpByEmpId(@Param("id") Integer empId);
}

2.1 嵌套结果查询

<!--EmployeeMapper.xml-->
<mapper namespace="com.thr.mapper.EmployeeMapper">
    <resultMap id="employeeMap" type="com.thr.pojo.Employee">
        <id property="empId" column="employee_id"/>
        <result property="empName" column="employee_name"/>
        <result property="empAge" column="employee_age"/>
        <result property="empSex" column="employee_sex"/>
        <result property="empEmail" column="employee_email"/>
        <result property="empAddress" column="employee_address"/>
        <!-- 一对一关联对象 -->
        <association property="department" javaType="department">
            <id property="deptId" column="department_id"/>
            <result property="deptName" column="department_name"/>
        </association>
    </resultMap>
 
    <select id="selectAll" resultMap="employeeMap"><!-- 查询所有数据-->
        SELECT * FROM t_employee e, t_department d where e.department_id=d.department_id
    </select>
    <select id="selectEmpByEmpId" parameterType="int" resultMap="employeeMap"> <!--根据员工id查询数据-->
        SELECT * FROM t_employee e, t_department d where e.department_id=d.department_id and e.employee_id= #{id}
    </select>
</mapper>

2.2 嵌套 select 查询

嵌套 select 查询的这种方式是通过 association 标签中的 select 属性来完成，它需要执行另外一个 SQL 映射语句来返回预期的复杂类型，并且会从 column 属性指定的列中检索数据，作为参数传递给目标 select 语句。所以我们必须在关联的另一个 Mapper 接口中创建一个根据 id 查询数据的方法，并且表字段和实体属性如果不同还要进行映射。

public interface DepartmentMapper {
    Department selectDeptByDeptId(@Param("id") Integer deptId);
}

<!--EmployeeMapper.xml-->
<mapper namespace="com.thr.mapper.EmployeeMapper">
    <resultMap id="employeeMap" type="com.thr.pojo.Employee">
        <id property="empId" column="employee_id"/>
        <result property="empName" column="employee_name"/>
        <result property="empAge" column="employee_age"/>
        <result property="empSex" column="employee_sex"/>
        <result property="empEmail" column="employee_email"/>
        <result property="empAddress" column="employee_address"/>
        <!-- 一对一关联对象，注意：select方式需要加column属性，column属性会从当前查询出的指定列检索数据，
             这里为t_employee表中的department_id，然后作为参数传递给目标的select语句-->
        <association property="department" column="department_id" javaType="department"
                     select="com.thr.mapper.DepartmentMapper.selectDeptByDeptId"/>
    </resultMap>
 
    <select id="selectAll" resultMap="employeeMap">
      SELECT * FROM t_employee
    </select>
 
    <select id="selectEmpByEmpId" parameterType="int" resultMap="employeeMap">
        SELECT * FROM t_employee where employee_id= #{id}
    </select>
</mapper>
 
 
<!--DepartmentMapper.xml-->
<mapper namespace="com.thr.mapper.DepartmentMapper">
    <resultMap id="departmentMap" type="com.thr.pojo.Department">
        <id property="deptId" column="department_id"/>
        <result property="deptName" column="department_name"/>
    </resultMap>
 
    <select id="selectDeptByDeptId" parameterType="int" resultMap="departmentMap">
        select * from t_department where department_id = #{id}
    </select>
</mapper>

分布执行明显多执行了很多 SQL 语句，所以肯定会导致查询的效率变低，但是这种方式也有好处，那就是可以延迟加载。

3 一对多映射

一个 Department 对应多个 Employee。

// 员工类
@Data
public class Employee {
    private Integer empId;
    private String empName;
    private Integer empAge;
    private Integer empSex;
    private String empEmail;
    private String empAddress;
    private Department department;
}
//部门类
@Data
public class Department {
    private Integer deptId;
    private String deptName;
	private List<Employee> employees;
}
//员工Mapper接口
public interface EmployeeMapper {
    List<Employee> selectAll();
    Employee selectEmpByEmpId(@Param("id") Integer empId);
}
//部门Mapper接口
public interface DepartmentMapper {
    List<Department> selectAll();
    Department selectDeptByDeptId(@Param("id") Integer deptId);
}

注意，下面两种方法直接使用下面的代码获取 Department 对象，Department.employees.department 是 null。

List<Department> departments = mapper.selectAll();
Department department = mapper.selectDeptByDeptId(3);

3.1 嵌套结果查询

<!--DepartmentMapper.xml-->
<mapper namespace="com.thr.mapper.DepartmentMapper">
    <resultMap id="departmentMap" type="com.thr.pojo.Department">
        <id property="deptId" column="department_id"/>
        <result property="deptName" column="department_name"/>
        <!--一对多关联对象，ofType指定的是映射到list集合属性中pojo的类型，也就是尖括号的泛型-->
        <collection property="employees" ofType="employee">
            <id property="empId" column="employee_id"/>
            <result property="empName" column="employee_name"/>
            <result property="empAge" column="employee_age"/>
            <result property="empSex" column="employee_sex"/>
            <result property="empEmail" column="employee_email"/>
            <result property="empAddress" column="employee_address"/>
        </collection>
    </resultMap>
 
    <!-- 查询所有数据-->
    <select id="selectAll" resultMap="departmentMap">
      SELECT * FROM t_employee e, t_department d WHERE e.department_id=d.department_id
    </select>
 
    <!--根据部门id查询数据-->
    <select id="selectDeptByDeptId" parameterType="int" resultMap="departmentMap">
        SELECT * FROM t_employee e, t_department d WHERE e.department_id=d.department_id and d.department_id = # {id}
    </select>
</mapper>
 
<!--EmployeeMapper.xml-->
<mapper namespace="com.thr.mapper.EmployeeMapper">
    <resultMap id="employeeMap" type="com.thr.pojo.Employee">
        <id property="empId" column="employee_id"/>
        <result property="empName" column="employee_name"/>
        <result property="empAge" column="employee_age"/>
        <result property="empSex" column="employee_sex"/>
        <result property="empEmail" column="employee_email"/>
        <result property="empAddress" column="employee_address"/>
        <!-- 一对一关联对象 -->
        <association property="department" javaType="department">
            <id property="deptId" column="department_id"/>
            <result property="deptName" column="department_name"/>
        </association>
    </resultMap>
 
    <select id="selectAll" resultMap="employeeMap"><!-- 查询所有数据-->
        SELECT * FROM t_employee e, t_department d where e.department_id=d.department_id
    </select>
    <select id="selectEmpByEmpId" parameterType="int" resultMap="employeeMap"> <!--根据员工id查询数据-->
        SELECT * FROM t_employee e, t_department d where e.department_id=d.department_id and e.employee_id= #{id}
    </select>
</mapper>

3.2 嵌套 select 查询

由于 column 属性是根据当前 t_department 表查询出的 department_id 作为参数，然后通过 select 属性传递给关联对象的方法，所以我们在查询员工表时，应该根据 t_employee 表中的字段 department_id 来查询，而不再是根据 employee_id 来查询，这一点一定要理解清楚，否则这里无法进行下去。所以我们需要在 EmployeeMapper 接口中创建一个根据部门 id 查询员工信息的方法。

<!--DepartmentMapper.xml-->
<mapper namespace="com.thr.mapper.DepartmentMapper">
    <resultMap id="departmentMap" type="com.thr.pojo.Department">
        <id property="deptId" column="department_id"/>
        <result property="deptName" column="department_name"/>
        <!--一对多关联对象，ofType指定的是映射到list集合属性中pojo的类型，也就是尖括号的泛型
        注意：这里的column属性首先是查询出t_department表的department_id，然后将它以参数的形式传递给select属性
        中的EmployeeMapper.selectEmpByDeptId方法，进而查询出当前部门下的员工-->
        <collection property="employees" ofType="employee" column="department_id"
                    select="com.thr.mapper.EmployeeMapper.selectEmpByDeptId">
        </collection>
    </resultMap>
 
    <select id="selectAll" resultMap="departmentMap">
      SELECT * FROM t_department
    </select>
    <select id="selectDeptByDeptId" parameterType="int" resultMap="departmentMap">
        SELECT * FROM t_department WHERE department_id = #{id}
    </select>
</mapper>
 
 
<!--EmployeeMapper.xml-->
<mapper namespace="com.thr.mapper.EmployeeMapper">
    <resultMap id="employeeMap" type="com.thr.pojo.Employee">
        <id property="empId" column="employee_id"/>
        <result property="empName" column="employee_name"/>
        <result property="empAge" column="employee_age"/>
        <result property="empSex" column="employee_sex"/>
        <result property="empEmail" column="employee_email"/>
        <result property="empAddress" column="employee_address"/>
    </resultMap>
 
    <select id="selectAll" resultMap="employeeMap"><!-- 查询所有数据-->
        SELECT * FROM t_employee e, t_department d where e.department_id=d.department_id
    </select>
    <select id="selectEmpByEmpId" parameterType="int" resultMap="employeeMap"> <!--根据员工id查询数据-->
        SELECT * FROM t_employee e, t_department d where e.department_id=d.department_id and e.employee_id= #{id}
    </select>
	<select id="selectEmpByDeptId" parameterType="int" resultMap="employeeMap">
        SELECT * FROM t_employee where department_id= #{id}
    </select>
</mapper>

public interface EmployeeMapper {
    //查询所有数据
    List<Employee> selectAll();
    //根据员工id查询数据
    Employee selectEmpByEmpId(@Param("id") Integer empId);
 
    //据据员工表的department_id查询员工数据，用于一对多的关联查询
    Employee selectEmpByDeptId(@Param("id") Integer deptId);
}

这里需要注意的是：要注释掉一方中的关联映射，否则就会导致无限循环映射而导致报错。

4 多对多映射

多对多映射，其实就是多个一对多映射。

创建步骤分析：

1. User 表和 Role 表具有多对多关系，需要使用中间表(t_user_role)关联，中间表中包含各自的主键，在中间表中是外键；
2. 建立两个实体类：用户实体类中包含对角色的集合引用，角色实体类中包含对用户的集合引用；
3. 建立两个 Mapper 接口：用户的 Mapper 接口和角色的 Mapper 接口；
4. 建立两个配置文件：用户的配置文件和角色的配置文件；
5. 实现功能： 查询用户时，同时得到用户所包含的角色信息； 查询角色时，同时得到角色对应的用户信息；
6. 编写测试代码并查看运行结果，能否查询出相应的数据；

4.1 用户到角色的多对多查询

SELECT * FROM t_user u,t_user_role ur,t_role r
       WHERE u.id = ur.user_id AND ur.role_id = r.roleId ORDER BY id

//User实体类
public class User {
    private int userId;
    private String userName;
    private int userAge;
    private Date userBirthday;
    private int userSex;
    private String userAddress;
    private List<Role> roles;
}
//UserMapper接口
public interface UserMapper {
    List<User> selectAllUser();
    User selectUserByUserId(@Param("id") Integer userId);
}

<mapper namespace="com.thr.mapper.UserMapper">
    <resultMap id="userMap" type="com.thr.pojo.User">
        <id property="userId" column="id"/>
        <result property="userName" column="username"/>
        <result property="userAge" column="age"/>
        <result property="userBirthday" column="birthday"/>
        <result property="userSex" column="sex"/>
        <result property="userAddress" column="address"/>
        <!--一对多映射-->
        <collection property="roles" ofType="role">
            <id property="roleId" column="roleId"/>
            <result property="roleName" column="roleName"/>
            <result property="remake" column="remake"/>
        </collection>
    </resultMap>
 
    <select id="selectAllUser" resultMap="userMap">
        SELECT * FROM t_user u, t_user_role ur, t_role r
			WHERE u.id = ur.user_id AND ur.role_id = r.roleId ORDER BY id
    </select>
    <select id="selectUserByUserId" resultMap="userMap">
        SELECT * FROM t_user u, t_user_role ur, t_role r
        WHERE u.id = ur.user_id AND ur.role_id = r.roleId AND u.id = #{id}
    </select>
</mapper>