目录
编辑-- 以上问题的解决方案:使用标签代替SQL语句中的where关键字
3.2.2 MyBatis-动态SQL-if-案例(更新员工)
3.1 什么是动态SQL
- 随着用户的输入或外部条件的变化而变化的SQL语句,我们称为 动态SQL。
- 简单说就是这条SQL语句它不是固定的,是动态变化的。
在页面原型中,条件查询员工信息这个需求来说,列表上方的条件是动态的,是可以不传递的{如果不传递/指定,就代表没有限制条件,查询全部的员工信息},也可以只传递其中的1个或者2个或者全部。
而在我们刚才编写的SQL语句中,我们会看到,我们将三个条件直接写死了。 如果页面只传递了参数姓名name 字段,其他两个字段 性别 和 入职时间没有传递,那么这两个参数的值就是null。
此时,执行的SQL语句为:
package com.gch;
import com.gch.mapper.EmpMapper;
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
/**
从Spring的IOC容器当中,获取类型是EmpMapper的对象并注入
*/
@Autowired
private EmpMapper empMapper;
/**
条件查询
*/
@Test
public void testSelectList(){
/**
调用查询方法查询员工信息,并将查询返回的结果使用List集合接收封装
*/
List<Emp> list = empMapper.select("张", null,null,null);
// 遍历集合输出
list.stream().forEach(s ->{
System.out.println(s);
});
}
}
运行测试类: SqlSession
这个查询结果是不正确的。
正确的做法应该是:传递了参数,再组装这个查询条件;如果没有传递参数,就不应该组装这个查询条件。 --- 所以SQL语句我们是需要根据输入的条件动态来组装的。
比如:如果姓名输入了"张", 对应的SQL为:
select * from emp where name like '%张%' order by update_time desc;
如果姓名输入了"张",,性别选择了"男",则对应的SQL为:
select * from emp where name like '%张%' and gender = 1 order by update_time desc;
SQL语句会随着用户的输入或外部条件的变化而变化,我们称为:动态SQL。
在Mybatis中提供了很多实现动态SQL的标签,我们学习Mybatis中的动态SQL就是掌握这些动态SQL标签。
Mybatis动态SQL标签:
- <if> <where>、<set>
- <foreach>
- <sql><include>
3.2 动态SQL - <if>
<if>:用于判断条件是否成立{用来做条件判断的}。使用test属性进行条件判断,如果条件为true,则拼接SQL。
<if test="条件表达式">
要拼接的sql语句
</if>
接下来,我们就通过<if>
标签来改造之前条件查询的案例。
3.2.1 条件查询
示例:把SQL语句改造为动态SQL方式
-
原有的SQL语句
<select id="select" resultType="com.gch.pojo.Emp">
select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
from mybatis.emp
where name like concat('%', #{name}, '%')
and gender = #{gender}
and entrydate between #{begin} and #{end}
order by update_time desc
</select>
-
动态SQL语句
<select id="select" resultType="com.gch.pojo.Emp">
select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
from mybatis.emp
where
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>
测试方法:
/**
条件查询
*/
@Test
public void testSelectList(){
/**
调用查询方法查询员工信息,并将查询返回的结果使用List集合接收封装
*/
List<Emp> list = empMapper.select("张", null,null,null);
// 遍历集合输出
list.stream().forEach(s ->{
System.out.println(s);
});
}
执行的SQL语句以及查询返回的结果:
下面呢,我们修改测试方法中的代码,将参数name改为null,再次进行测试,观察执行情况:
/**
条件查询
*/
@Test
public void testSelectList(){
/**
调用查询方法查询员工信息,并将查询返回的结果使用List集合接收封装
*/
List<Emp> list = empMapper.select(null, (short)1,null,null);
// 遍历集合输出
list.stream().forEach(s ->{
System.out.println(s);
});
}
执行结果:
注意:框架遇到的错,一定要从下往上看!拉到最后一行,找到最后一个Casued by
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;
原因检查:
再次修改测试方法中的代码,将传递的数据全部改为null,再次进行测试:
/**
条件查询
*/
@Test
public void testSelectList(){
/**
调用查询方法查询员工信息,并将查询返回的结果使用List集合接收封装
*/
List<Emp> list = empMapper.select(null, null,null,null);
// 遍历集合输出
list.stream().forEach(s ->{
System.out.println(s);
});
}
运行测试方法后:
执行的SQL语句:
-- 以上问题的解决方案:使用<where>
标签代替SQL语句中的where关键字
使用<where>标签将所有的查询条件包裹起来!
<where>标签的两层作用:
<where>标签只会在子元素有内容的情况下才插入where子句{<where>标签它会根据里面的这些子标签动态的来判断里面的条件,如果里面所有的条件都不成立,它就不会生成where关键字;如果里面有一个条件成立,它就会生成where关键字}
而且<where>标签会自动去除子句的开头的AND或OR
加上<where>标签之后:
<!--动态条件查询操作 resultType:指的是单条记录所封装的类型{实体类全类名}-->
<select id="select" resultType="com.gch.pojo.Emp">
select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
from mybatis.emp
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>
测试方法:
/**
条件查询
*/
@Test
public void testSelectList(){
/**
调用查询方法查询员工信息,并将查询返回的结果使用List集合接收封装
*/
List<Emp> list = empMapper.select(null, null,null,null);
// 遍历集合输出
list.stream().forEach(s ->{
System.out.println(s);
});
}
3.2.2 MyBatis-动态SQL-if-案例(更新员工)
案例:完善更新员工功能,将更新员工的功能修改为动态更新员工数据信息
之前的更新操作所存在的问题:
需求:
-
动态更新员工信息,如果更新时有传递值,则更新该字段;如果更新时没有传递值,则不更新该字段
-
解决方案:动态SQL
修改Mapper接口中的接口方法:
package com.gch.mapper;
import com.gch.pojo.Emp;
import org.apache.ibatis.annotations.*;
import java.time.LocalDate;
import java.util.List;
/**
加上@Mapper注解就代表程序在运行时会自动的创建该接口的代理对象,并且会将这个代理对象放入到IOC容器当中
*/
@Mapper
public interface EmpMapper {
/**
* 动态更新员工信息
* 删除@Update注解所编写的SQL语句
* 将update操作的SQL语句编写在Mapper映射文件中
* @param emp => 将传递的多个参数封装到实体类对象当中
*/
public void update(Emp emp);
}
修改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.gch.mapper.EmpMapper">
<!-- 动态更新操作 方法名需要与SQL语句的id保持一致-->
<update id="update">
update mybatis.emp
set
<if test="username != null">username = #{username},</if>
<if test="name != null">name = #{name},</if>
<if test="gender != null">gender = #{gender},</if>
<if test="image != null">image = #{image},</if>
<if test="job != null">job = #{job},</if>
<if test="entrydate != null">entrydate = #{entrydate},</if>
<if test="deptId != null">dept_id = #{deptId},</if>
<if test="updateTime != null">update_time = #{updateTime}</if>
where id = #{id}
</update>
</mapper>
测试方法:
c @Test
public void testUpdate2(){
// 构建Emp员工对象
Emp emp = new Emp();
// 要修改的员工信息
emp.setId(5);
emp.setDeptId(2);
// 调用方法,修改员工数据
empMapper.update(emp);
}
运行后的报错以及执行的SQL语句:
以上问题的解决方案:使用<set>标签代替SQL语句中的set关键字
-
<set>:动态的在SQL语句中插入set关键字,并会删掉额外的逗号。(用于update语句中)
<?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.gch.mapper.EmpMapper">
<!-- 动态更新操作 方法名需要与SQL语句的id保持一致-->
<update id="update">
update mybatis.emp
<!-- 使用<set>标签,代替update语句中的set关键字 -->
<set>
<if test="username != null">username = #{username},</if>
<if test="name != null">name = #{name},</if>
<if test="gender != null">gender = #{gender},</if>
<if test="image != null">image = #{image},</if>
<if test="job != null">job = #{job},</if>
<if test="entrydate != null">entrydate = #{entrydate},</if>
<if test="deptId != null">dept_id = #{deptId},</if>
<if test="updateTime != null">update_time = #{updateTime}</if>
</set>
where id = #{id}
</update>
</mapper>
再次执行测试方法,执行的SQL语句:
小结:
<if>
-
用于判断条件是否成立,如果条件为true,则拼接SQL
-
形式:
<if test="name != null"> … </if>
-
<where>
-
where元素只会在子元素有内容的情况下才插入where子句,而且会自动去除子句的开头的AND或OR
-
-
<set>
-
动态地在行首插入 SET 关键字,并会删掉额外的逗号。(用在update语句中)
-
<?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.gch.mapper.EmpMapper">
<!-- 动态更新操作 方法名需要与SQL语句的id保持一致-->
<update id="update">
update mybatis.emp
<!-- 使用<set>标签,代替update语句中的set关键字 -->
<set>
<if test="username != null">username = #{username},</if>
<if test="name != null">name = #{name},</if>
<if test="gender != null">gender = #{gender},</if>
<if test="image != null">image = #{image},</if>
<if test="job != null">job = #{job},</if>
<if test="entrydate != null">entrydate = #{entrydate},</if>
<if test="deptId != null">dept_id = #{deptId},</if>
<if test="updateTime != null">update_time = #{updateTime}</if>
</set>
where id = #{id}
</update>
<!--动态条件查询操作 resultType:指的是单条记录所封装的类型{实体类全类名}-->
<select id="select" resultType="com.gch.pojo.Emp">
select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
from mybatis.emp
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>
</mapper>
3.3 动态SQL - <foreach> - "批量"
案例:员工删除功能(既支持删除单条记录,又支持批量删除)
SQL语句:
-- 批量删除ID为18,19,20
delete from emp where id in(18,19,20);
Mapper接口:
package com.gch.mapper;
import com.gch.pojo.Emp;
/**
加上@Mapper注解就代表程序在运行时会自动的创建该接口的代理对象,并且会将这个代理对象放入到IOC容器当中
*/
@Mapper
public interface EmpMapper {
/**
* 批量删除员工操作
* 由于是批量删除,一般会使用数组或者集合来封装多个ID值
* @param ids => 批量删除数据的多个ID值,封装到List集合
*/
public void deleteByIds(List<Integer> ids);
}
XML映射文件:
-
使用<foreach> 遍历deleteByIds方法中传递的参数ids集合
<foreach collection="集合名称" item="集合遍历出来的元素/项" separator="每一次遍历使用的分隔符"
open="遍历开始前拼接的片段" close="遍历结束后拼接的片段">
</foreach>
<?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.gch.mapper.EmpMapper">
<!-- 批量删除操作 delete from emp where id in (18,19,20)-->
<!--
collection:要遍历的集合名称
item:遍历出来的元素
separator:每一次遍历出来的元素在拼接的时候(使用什么分隔/使用的分隔符)
open:遍历开始前拼接的SQL片段
close:遍历结束后拼接的SQL片段
-->
<delete id="deleteByIds">
delete
from mybatis.emp
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
</mapper>
测试方法:
package com.gch;
import com.gch.mapper.EmpMapper;
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
/**
从Spring的IOC容器当中,获取类型是EmpMapper的对象并注入
*/
@Autowired
private EmpMapper empMapper;
@Test
public void testDelete(){
// 通过Arrays工具类构造List集合,并使用List集合封装批量删除数据的ID值
List<Integer> listIds = Arrays.asList(18,19,20);
// 调用方法,批量删除员工操作
empMapper.deleteByIds(listIds);
}
}
执行的SQL语句:
总结:
3.4 动态SQL - <sql> & <include>
问题分析:
-
在xml映射文件中配置的SQL,有时可能会存在很多重复的片段,此时就会存在很多冗余的代码
-
<sql>:定义可重用的SQL片段,我们可以对重复的代码片段进行抽取,将其通过<sql>标签封装到一个SQL片段,在抽取的时候,需要给这个SQL片段分配一个唯一标识,也就是定义一个id属性;
- <include>:通过属性refid,指定包含的SQL片段,然后再通过<include>标签进行引用。<include>标签负责在原来抽取的位置将这个SQL片段再引用进来,需要通过refid这个属性来指定我到底要引入的是哪一个SQL片段。这里指定的refid属性值关联的就是SQL片段的ID属性值。
SQL片段: 抽取重复的代码
<!-- 通过<sql>标签对重复的代码段进行抽取封装-->
<sql id="commonSelect">
select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
from mybatis.emp
</sql>
然后通过<include>标签在原来抽取的地方进行引用,操作如下:
<!--动态条件查询操作 resultType:指的是单条记录所封装的类型{实体类全类名}-->
<select id="select" resultType="com.gch.pojo.Emp">
<!-- <include refid="commonSelect"></include> == 自闭合<include refid="commonSelect"/> -->
<!-- 通过<include>标签在原来抽取的地方进行有引用,并通过refid属性来指定引入的SQL片段 -->
<include refid="commonSelect"/>
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>