什么是动态SQL?
动态 SQL :指在运行时动态构建 SQL 查询或命令的过程,动态 SQL 允许根据不同的条件或变量生成不同的 SQL 语句,从而实现更灵活的数据操作
在这里,使用的数据表是之前文章 MyBatis(一) 中创建的 userinfo 表
<if>标签
当用户在进行注册时,注册的字段分为两种:必填字段和非必填字段
则在添加用户时就会有不确定的字段(非必填字段)传入,这时我们该如何实现呢?
此时,就需要使用 动态标签,来进行判断
需求: gender 为非必填字段
接口定义:
Integer insertUserByCondition(UserInfo userInfo);v
xml实现:
<insert id="insertUserByCondition">
insert into userinfo (
username,
password,
age,
<if test="gender != null">
gender,
</if>
phone)
values (
#{username},
#{password},
#{age},
<if test="gender != null">
#{gender},
</if>
#{phone})
</insert>
测试:
当 gender 为空时:
@Test
void insertUserByCondition() {
UserInfo userInfo = new UserInfo();
userInfo.setUsername("hhh");
userInfo.setPassword("hhh");
userInfo.setAge(18);
userInfo.setPhone("XXXXXXXX");
System.out.println(userInfoXMLMapper.insertUserByCondition(userInfo));
}
当 gender 不为空时:
@Test
void insertUserByCondition() {
UserInfo userInfo = new UserInfo();
userInfo.setUsername("hhh");
userInfo.setPassword("hhh");
userInfo.setAge(18);
userInfo.setGender(1);
userInfo.setPhone("XXXXXXXX");
System.out.println(userInfoXMLMapper.insertUserByCondition(userInfo));
}
<if> 标签里 test 中 的 gender ,是传入对象中的属性,而不是数据库字段
使用注解实现:
使用注解时,只需要将 xml实现的SQL 使用 <script></script> 标签括起来就可以了
@Select("<script>" +
"insert into userinfo (" +
" username, " +
" password, " +
" age," +
" <if test='gender != null'>" +
" gender," +
" </if>" +
" phone)" +
" values (" +
" #{username}," +
" #{password}," +
" #{age}," +
" <if test='gender != null'>" +
" #{gender}," +
" </if>" +
" #{phone})" +
"</script>")
Integer insertUserByCondition(UserInfo userInfo);
同样的,进行测试,测试的过程与 使用 xml 实现是相同的,在这里就不再进行了
由于使用 注解 实现时,是进行字符串的拼接,若出现错误(如</if> 写成 /if>)不会有提示信息,因此,更推荐使用 xml 的方式实现动态SQL,在后续实现中,便不再演示 注解实现 的代码了
若当 phone 也为 非必填字段,此时:
<insert id="insertUserByCondition">
insert into userinfo (
username,
password,
age,
<if test="gender != null">
gender,
</if>
<if test="phone != null">
phone
</if>
)
values (
#{username},
#{password},
#{age},
<if test="gender != null">
#{gender},
</if>
<if test="phone != null">
#{phone}
</if>
)
</insert>
此时,若 phone 为 null:
由于 phone 为空,此时 gender, 后无字段, values 中 , 后也无值,因此程序报错
此时该如何解决呢?
<trim>标签
我们可以使用 <trim>标签,对每个字段都采取动态生成的方式
<insert id="insertUserByCondition">
insert into userinfo
<trim prefix="(" suffix=")" suffixOverrides=",">
username,
password,
age,
<if test="gender != null">
gender,
</if>
<if test="phone != null">
phone,
</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
#{username},
#{password},
#{age},
<if test="gender != null">
#{gender},
</if>
<if test="phone != null">
#{phone},
</if>
</trim>
</insert>
<trim>标签中有以下属性:
prefix:整个语句块,以 prefix 的值作为前缀
suffix:整个语句块,以 suffix 的值作为后缀
prefixOverrides:整个语句块要去除的前缀
suffixOverrides:整个语句块要去除的后缀
因此,上述SQL动态解析时:
基于 prefix 配置,在开始部分 加上 (
基于 suffix 配置,在结束部分加上 )
基于 suffixOverrides 配置,在拼接好字符串后,若字符串以 , 结尾,则会去除最后一个 ,
此时再进行测试:
phone 值为 null,此时 gender 后的 , 被去除了,插入成功
<where>标签
在实际情况中,我们可能会通过一定的条件进行筛选
系统会根据筛选条件,动态组装 where 条件
此时我们应该如何实现上述功能呢?
需求:根据 age 和 gender 进行查询,若其不为空,则为查询条件
接口定义:
List<UserInfo> queryByCondition(UserInfo userInfo);
xml 实现:
<select id="queryByCondition">
select * from userinfo where
<trim prefix="(" suffix=")" prefixOverrides="and">
<if test="age != null">
age = #{age}
</if>
<if test="gender != null">
and gender = #{gender}
</if>
</trim>
</select>
测试:
@Test
void queryByCondition() {
UserInfo userInfo = new UserInfo();
userInfo.setAge(18);
System.out.println(userInfoXMLMapper.queryByCondition(userInfo));
}
运行结果:
此时查询条件为 age = 18,从运行结果中我们可以看到,所以 age = 18 的数据被查找到
我们可以使用 <where> 标签进行条件查询:
<select id="queryByCondition">
select * from userinfo
<where>
<if test="age != null">
and age = #{age}
</if>
<if test="gender != null">
and gender = #{gender}
</if>
</where>
</select>
<where>标签只有在子元素有内容的情况下才会插入 where 子句,且会自动去除子句开头的 and 或 or
当 age 和 gender 都为空时:
子元素无内容, <where> 标签时则不会插入 where 子句
而当 使用 <trim prefix="(" suffix=")" prefixOverrides="and"> 时:
子元素无内容时,where 关键字仍会被保留
由于 <where> 标签会自动去除子句中开头的 and 或 or,因此在使用 <where>时,要注意 and 或 or 应该添加在子句开头
当添加到子句结束时:
<select id="queryByCondition">
select * from userinfo
<where>
<if test="age != null">
age = #{age} and
</if>
<if test="gender != null">
gender = #{gender}
</if>
</where>
</select>
<set>标签
当需要更新数据时,我们可以使用 <set> 标签进行更新
需求:通过传入的用户对象属性更新用户数据,根据传入的用户 id 属性,修改其他不为 null 的属性
接口定义:
Integer updateByCondition(UserInfo userInfo);
xml 实现:
<update id="updateByCondition">
update userinfo
<set>
<if test="username != null">
username = #{username},
</if>
<if test="age != null">
age = #{age},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="deleteFlag != null">
delete_flag = #{deleteFlag},
</if>
</set>
where id = #{id}
</update>
测试:
@Test
void updateByCondition() {
UserInfo userInfo = new UserInfo();
userInfo.setId(8);
userInfo.setAge(30);
userInfo.setUsername("111");
userInfoXMLMapper.updateByCondition(userInfo);
}
运行结果:
修改成功
<set>标签用户 update语句中,且会删除额外的逗号
也可以使用 <trim prefix="set" suffixOverrides=","> 替换,(逗号也可以放在子句后,此时使用 suffixOverrides="," )
<foreach>标签
当我们需要对集合进行遍历时,可以使用 <foreach>标签
需求:根据多个 userid,删除用户数据
接口定义:
Integer deleteByIds(List<Integer> idList);
xml 实现:
<delete id="deleteByIds">
delete from userinfo
where id in
<foreach collection="idList" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
其中,标签的属性有:
collection :绑定方法参数中的集合(List、Set、Map、数组等)
item:遍历时的每一个对象
open:语句块开头的字符串
close:语句块结束的字符串
separator:每次遍历之间间隔的字符串
测试:
@Test
void deleteByIds() {
List idList = new ArrayList();
idList.add(14);
idList.add(15);
idList.add(16);
userInfoXMLMapper.deleteByIds(idList);
}
运行结果:
删除成功
<include>标签
在 xml 映射文件中配置的SQL,可能会存在很多重复的片段,此时就会存在冗余的代码
此时,我们可以对重复的代码片段进行抽取,将其通过 <sql> 标签封装到一个 SQL 片段,再通过 <include> 标签进行引用
<sql>:定义可重复的 SQL片段
<sql id="allColum">
id, username, age, gender, phone, delete_flag, create_time, update_time
</sql>
<include>:通过属性 refid,指定包含的 SQL片段
<select id="selectAllUser" resultType="com.example.mybatis.model.UserInfo">
select
<include refid="allColum"></include>
from userinfo
</select>