Mybatis的联合查询

数据库表结构

department

employee

要求一

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

JavaBean

Copy

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

Copy

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

1、级联属性封装结果集#

实现#

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

方法一#

Copy

<!-- 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>

方法二#

Copy

<!-- 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>

测试#

Copy

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

结果#

2、分步查询#

方法#

DepartmentMapper.xml

Copy

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

EmployeeMaper.xml

Copy

<!-- 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>

测试#

Copy

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

结果#

要求二

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

JavaBean

Copy

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

Copy

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

1、级联属性封装结果集#

方法#

Copy

<!-- 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>

测试#

Copy

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

结果#

2、分步查询#

EmployeeMaper.xml

Copy

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

DepartmentMapper.xml

Copy

<!-- 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>

测试#

Copy

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

结果#