mybatis系列之动态sql

一、动态sql

动态 SQL是MyBatis强大特性之一。极大的简化拼装 SQL的操作。 常用的几种元素如下：

if choose (when, otherwise) trim (where, set) foreach

1.1 if

判断语句，常常与test属性联合使用。

<select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select>

这条语句提供了一种可选的查找文本功能。如果没有传入title，那么所有处于ACTIVE状态的BLOG都会返回；反之若传入了title，那么就会对title一列进行模糊查找并返回 BLOG 结果

---

如果希望通过title和author两个参数进行可选搜索该怎么办呢？首先，改变语句的名称让它更具实际意义；然后只要加入另一个条件即可

<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>

---

test 判断表达式（OGNL）,遇见特殊符号应该去写转义字符。

<if test="id!=null"> id=#{id} </if> <if test="lastName!=null && lastName!="""> and last_name like #{lastName} </if> <if test="email!=null and email.trim()!="""> and email=#{email} </if> <!-- ognl会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> and gender=#{gender} </if>

---

1.2 choose, when, otherwise

choose、when、otherwise 类似 switch .. case .. default

提供了title就按title查找，提供了author就按author查找的情形，若两者都没有提供，就返回所有符合条件的 BLOG

<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>

---

1.3 trim, where, set

现在回到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> </select>

如果这些条件没有一个能匹配上会发生什么？最终这条 SQL 会变成这样

SELECT * FROM BLOG WHERE

这会导致查询失败。如果仅仅第二个条件匹配又会怎样？这条 SQL 最终会是这样:

SELECT * FROM BLOG WHERE AND title like ‘someTitle’

这个查询也会失败。这个问题不能简单地用条件句式来解决。

MyBatis 有一个简单的处理，这在 90% 的情况下都会有用。而在不能使用的地方，你可以自定义处理方式来令其正常工作。一处简单的修改就能达到目的。

<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 元素也会将它们去除

---

如果 where元素没有按正常套路出牌，我们可以通过自定义 trim 元素来定制 where元素的功能。比如，和where元素等价的自定义trim元素为

<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 属性中的内容，并且插入prefix 属性中指定的内容

---

<!--public List<Employee> getEmpsByConditionTrim(Employee employee); --> <select id="getEmpsByConditionTrim" resultType="com.bean.Employee"> select * from tbl_employee <!-- 后面多出的and或者or where标签不能解决 prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。 prefix给拼串后的整个字符串加一个前缀 prefixOverrides="": 前缀覆盖： 去掉整个字符串前面多余的字符 suffix="":后缀 suffix给拼串后的整个字符串加一个后缀 suffixOverrides="" 后缀覆盖：去掉整个字符串后面多余的字符 --> <!-- 自定义字符串的截取规则 --> <trim prefix="where" suffixOverrides="and"> <if test="id!=null"> id=#{id} and </if> <if test="lastName!=null && lastName!="""> last_name like #{lastName} and </if> <if test="email!=null and email.trim()!="""> email=#{email} and </if> </trim> </select>

---

类似的用于动态更新语句的解决方案叫做 set。set 元素可以用于动态包含需要更新的列，而舍去其它的。比如：

<update id="updateAuthorIfNecessary"> update Author <set> <if test="username != null">username=#{username},</if> <if test="password != null">password=#{password},</if> <if test="email != null">email=#{email},</if> <if test="bio != null">bio=#{bio}</if> </set> where id=#{id} </update>

---

这里，set 元素会动态前置 SET关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。

<trim prefix="SET" suffixOverrides=","> ... </trim>

---

1.4 foreach

动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候

<select id="selectPostIn" resultType="domain.blog.Post"> SELECT * FROM POST P WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>

foreach元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符

---

注意：你可以将任何可迭代对象（如 List、Set 等）、Map对象或者数组对象传递给foreach作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

---

<select id="getEmpsByConditionForeach" resultType="com.bean.Employee"> select * from tbl_employee <!-- collection：指定要遍历的集合： list类型的参数会特殊处理封装在map中，map的key就叫list item：将当前遍历出的元素赋值给指定的变量 separator:每个元素之间的分隔符 open：遍历出所有结果拼接一个开始的字符 close:遍历出所有结果拼接一个结束的字符 index:索引。遍历list的时候是index就是索引，item就是当前值 遍历map的时候index表示的就是map的key，item就是map的值 #{变量名}就能取出变量的值也就是当前遍历出的元素 --> <foreach collection="ids" item="item_id" separator="," open="where id in(" close=")"> #{item_id} </foreach> </select>

---

1.5 bind

bind元素可以从 OGNL表达式中创建一个变量并将其绑定到上下文

<select id="selectBlogsLike" resultType="Blog"> <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" /> SELECT * FROM BLOG WHERE title LIKE #{pattern} </select>

注意：OGNL语法

---

1.6 多数据库支持

一个配置了_databaseId变量的 databaseIdProvider 可用于动态代码中，这样就可以根据不同的数据库厂商构建特定的语句

<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>

---

其他补充

---

<!-- mybatis默认还有两个内置参数： _parameter:代表整个参数 单个参数：_parameter就是这个参数 多个参数：参数会被封装为一个map；_parameter就是代表这个map --> <!--public List<Employee> getEmpsTestInnerParameter(Employee employee); --> <select id="getEmpsTestInnerParameter" resultType="com.bean.Employee"> <bind name="_lastName" value="'%'+lastName+'%'"/> <if test="_databaseId=='mysql'"> select * from tbl_employee <if test="_parameter!=null"> where last_name like #{lastName} </if> </if> <if test="_databaseId=='oracle'"> select * from employees <if test="_parameter!=null"> where last_name like #{parameter.lastName} </if> </if> </select> _parameter:代表整个参数 单个参数：_parameter就是这个参数 多个参数：参数会被封装为一个map；_parameter就是代表这个map

---

OGNL（ Object Graph Navigation Language ）对象图导航语言，这是一种强大的 表达式语言，通过它可以非常方便的来操作对象属性。 |描述|用法| |:|:| |访问对象属性|person.name| |调用方法| person.getName()| |调用静态属性/方法| @java.lang.Math@PI @java.util.UUID@randomUUID() | |调用构造方法| new com.bean.Person(‘admin’).name | |运算符| +,-*,/,% | |逻辑运算符| in,not in,>,>=,<,<=,==,!= 注意：xml中特殊符号如”,>,<等这些都需要使用转义字符|

更多内容： 可以查看OGNL语法

---

参考

官方文档 http://www.atguigu.com/download.shtml#MyBatis