Mybatis动态SQL

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 * from student where ssex = #{ssex} and sage > #{sage}
   
   
   分析：这个语句是存在漏洞的，假如ssex的值为空，则会出现
    select * from student where ssex =  and sage > sage
   

2. 使用if语句来优化

   
       select * from student where 1=1     
       
       
           and ssex = #{ssex}
       
       
           and sage > #{sage}
       
       
           and sdept =#{sdept}
       
   
   
   看上去优化了不少
   不过多加入一个条件1=1就是让人不爽
   那么我们还可以使用where标签进行优化
   

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

   
       select * from student
           
           
               ssex = #{ssex}
           
           and sage >30
       
   
   
   where标签可以自动处理AND或者OR这些
   我们还可以通过 trim 标签去自定义这种处理规则。
    
    
   使用trim标签进行处理规则
    
   
       select * from student
       
       
           
               ssex = #{ssex}
           
           and sage > 30
       
   
   
   prefix的意思是前缀
   trim中的语句意思为，去除前缀为where后面的and或or字符串
   这样的话就可以直接处理where和and、or直接拼接的问题了
   当然，对于查询条件唯一但不确定的情况下，我们还可以使用choose-when-otherwise进行优化
   

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

   
       select * from student
           
           
               
                   ssex = #{ssex}
               
               
                   ssex = '女'
               
           
           and sage =34
       
   
     
   二、动态更新 
    
   普通的更新语句
    
   
       update student set (ssex = #{ssex},sname = #{sname}) where sno=#{sno}
   
   分析：普通的更新语句中，若出现某字段为空的情况，还是会报错
   那么我们就可以使用if语句来优化该语句
    
    
   使用if标签来优化更新语句
    
   
       update student set
       
           ssex = #{ssex},
       
       
           sname = #{sname}
       
       where sno=#{sno}
   
   
   接着，我们可以使用set标签来优化
    
    
   使用set标签继续优化更新语句
    
   
       update student
       
           
               ssex = #{ssex},
           
           
               sname = #{sname}
           
       
       where sno=#{sno}
   
   问题到这里其实就已经结束了
   不过还有一种思路、
   使用trim标签来进行优化更新语句
    
    
   使用trim标签进行优化更新语句
    
   
       update student
       
           
               ssex = #{ssex},
           
           
               sname = #{sname}
           
       
       where sno=#{sno}
   
    
    
   三、动态删除 
    
   普通的删除语句
    
   
       delete from student where sno in (#{list[1]},#{list[2]},#{list[3]})
   
   
   可以看到，普通的删除语句要想删除多个数据,要进行拼接大量的字符
   那么我们就可以使用foreach标签来进行循环拼接语句
    
    
   使用foreach批量删除语句
    
   
       delete from student where sno in
       
           #{item}
       
   
   
   在foreach标签中，我们可以看到：
   collection是传入的一个list集合
   item是从集合中遍历出来的元素
   index是元素的下标
   open是为数据集加上一个前缀
   相应的数据集的最后也需要加上一个括号）
   close是为sql语句最后加上一个符号
   separator是指定数据分割符
    
    
   四、动态插入 
   ​ 动态插入没什么好说的，只要掌握上面的几个标签，在数据库操作中就想干嘛干嘛，要学会举一反三
    
   以下内容来自开发文档
    
   五、各类语句详解 
    
   if语句
    
   
     SELECT * FROM BLOG
     WHERe state = ‘ACTIVE’
     
       AND title like #{title}
     
   
   这条语句提供了可选的查找文本功能。如果不传入 “title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果（细心的读者可能会发现，“title” 的参数值需要包含查找掩码或通配符字符）。
   如果希望通过 “title” 和 “author” 两个参数进行可选搜索该怎么办呢？首先，我想先将语句名称修改成更名副其实的名称；接下来，只需要加入另一个条件即可。
   
     SELECT * FROM BLOG WHERe state = ‘ACTIVE’
     
       AND title like #{title}
     
     
       AND author_name like #{author.name}
     
   
    
    choose、when、overwise 
   有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
   
   还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员精选的 Blog）
   
     SELECT * FROM BLOG WHERe state = ‘ACTIVE’
     
       
         AND title like #{title}
       
       
         AND author_name like #{author.name}
       
       
         AND featured = 1
       
     
   
   

5. trim、where、set

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

   
     SELECT * FROM BLOG
     WHERe
     
       state = #{state}
     
     
       AND title like #{title}
     
     
       AND author_name like #{author.name}
     
   
    
   如果没有匹配的条件会怎么样？最终这条 SQL 会变成这样：
    
   SELECT * FROM BLOG
   WHERe
    
   这会导致查询失败。如果匹配的只是第二个条件又会怎样？这条 SQL 会是这样:
    
   SELECt * FROM BLOG
   WHERe
   AND title like ‘someTitle’
    
   这个查询也会失败。这个问题不能简单地用条件元素来解决。这个问题是如此的难以解决，以至于解决过的人不会再想碰到这种问题。
    
   MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中，可以对其进行自定义以符合需求。而这，只需要一处简单的改动：
    
   
     SELECT * FROM BLOG
     
       
            state = #{state}
       
       
           AND title like #{title}
       
       
           AND author_name like #{author.name}
       
     
   
    
   where 元素只会在子元素返回任何内容的情况下才插入 “WHERe” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
    
   如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
    
   
     ...
   
    
   prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。
    
   用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：
    
   
     update Author
       
         username=#{username},
         password=#{password},
         email=#{email},
         bio=#{bio}
       
     where id=#{id}
   
    
   这个例子中，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
    
   来看看与 set 元素等价的自定义 trim 元素吧：
    
   
     ...
   
    
   注意，我们覆盖了后缀值设置，并且自定义了前缀值
    
    
   foreach
    
   动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。比如：
    
   
     SELECT *
     FROM POST P
     WHERe ID in
     
           #{item}
     
   
   

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

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

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

6. script

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

       @Update({""})
       void updateAuthorValues(Author author);
   

7. bind

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

   
     
     SELECT * FROM BLOG
     WHERe title LIKE #{pattern}
   
    
    
   多数据库支持
    
   如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。比如下面的例子：
    
   
     
       
         select seq_users.nextval from dual
       
       
         select nextval for seq_users from sysibm.sysdummy1"
       
     
     insert into users values (#{id}, #{name})
   
    
    
   动态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 文件中将它设置为默认语言：
    
   
     
   
   
     
   
    
   或者，你也可以使用 lang 属性为特定的语句指定语言：
    
   
     SELECT * FROM BLOG
   
   

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

   public interface Mapper {
     @Lang(MyLanguageDriver.class)
     @Select("SELECT * FROM BLOG")
     List selectBlog();
   }
   

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

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

问题

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();
}