在项目中写SQL语句的时候经常会用到一些动态SQL语句进行一系列的操作,因现在无SQL技术的大面积使用,导致对Mybatis动态标签的记忆度不够,故 再次记录,后面会持续更新,做到尽可能的全。
动态 SQL
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
IF
使用动态SQL最常见的情景是根据条件包含where子句的一部分,如
<select id="getUserInfo" resultType="A_USER">
SELECT * FROM A_USER WHERE USERNAME = ‘测试’
<if test="status != nul and status != ''l">
AND STATUS = #{status}
</if>
</select>
choose、when、otherwise
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
还是上面的例子,变为:传入了 “sex” 就按 “sex” 查找,传入了 “age” 就按 “age” 查找的情形。若两者都没有传入,就返回标记为 IN_USE 的 用户信息。
<select id="getUserInfo" resultType="A_USER">
SELECT * FROM A_USER WHERE USERNAME = ‘测试’
<choose>
<when test="sex != null and sex != ''">
AND SEX like #{sex}
</when>
<when test="author != null and author.name != null">
AND AGE = #{age}
</when>
<otherwise>
AND IN_USE = '1'
</otherwise>
</choose>
</select>
trim、where、set
前面几个例子已经方便地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例,这次将条件都设置成动态条件,看看会发生什么。
<select id="getUserInfo" resultType="A_USER">
SELECT * FROM A_USER WHERE
<if test="status != null and status != ''">
STATUAS = #{status}
</if>
<if test="age != null and age != ''">
AND AGE = #{age}
</if>
<if test="userName != null and userName != ''">
AND USER_NAME like #{userName}
</if>
</select>
如果没有匹配的参数,最终SQL会变成:
SELECT * FROM A_USER WHERE
这样会导致查询失败,如果只有第二个IF匹配成功,将变成:
SELECT * FROM A_USER WHERE AND AGE = '18'
这样也会查询失败,这个问题不能简单地用条件元素来解决。这个问题是如此的难以解决,以至于之前解决过的人不会再想碰到这种问题。
MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中,可以对其进行自定义以符合需求。而这,只需要一处简单的改动:
<select id="getUserInfo" resultType="A_USER">
SELECT * FROM A_USER
<where>
<if test="status != null and status != ''">
STATUAS = #{status}
</if>
<if test="age != null and age != ''">
AND AGE = #{age}
</if>
<if test="userName != null and userName != ''">
AND USER_NAME like #{userName}
</if>
</where>
</select>
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND | OR ">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:
<update id="updateUserInfoByUserId">
update A_USER
<set>
<if test="userName != null and userName != ''">USERNAME = #{userName},</if>
<if test="password != null and password != ''">PASSWORD = #{password},</if>
<if test="email != null and email != ''">EMAIL = #{email},</if>
<if test="age != null and age != ''">AGE = #{age}</if>
</set>
where id = #{userId}
</update>
这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
来看看与 set 元素等价的自定义 trim 元素吧:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
注意,我们覆盖了后缀值设置,并且自定义了前缀值。
foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
<where>
<foreach item="item" index="index" collection="list"
open="ID in (" separator="," close=")" nullable="true">
#{item}
</foreach>
</where>
</select>
foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,看它多智能!
提示 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
下面将详细探讨 Java API,以便你能充分利用已经创建的映射配置。
script
个人不建议这样使用,复杂的SQL尽量放到XML中做映射使用。
要在带注解的映射器接口类中使用动态 SQL,可以使用 script 元素。比如:
@Update({"<script>",
"update A_USER",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" <if test='email != null'>email=#{email},</if>",
" </set>",
"where id=#{id}",
"</script>"})
void updateUserInfoByUserId(AUser auser);
bind
bind 元素允许你在 OGNL 表达式以外创建一个变量,并将其绑定到当前的上下文。比如:
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
多数据库支持
如果配置了 databaseIdProvider,你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。比如下面的例子:
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
感谢您的观看,后面尽可能的将其他的参数补全。