MyBatis实现多表查询
前面已经使用MyBatis完成了对Emp表的CRUD操作,不管是使用SqlSession直接操作,还是使用Mapper代理方式,都只是完成了对单个数据库表的操作。这肯定是远远不够的。
在实际开发中,经常会将来自多张表的数据在一个位置显示。比如查询并显示的员工信息中会有来自部门表、岗位表的数据,而后台一般是定义一个方法:
1.关联查询
1.1手动处理映射关系resultMap
实体类
public class Emp implements Serializable {
private Integer empno;
private String name;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
}
映射文件
<mapper namespace="com.msb.mapper.EmpMapper">
<!--手动处理数据库查询字段和封装实体类属性之间的映射关系
1 主键一般使用id属性
2 当属性名和查询出的数据表字段名相同 可以不写映射关系
-->
<resultMap id="empMap" type="emp">
<!--<id property="empno" column="empno"></id>-->
<result property="name" column="ename"></result>
<!--<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>-->
</resultMap>
<select id="findByEmpno" resultMap="empMap" >
select * from emp where empno =#{empno}
</select>
</mapper>
数据准备: 创建项目表和项目记录表
项目表、项目记录表
-- 项目表
CREATE TABLE `projects` (
`pid` int(2) NOT NULL AUTO_INCREMENT,
`pname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`money` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `projects` VALUES (1, ' ***大学OA', 500000);
INSERT INTO `projects` VALUES (2, '学生选课系统', 100000);
INSERT INTO `projects` VALUES (3, '讲师测评系统', 20000);
INSERT INTO `projects` VALUES (4, '线上问答系统 ', 20000);
-- 项目记录表
CREATE TABLE `projectrecord` (
`empno` int(4) NOT NULL,
`pid` int(2) NOT NULL,
PRIMARY KEY (`empno`, `pid`) USING BTREE,
INDEX `fk_project_pro`(`pid`) USING BTREE,
CONSTRAINT `fk_emp_pro` FOREIGN KEY (`empno`) REFERENCES `emp` (`EMPNO`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_project_pro` FOREIGN KEY (`pid`) REFERENCES `projects` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `projectrecord` VALUES (7369, 1);
INSERT INTO `projectrecord` VALUES (7521, 1);
INSERT INTO `projectrecord` VALUES (7369, 2);
INSERT INTO `projectrecord` VALUES (7499, 2);
INSERT INTO `projectrecord` VALUES (7521, 2);
INSERT INTO `projectrecord` VALUES (7369, 3);
INSERT INTO `projectrecord` VALUES (7499, 3);
INSERT INTO `projectrecord` VALUES (7521, 3);
INSERT INTO `projectrecord` VALUES (7369, 4);
INSERT INTO `projectrecord` VALUES (7499, 4);
1.2一对一关联查询
需求:根据编号查询员工信息及所在的部门信息
实体类添加一个部门作为属性
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Emp implements Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
// 组合一个Dept对象作为自己的属性
private Dept dept;
}
接口
public interface EmpMapper {
/**
* 根据员工编号查询员工的所有信息并携带所在的部门信息
* @param empno 要查询的员工编号
* @return Emp对象,组合了Dept对象作为属性,对部门信息进行存储
*/
Emp findEmpJoinDeptByEmpno(int empno);
}
映射文件
<?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.msb.mapper.EmpMapper">
<!--Emp findEmpJoinDeptByEmpno(int empno);-->
<resultMap id="empJoinDept" type="emp">
<!--设置emp本身的八个属性的映射关系-->
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
<!--
association 处理一对一
封装一对一信息关系的标签
property emp类的属性名
javaType 用哪个类的对象给属性赋值
-->
<association property="dept" javaType="dept">
<id column="deptno" property="deptno"></id>
<result column="dname" property="dname"></result>
<result column="loc" property="loc"></result>
</association>
</resultMap>
<select id="findEmpJoinDeptByEmpno" resultMap="empJoinDept" >
select * from
emp e
left join dept d
on e.deptno =d.deptno
where empno = #{empno}
</select>
</mapper>
测试代码
@Test
public void testOneToOne() throws ParseException {
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.findEmpJoinDeptByEmpno(7499);
System.out.println(emp);
}
1.3一对多关联查询
根据部门号查询部门信息及该部门的所有员工信息
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
private Integer deptno;
private String dname;
private String loc;
// 组合一个Emp的List集合作为属性
private List<Emp> empList;
}
接口
package com.msb.mapper;
import com.msb.pojo.Dept;
public interface DeptMapper {
/**
* 根据部门编号查询部门信息及该部分的所有员工信息
* @param deptno 要查询的部门编号
* @return Dept对象,内部组合了一个Emp的List属性用于封装部门的所有员工信息
*/
Dept findDeptJoinEmpsByDeptno(int deptno);
}
映射文件
<?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.msb.mapper.DeptMapper">
<!--Dept findDeptJoinEmpsByDeptno(int deptno);-->
<resultMap id="deptJoinEmps" type="dept">
<id column="deptno" property="deptno"></id>
<result column="dname" property="dname"></result>
<result column="loc" property="loc"></result>
<!--处理一对多关系的标签-->
<collection property="empList" ofType="emp" >
<!--设置emp本身的八个属性的映射关系-->
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
</collection>
</resultMap>
<select id="findDeptJoinEmpsByDeptno" resultMap="deptJoinEmps">
select * from dept d left join emp e on d.deptno =e.deptno where d.deptno =#{deptno}
</select>
</mapper>
测试代码
@Test
public void testOneToMany() throws ParseException {
DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = mapper.findDeptJoinEmpsByDeptno(20);
System.out.println(dept);
System.out.println("---------");
List<Emp> empList = dept.getEmpList();
empList.forEach(System.out::println);
}
1.4多对多关联查询
根据项目编号查询项目信息,以及参与到该项目之中的所有的员工信息
实体类
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Project implements Serializable {
private Integer pid;
private String pname;
private Integer money;
// 组合一个ProjectRecord对象集合作为属性
private List<ProjectRecord> projectRecords;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProjectRecord implements Serializable {
private Integer empno;
private Integer pid;
// 组合一个Emp对象作为属性
private Emp emp;
}
接口
package com.msb.mapper;
import com.msb.pojo.Emp;
import com.msb.pojo.Project;
public interface ProjectMapper {
/**
* 根据项目编号查询一个项目信息及参与该项目的所有员工信息
* @param pid 项目编号
* @return 所有信息封装的Project对象
*/
Project findProjectJoinEmpsByPid(int pid);
}
映射文件
<?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.msb.mapper.ProjectMapper">
<!--Project findProjectJoinEmpsByPid(int pid);-->
<resultMap id="projectJoinEmps" type="project">
<id column="pid" property="pid"></id>
<result column="pname" property="pname"></result>
<result column="money" property="money"></result>
<!--一对多 集合属性 collection-->
<collection property="projectRecords" ofType="projectRecord">
<id column="empno" property="empno"></id>
<id column="pid" property="pid"></id>
<!--一对一 -->
<association property="emp" javaType="emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
</association>
</collection>
</resultMap>
<select id="findProjectJoinEmpsByPid" resultMap="projectJoinEmps">
select * from
project p
left join projectrecord pr
on p.pid = pr.pid
left join emp e
on e.empno = pr.empno
where p.pid= #{pid}
</select>
</mapper>
测试代码
@Test
public void testManyToMany() throws ParseException {
ProjectMapper mapper = sqlSession.getMapper(ProjectMapper.class);
Project project = mapper.findProjectJoinEmpsByPid(2);
System.out.println(project.getPid());
System.out.println(project.getPname());
System.out.println(project.getMoney());
List<ProjectRecord> projectRecords = project.getProjectRecords();
for (ProjectRecord projectRecord : projectRecords) {
Emp emp = projectRecord.getEmp();
System.out.println(emp);
}
}
2.级联查询
级联查询,顾名思义,就是利于数据库表间的外键关联关系进行自动的级联查询操作。使用MyBatis实现级联查询,除了实体类增加关联属性外,还需要在映射文件中进行配置。
2.1立即加载
功能1:查询所有员工的信息(多对一关联)
经过对比,发现经过在映射文件中配置,测试类的代码大大简化了,无序手动进行关联查询和组装数据了。
功能2:查询10号部门及其该部门员工信息。
Dept和Emp实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
private Integer deptno;
private String dname;
private String loc;
// 当前部门下的所有员工对象的List集合
private List<Emp> empList;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
}
DeptMapper和 EmpMapper接口
package com.msb.mapper;
import com.msb.pojo.Dept;
public interface DeptMapper {
Dept findDeptByDeptno(int deptno);
}
package com.msb.mapper;
import com.msb.pojo.Emp;
import java.util.List;
/**
* @Author: Ma HaiYang
* @Description: MircoMessage:Mark_7001
*/
public interface EmpMapper {
List<Emp> findEmpsByDeptno(int deptno);
}
DeptMapper和EmpMapper映射文件
<?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.msb.mapper.DeptMapper">
<!--Dept findDeptByDeptno(int deptno);
select="com.msb.mapper.EmpMapper.findEmpsByDeptno" 调用的另一个SQL语句
javaType="list" 实体类的属性数据类型
column="deptno" 给另一个SQL语句传入的参数列
jdbcType="INTEGER" 参数对应JDBC的数据类型
fetchType="eager" 加载方式 eager 积极加载 lazy延迟加载-->
<resultMap id="deptJoinEmps" type="dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<collection property="empList"
select="com.msb.mapper.EmpMapper.findEmpsByDeptno"
javaType="list"
column="deptno"
jdbcType="INTEGER"
fetchType="eager">
</collection>
</resultMap>
<select id="findDeptByDeptno" resultMap="deptJoinEmps">
select * from dept where deptno =#{deptno}
</select>
</mapper>
<?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.msb.mapper.EmpMapper">
<!--List<Emp> findEmpsByDeptno(int deptno);-->
<select id="findEmpsByDeptno" resultType="emp">
select * from emp where deptno =#{deptno}
</select>
</mapper>
测试代码
@Test
public void testFindByDetpno() {
DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
Dept dept = deptMapper.findDeptByDeptno(20);
System.out.println(dept.getDname());
System.out.println(dept.getDeptno());
System.out.println(dept.getLoc());
List<Emp> empList = dept.getEmpList();
empList.forEach(System.out::println);
}
2.2延迟加载
**延迟加载,又称按需加载。**延迟加载的内容等到真正使用时才去进行加载(查询)。多用在关联对象或集合中。
延迟加载的好处:先从单表查询、需要时再从关联表去关联查询,大大降低数据库在单位时间内的查询工作量,将工作在时间上的分配更加均匀,而且单表要比关联查询多张表速度要快。
延迟加载的设置
第一步:全局开关:在sqlMapConfig.xml中打开延迟加载的开关。配置完成后所有的association和collection元素都生效
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="true"/>
</settings>
lazyLoadingEnabled:是否开启延迟加载。是Mybatis是否启用懒加载的全局开关。当开启时,所有关联对象都会延迟加载。特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态
aggressiveLazyLoading:当开启时,任何方法的调用都会懒加载对象的所有属性。否则,每个属性会按需加载,
第二步:分开关:指定的association和collection元素中配置fetchType属性。eager
:表示立刻加载;lazy
:表示延迟加载。将覆盖全局延迟设置。
2.3多表查询总结与扩展
resultMap中的常见属性
属性 | 描述 |
---|---|
property | 需要映射到JavaBean 的属性名称。 |
javaType | property的类型,一个完整的类名,或者是一个类型别名。如果你匹配的是一个JavaBean,那MyBatis 通常会自行检测到。 |
column | 数据表的列名或者列别名。 |
jdbcType | column在数据库表中的类型。这个属性只在insert,update 或delete 的时候针对允许空的列有用。JDBC 需要这项,但MyBatis 不需要。 |
typeHandler | 使用这个属性可以覆写类型处理器,实现javaType、jdbcType之间的相互转换。一般可以省略,会探测到使用的什么类型的typeHandler进行处理 |
fetchType | 自动延迟加载 |
select | association、collection的属性,使用哪个查询查询属性的值,要求指定namespace+id的全名称 |
ofType | collection的属性,指明集合中元素的类型(即泛型类型) |
级联查询和多表查询的比较及其选择
级联查询 | 多表查询 | |
---|---|---|
SQL语句数量 | 多条 | 一条 |
性能 | 性能低 | 性能高 |
延迟加载 | 立即加载、延迟加载 | 只有立即加载 |
灵活性 | 更灵活 | 不灵活 |
SQL难易度 | 简单 | 复杂 |
选择依据 | 简单、灵活 | 高性能 |
ResultType和ResultMap使用场景
-
如果你做的是单表的查询并且封装的实体和数据库的字段一一对应 resultType
-
如果实体封装的属性和数据库的字段不一致 resultMap
-
使用N+1级联查询的时候 resultMap
-
使用的是多表的连接查询 resultMap
一对一关联映射的实现
-
实例:学生和学生证、雇员和工牌
-
数据库层次:主键关联或者外键关联(参看之前内容)
-
MyBatis层次:在映射文件的设置双方均使用association即可,用法相同
多对多映射的实现
-
实例:学生和课程、用户和角色
-
数据库层次:引入一个中间表将一个多对多转为两个一对多
-
MyBatis层次
方法1:在映射文件的设置双方均使用collection即可,不用引入中间类
方法2:引入中间类和中间类的映射文件,按照两个一对多处理
自关联映射
-
实例:Emp表中的员工和上级。一般是一对多关联
-
数据库层次:外键参考当前表的主键(比如mgr参考empno)
-
MyBatis层次:按照一对多处理,但是增加的属性都写到一个实体类中,增加的映射也都写到一个映射文件中