Mybatis3详解（九）----高级映射之一对多映射

1、案例分析

继续接着上一章来，案例：一个部门可以包含多个员工；Department—>Employee（一对多）。一对多映射用到的resultMap标签中的collection子标签。它的属性和association标签基本一致，可以参考上一章的内容：链接 。下面我们就通过代码来实现一对多映射。

2、嵌套结果

①、分别定义Employee和Department实体类

Employee实体类：(不变，和上一章一样)

Department实体类(加入属性List<Employee> employees用于映射多个员工)：

/**
 * 部门实体类
 */
public class Department {
    //部门id
    private Integer deptId;
    //部门名称
    private String deptName;

    //部门有哪些员工
    private List<Employee> employees;

    //getter、setter、toString方法和一些构造方法省略...
}

②、创建DepartmentMapper接口和DepartmentMapper.xml 文件

DepartmentMapper接口：

/**
 * 部门Mapper接口
 */
public interface DepartmentMapper {
    //查询所有数据
    List<Department> selectAll();
    //根据部门id查询数据，这个方法是上一章创建了的
    Department selectDeptByDeptId(@Param("id") Integer deptId);
}

DepartmentMapper.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.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 文件（参考上一章）

⑤、编写测试代码（稍微有一点点改动）

/**
 * 测试代码
 */
public class MybatisTest {
    //定义 SqlSession
    private SqlSession sqlSession = null;
    //定义 DepartmentMapper对象
    private DepartmentMapper mapper = null;

    @Before//在测试方法执行之前执行
    public void getSqlSession(){
        //1、加载 mybatis 全局配置文件
        InputStream is = MybatisTest.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        //2、创建SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3、根据 sqlSessionFactory 产生 session
        sqlSession = sqlSessionFactory.openSession();
        //4、创建Mapper接口的的代理对象，getMapper方法底层会通过动态代理生成DepartmentMapper的代理实现类
        mapper = sqlSession.getMapper(DepartmentMapper.class);
    }

    @After//在测试方法执行完成之后执行
    public void destroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();

    }
    //查询所有数据
    @Test
    public void testSelectAll(){
        List<Department> departments = mapper.selectAll();
        for (Department department : departments) {
            System.out.println(department);
        }
    }

    //根据部门id查询数据
    @Test
    public void testSelectEmpByEmpId(){
        Department department = mapper.selectDeptByDeptId(3);
        System.out.println(department);
    }
}

⑥、运行结果

       查询所有数据：

通过运行结果可以发现，编号为3和7 部门下分别有多名员工。

根据部门id查询数据：

3、分步查询

使用分步查询的好处就是可以设置延迟加载，延迟加载后面会有介绍。

①、更改DepartmentMapper.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.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>

    <!--根据部门id查询数据-->
    <select id="selectDeptByDeptId" parameterType="int" resultMap="departmentMap">
        SELECT * FROM t_department WHERE department_id = #{id}
    </select>
</mapper>

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

 ②、分别在EmployeeMapper接口和EmployeeMapper.xml文件中添加如下代码：

EmployeeMapper接口：

/**
 * 员工Mapper接口
 */
public interface EmployeeMapper {
    //查询所有数据
    List<Employee> selectAll();
    //根据员工id查询数据
    Employee selectEmpByEmpId(@Param("id") Integer empId);

    //据据员工表的department_id查询员工数据，用于一对多的关联查询
    Employee selectEmpByDeptId(@Param("id") Integer deptId);
}

EmployeeMapper.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.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" column="department_id" javaType="department"
                     select="com.thr.mapper.DepartmentMapper.selectDeptByDeptId"/>-->
    </resultMap>
    
    <!-- 查询所有数据-->
    <select id="selectAll" resultMap="employeeMap">
      SELECT * FROM t_employee
    </select>

    <!--根据员工id查询数据-->
    <select id="selectEmpByEmpId" parameterType="int" resultMap="employeeMap">
        SELECT * FROM t_employee where department_id= #{id}
    </select>

    <!--根据员工表的department_id查询员工数据，用于一对多的关联查询-->
    <select id="selectEmpByDeptId" parameterType="int" resultMap="employeeMap">
        SELECT * FROM t_employee where department_id= #{id}
    </select>
</mapper>

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

③、测试代码

/**
 * 测试代码
 */
public class MybatisTest {
    //定义 SqlSession
    private SqlSession sqlSession = null;
    //定义 DepartmentMapper对象
    private DepartmentMapper mapper = null;

    @Before//在测试方法执行之前执行
    public void getSqlSession(){
        //1、加载 mybatis 全局配置文件
        InputStream is = MybatisTest.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        //2、创建SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3、根据 sqlSessionFactory 产生 session
        sqlSession = sqlSessionFactory.openSession();
        //4、创建Mapper接口的的代理对象，getMapper方法底层会通过动态代理生成UserMapper的代理实现类
        mapper = sqlSession.getMapper(DepartmentMapper.class);
    }

    @After//在测试方法执行完成之后执行
    public void destroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();

    }
    //查询所有数据
    @Test
    public void testSelectAll(){
        List<Department> departments = mapper.selectAll();
        for (Department department : departments) {
            System.out.println(department);
        }
    }

    //根据部门id查询数据
    @Test
    public void testSelectDeptByDeptId(){
        Department department = mapper.selectDeptByDeptId(3);
        System.out.println(department);
    }
}

④、运行结果

查询所有数据：

根据部门id查询数据：