MyBatis动态SQL

原创
${user.getUserNickname()}
LoveIT
2019-07-29 10:36:59
701

MyBatis的强大特性之一便是它的动态SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

MyBatis动态SQL元素和使用 JSTL或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。主要的有以下几个：

  • if 可以筛选一到多条SQL分支
  • where 一般配合if使用，作用相当于SQL语句中的where关键字
  • choose 、when、otherwise 筛选一条SQL分支
  • trim 类似于 replace 效果，可以完成 set 或者是 where 标记的功能
  • set 相当于SQL中的set关键字
  • foreach 遍历集合

一、if 元素

使用if可以用条件的筛选SQL语句分支，只有条件满足的时候才会执行。实例：

<select id="get" resultType="Employee">
    select * from employee
       <if test="empSex!=null">and emp_sex=#{empSex} </if>
       <if test="empAge!=null">and emp_age=#{empAge}</if>
       <if test="empName!=null">or emp_name=#{empName}</if>
</select>

这条sql语句就会动态的根据传入的参数值来查询，比如当只传了empAge=23，其他都为空，那么生成的sql语句就是：select * from employee where emp_age=23。其实管使用if标签这么写是有问题的，下面有具体分析以及改进方法。

二、choose、when、otherwise元素

MyBatis 提供了 choose元素，它有点像 Java 中的 switch 语句，即在choose中的SQL分支是由一条会被执行。如下：

<!--下面的条件分支中只会有一条被执行-->
<select id="getBycondition" resultType="Employee">
select * from employee where
<choose>
    <when test="empId!=null">emp_id=#{empId}</when>
    <when test="empAge!=null">emp_age=#{empAge}</when>
    <when test="empName!=null">emp_name=#{empName}</when>
	<!--默认情况上面都没有匹配上的时候采用这个分支-->
    <otherwise>emp_sex='女'</otherwise>
</choose>
</select>

测试代码：

public class AppTest{
    SqlSessionFactory sqlSessionFactory;
    private static final Logger log=Logger.getLogger(AppTest.class);

    @Before
    public void createSqlSession() throws IOException {
        String resource="mybatis-config.xml";
        try (InputStream inputStream = Resources.getResourceAsStream(resource)) {
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }
    }

    @Test
    public void get(){
        SqlSession session=sqlSessionFactory.openSession(true);
        try{
            Employee employee=new Employee();
            employee.setEmpAge(34);
            employee.setEmpId("3343");
            EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
            List<Employee>lists=mapper.getBycondition(employee);
            for(Employee list:lists){
                System.out.println(list.toString());
            }
            log.info(mapper);
        }catch (Exception  e){
            System.out.println("查询数据异常..."+e);
            session.rollback();
        }finally {
            session.close();
        }
    }
}

可以看到，即使在条件中给了两个不为空的，但是由于EmpId在最前面，因此首先匹配上后就不在往下找了，和switch很像。最终的运行结果：

三、trim, where元素

看看下面的这条这种情况（一中的例子）：

<select id="getEmp" resultType="Employee">
   select * from employee where 
   <if test="empAge!=null">emp_age=#{empAge}</if>
   <if test="empAge!=null"> and emp_age=#{empAge}</if>
   <if test="empAge!=null">and emp_age=#{empAge}</if>
</select>

如果三个分之没有一条分支匹配上，那么最终的sql语句会变成：

select * from employee where

这显然是会导致查询失败的。如果仅仅第二条或第三条匹配，那么sql语句会变成这样:

select * from employee where  and emp_age=#{empAge}
/*或者是下面的样子*/
select * from employee where  and emp_age=#{empAge}

不论是那种情况，都会导致由于sql语句错误而查询失败。MyBatis有一个简单的处理，这在大多数情况下都会有用——那就是使用where元素，where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。例如：

<select id="getEmp" resultType="Employee">
   select * from employee 
   <where> 
      <if test="empAge!=null">emp_age=#{empAge}</if>
      <if test="empAge!=null"> and emp_age=#{empAge}</if>
      <if test="empAge!=null">and emp_age=#{empAge}</if>
   </where>
</select>

这样即使三条语句都不满足条件，那么最终的sql语句是：select * from employee，也不会影响正常的查询。

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

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

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除，并且插入 prefix 属性中指定的内容。 类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的.

trim标签的全部属性

prefix：前缀增加的内容 suffix：后缀增加的内容 prefixOverrides：前缀覆盖第一个判断的条件 suffixOverrides：后缀覆盖最后一个判断的条件

四、set元素

MyBatis中用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的.示例：

<update id="updateByConditon">
update employee
<set>
    <if test="param1.empId!=null">
      emp_id=#{param1.empId},
    </if>
    <if test="param1.empName!=null">
       emp_name=#{param1.empName},
    </if>
    <if test="param1.empSex!=null">
       emp_sex=#{param1.empSex},
    </if>
    <if test="param1.empAge!=null">
       emp_age=#{param1.empAge}
    </if>
</set>
where emp_age=#{param2}
</update>

测试代码：

 @Test
public void updateByConditon(){
   SqlSession session=sqlSessionFactory.openSession(true);
   try{
        EmployeeMapper em=session.getMapper(EmployeeMapper.class);
        List<Employee> lists=em.getAll();
        for(Employee list:lists){
           list.setEmpName("蔡徐坤");
           em.updateByConditon(list,22);
         }
         log.info(em);
     }finally {
         session.close();
     }
}

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

五、foreach元素

动态 SQL 的另外一个常用的必要操作是需要各种集合集合进行遍历，通常是在构建各种条件语句以及批量插入数据的时候的时候使用较多。

foreach元素有许多属性，在使用之前先了解一下foreach元素的属性：

属性作用
collectioncollection用于遍历的集合的名字，MyBatis支持任何可迭代对象的迭代，常见的比如 List、Set 、Map对象或者数组等
item表示本次迭代获取到的元素
index遍历过程的索引值 。
open前缀
close后缀
separator分隔符，表示迭代时每个元素之间以什么分隔

注意！ 当使用 Map 对象（或者 Map.Entry 对象的集合）迭代时，index 是键，item 是值

1、foreach遍历传递进来的集合，构建条件

有时候我们可能会有下面的需求，根据多个 id 查询对应的信息，这多个 id 的数量是不固定的。

SELECT * FROM t_employee
    WHERE id IN (1, 2, 3, ...)

这时候我们可以通过使用foreach标签来遍历集合中的参数，完成多个 id 之间的拼接。

Mapper接口：

 /**
  * 根据分类名模糊查询这一类的文章
  * @param query  分类名
  * @return  java.util.List
  */
List<Article> getArticleByCategoryNameFuzzy(@Param("query") ArrayList<String> query);

SQL 映射文件：

<select id="getArticleByCategoryNameFuzzy" parameterType="arraylist" resultType="top.easyblog.bean.Article">
    select * from article where
    <foreach collection="query" item="key" separator=" or">                               
        article_category like concat(#{key},'%')                                         
    </foreach>   
</select>
2、使用foreach元素批量插入数据

当需要插入的数据比较多的时候，此时再一条一条的插入数据就显得比较慢了，MySQL提供了一次插入多条数据的语法insert into tableName(......) values (......)[,(......),(......)],使用这条语句可以一次插入多条数据 Mapper接口：

/**
*批量插入数据
*/
int insertBatch(ArrayList<String> args);

SQL 映射文件：

<insert id="insertBatch" parameterType="com.jas.mybatis.bean.Employee">
    INSERT INTO t_employee(username, gender, email) VALUES 
    <foreach collection="list" item="emp" open="(" separator=","  close=")">
          (#{emp.username}, #{emp.gender}, #{emp.email})
    </foreach>
</insert>

总结

这篇博文主要对 常用的MyBatis动态SQL元素进行了介绍与其使用场景的应用，MyBatis 还提供了其他的元素来支持动态 SQL，熟练使用这些元素，才能在开发的时候写出简洁高效的SQL语句，不常用的元素信息可以到官方文档进行深入了解，希望这篇博文能够为你提供一些帮助。

MyBatis动态 SQL 官方文档链接： http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html

留言区

还能输入500个字符