0%

动态sql

动态sql

在写sql语句时如果使用语句拼接,根据不同的参数组织不同的语句,经常出现少或多一个and,缺少空格,最后出现逗号等问题,mybatis通过动态sql来解决这些问题。

mybatis中包含有if、choose、when、otherwise、trim、where、set、foreach等标签

动态sql中的表达式使用的是OGNL表达式

if标签

使用if标签来判断是否符合该条件<if test="">,if标签结合test属性来使用

1
2
3
4
5
6
7
8
9
10
<!-- 使用动态sql  测试if标签 -->
<select id="getEmployeeByConditon" resultMap="emp">
select * from employee where id = #{id}
<if test="name != null and name!=''">
and name like #{name}
</if>
<if test="salary != null and salary != 0">
and name = #{salary}
</if>
</select>

where标签

可能遇到的情况

有时通过if条件判断之后导致后续一个条件都没有,此时SQL就变成了

1
select * from employee where

此时将不需要使用where条件

也有可能会出现有一个条件符合,但是会多出来一个and,此时SQL就变成了

1
select * from employee where and id = 1

很明显,这个多出来的and会导致报错

出现这种情况有两种方式解决

  • 可以使用1=1条件
  • 可以使用where标签,where会将where标签中拼接的sql中开头多出来的and或者or去掉
  • 可以使用trim
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!-- 使用动态sql  测试if和where标签 -->
<select id="getEmployeeByCondition" resultMap="emp">
select * from employee
<where>
<if test="id != null and id != 0">
and id = #{id}
</if>
<if test="name != null and name!=''">
and name like #{name}
</if>
<if test="salary != null and salary != 0">
and salary = #{salary}
</if>
</where>
</select>

trim标签

由于where只会去除开头的and或or,有时使用where元素与预期不符,此时可以使用trim标签

trim标签有几个子元素prefix/prefixOverrides/suffixOverrides

  • prefix 前缀,trim标签体中是整个字符串拼接后的结果,prefix给拼串后的整个字符串加一个前缀
  • prefixOverrides 前缀覆盖,去掉整个字符串中前面多余的字符
  • suffix 后缀,在整个字符串后加一个后缀
  • suffixOverrides 后缀覆盖,去掉整个字符串中后面多余的字符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="getEmployeeByCondition" resultMap="emp">
select * from employee
<trim prefix="where" suffixOverrides="and | or">
<if test="id != null and id != 0">
id = #{id} and
</if>
<if test="name != null and name!=''">
name like #{name} and
</if>
<if test="salary != null and salary != 0">
salary = #{salary} and
</if>
</trim>
</select>

where标签可以使用trim来表示 <trim prefix="where" suffixOverrides="and | or">

set标签可以用trim来表示 <trim prefix="SET" suffixOverrides=",">

choose标签

choose与when和otherwise组合使用,根据when标签中的test条件是否成立来进行分支选择,相当于switch-case,只会进入一个分支,如果有一个成立,则choose结束,如果when都不满足,则拼接otherwise标签中的sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<select id="getEmployeeByConditionChoose" resultMap="emp">
select * from employee
<trim prefix="where" suffixOverrides="and | or" prefixOverrides="and | or">
<!-- 有id时用id查,有name时用name查,否则查询所有 -->
<choose>
<when test="id != null and id != 0">
id = #{id} and
</when>
<when test="name != null and name!=''">
name like #{name} and
</when>
<otherwise>

</otherwise>
</choose>
</trim>
</select>

set标签

可能遇到的情况

对于update语句,也可能出现经过if判断之后一个条件都不符合的情况,此时sql语句就会变成

1
update employee set where id = 1

很明显此时的set就不应该存在了

也可能会出现这种情况,最后一条if判断不成立,导致最后多出来一个逗号,此时sql语句就变成了

1
update employee set name='张三', where id = 1

这个sql语句也不会执行成功

set标签用于动态更新,只更新需要更新的列,忽略其他不更新的列,set元素会在字符串开头添加set,并且会删除掉额外的逗号

1
2
3
4
5
6
7
8
9
10
11
12
<update id="updateEmployeeBySet">
update employee
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="salary != null and salary != 0">
salary = #{salary},
</if>
</set>
where id = #{id}
</update>

也可以使用trim来实现

1
2
3
4
5
6
7
8
9
10
11
12
<update id="updateEmployeeBySet">
update employee
<trim prifix="set" suffixOverrides=",">
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="salary != null and salary != 0">
salary = #{salary},
</if>
</trim>
where id = #{id}
</update>

foreach标签

使用foreach标签来进行对集合的遍历

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="selectByIds" resultType="User">
select * from user
<where>
id in
<!-- collection:指定遍历的集合 list类型参数会特殊封装到map中,key为list;array类型参数封装到map,key为array
item: 将当前遍历出的元素赋值给指定的变量,元素别名
separator: 每个元素之间的分隔符
open: 遍历所有结果拼接一个开始的字符
close: 遍历所有结果拼接一个结束的字符
index: 索引。遍历list的时候,index是索引,item是当前值;遍历map的时候,index是map的key,item是map的值
-->
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>

foreach还可以实现批量插入

1
2
3
4
5
6
7
8
9
10
<insert id="insertList">
insert into student(name, phone, sex)
values
<foreach collection="list" item="student" separator=",">
(
#{student.name}, #{student.phone},
#{student.sex}
)
</foreach>
</insert>

模糊查询

有时可能需要进行模糊查询,需要拼接%,可以使用concat

1
2
3
4
5
6
7
8
<select id="selectByName" resultType="User">
select * from user
<where>
<if test="name != null">
name like concat(#{name},'%')
</if>
</where>
</select>

内置参数

mybatis包含两个内置参数

  • _parameter:代表整个参数。单个参数时,_parameter就是这个参数;多个参数时,参数会被封装为一个map,_parameter代表这个map
  • _databaseId:如果配置了databaseIdProvider标签,_databaseId就是代表当前数据库的别名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="selectUserByCondition" resultType="User">
select * from user
<if test="_parameter != null">
<where>
<if test="id != null and id != 0">
and id = #{_parameter.id}
</if>
<if test="name != null">
and name = #{_parameter.name}
</if>
<if test="account != null and account != 0">
and account = #{_parameter.account}
</if>
</where>
</if>
</select>

bind绑定

bind可以将OGNL表达式的值绑定到一个变量中,并将其绑定到当前上下文,方便后来引用这个变量的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="selectUserByCondition" resultType="User">
select * from user
<where>
<if test="id != null and id != 0">
and id = #{id}
</if>
<if test="name != null">
<bind name="_name" value="'%'+name+'%'"/>
and name like #{_name}
</if>
<if test="account != null and account != 0">
and account = #{account}
</if>
</where>
</select>

sql标签

sql标签用于抽取可重用的sql片段,使用include标签来引用sql标签

  • 经常将查询的列名和插入的列名抽取出来
  • include来引用抽取的sql
  • include还可以自定义一些property,sql标签内部可以使用自定义的属性,使用${属性名}来取自定义的属性
1
2
3
4
5
6
7
8
9
10
11
<sql id="insertColumn">
`name`,account
</sql>

<insert id="addUser">
insert into `user` (
<include refid="insertColumn">
</include>
)
values (#{name},#{account})
</insert>

欢迎关注我的其它发布渠道