之前我们学习过,在关联查询中,我们的处理办法是在表连接之后,查出数据,分别做出映射。
<resultMap id="Department_Employee" type="Department">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="location" property="location"></result>
<collection property="employees" ofType="Employee">
<id column="emp_id" property="id"></id>
<result column="emp_name" property="name"></result>
<result column="salary" property="salary"></result>
</collection>
</resultMap>
<select id="queryDepartmentById" resultMap="Department_Employee">
select t_departments.id,t_departments.name,t_departments.location,
t_employees.id as emp_id,t_employees.name as emp_name,t_employees.salary
from t_departments join t_employees
on t_departments.id = t_employees.dept_id
where t_departments.id = #{id}
</select>
而嵌套查询是,在关联查询中,我们不再是一次查询,重新定义封装规则,而是分两次查询,分别封装。
以查询部门以及其所有的员工信息为例:
1. 我们简化部门的查询方法映射,让它只负责填写部门的基本信息
<select id="queryDepartmentById" resultMap="Department_Employee">
select id,name,location
from t_departments
where id = #{id}
</select>
2. 我们在员工的DAO中增加一个根据部门ID查询员工信息的方法
List<Employee> queryEmployeesByDeptId(@Param("deptId") Integer deptId);
3. 定义mapper.xml对应的SQL
<select id="queryEmployeesByDeptId" resultType="Employee">
select id,name,salary
from t_employees
where dept_id = #{deptId}
</select>
4. 在部门DAO对应的mapper.xml中调用这个方法
<resultMap id="Department_Employee" type="Department">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="location" property="location"></result>
<collection property="employees" ofType="Employee"
select="com.zt.DAO.EmployeeDAO.queryEmployeesByDeptId"
column="id"/>
</resultMap>
执行流程为:首先执行queryDepartmentById,并按照resultMap的规则进行基础封装,而内部集合的结果需要再调用一个方法queryEmployeesByDeptId获得,传递的参数就是当前查出来的id,即在映射规则中定义的id,它又有自己的mapper定义,于是做了两次查询得到结果。
5. 编写测试
public static void main(String[] args) {
DepartmentDAO mapper = MybatisUtil.getMapper(DepartmentDAO.class);
Department department = mapper.queryDepartmentById(1);
System.out.println(department);
List<Employee> employees = department.getEmployees();
for (Employee employee : employees) {
System.out.println(employee);
}
}
可以看到执行了两条SQL语句,这样做的好处在于,不会因为表连接导致产生一个过大的中间结果。如果我们A表有m条数据,B条有n条数据,那么连接后会产生m*n条数据,再在它的基础上进行修改。而这样做,我们可以现在A表上查询一次,再利用结果对B表查询一次,即不会有中间结果,查询次数也从m*n降低到了m+n。
举一反三:现在我们反过来写查员工顺带查部门
EmployeeMapper.xml:
<resultMap id="Empolyee_Department" type="Employee">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="salary" property="salary"></result>
<association property="department" javaType="Department"
select="com.zt.DAO.DepartmentDAO.queryDepartmentById"
column="dept_id"/>
</resultMap>
<select id="queryEmployeeById" resultMap="Empolyee_Department">
select id,name,salary,dept_id
from t_employees
where id = #{id}
</select>
编写测试:
public static void main(String[] args) {
EmployeeDAO mapper = MybatisUtil.getMapper(EmployeeDAO.class);
Employee employee = mapper.queryEmployeeById(1);
System.out.println(employee);
Department department = employee.getDepartment();
System.out.println(department);
}
值得注意的是,因为DepartmentDAO本身就是根据ID查询的方法,因此我们直接在员工根据ID查询的方法上绑定了他,而我们先前又对部门根据ID查询的方法绑定了查询一批员工的方法,因此这里会执行三个SQL。
延迟加载:尽管如此绑定可以简化SQL,便于查询操作,但是这种嵌套查询是自动触发的,有时候我们并不需要查那些信息,Mybatis提供了延迟加载的选项,如果我们没有使用到那些级联数据,就不会触发嵌套查询,有利于提高速度,节省内存。
1. 在主配置文件中设置
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
2. 实验
A.查部门不使用员工数据
public static void main(String[] args) {
DepartmentDAO mapper = MybatisUtil.getMapper(DepartmentDAO.class);
Department department = mapper.queryDepartmentById(1);
System.out.println(department.getId());
/*List<Employee> employees = department.getEmployees();
for (Employee employee : employees) {
System.out.println(employee);
}*/
}
[这里有个很大的坑]:如果我们直接打印查出来的department对象,实际上会触发嵌套查询,但是我们的department覆写的toString()方法里根本就没有使用到employees属性,这可能就是Mybatis默认直接使用类那么类的数据必须齐全,因此触发查询了吧:
@Override
public String toString() {
return "Department{" +
"id=" + id +
", name='" + name + '\'' +
", location='" + location + '\'' +
'}';
}
B. 查部门使用员工数据
public static void main(String[] args) {
DepartmentDAO mapper = MybatisUtil.getMapper(DepartmentDAO.class);
Department department = mapper.queryDepartmentById(1);
System.out.println(department.getId());
List<Employee> employees = department.getEmployees();
for (Employee employee : employees) {
System.out.println(employee);
}
}