MyBatis——动态SQL，MyBatis实现多表查询

木鱼-

已于 2023-09-18 15:23:29 修改

阅读量571

点赞数

文章标签： mybatis java 数据库

于 2023-09-18 15:04:49 首次发布

动态SQL

经常遇到很多按照很多查询条件进行查询的情况，比如京东根据不同的条件筛选商品。其中经常出现很多条件不取值的情况，在后台应该如何完成最终的SQL语句呢？

如果采用JDBC进行处理，需要根据条件是否取值进行SQL语句的拼接，一般情况下是使用StringBuilder类及其append方法实现，还是有些繁琐的。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

MyBatis在简化操作方法提出了动态SQL功能，将使用Java代码拼接SQL语句，改变为在XML映射文件中截止标签拼接SQL语句。相比而言，大大减少了代码量，更灵活、高度可配置、利于后期维护。

MyBatis中动态SQL是编写在mapper.xml中的，其语法和JSTL类似，但是却是基于强大的OGNL表达式实现的。

MyBatis也可以在注解中配置SQL，但是由于注解功能受限，尤其是对于复杂的SQL语句，可读性很差，所以较少使用。

1.if标签
接口

public interface EmpMapper2 {
List<Emp> findByCondition(Emp emp);
}
SQL语句

<?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">

<select id="findByCondition" resultType="emp">
select * from emp where 1=1
<if test="empno != null">
and empno =#{empno}
</if>
<if test="ename != null and ename != ''">
and ename like concat('%',#{ename},'%')
</if>
<if test="job != null and job != ''">
and job =#{job}
</if>
<if test="mgr != null">
and mgr =#{mgr}
</if>
<if test="hiredate != null">
and hiredate =#{hiredate}
</if>
<if test="sal != null">
and sal =#{sal}
</if>
<if test="comm != null">
and comm =#{comm}
</if>
<if test="deptno != null">
and deptno =#{deptno}
</if>

</select>

</mapper>
测试代码

public static void main(String[] args) {
SqlSession sqlSession = MyBatisUtil.getSqlSession(false);
EmpMapper2 mapper = sqlSession.getMapper(EmpMapper2.class);
Emp condition =new Emp();
/* condition.setDeptno(20);*/
/* condition.setSal(3000.0);*/
/*condition.setHiredate(new java.sql.Date(81,1,22));*/
condition.setComm(0.0);
condition.setDeptno(20);
List<Emp> emps = mapper.findEmpByCondition(condition);
for (Emp e:emps
) {
System.out.println(e);
}
}
2.where标签
用于处理where关键字和and

<select id="findEmpByCondition" resultType="emp">
select * from emp
<where>
<if test="empno != null">
and empno= #{empno}
</if>
<if test="ename != null and ename != ''">
and ename= #{ename}
</if>
<if test="job != null and job != ''">
and job= #{job}
</if>
<if test="mgr != null ">
and mgr= #{mgr}
</if>
<if test="hiredate != null ">
and hiredate= #{hiredate}
</if>
<if test="sal != null">
and sal= #{sal}
</if>
<if test="comm != null ">
and comm =#{comm}
</if>
<if test="deptno != null ">
and deptno= #{deptno}
</if>
</where>
</select>

Choose标签Choose标签CCCCCCCCCC
3.Choose标签
前面的when条件成立后面的 when就不再判断了，和where标签的区别

<select id="findEmpByCondition2" resultType="emp">
select * from emp
<where>
<choose>
<when test="empno != null">
and empno= #{empno}
</when>
<when test="ename != null and ename != ''">
and ename= #{ename}
</when>
<when test="job != null and job != ''">
and job= #{job}
</when>
<when test="mgr != null ">
and mgr= #{mgr}
</when>
<when test="hiredate != null ">
and hiredate= #{hiredate}
</when>
<when test="sal != null">
and sal= #{sal}
</when>
<when test="comm != null ">
and comm =#{comm}
</when>
<when test="deptno != null ">
and deptno= #{deptno}
</when>
</choose>
</where>
</select>

4.Set标签
接口

int updateEmpByCondtion(Emp emp);
SQL语句

<update id="updateEmpByCondtion" >
update emp
<set>
<if test="ename != null and ename != '' ">
, ename =#{ename}
</if>
<if test="job != null and ename != '' ">
, job =#{job}
</if>
<if test="mgr != null ">
, mgr =#{mgr}
</if>
<if test="hiredate != null ">
, hiredate =#{hiredate}
</if>
<if test="sal != null ">
, sal =#{sal}
</if>
<if test="comm != null ">
, comm =#{comm}
</if>
<if test="deptno != null ">
, deptno =#{deptno}
</if>
</set>
where empno =#{empno}
</update>

5.Trim标签
Trim 标签处理 set

接口

int updateEmpByCondtion(Emp emp);
SQL语句

<update id="updateEmpByCondition2" >
update emp

<trim prefix="set" suffixOverrides="," >
<if test="ename != null and ename != ''">
ename= #{ename},
</if>
<if test="job != null and job != ''">
job= #{job},
</if>
<if test="mgr != null ">
mgr= #{mgr},
</if>
<if test="hiredate != null ">
hiredate= #{hiredate},
</if>
<if test="sal != null">
sal= #{sal},
</if>
<if test="comm != null ">
comm =#{comm},
</if>
<if test="deptno != null ">
deptno= #{deptno},
</if>
</trim>
where empno = #{empno}
</update>
测试代码

Trim标签处理where

<select id="findEmpByCondition" resultType="emp">
select * from emp
<trim prefix="where" prefixOverrides="and">
<if test="empno != null">
and empno= #{empno}
</if>
<if test="ename != null and ename != ''">
and ename= #{ename}
</if>
<if test="job != null and job != ''">
and job= #{job}
</if>
<if test="mgr != null ">
and mgr= #{mgr}
</if>
<if test="hiredate != null ">
and hiredate= #{hiredate}
</if>
<if test="sal != null">
and sal= #{sal}
</if>
<if test="comm != null ">
and comm =#{comm}
</if>
<if test="deptno != null ">
and deptno= #{deptno}
</if>
</trim>
</select>

6.Bind标签
一般用于处理模糊查询的模板

接口

SQL语句

7.Sql标签
<sql id="empColumn">empno,ename,job,mgr,hiredate,sal,comm,deptno</sql>
<sql id="baseSelect">select <include refid="empColumn"></include> from emp</sql>

<select id="findByCondition" resultType="emp">
<include refid="baseSelect"></include>
<trim prefix="where" prefixOverrides="and">
<if test="empno != null">
and empno =#{empno}
</if>
<if test="ename != null and ename != ''">
<bind name="likePattern" value="'%'+ename+'%'"/>
and ename like #{likePattern}
</if>
<if test="job != null and job != ''">
and job =#{job}
</if>
<if test="mgr != null">
and mgr =#{mgr}
</if>
<if test="hiredate != null">
and hiredate =#{hiredate}
</if>
<if test="sal != null">
and sal =#{sal}
</if>
<if test="comm != null">
and comm =#{comm}
</if>
<if test="deptno != null">
and deptno =#{deptno}
</if>
</trim>

</select>
8.Foreach标签
接口

SQL语句

<!--List<Emp> findByEmpnos1(int[] empnos);
collection="" 遍历的集合或者是数组
参数是数组,collection中名字指定为array
参数是List集合,collection中名字指定为list
separator="" 多个元素取出的时候用什么文字分隔
open="" 以什么开头
close="" 以什么结尾
item="" 中间变量名
for(Person per:PersonList)

-->
<select id="findByEmpnos1" resultType="emp">
select * from emp where empno in
<foreach collection="array" separator="," open="(" close=")" item="deptno">
#{deptno}
</foreach>
</select>

<select id="findByEmpnos2" resultType="emp">
select * from emp where empno in
<foreach collection="list" separator="," open="(" close=")" item="deptno">
#{deptno}
</foreach>
</select>
————————————————
版权声明：本文为CSDN博主「隔绝知识的饭桶」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/m0_68519425/article/details/130978389

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

<resultMap id="empMap" type="emp">

<result property="name" column="ename"></result>

</resultMap>
<select id="findByEmpno" resultMap="empMap" >
select * from emp where empno =#{empno}
</select>
</mapper>
数据准备: 创建项目表和项目记录表

项目表

项目记录表

sql语句

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

<resultMap id="empJoinDept" type="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="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;

/**
* @Author: Ma HaiYang
* @Description: MircoMessage:Mark_7001
*/
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">

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

/**
* @Author: Ma HaiYang
* @Description: MircoMessage:Mark_7001
*/
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">

<resultMap id="projectJoinEmps" type="project">
<id column="pid" property="pid"></id>
<result column="pname" property="pname"></result>
<result column="money" property="money"></result>

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

/**
* @Author: Ma HaiYang
* @Description: MircoMessage:Mark_7001
*/
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">

</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">

<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>
azyLoadingEnabled:是否开启延迟加载。是Mybatis是否启用懒加载的全局开关。当开启时，所有关联对象都会延迟加载。特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态

aggressiveLazyLoading：当开启时，任何方法的调用都会懒加载对象的所有属性。否则，每个属性会按需加载,

第二步：分开关：指定的association和collection元素中配置fetchType属性。eager：表示立刻加载；lazy：表示延迟加载。将覆盖全局延迟设置。

2.3多表查询总结与扩展
resultMap中的常见属性