MyBatis 多对一 二次查询

EmployeeDao的映射文件

<?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.hsj.dao.EmployeeDao">


    <!-- 查询所有雇员和部门信息
    select * from t_employee e
     -->
    <select id="getEmployees" resultMap="resultMap_Employee">
        select * from t_employee
    </select>

    <resultMap type="employee" id="resultMap_Employee">
        <id column="e_id" property="id"/>
        <result column="e_name" property="name"/>
        <result column="e_age" property="age"/>
        <result column="e_sex" property="sex"/>
        <!-- column属性指定外键字段的名 
        select="getDepartmentById":指定二次查询的唯一标识
        -->
        <association property="department" column="depart_id" 
        javaType="department"
        select="getDepartmentById">

        </association>
    </resultMap>

    <!-- 
    通过关联属性中的column属性指定的外键字段的值作为当前select中占位符的值
    select * from t_department where d_id=e.depart_id
     -->
    <select id="getDepartmentById" parameterType="int" resultMap="resultMap_Department">
        select * from t_department where d_id=#{id}
    </select>

    <resultMap type="department" id="resultMap_Department">
        <id column="d_id" property="id"/>
        <result column="d_name" property="name"/>
    </resultMap>



    <!-- 根据雇员编号好像雇员及其部门信息 -->
    <select id="getEmployeeById" parameterType="int" resultMap="resultMap_Employee">
        select * from t_employee e inner join t_department d on e.depart_id=d.d_id and e_id=#{id}
    </select>



</mapper>

DepartmentDao的映射文件

<?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.hsj.dao.DepartmentDao">


    <!-- 
    select * from t_department
     -->
    <select id="getDepartments" resultMap="resultMap_Department">
        select * from t_department
    </select>

    <resultMap type="department" id="resultMap_Department">
        <id column="d_id" property="id"/>
        <result column="d_name" property="name"/>

        <!-- 
        select="getEmployeesByDepartId":指定二次查询的唯一标识
         column="d_id":将当前查询的指定字段的值作为二次查询的条件
         -->
        <collection property="employees" column="d_id" 
            ofType="employee" 
            select="getEmployeesByDepartId">

        </collection>
    </resultMap>

    <!-- 二次查询的sql语句：
     -->
    <select id="getEmployeesByDepartId" parameterType="int" resultMap="resultMap_Employee">
        select * from t_employee where depart_id=#{id}
    </select>

    <resultMap type="employee" id="resultMap_Employee">
        <id column="e_id" property="id"/>
        <result column="e_name" property="name"/>
        <result column="e_age" property="age"/>
        <result column="e_sex" property="sex"/>
    </resultMap>



    <!-- 根据部门编号查询部门对象 -->
    <select id="getDepartmentById" parameterType="int" resultMap="resultMap_Department">
        select * from t_department where d_id=#{id}
    </select>

</mapper>

其中的一个测试类

public class DepartmentDaoTest {

    private DepartmentDao departmentDao;

    @Before
    public void setUp() throws Exception {
        SqlSession sqlSession=MyBatisUtils.getSqlSession();

        this.departmentDao=sqlSession.getMapper(DepartmentDao.class);

    }

    @Test
    public void testGetDepartments() {
        try {
            List<Department> departments=this.departmentDao.getDepartments();
            for(Department department:departments){
                System.out.println(department);
                System.out.println("雇员信息：");

                for(Employee e :department.getEmployees()){
                    System.out.println(e);
                }

                System.out.println("============");
            }
            System.out.println();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    @Test
    public void testGetDepartmentById() {
        int id=1;
        try {
            Department department=this.departmentDao.getDepartmentById(id);
            System.out.println(department);
            System.out.println("雇员信息：");
            for(Employee e:department.getEmployees()){
                System.out.println(e);
            }


        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}