package com.atgui.spring.mybatis.entities;
import java.util.List;
public class Department {
private Integer id;
private String name;
private List<Employee> emps;
@Override
public String toString() {
return "Department [id=" + id + ", name=" + name + ", emps=" + emps + "]";
}
public List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.atgui.spring.mybatis.entities;
public class Employee {
private Integer id;
private String lastName;
private String email;
private double salary;
private Integer deptId;
private Department dept;
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
public Employee() {
super();
}
public Employee(Integer id, String lastName, String email, double salary, Integer deptId) {
super();
this.id = id;
this.lastName = lastName;
this.email = email;
this.salary = salary;
this.deptId = deptId;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
@Override
public String toString() {
return "Employee2 [id=" + id + ", lastName=" + lastName + ", email=" + email + ", salary=" + salary
+ ", deptId=" + deptId + ", dept=" + dept + "]";
}
}
<?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">
<!-- namespace:要实现的接口的全类名 -->
<mapper namespace="com.atguigu.mybatis.mapper.DepartmentMapper">
<select id="getDepartmentById" resultType="com.atguigu.mybatis.entities.Department">
select id,name deptName from departments where id = #{deptId}
</select>
<!-- 需求二:在将部门查出来的同时将部门下所有的员工也查询出来 -->
<select id="getDepartmentContainsEmps" resultMap="customMap">
SELECT d.id d_id,d.name d_name,e.*
FROM departments d
LEFT JOIN employees e
ON d.id = e.dept_id
WHERE d.id = #{id};
</select>
<!-- 自定义结果集 -->
<resultMap type="com.atguigu.mybatis.entities.Department" id="customMap">
<!-- 映射主键 -->
<id column="d_id" property="id"/>
<!-- 映射其他列 -->
<result column="d_name" property="deptName"/>
<!-- 通过collection进行联合查询
property:指定要映射的属性的属性名
ofType:指定集合中泛型的类型
-->
<collection property="emps" ofType="com.atguigu.mybatis.entities.Employee">
<!-- 映射主键 -->
<id column="id" property="id"/>
<!-- 映射其他列 -->
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="salary" property="salary"/>
</collection>
</resultMap>
<!-- 通过collection进行分步查询
1.根据部门的id查询出Department对象
2.根据Department的id查询出来部门下所有的员工
3.将员工设置到Department对象中
-->
<select id="getDepartmentContainsEmpsByStep" resultMap="customMap2">
select id,name deptName from departments where id = #{id}
</select>
<!-- 自定义结果集 -->
<resultMap type="com.atguigu.mybatis.entities.Department" id="customMap2">
<!-- 映射主键 -->
<id column="id" property="id"/>
<!-- 映射其他列 -->
<result column="deptName" property="deptName"/>
<!-- 通过connection进行分步查询 -->
<collection property="emps" select="com.atguigu.mybatis.mapper.EmployeeMapper.getEmployeesByDeptId"
column="id"></collection>
</resultMap>
</mapper>
//联合查询部门和部门下所有员工
@Test
public void getDepartmentAndAllEmployeeById() throws IOException {
//1.创建SQLSessionFactory对象
SqlSessionFactory sqlSessionFactory = getSessionFactory();
//回去sqlsession ,相当于jdbc中的connection
SqlSession session = sqlSessionFactory.openSession();
try {
DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);
Department departmentAndAllEmployeeById = mapper.getDepartmentAndAllEmployeeById(3);
System.out.println(departmentAndAllEmployeeById);
session.commit();
} finally {
session.close();
}
}
分步查询
<select id="getEmployeeById" resultType="com.atgui.spring.mybatis.entities.Employee">
select * from employees where id=#{id}
</select>
<select id="getDepartmentAndAllEmployeeByStep" resultMap="customMap2">
select * from departments where id=#{id}
</select>
<resultMap type="com.atgui.spring.mybatis.entities.Department"
id="customMap2">
<id column="d_id" property="id"></id>
<result column="d_name" property="name" />
<collection property="emps"
select="com.atgui.spring.mybatis.mapper.EmployeeMapper.getEmployeeById"
column="id">
</collection>
</resultMap>