Mybatis开发文档：https://mybatis.org/mybatis-3/zh/dynamic-sql.html

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

下面我们来体验一下动态SQL

一、动态查询

1. 普通查询语句

   <select id="getStu" resultType="Student"> select * from student where ssex = #{ssex} and sage > #{sage} </select> 分析：这个语句是存在漏洞的，假如ssex的值为空，则会出现 select * from student where ssex = and sage > sage 

   
   讯享网
2. 使用if语句来优化

   讯享网<select id="getStu" resultType="Student"> select * from student where 1=1 <!--这里where 1=1是为了避免后面传入的数值为空的状况--> <if test="ssex != null"> and ssex = #{ssex} </if> <if test="sage != null"> and sage > #{sage} </if> <if test="sdept != null"> and sdept =#{sdept} </if> </select> 看上去优化了不少 不过多加入一个条件1=1就是让人不爽 那么我们还可以使用where标签进行优化 

3. 使用where标签继续进行优化

   <select id="getStu1" resultType="Student"> select * from student <where> <if test="ssex !=null"> ssex = #{ssex} </if> and sage >30 </where> </select> where标签可以自动处理AND或者OR这些 我们还可以通过 trim 标签去自定义这种处理规则。 

4. 使用trim标签进行处理规则

   讯享网<!--使用trim处理where后面紧跟and和or的情况--> <select id="getStu2" resultType="Student"> select * from student <!--这里语句的意思是去除前缀为 where的and和 or 语句--> <trim prefix="WHERE" prefixOverrides="AND |OR "> <if test="ssex !=null"> ssex = #{ssex} </if> and sage > 30 </trim> </select> prefix的意思是前缀 trim中的语句意思为，去除前缀为where后面的and或or字符串 这样的话就可以直接处理where和and、or直接拼接的问题了 当然，对于查询条件唯一但不确定的情况下，我们还可以使用choose-when-otherwise进行优化 

5. 使用choose-when-otherwise选择查询条件

   <select id="getStu3" resultType="Student"> select * from student <where> <choose> <when test="ssex !=null"> ssex = #{ssex} </when> <otherwise> ssex = '女' </otherwise> </choose> and sage =34 </where> </select> 

二、动态更新

1. 普通的更新语句

   讯享网<update id="updateStu" parameterType="Student"> update student set (ssex = #{ssex},sname = #{sname}) where sno=#{sno} </update> 分析：普通的更新语句中，若出现某字段为空的情况，还是会报错 那么我们就可以使用if语句来优化该语句 

2. 使用if标签来优化更新语句

   <update id="updateStu" parameterType="Student"> update student set <if test="ssex !=null"> ssex = #{ssex}, </if> <if test="sname!= null"> sname = #{sname} </if> where sno=#{sno} </update> <!--单纯使用if修改数据，会出现问题，若sname为空的情况下，语句where前面会紧跟,--> 接着，我们可以使用set标签来优化 

3. 使用set标签继续优化更新语句

   讯享网<!--使用set标签优化修改语句，解决where前面有，的问题--> <update id="updateStu1" parameterType="Student"> update student <set> <if test="ssex !=null"> ssex = #{ssex}, </if> <if test="sname!= null"> sname = #{sname} </if> </set> where sno=#{sno} </update> 问题到这里其实就已经结束了 不过还有一种思路、 使用trim标签来进行优化更新语句 

4. 使用trim标签进行优化更新语句

   <!--使用trim标签来解决wheere前面会有，的bug--> <update id="updateStu2" parameterType="Student"> update student <trim prefix="set" suffixOverrides=","> <if test="ssex !=null"> ssex = #{ssex}, </if> <if test="sname!= null"> sname = #{sname} </if> </trim> where sno=#{sno} </update> 

三、动态删除

1. 普通的删除语句

   讯享网<delete id="deleteStu"> delete from student where sno in (#{list[1]},#{list[2]},#{list[3]}) </delete> 可以看到，普通的删除语句要想删除多个数据,要进行拼接大量的字符 那么我们就可以使用foreach标签来进行循环拼接语句 

2. 使用foreach批量删除语句

   <delete id="deleteList"> delete from student where sno in <foreach collection="list" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach> </delete> 在foreach标签中，我们可以看到： collection是传入的一个list集合 item是从集合中遍历出来的元素 index是元素的下标 open是为数据集加上一个前缀 相应的数据集的最后也需要加上一个括号） close是为sql语句最后加上一个符号 separator是指定数据分割符 

四、动态插入

​ 动态插入没什么好说的，只要掌握上面的几个标签，在数据库操作中就想干嘛干嘛，要学会举一反三

以下内容来自开发文档

五、各类语句详解

1. if语句

   讯享网<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” 的参数值需要包含查找掩码或通配符字符）。 如果希望通过 “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> 

2. choose、when、overwise

   有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。 还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员精选的 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> 

3. trim、where、set

   前面几个例子已经方便地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例，这次我们将 “state = ‘ACTIVE’” 设置成动态条件，看看会发生什么。

   讯享网<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 有一个简单且适合大多数场景的解决办法。而在其他场景中，可以对其进行自定义以符合需求。而这，只需要一处简单的改动：

   <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 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

   如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

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

   用于动态更新语句的类似解决方案叫做 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 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

   来看看与 set 元素等价的自定义 trim 元素吧：

   讯享网<trim prefix="SET" suffixOverrides=","> ... </trim> 

   注意，我们覆盖了后缀值设置，并且自定义了前缀值

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 是值。

   至此，我们已经完成了与 XML 配置及映射文件相关的讨论。下一章将详细探讨 Java API，以便你能充分利用已经创建的映射配置。

5. script

   要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如:

   讯享网 @Update({"<script>", "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}", "</script>"}) void updateAuthorValues(Author author); 

6. bind

   bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：

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

7. 多数据库支持

   如果配置了 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> 

8. 动态SQL中的插入脚本语言

   MyBatis 从 3.2 版本开始支持插入脚本语言，这允许你插入一种语言驱动，并基于这种语言来编写动态 SQL 查询语句。

   可以通过实现以下接口来插入一种语言：

   public interface LanguageDriver { ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql); SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType); SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType); } 

   实现自定义语言驱动后，你就可以在 mybatis-config.xml 文件中将它设置为默认语言：

   讯享网<typeAliases> <typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/> </typeAliases> <settings> <setting name="defaultScriptingLanguage" value="myLanguage"/> </settings> 

   或者，你也可以使用 lang 属性为特定的语句指定语言：

   <select id="selectBlog" lang="myLanguage"> SELECT * FROM BLOG </select> 

   或者，在你的 mapper 接口上添加 @Lang 注解：

   讯享网public interface Mapper { @Lang(MyLanguageDriver.class) @Select("SELECT * FROM BLOG") List<Blog> selectBlog(); } 

   提示 可以使用 Apache Velocity 作为动态语言，更多细节请参考 MyBatis-Velocity 项目。

   你前面看到的所有 xml 标签都由默认 MyBatis 语言提供，而它由语言驱动 org.apache.ibatis.scripting.xmltags.XmlLanguageDriver（别名为 xml）所提供。

六、SQL 语句构建器

问题

Java 程序员面对的最痛苦的事情之一就是在 Java 代码中嵌入 SQL 语句。这通常是因为需要动态生成 SQL 语句，不然我们可以将它们放到外部文件或者存储过程中。如你所见，MyBatis 在 XML 映射中具备强大的 SQL 动态生成能力。但有时，我们还是需要在 Java 代码里构建 SQL 语句。此时，MyBatis 有另外一个特性可以帮到你，让你从处理典型问题中解放出来，比如加号、引号、换行、格式化问题、嵌入条件的逗号管理及 AND 连接。确实，在 Java 代码中动态生成 SQL 代码真的就是一场噩梦。例如：

String sql = "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, " "P.LAST_NAME,P.CREATED_ON, P.UPDATED_ON " + "FROM PERSON P, ACCOUNT A " + "INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID " + "INNER JOIN COMPANY C on D.COMPANY_ID = C.ID " + "WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) " + "OR (P.LAST_NAME like ?) " + "GROUP BY P.ID " + "HAVING (P.LAST_NAME like ?) " + "OR (P.FIRST_NAME like ?) " + "ORDER BY P.ID, P.FULL_NAME"; 

解决方案

MyBatis 3 提供了方便的工具类来帮助解决此问题。借助 SQL 类，我们只需要简单地创建一个实例，并调用它的方法即可生成 SQL 语句。让我们来用 SQL 类重写上面的例子：

讯享网private String selectPersonSql() { 
    return new SQL() { 
   { 
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME"); SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON"); FROM("PERSON P"); FROM("ACCOUNT A"); INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID"); INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID"); WHERE("P.ID = A.ID"); WHERE("P.FIRST_NAME like ?"); OR(); WHERE("P.LAST_NAME like ?"); GROUP_BY("P.ID"); HAVING("P.LAST_NAME like ?"); OR(); HAVING("P.FIRST_NAME like ?"); ORDER_BY("P.ID"); ORDER_BY("P.FULL_NAME"); }}.toString(); } 

这个例子有什么特别之处吗？仔细看一下你会发现，你不用担心可能会重复出现的 “AND” 关键字，或者要做出用 “WHERE” 拼接还是 “AND” 拼接还是不用拼接的选择。SQL 类已经为你处理了哪里应该插入 “WHERE”、哪里应该使用 “AND” 的问题，并帮你完成所有的字符串拼接工作。

SQL 类

这里有一些示例：

// 匿名内部类风格 public String deletePersonSql() { 
    return new SQL() { 
   { 
    DELETE_FROM("PERSON"); WHERE("ID = #{id}"); }}.toString(); } // Builder / Fluent 风格 public String insertPersonSql() { 
    String sql = new SQL() .INSERT_INTO("PERSON") .VALUES("ID, FIRST_NAME", "#{id}, #{firstName}") .VALUES("LAST_NAME", "#{lastName}") .toString(); return sql; } // 动态条件（注意参数需要使用 final 修饰，以便匿名内部类对它们进行访问） public String selectPersonLike(final String id, final String firstName, final String lastName) { 
    return new SQL() { 
   { 
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME"); FROM("PERSON P"); if (id != null) { 
    WHERE("P.ID like #{id}"); } if (firstName != null) { 
    WHERE("P.FIRST_NAME like #{firstName}"); } if (lastName != null) { 
    WHERE("P.LAST_NAME like #{lastName}"); } ORDER_BY("P.LAST_NAME"); }}.toString(); } public String deletePersonSql() { 
    return new SQL() { 
   { 
    DELETE_FROM("PERSON"); WHERE("ID = #{id}"); }}.toString(); } public String insertPersonSql() { 
    return new SQL() { 
   { 
    INSERT_INTO("PERSON"); VALUES("ID, FIRST_NAME", "#{id}, #{firstName}"); VALUES("LAST_NAME", "#{lastName}"); }}.toString(); } public String updatePersonSql() { 
    return new SQL() { 
   { 
    UPDATE("PERSON"); SET("FIRST_NAME = #{firstName}"); WHERE("ID = #{id}"); }}.toString(); } 

提示 注意，SQL 类将原样插入 LIMIT、OFFSET、OFFSET n ROWS 以及 FETCH FIRST n ROWS ONLY 子句。换句话说，类库不会为不支持这些子句的数据库执行任何转换。 因此，用户应该要了解目标数据库是否支持这些子句。如果目标数据库不支持这些子句，产生的 SQL 可能会引起运行错误。

从版本 3.4.2 开始，你可以像下面这样使用可变长度参数：

讯享网public String selectPersonSql() { 
    return new SQL() .SELECT("P.ID", "A.USERNAME", "A.PASSWORD", "P.FULL_NAME", "D.DEPARTMENT_NAME", "C.COMPANY_NAME") .FROM("PERSON P", "ACCOUNT A") .INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID", "COMPANY C on D.COMPANY_ID = C.ID") .WHERE("P.ID = A.ID", "P.FULL_NAME like #{name}") .ORDER_BY("P.ID", "P.FULL_NAME") .toString(); } public String insertPersonSql() { 
    return new SQL() .INSERT_INTO("PERSON") .INTO_COLUMNS("ID", "FULL_NAME") .INTO_VALUES("#{id}", "#{fullName}") .toString(); } public String updatePersonSql() { 
    return new SQL() .UPDATE("PERSON") .SET("FULL_NAME = #{fullName}", "DATE_OF_BIRTH = #{dateOfBirth}") .WHERE("ID = #{id}") .toString(); } 

从版本 3.5.2 开始，你可以像下面这样构建批量插入语句：

public String insertPersonsSql() { 
    // INSERT INTO PERSON (ID, FULL_NAME) // VALUES (#{mainPerson.id}, #{mainPerson.fullName}) , (#{subPerson.id}, #{subPerson.fullName}) return new SQL() .INSERT_INTO("PERSON") .INTO_COLUMNS("ID", "FULL_NAME") .INTO_VALUES("#{mainPerson.id}", "#{mainPerson.fullName}") .ADD_ROW() .INTO_VALUES("#{subPerson.id}", "#{subPerson.fullName}") .toString(); } 

从版本 3.5.2 开始，你可以像下面这样构建限制返回结果数的 SELECT 语句,：

讯享网public String selectPersonsWithOffsetLimitSql() { 
    // SELECT id, name FROM PERSON // LIMIT #{limit} OFFSET #{offset} return new SQL() .SELECT("id", "name") .FROM("PERSON") .LIMIT("#{limit}") .OFFSET("#{offset}") .toString(); } public String selectPersonsWithFetchFirstSql() { 
    // SELECT id, name FROM PERSON // OFFSET #{offset} ROWS FETCH FIRST #{limit} ROWS ONLY return new SQL() .SELECT("id", "name") .FROM("PERSON") .OFFSET_ROWS("#{offset}") .FETCH_FIRST_ROWS_ONLY("#{limit}") .toString(); } 

SqlBuilder 和 SelectBuilder (已经废弃)

在版本 3.2 之前，我们的实现方式不太一样，我们利用 ThreadLocal 变量来掩盖一些对 Java DSL 不太友好的语言限制。现在，现代 SQL 构建框架使用的构建器和匿名内部类思想已被人们所熟知。因此，我们废弃了基于这种实现方式的 SelectBuilder 和 SqlBuilder 类。

下面的方法仅仅适用于废弃的 SqlBuilder 和 SelectBuilder 类。

SelectBuilder 和 SqlBuilder 类并不神奇，但最好还是知道它们的工作原理。 SelectBuilder 以及 SqlBuilder 借助静态导入和 ThreadLocal 变量实现了对插入条件友好的简洁语法。要使用它们，只需要静态导入这个类的方法即可，就像这样（只能使用其中的一条，不能同时使用）:

import static org.apache.ibatis.jdbc.SelectBuilder.*; import static org.apache.ibatis.jdbc.SqlBuilder.*; 

然后就可以像下面这样创建一些方法：

讯享网/* 已被废弃 */ public String selectBlogsSql() { 
    BEGIN(); // 重置 ThreadLocal 状态变量 SELECT("*"); FROM("BLOG"); return SQL(); } /* 已被废弃 */ private String selectPersonSql() { 
    BEGIN(); // 重置 ThreadLocal 状态变量 SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME"); SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON"); FROM("PERSON P"); FROM("ACCOUNT A"); INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID"); INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID"); WHERE("P.ID = A.ID"); WHERE("P.FIRST_NAME like ?"); OR(); WHERE("P.LAST_NAME like ?"); GROUP_BY("P.ID"); HAVING("P.LAST_NAME like ?"); OR(); HAVING("P.FIRST_NAME like ?"); ORDER_BY("P.ID"); ORDER_BY("P.FULL_NAME"); return SQL(); }