sql case when用法_一文打尽动态SQL

兄弟们好，今天我们来聊一下动态SQL

什么是动态的SQL呢？顾名思义，SQL可以动态的更新，在我们业务中往往会根据某些条件对数据库中的数据进行一些特殊的更新，而在不是用动态SQL技术的时候，这种需求的实现往往很复杂，同时也很难维护。在MyBatis中提供了动态SQL的支持，他依靠OGNL表达式实现了一些条件的判断和数据的迭代。下面我们一起来聊聊MyBatis中的动态SQL。

1. 条件语句

1.1 if

if语句和我们在Java中的if相同，也是根据条件来执行代码块中的内容。我们来看一下它的语法规则。

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

上述代码分析：

• if标签中的test属性中的语句如果为true则执行标签内的语句。

如果条件为真，则语句为

SELECT * FROM BLOG  WHERE state = ‘ACTIVE’AND title like #{title}

否则为

SELECT * FROM BLOG  WHERE state = ‘ACTIVE

我们再来看一下这种情况：

<select id="findActiveBlogWithTitleLike"resultType="Blog">
  SELECT * FROM BLOG
  <if test="title != null">
    AND title like #{title}
  if>
select>

上述代码分析：

如果test条件为真，则语句为：

SELECT * FROM BLOG  WHERE AND title like #{title}

这个时候的语句就违背了SQL的语法规则，所以在单独使用if语句的时候，往往需要添加如下代码

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

上述代码分析：

条件为真语句为：

SELECT * FROM BLOG  WHERE 1=1 AND title like #{title}

注意：

• if语句可以多次出现，也就是可以进行多重if判断

1.2 choose、when、otherwise

这个语句类似于我们Java中的IF ELSE语句。看它的语法

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

上述代码分析：

• 执行choose中的脚本内容
• 首先判断第一个when中的test是否为真，则之后的在choose中的脚本都不会执行，相当于直接break
• 如果第一个when的结果为false，则进入第二个；
• 如果第二个when的结果也为false，则直接执行otherwise中的内容

我们看一下场景的SQL

第一个when为真

SELECT * FROM BLOG WHERE state = ‘ACTIVE’AND title like #{title}

第二个when为真

SELECT * FROM BLOG WHERE state = ‘ACTIVE’AND author_name like #{author.name}

前两个都为假

SELECT * FROM BLOG WHERE state = ‘ACTIVE’ AND featured = 1

同样，这个语句也会出现上面if所出现的当where后面没有语句的时候，直接使用条件判断语句，会出现SQL的语法错误现象，也需要通过1=1来解决。

1.3 CASE WHEN THEN ELSE END

这个语句不是由MyBatis提供的，而是原声的SQL语句，它可以简化我们很多的操作，租用有点类似于Switch。

<select id="findActiveBlogLike"resultType="Blog">
  SELECT * FROM BLOG WHERE state = CASE name
     WHEN ${name} THEN '第一个执行结果'
     WHEN ${name}  THEN '第二个执行结果'
   ELSE '最后的执行结果'
     END
select>

上述代码分析：

• 首先它会使用CASE后面的变量和WHEN后面的变量进行比较(这个地方类似于一个switch语句)
• 如果第一个WHEN匹配，则直接返回THEN以后的值
• 如果WHEN中没有匹配的直接返回ELSE中的值，最后以END结束

我们再来看它的另一种写法，这个写法类似于一个IF ELSE

  SELECT * FROM BLOG WHERE state = CASE 
     WHEN name = ${name} THEN '第一个执行结果'
     WHEN name = ${name}  THEN '第二个执行结果'
   ELSE '最后的执行结果'
     END

上述代码分析：

• 直接在WHEN语句中书写判断式的语句，和if ELSE相同，也是仅仅执行一个分支

1.4 TRIM WHERE SET

在上面聊if和choose,when,otherwise的时候，我们发现了一个问题，当在where关键字后面不存在已有条件的时候，会出现SQL语句语法出错的情况，我们的解决办法是加上1=1让一个条件恒成立。不过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元素实现

<select id="findActiveBlogLike"resultType="Blog">
  SELECT * FROM BLOG
   <trim prefix="WHERE" prefixOverrides="AND |OR ">
    <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>
 trim>
select>

分析上述代码：

• trim标签的prefix前缀会在子元素有返回的时候返回一个该属性的属性值
• prefixOverrides属性，会将子句返回的结果中开头为属性值的元素移除，通过管道符|进行分割(注意空格)

这个时候还需要和大家提一个元素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>

分析上述代码：

• 和where类似，它也是在子句有返回的时候才会添加一个SET语句

同样它也可以写成trim的方式

<update id="updateAuthorIfNecessary">
  update Author
    <trim prefix="SET" suffixOverrides=",">
           <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>
 trim>
  where id=#{id}
update>

2. 循环语句

循环语句只有一个foreach,这个名字非常熟悉，几乎所有的语言中都这个名字。我们来看一下它的语法

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

分析上述代码：

• 当collection是列表的时候
  • item代表列表中的元素
  • index代表正在迭代的下标
• 当collection是映射的时候
  • item代表的是value
  • index代表key
• open代表了迭代最开始的符号
• separator代表了每一个迭代元素之间的分割符号
• close代表了结束符号

当collection是map的时候

<update id="updatePostIn" resultType="int">
  UPDATE POST P
  WHERE CASE NAME
  <foreach item="value" index="key" collection="map.entrySet()"open="" separator="\n" close="END">
        WHEN '${key}' THEN '${value}'
  foreach>
select>

分析上述代码：

• 上述代码使用了CASE WHEN THEN ELSE END语法，进行了一个判断筛选

3. 注解使用动态SQL

现在我们业务开发一般都是基于注解的驱动开发，所以需要在注解中直接书写动态SQL，MyBatis提供了一个script标签帮助我们来注解中书写动态SQL

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

语法和xml类似，就通过一个scirpt标签进行了包裹而以

4. 使用Java代码构建SQL

Mybatis提供了一个SQL构建器，帮助我们可以通过Java代码构建一条SQL

我们来看一下案例

public class UserSqlProvider {
    private String findAll(){
        return new SQL()
                .SELECT("u.id","u.username","u.password","i.image")
                .FROM("user u")
                .LEFT_OUTER_JOIN("image i on i.uid=u.id")
                .WHERE("u.username='makerStack'")
                .OR()
                .WHERE("u.id <> 3")
                .WHERE("i.id = 5").toString();
    }
}


//Mapper接口
public interface UserMapper {

    @SelectProvider(type = UserSqlProvider.class , method = "findAll")
    List findAll();

分析上述代码;

• 这是通过链式语法构建的SQL
  • WHERE后面默认是AND，如果想要使用OR，需要在后面写明OR()(如上例)
• 通过@SelectProvider注解，调用UserSqlProvider中定义的SQL

再来看一下另一种写法

public String findById(final Integer id){
        return new SQL(){{
            SELECT("P.ID");
            FROM("PERSON");
            if (id != 1){
                WHERE("");
            }
        }}.toString();
    }

分析上述代码：

• 和上面的相比，它使用了匿名内部类的方式，通过代码块直接调用内部的方法构建SQL，所以可以书写if语句，进行条件判断。

好啦，关于动态SQL的内容就只有这些了，兄弟们，下期见

往期原创好文推荐：

lsp都要看的内存模型

抽丝剥茧——状态设计模式

抽丝剥茧——模板方法设计模式

抽丝剥茧——迭代器设计模式

ThreadLocal为啥要用弱引用？不知道