Mybatis动态SQL的使用方法
2018.06.04 19:23:28字数 1286阅读 1278
Mybatis
的强大特性之一便是它的动态SQL
。在实际开发中,数据库的查询比较复杂,需要在不同的逻辑中执行不同的SQL语句。我们往往要根据各种不同的场景拼接出不同的SQL语句,而Mybatis
给我们提供了动态SQL,可以让我们根据具体的业务逻辑来拼接SQL语句。(Mybatis采用功能强大的基于OGNL的表达式来淘汰其他的大部分元素)
if条件(简单的条件判断)
用户可以选择其中的任意某些字段或者都不选,这个时候我们的查询语句该怎么写呢。 其实很简单,我们只需要写一些动态的sql就可以实现这一功能。如下代码,只需要在拼接查询条件前,先用<if>
是否为null
,如果不为null
,则执行代码时会把条件拼接在<where>
后
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
现在回到“if”示例,这次我们将“ACTIVE = 1”
也设置成动态的条件,看看会发生什么。
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且,若语句的开头为“AND”或“OR”,where 元素也会将它们去除。
test属性
test
的属性用于条件判断的语句中,它在Mybatis
中广泛使用。它的作用相当于判断真假。在大部分场景中我们都是用它判断空和非空。有时候我们需要判断字符串、数字和枚举等。
choose(选择其中的一项)
有时我们不想应用到所有的条件语句,而只想从中择其一项。针对这种情况,MyBatis
提供了 choose
元素,它有点像 Java 中的switch
语句 我们来看一个简单的例子:
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
where(简化SQL语句中where条件判断)
在上面的案例中小伙伴们可能都发现了一个问题,就是我们在添加查询条件的时候,在查询条件之前都先添加了where 1=1
,然后后面直接在这之后再追加and什么什么的,那么每次这样来写显然有点麻烦,有没有简单一点的方案呢?当然有,我们可以通过where元素简化SQL语句中的where条件,如下:
<select id="getUser3" resultMap="u">
SELECT * FROM user2
<where>
<choose>
<when test="id!=null">
AND id=#{id}
</when>
<when test="address!=null">
AND address=#{address}
</when>
<when test="username!=null">
AND user_name LIKE concat(#{username},'%')
</when>
<otherwise>
AND 10>id
</otherwise>
</choose>
</where>
</select>
这样,只有where元素中有条件成立,才会将where关键字组装到SQL中,这样就比前一种方式简单许多。
trim(替换和添加)
trim
t元素就意味着我们需要去掉一些特殊的字符串,prefix
代表的语句中的前缀,而prefixOverrides
代表的是你需要去掉的那种字符串,我们可以将and替换为where,如下:
<select id="getUser4" resultMap="map">
SELECT * FROM user2
<!--这个语句中是把AND换成where,下面的写法基本与where是等效的-->
<trim prefix="where" prefixOverrides="and">
AND id=1
</trim>
</select>
这个最终执行的sql是SELECT * FROM user2 where id=1
。
set(主要用于更新)
set是我们在更新表的时候使用的元素,通过set元素,我们可以逐字段的修改一条数据,如下:
<update id="update">
UPDATE user2
<set>
<if test="username!=null">
user_name=#{username},
</if>
<if test="password!=null">
password=#{password}
</if>
</set>
WHERE id=#{id}
</update>
在set元素中,如果遇到了逗号,系统会自动将之去除。
foreach(主要用于IN语句中)
foreach
元素用来遍历集合,比如我想查询多个城市的人,我的sql语句可能是这样SELECT * FROM user2 WHERE address IN('西安','北京')
,我在查询的时候可能只是传入了一个list集合,该集合中有西安和北京两个查询条件,那我如何将这个集合组装成一个sql语句呢?很简单,如下:
<!--foreach用于迭代数据的元素
open表示开始的符号
close表示结束符号
seprator表示元素间的分隔符
items表示迭代的数组
-->
<select id="getUserInCities" resultMap="BaseResultMap">
SELECT * FROM user2
WHERE address IN
<foreach collection="cities" index="index" open="(" separator="," close=")" item="city">
#{city}
</foreach>
</select>
collection
表示传入的参数中集合的名称,index
表示是当前元素在集合中的下标,open
和close
则表示如何将集合中的数据包装起来,separator
表示分隔符,item
则表示循环时的当前元素。这样一段配置最终组合成的sql就是SELECT * FROM user2 WHERE address IN('西安','北京')
。
注意 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象传递给
*foreach*
作为集合参数。当使用可迭代对象或者数组时,index 是当前迭代的次数,item 的值是本次迭代获取的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值
bind(预定义变量)
使用bind元素我们可以预先定义一些变量,然后在查询语句中使用,如下:
<select id="getUserByName" resultMap="">
<bind name="un" value="username+'%'"></bind>
SELECT* FROM user2 WHERE user_name LIKE #{un}
</select>
OGNL(Object-Graph Navigation Language )表达式
在Mybatis
的动态SQL和${}形式的参数中都用到了OGNL表达式,所以我们有必要了解OGNL的简单用法。Mybatis常用的OGNL表达式如下:
OGNL表达式 | 含义 |
---|---|
e1 or e2 | |
e1 and e2 | |
e1 == e2 或 e1 eq e2 | |
e1 != e2 或 e1 neq e2 | |
e1 lt e2(小于) | |
e1 lte e2(小于等于,gt(大于),gte(大于等于)) | |
e1 + e2、e1*e2、e1/e2、e1-e2、e1%e2 | |
!e 或 not e | 非 |
e.method(args) | 调用对象方法 |
e.property | 对象属性值 |
e1[e2] | 按索引取值(List、数组和) |