MyBatis系列（九）- MyBatis的动态SQL

MyBatis系列汇总：MyBatis系列

前言

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
上面这句话是MaBatis官网说的！这篇文章很重要！在工作中必不可少！
首先我们先建立一些测试的库。下面的示例都是建立在此表进行测试的！

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private Integer id;
    private String title;
    private String autor;
    private Date creat_time;
    private Integer reads;
}

使用动态标签，可以在xml中也就是sql中写一些基本的逻辑。十分方便好用！
造点数据，方便测试。

一、if 、where 标签

xml

    <select id="getBlogInfo" resultType="Blog" parameterType="map">
        select
           *
        from
           myblog
        where
           1 = 1
        <if test="title!='' and title!=null">
            and title like concat(concat('%',#{title}),'%')
        </if>
    </select>

mapper
- ```
List<Blog> getBlogInfo(Map<String,Object> map);
```
- 在实际工作开发中，我们传入的值一般使用map来。这样会更加方便和易扩展。
- 而返回的result，我们一般使用实体类来实现，因为Swgger，前后端联调无敌方便！！强烈推荐！
- 如果有小伙伴想了解Swgger的话，欢迎留言，博主会根据需要的程度来决定是否单独开一篇来详解Swgger！！！

Junit Test

    @Test
    public void getMyBlog(){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("title","Mybatis");
        List<Blog> myBlogMappers = mapper.getBlogInfo(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }

执行结果：
- 完美查出。
可能有人会问，为什么要加这个标签呢？
- 首先，if 标签可以让我们的sql更加灵活，这个 title 的值传的话就代表带条件查询，不传的话，即是无效代码，查询所有！
- 然后，如果在service中实现这种效果也是可以的，但是十分繁琐。所以在实际工作中，if 标签是用的最多的！
- 最后，同学们注意到上面sql中 1=1，为啥要写这个，在实际开发中，我们一般都是软删除（逻辑删除），所以一般都会有个删除标识来确定这条数据是否存在！加这个纯粹是为了模拟真实开发代码！

那么where标签什么时候用呢？

xml

    <select id="getBlogInfo1" resultType="Blog" parameterType="map">
        select
        *
        from
        myblog
        where
        <if test="title!='' and title!=null">
            title like concat(concat('%',#{title}),'%')
        </if>
        <if test="id!='' and id!=null">
            and id = #{id}
        </if>
    </select>

如果我们的语句是这样子的，那么如果 title 为空，sql语句是不是相当于where后面直接加上了and？

演示：

    @Test
    public void getMyBlog1(){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("id","2");
        List<Blog> myBlogMappers = mapper.getBlogInfo1(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }

报错信息如下

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id = '2'' at line 8
### The error may exist in com/dy/dynamic/mapper/MyBlogMapper.xml
### The error may involve com.dy.dynamic.mapper.MyBlogMapper.getBlogInfo1-Inline
### The error occurred while setting parameters
### SQL: select         *         from         myblog         where                                 and id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id = '2'' at line 8

使用where标签

    <select id="getBlogInfo1" resultType="Blog" parameterType="map">
        select
        *
        from
        myblog
        <where>
        <if test="title!='' and title!=null">
            title like concat(concat('%',#{title}),'%')
        </if>
        <if test="id!='' and id!=null">
            and id = #{id}
        </if>
        </where>
    </select>

执行看结果
完美解决！

如果没有匹配的条件会怎么样？最终这条 SQL 会变成这样：
- ```
SELECT * FROM BLOG
WHERE
```
这会导致查询失败。如果匹配的只是第二个条件又会怎样？这条 SQL 会是这样:
- ```
SELECT * FROM BLOG
WHERE AND id = 2
```
- 这个查询也会失败。这个问题不能简单地用条件元素来解决。
where标签的作用显而易见了！
- 当条件有and时，它可以判断该条件是否是第一个条件，是的话自动去除。
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
- ```
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
```
- 这个trim 下面再讲。

二、choose、when、otherwise 标签

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
if 讲完了，记不记得还有一种判断条件的东东？
- 没错就是 switch case (JAVA中)
- 在动态sql中就是 choose
- 下面来玩一玩这玩意

xml

    <select id="getBlogInfoWhoose" resultType="Blog" parameterType="map">
        select
            *
        from
            myblog
       <where>
          <choose>
              <when test="title!='' and title!=null">
                  title like concat(concat('%',#{title}),'%')
              </when>
              <when test="id!='' and id!=null">
                  and id = #{id}
              </when>
              <otherwise>
                  AND `reads` > 10000
              </otherwise>
          </choose>
       </where>
    </select>

mapper

List<Blog> getBlogInfoWhoose(Map<String,Object> map);

Junit Test

    @Test
    public void getMyBlog1(){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("title","Spring");
        List<Blog> myBlogMappers = mapper.getBlogInfoWhoose(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }

执行结果

当没有满足choose中的条件时

Junit Test

@Test
 public void getMyBlog1(){
     SqlSession session = MybatisUtils.getSession();
     MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
     Map<String,Object> map = new HashMap<>();
//        map.put("title","Spring");
        List<Blog> myBlogMappers = mapper.getBlogInfoWhoose(map);
        for (Blog myBlogMapper : myBlogMappers) {
            System.out.println(myBlogMapper);
        }
        session.close();
    }

执行结果
- 这样只查出来readis（阅读量）大于10000的数据

总结下这几个标签
- 类似reads这种关键字，需要加飘号 `
- 所有条件都不满足的时候就输出 otherwise 中的内容。
- when元素表示当 when 中的条件满足的时候就输出其中的内容，跟 JAVA 中的 switch 效果差不多的是按照条件的顺序。

三、trim、set 标签

上面说的这么多，都是在讲select（查询），那么现在来讲一下 update 中的标签 set
基本的update语句我这里就不再重复写了，大家只需要关注这种动态语句即可。在实际工作中开发一般以这种居多，毕竟它比较灵活嘛！

①、set 标签

xml

    <update id="updateBlogName" parameterType="map">
        update myblog
        <set>
            <if test="title!=null and title!=''">
                title = #{title},
            </if>
            <if test="autor!=null and autor!=''">
                autor = #{autor},
            </if>
            <if test="reads!=null and reads!=''">
                `reads` = #{reads}
            </if>
        </set>
        where
            id = #{id}
    </update>

mapper

Integer updateBlogName(Map<String,Object> map);

Junit Test

    @Test
    public  void updateBlogName(){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("id","3");
        map.put("title","富婆让我陪她逛街");
        map.put("autor","大大大大鱼");
        map.put("reads","100000");
        Integer num = mapper.updateBlogName(map);
        System.out.println("一共更新了："+num+"条数据");
        session.commit();//更新不要忘记提交事务哦
        session.close();
    }

执行结果
大家有没有发现，我们在sql中是将set语句后面的逗号写死的？
- 那么，如果我们只传了title 和autor呢？
- 理论上是不是语句变成这样？
  - ```
  update myblog set
      title = #{title},
  	autor = #{autor},
  where id = #{id}
```
- 我们执行一下试试
- 更新成功，原来set标签和where标签一样，这么智能呀！
- set标签可以自动帮我识别语句结尾的逗号并进行一定的处理！
- 这样我们写的sql语句是不是更加具有灵活性呢？十分方便好用！

②、trim 标签

通过上面的例子我们知道了，where 、set 标签可以去除逗号、and 、or 这种连接符。
trim 标签也是可以做到的！

xml

    <insert id="insertBlogName" parameterType="map">
        insert into myblog
        (
        <if test="title!=null and title!=''">
            title,
        </if>
        <if test="autor!=null and autor!=''">
            autor,
        </if>
        <if test="reads!=null and reads!=''">
            `reads`,
        </if>
        <if test="creat_time!=null">
            `creat_time`
        </if>
        )
        values(
        <if test="title!=null and title!=''">
            #{title},
        </if>
        <if test="autor!=null and autor!=''">
            #{autor},
        </if>
        <if test="reads!=null and reads!=''">
            #{reads},
        </if>
        <if test="creat_time!=null">
            #{creat_time}
        </if>
        )
    </insert>

mapper

Integer insertBlogName(Map<String,Object> map);

Junit Test

    @Test
    public void insetBlogInfo(){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("title","如何榜上富婆？");
        map.put("autor","大鱼");
        map.put("reads","1000");
        map.put("creat_time",new Date());
        Integer num = mapper.insertBlogName(map);
        System.out.println("一共新增了："+num+"条数据");
        session.commit();
        session.close();
    }

执行结果
记得session.commit(); 提交事务哦~
这个sql如果我们不是传所有，是不是也会出现上面的问题？就是多个逗号，导致sql执行出错？

我们可以使用trim标签来完成自动去除逗号等连接符

xml改造

    <insert id="insertBlogName1" parameterType="map">
        insert into myblog
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="title!=null and title!=''">
                title,
            </if>
            <if test="autor!=null and autor!=''">
                autor,
            </if>
            <if test="reads!=null and reads!=''">
                `reads`,
            </if>
            <if test="creat_time!=null">
                `creat_time`
            </if>
        </trim>
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="title!=null and title!=''">
                #{title},
            </if>
            <if test="autor!=null and autor!=''">
                #{autor},
            </if>
            <if test="reads!=null and reads!=''">
                #{reads},
            </if>
            <if test="creat_time!=null">
                #{creat_time}
            </if>
        </trim>
    </insert>

执行结果
完美解决问题！
重点解析
- prefix：开头所需加的
- suffix：结束所需加的
- suffixOverrides：每行语句结束时需要加的东西
- prefixOverrides：每行语句开始时需要加的东西
注意点
- 当数据库中设置了时间为datetime时，我们if标签中不要判断不为空
- 只需要判断不为null即可

四、foreach 标签

在sql中写for循环是种什么感受？
来玩一个需求：查询id为1、2、4、5的博客数据。
学到这里了，千万不要这样用and这种捞的写法。
- ```
select * from myblog where id = 1 or id = 2 or id = 4 or id = 5
```
①、foreach简单用法

xml

   <select id="getBlogInfos" parameterType="map" resultType="Blog">
        select * from myblog
        <where>
            <foreach collection="ids" item="id" open="and ("
                     close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

mapper

List<Blog> getBlogInfos(Map<String,Object> map);

Junit Test

    @Test
    public void getBlogInfos(){
        SqlSession session = MybatisUtils.getSession();
        MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        List<String> ids = new ArrayList<>();
        ids.add("1");
        ids.add("2");
        ids.add("4");
        ids.add("5");
        map.put("ids",ids);
        List<Blog> list = mapper.getBlogInfos(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
        session.close();
    }

执行结果
- foreach标签
  - collection ：这个是我们在标签内所需遍历的集合，即放入map中的key
  - item ：遍历出来的值我们所赋予的key
  - open ：开始所加的参数
  - close ：结束所加的参数
  - separator ：每个值中间所加的参数
②、where in 配合foreach用法
- xml
  - ```
     <select id="getBlogInfos1" parameterType="map" resultType="Blog">
          select * from myblog
          <where>
              id in
              <foreach collection="ids" item="id" open="("
                       close=")" separator=",">
                  #{id}
              </foreach>
          </where>
      </select>
```
- 其他地方不需要变
- 这个最终效果是
  - ```
  select * from myblog WHERE id in ( ? , ? , ? , ? ) 
```
- 只列举这两个例子吧，具体怎么用还是得看具体的需求。
- sql的长度是有限制的，这个ids里面的值太多会无法查询。
- sql最大长度是：默认的SQL拼接长度最大值是2000个参数。

五、sql、include 标签

上面那么多标签玩下来，大家会发现一个问题，就是同一个业务中，可能会有很多重复的内容，比如每个查询都有title、autor等相同内容。
那么我们有没有类似于java中工具类的方法呢？
这时候就需要用到我们的sql、include 标签啦~

xml

    <sql id="if-key-info">
        <if test="title!=null and title!=''">
            title = #{title}
        </if>
        <if test="autor!=null and autor!=''">
            and autor = #{autor}
        </if>
        <if test="reads!=null and reads!=''">
            and `reads` = #{reads}
        </if>
    </sql>

    <select id="getBlogInfo" resultType="Blog" parameterType="map">
        select
           *
        from
           myblog
        <where>
            <include refid="if-key-info"></include>
        </where>
    </select>