前面的例子都是查询一张表,name查询两张表结果怎么封装呢,这就是关联查询,下面介绍关联查询的几种封装方式
前期准备
在员工信息中添加一条部门信息,标志这个员工是属于哪个部门的.
Employee.java
package com.fish.pojo;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Dapartment dapartment;//添加了一个部门属性
***
***
@Override
public String toString() {
return "Dapartment [id=" + id + ", departmentName=" + departmentName + "]";
}
}
Department.java(新增)
package com.fish.pojo;
public class Dapartment {
private Integer id;
private String departmentName;
***
***
@Override
public String toString() {
return "Dapartment [id=" + id + ", departmentName=" + departmentName + "]";
}
}
tbl_employee(新增d_id:部门id字段)
tbl_dapartment
一, 级联属性封装结果
接口
/**
* 根据员工id查询员工信息以及所属部门的相关信息
* @Author ZFH
* @Date 2019年7月20日
*/
public Employee findEmpAndDept(Integer id);
配置文件
<select id="findEmpAndDept" resultMap="emp2">
select e.*,d.id did,d.department_name from tbl_employee
e left join tbl_department d on e.d_id = d.id where e.id = #{id}
</select>
到了这里我们会有一个疑问,怎么把查询到的部门信息封装到Employe中去呢,这里还是用到了我们的resultMap中的级联查询,配置文件中作如下修改
<resultMap type="com.fish.pojo.Employee" id="emp2">
<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="dapartment.id" />
<result column="department_name" property="dapartment.departmentName" />
</resultMap>
<!-- 使用resultMap -->
<select id="findEmpAndDept" resultMap="emp2">
select e.*,d.id did,d.department_name from tbl_employee
e left join tbl_department d on e.d_id = d.id where e.id = #{id}
</select>
测试方法
@org.junit.Test
public void test02() throws IOException {
//读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = sqlSessionFactory.openSession();
//调用getMapper方法获取该接口的实现类
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee selectEmpById = mapper.findEmpAndDept(1);
System.out.println(selectEmpById);
//关闭Session
openSession.close();
}
运行结果
Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1, dapartment=Dapartment [id=1, departmentName=123]]
二, 使用association标签封装结果
接口
/**
* 根据员工id查询员工信息以及所属部门的相关信息
* @Author ZFH
* @Date 2019年7月20日
*/
public Employee findEmpAndDept2(Integer id);
配置文件
<!-- 使用级联属性封装结果集 -->
<resultMap type="com.fish.pojo.Employee" id="emp3">
<id column="id" property="id" />
<result column="last_name" property="lastName" />
<result column="email" property="email" />
<result column="gender" property="gender" />
<!-- association标签可以指定联合的JavaBean对象 javaType:指定这个属性对象的类型(不能省略) -->
<association property="department"
javaType="com.fish.pojo.Department">
<id column="did" property="id" />
<result column="department_name" property="departmentName" />
</association>
</resultMap>
<select id="findEmpAndDept2" resultMap="emp3">
select e.*,d.id did,d.department_name from tbl_employee
e left join tbl_department d on e.d_id = d.id where e.id = #{id}
</select>
测试类
@org.junit.Test
public void test03() throws IOException {
//读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = sqlSessionFactory.openSession();
//调用getMapper方法获取该接口的实现类
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee selectEmpById = mapper.findEmpAndDept2(1);
System.out.println(selectEmpById);
//关闭Session
openSession.close();
}
运行结果
Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1, department=Department [id=1, departmentName=123]]
三, 使用association分步查询
优点:
可以使用简单的方法实现复杂的功能可以使用延迟加载
接口
DepartmentMapper.java
package com.fish.dao;
import com.fish.pojo.Department;
public interface DepartmentMapper {
/**
* 根据id查询部门信息
* @Author ZFH
* @Date 2019年7月22日
*/
public Department selDepartmentByID(Integer id);
}
EmployeeMapper.java
/**
* 根据员工id查询员工信息以及所属部门的相关信息
*
* @Author ZFH
* @Date 2019年7月20日
*/
public Employee findEmpByIdStep(Integer id);
配置文件
DepartmentMapper.xml
<?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.fish.dao.DepartmentMapper">
<select id="selDepartmentByID"
resultType="com.fish.pojo.Department">
select id, department_name departmentName from tbl_department where id = #{id}
</select>
</mapper>
EmployeeMapper.xml
<!-- 使用association分步查询
1. 先按照员工id查询员工信息
2. 根据员工信息中的d_id去查询部门信息
3. 将部门信息设置到员工信息中
流程: 调用select指定的方法传入column指定的值 查询对象 将结果封装到property对应的属性中
-->
<resultMap type="com.fish.pojo.Employee" id="selEmployeeStep">
<id column="id" property="id" />
<result column="last_name" property="lastName" />
<result column="email" property="email" />
<result column="gender" property="gender" />
<!-- association:定义关联对象的封装规则
select:表名当前属性是调用select指定的方法查询出的结果
column:指定将那一列的值传给这个方法
-->
<association property="department"
select="com.fish.dao.DepartmentMapper.selDepartmentByID"
column="d_id">
</association>
</resultMap>
<select id="findEmpByIdStep" resultMap="selEmployeeStep">
select * from
tbl_employee where id = #{id}
</select>
主配置文件(mybatis-config.xml)
<mappers>
<mapper resource="com/fish/dao/EmployeeMapper.xml" />
<mapper resource="com/fish/dao/DepartmentMapper.xml" />
</mappers>
测试类
@org.junit.Test
public void test04() throws IOException {
//读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = sqlSessionFactory.openSession();
//调用getMapper方法获取该接口的实现类
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee selectEmpById = mapper.findEmpByIdStep(1);
System.out.println(selectEmpById);
//关闭Session
openSession.close();
}
运行结果
Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1, department=Department [id=1, departmentName=123]]
association的延迟加载
延迟加载:每次查询的时候都是将员工信息与部门信息一起查询出来,使用延迟加载之后可以只查员工信息,部门的相关信息只有在用到的时候才会查询
实现:上面的方法不需改动,只需要在主配置文件里开启相应的配置即可
主配置文件(mybatis-config.xml)
<settings>
<!-- 开启驼峰命名 -->
<setting name="mapUnderscoreToCamelCase" value="false" />
<!-- 开启懒加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 关闭侵入式懒加载 即实现按需加载 -->
<setting name="aggressiveLazyLoading" value="flase"/>
</settings>
四, collection嵌套结果集
需求:一个部门中包含多个员工,查询部门信息的时候将员工信息一块查出来
Department.java
package com.fish.pojo;
import java.util.List;
public class Department {
private Integer id;
private String departmentName;
private List<Employee> employees;
}
接口(DepartmentMapper.java)
package com.fish.dao;
import com.fish.pojo.Department;
public interface DepartmentMapper {
/**
* 使用collection嵌套结果集查询
* @Author ZFH
* @Date 2019年7月24日
*/
public Department selDeptByCol(Integer id);
}
配置文件(DepartmentMapper.xml)
<resultMap type="com.fish.pojo.Department" id="myDemp">
<id column="d_id" property="id"/>
<result column="department_name" property="departmentName"/>
<!--
collection:定义关联集合类型的属性的封装规则
property:集合属性名称
ofType:指定集合的元素的类型
-->
<collection property="employees" ofType="com.fish.pojo.Employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="id" property="id" />
<result column="last_name" property="lastName" />
<result column="email" property="email" />
<result column="gender" property="gender" />
</collection>
</resultMap>
<select id="selDeptByCol" resultMap="myDemp">
select d.id d_id,department_name,e.* from tbl_department d LEFT JOIN tbl_employee e on d.id = e.d_id where d.id = 1
</select>
测试类
@org.junit.Test
public void test05() throws IOException {
//读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = sqlSessionFactory.openSession();
//调用getMapper方法获取该接口的实现类
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department selDeptByCol = mapper.selDeptByCol(1);
System.out.println(selDeptByCol);
System.out.println(selDeptByCol.getEmployees());
//关闭Session
openSession.close();
}
运行结果
Department [id=1, departmentName=123]
[Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1], Employee [id=5, lastName=22, email=lisi@163.com, gender=1]]
collection分步查询(跟association分布查询相似)
接口(DepartmentMapper.java)
/**
* 分步查询
* 使用collection嵌套结果集查询
* @Author ZFH
* @Date 2019年7月24日
*/
public Department selDeptByColStep(Integer id);
EmployeeMapper.java
/**
* 根据部门id查询员工信息
*
* @Author ZFH
* @Date 2019年7月20日
*/
public Employee findEmpByDeptId(Integer id);
配置文件(EmployeeMapper.xml)
<select id="findEmpByDeptId" resultType="com.fish.pojo.Employee">
select id,last_name lastName,gender,email,d_id from
tbl_employee where id = #{id}
</select>
DepartmentMapper.xml
<resultMap type="com.fish.pojo.Department" id="myDemp2">
<id column="id" property="id"/>
<result column="department_name" property="departmentName"/>
<collection property="employees"
select="com.fish.dao.EmployeeMapper.findEmpByDeptId"
column="id"></collection>
</resultMap>
<select id="selDeptByColStep" resultMap="myDemp2">
select * from tbl_department where id = #{id}
</select>
测试类
@org.junit.Test
public void test06() throws IOException {
//读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = sqlSessionFactory.openSession();
//调用getMapper方法获取该接口的实现类
DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
Department selDeptByCol = mapper.selDeptByColStep(1);
System.out.println(selDeptByCol);
System.out.println(selDeptByCol.getEmployees());
//关闭Session
openSession.close();
}
运行结果
Department [id=1, departmentName=123]
[Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1]]
使用column传递多个参数
要想使用column传递多个参数的话,可以将参数封装成一个map,例如 column="{key1 = column1,key2 = column2}
局部懒加载(fetchType)
如果你的某一个方法不想使用懒加载,而你又不想修改主配置文件,那么你可以通过修改collection标签或association标签的fetchType属性的值来实现,fetchType取值:lazy(懒加载),eager(立即加载)
鉴别器(discriminator)
接口
EmployeeMapper.java
/**
* 根据员工id查询员工信息以及所属部门的相关信息
* 使用鉴别器
*
* @Author ZFH
* @Date 2019年7月20日
*/
public Employee findEmpByIdStep1(Integer id);
DepartmentMapper.java
/**
* 根据id查询部门信息
* @Author ZFH
* @Date 2019年7月22日
*/
public Department selDepartmentByID(Integer id);
配置文件
DepartmentMapper.xml
<select id="selDepartmentByID"
resultType="com.fish.pojo.Department">
select id, department_name departmentName from tbl_department where id = #{id}
</select>
EmployeeMapper.xml
<resultMap type="com.fish.pojo.Employee" id="selEmployeeStep1">
<id column="id" property="id" />
<result column="last_name" property="lastName" />
<result column="email" property="email" />
<result column="gender" property="gender" />
<!-- discriminator鉴别器:mybais可以使用discriminator去判断某一列的值,然后根据这个值去改变封装规则
javaType:列值对应的java类型
column:指定判定的列名
-->
<discriminator javaType="string" column="gender">
<!-- 如果是女生,正常封装 resultType:指定封装的结果类型 -->
<case value="0" resultType="com.fish.pojo.Employee">
<association property="department"
select="com.fish.dao.DepartmentMapper.selDepartmentByID"
column="d_id">
</association>
</case>
<!-- 如果是男生,email取last_name的值 -->
<case value="1" resultType="com.fish.pojo.Employee">
<id column="id" property="id" />
<result column="last_name" property="lastName" />
<result column="last_name" property="email" />
<result column="gender" property="gender" />
</case>
</discriminator>
<!-- association:定义关联对象的封装规则
select:表名当前属性是调用select指定的方法查询出的结果
column:指定将那一列的值传给这个方法
-->
</resultMap>
<select id="findEmpByIdStep1" resultMap="selEmployeeStep1">
select * from
tbl_employee where id = #{id}
</select>
测试类
@org.junit.Test
public void test07() throws IOException {
//读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
//获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = sqlSessionFactory.openSession();
//调用getMapper方法获取该接口的实现类
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee findEmpByIdStep1 = mapper.findEmpByIdStep1(5);
System.out.println(findEmpByIdStep1);
System.out.println(findEmpByIdStep1.getDepartment());
//关闭Session
openSession.close();
}
运行结果
---女
Employee [id=5, lastName=22, email=lisi@163.com, gender=0]
Department [id=1, departmentName=123]
---男
Employee [id=1, lastName=222, email=222, gender=1]
null
