数据库表结构

department

employee

要求一

现在的要求是输入 id 把 employee 表的对应员工数据查询出来，并且查询出该员工的所处部门信息

JavaBean

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Department dept;
    setter和getter.......
}


public class Department {
    private Integer id;
    private String departmentName;
    setter和getter.......
}

实现

这个要求很明显就要用到两个表，想要把部门信息封装到Employee对象的dept字段需要用到resultMap属性

方法一

 <!-- public Employee getEmployee(int id); -->
<select id="getEmployee" resultMap="emp1">
    select e.*, d.id did, d.department_name
    from employee e,
        department d
    where e.d_id = d.id
    and e.id = #{id}
</select>
<resultMap id="emp1" type="employee">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>
    <result column="did" property="dept.id"/>
    <result column="department_name" property="dept.departmentName"/>
</resultMap>

方法二

<!-- public Employee getEmployee(int id); -->
<select id="getEmployee" resultMap="emp2">
    select e.*, d.id did, d.department_name
    from employee e,
        department d
    where e.d_id = d.id
    and e.id = #{id}
</select>
<resultMap id="emp2" type="employee">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>
    <association property="dept" javaType="department">
        <id column="did" property="id"/>
        <result column="department_name" property="departmentName"/>
    </association>
</resultMap>

测试

     @Test
    public void test1() {
        SqlSession sqlSession = MyTest.getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        System.out.println(mapper.getEmployee(1));
    }

结果

方法

DepartmentMapper.xml

<!-- public Department getDepartment2(int id); -->
<select id="getDepartment2" resultType="department">
    select * from department where id = #{id}
</select>

EmployeeMaper.xml

<!-- public Employee getEmployee2(int id); -->
<!-- 分步查询 -->
<select id="getEmployee2" resultMap="emp3">
    select * from employee where id = #{id}
</select>
<resultMap id="emp3" type="employee">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>
    <association property="dept" select="com.workhah.mapper.department.DepartmentMapper.getDepartment2" column="d_id"/>
</resultMap>

测试

     @Test
    public void test1() {
        SqlSession sqlSession = MyTest.getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        System.out.println(mapper.getEmployee2(1));
    }

结果

要求二

现在的要求是输入 id 把 department 表对应的部门信息查询出来，并且查询该部门下的所有员工信息

JavaBean

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    setter和getter.......
}


public class Department {
    private Integer id;
    private String departmentName;
    private List<Employee> employees;
    setter和getter.......
}

方法

<!--   public Department getDepartment(int id); -->
<select id="getDepartment" resultMap="dep1">
    select d.*, e.id eid, e.last_name, e.email, e.gender
    from department d
        left join employee e on d.id = e.d_id
    where d.id = #{id}
</select>
<resultMap id="dep1" type="department">
    <id column="id" property="id"/>
    <result column="department_name" property="departmentName"/>
    <collection property="employees" ofType="employee">
        <id column="eid" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </collection>
</resultMap>

测试

     @Test
    public void test2() {
        SqlSession sqlSession = MyTest.getSqlSession();
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
        System.out.println(mapper.getDepartment(1));
    }

结果

EmployeeMaper.xml

<!--  public List<Employee> getEmployeeByDid(int did); -->
<select id="getEmployeeByDid" resultType="employee">
    select *
    from employee
    where d_id = #{did}
</select>

DepartmentMapper.xml

<!-- public Department getDepartment3(int id); -->
<select id="getDepartment3" resultMap="dep2">
    select *
    from department
    where id = #{id}
</select>
<resultMap id="dep2" type="department">
    <id column="id" property="id"/>
    <result column="depart_name" property="departName"/>
    <collection property="employees" ofType="employee"
        select="com.workhah.mapper.employee.EmployeeMapper.getEmployeeByDid" column="id"/>
</resultMap>

测试

     @Test
    public void test2() {
        SqlSession sqlSession = MyTest.getSqlSession();
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
        System.out.println(mapper.getDepartment3(1));
    }

结果