动态SQL语句
1.基本介绍
-
为什么需要动态SQL?
- 动态SQL是MyBatis的强大特性之一
- 使用 JDBC 或其他类似的框架,根据不同条件拼接SQL语句非常麻烦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号等
- SQL映射语句中的强大动态SQL语言可以很好地解决这个问题
-
基本介绍
- 在一个实际的项目中,sql语句往往是比较复杂的
- 为了满足更加复杂的业务需求,MyBatis的设计者,提供了动态生成SQL的功能
-
动态SQL的必要性
- 这里有一些需求:查询Monster时,如果程序员输入的age不大于0,sql语句就不要带age条件;更新记录时,如果传入的Monster对象没有设置新的属性值,数据库表字段就保持原来的值,设置了新的值,就更新
- 解决方案:从上面的需求中可以看出,有时我们在生成sql语句时,在同一个方法中,还要根据不同的情况生成不同的sql语句,解决的办法就是使用MyBatis提供的动态SQL机制
-
动态SQL常用标签 -- 动态SQL提供了如下几种常用的标签,类似Java的控制语句:
- if [判断]
- where [拼接where子句]
- choose /when /otherwise [类似java的switch,注意是单分支]
- foreach [类似in]
- trim [替换关键字/定制元素的功能]
- set [在update的set中,可以保证进入set标签的属性被修改,没有进入set的就保持原来的值]
2.动态SQL-案例演示
2.1if标签
使用动态 SQL 最常见情景是,根据条件,用if标签包含 where 子句的一部分。
例子:要求查询age大于10的所有记录,如果age不大于0,则输出所有记录。
<!--配置方法findMonsterByAge
请查询age大于10的所有记录,如果程序员输入的age不大于0,则输出所有的记录
1.如果在test表达式中使用#{age},是取不出值的
2.要在接口声明的入参使用@param注解为"age"-->
<select id="findMonsterByAge" resultType="Monster" parameterType="Integer">
SELECT * FROM `monster`
<if test="age >= 0">
WHERE `age`> #{age}
</if>
</select>
当方法入参>=0时,底层的sql语句为:
当入参<0时,sql为:
2.2where标签
where 标签只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
例子:查询id>5,并且名字为“牛魔王”的所有记录。如果名字为空,则不拼接带名字的条件,如果id<0,则不拼接带id的条件
<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>
如下,当id的值小于0时,会自动去除id子句和name子句前的AND
2.3choose/when/otherwise标签
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
<choose>
<when></when>
<when></when>
<otherwise></otherwise>
</choose>
例子:
(1)如果给的name不为空,就按名字查询妖怪
(2)如果指定的id>0,就按id来查询妖怪
(3)如果前面两个添加都不满足,就默认查询salary>100的
<select id="findMonsterByIdOrName" parameterType="map" resultType="Monster">
SELECT * FROM `monster`
<choose>
<when test="name!=null and name != ''">
WHERE `name` = #{name}
</when>
<when test="id >= 0 and id !=null">
WHERE `id` >= #{id}
</when>
<otherwise>
WHERE `salary` > 100
</otherwise>
</choose>
</select>
当入参name不符合条件时,选择id的子句
当入参name和id都不符合条件时,默认选择otherwise的子句
2.4foreach标签
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。
foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头(open)与结尾(close)的字符串以及集合项迭代之间的分隔符(separator),这个元素也不会错误地添加多余的分隔符。
提示 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
例子:查询id为 10,12,14 的记录
<!--1.先判断集合是否为空,如果非空,则进行遍历
2.collection对应入参 map,item是遍历的当前值value,open是sql的 ( ,
separator是多个值之间的分隔符,close是 sql的 )
3. #{id}对应 item="id"-->
<select id="findMonsterById_forEach" parame