MyBatis-06-动态SQL

十、动态SQL

• 什么是动态SQL？
  • 根据给出参数的不同，通过拼接等操作，生成不同的SQL语句

10.1 构造实验环境

• 数据库设计

  CREATE TABLE `blog` (
    `id` varchar(50) NOT NULL COMMENT '博客id',
    `title` varchar(100) NOT NULL COMMENT '博客标题',
    `author` varchar(30) NOT NULL COMMENT '博客作者',
    `create_time` datetime NOT NULL COMMENT '创建时间',
    `views` int NOT NULL COMMENT '浏览量'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  

• mybatis-config.xml

  <settings>
      <!--开启日志以及自动转换驼峰命名-->
      <setting name="logImpl" value="STDOUT_LOGGING"/>
      <setting name="mapUnderscoreToCamelCase" value="true"/>
  </settings>
  

• 实体类

  package com.pbx.pojo;
  
  import java.util.Date;
  
  /**
   * @author BruceXu
   * @date 2020/11/8
   */
  public class Blog {
      private String id;
      private String title;
      private String author;
      private Date createTime;
      private int views;
  }
  

• id工具类

  package com.pbx.utils;
  
  import java.util.UUID;
  
  /**
   * @author BruceXu
   * @date 2020/11/8
   */
  public class IDUtils {
      public static String getId() {
          return UUID.randomUUID().toString().replaceAll("-","");
      }
  }
  

• BlogMapper接口

  package com.pbx.mapper;
  
  import com.pbx.pojo.Blog;
  import org.apache.ibatis.annotations.Insert;
  
  /**
   * @author BruceXu
   * @date 2020/11/8
   */
  public interface BlogMapper {
      @Insert("insert into blog (id, title, author, create_time, views) values (#{id},#{title},#{author},#{createTime},#{views});")
      int addBlog(Blog blog);
  }
  
  

• 插入数据

  @Test
  public void addBlog() {
      SqlSession session = MyBatisUtils.getSqlSession();
      BlogMapper mapper = session.getMapper(BlogMapper.class);
      Blog blog = new Blog();
      blog.setId(IDUtils.getId());
      blog.setTitle("Mybatis如此简单");
      blog.setAuthor("BruceXu");
      blog.setCreateTime(new Date());
      blog.setViews(9999);
  
      mapper.addBlog(blog);
  
      blog.setId(IDUtils.getId());
      blog.setTitle("Java如此简单");
      mapper.addBlog(blog);
  
      blog.setId(IDUtils.getId());
      blog.setTitle("Spring如此简单");
      mapper.addBlog(blog);
  
      blog.setId(IDUtils.getId());
      blog.setTitle("微服务如此简单");
      mapper.addBlog(blog);
  
      blog.setId(IDUtils.getId());
      blog.setTitle("狂神说Java真的好");
      mapper.addBlog(blog);
      session.commit();
      session.close();
  }
  

10.2 if

• 这个if和高级语言里面的判断语句if是一样的功能

• 需求

  • 根据author和title来查询博客，如果一个为空，则就按照另外一个进行查询

• BlogMapper接口

  List<Blog> getBlog(Map map);
  

• BlogMapper.xml

  <mapper namespace="com.pbx.mapper.BlogMapper">
      <select id="getBlog" parameterType="map" resultType="Blog">
          select * from blog where 1=1
          <if test="title != null">
              and title = #{title}
          </if>
          <if test="author != null">
              and author = #{author}
          </if>
      </select>
  </mapper>
  

• 测试

  @Test
  public void getBlog() {
      SqlSession session = MyBatisUtils.getSqlSession();
      BlogMapper mapper = session.getMapper(BlogMapper.class);
      Map<String, String> map = new HashMap<>();
  //        map.put("title", "Mybatis如此简单");
  //        map.put("author", "BruceXu");
  
      List<Blog> blogList = mapper.getBlog(map);
      for (Blog blog : blogList) {
          System.out.println(blog);
      }
      session.close();
  }
  

  

  

  

• 如果说if中的条件有满足的话，则拼接上SQL语句如果没有满足的话，则不进行拼接

10.3 choose (when, otherwise)

• choose的作用类似于高级语言中的switch~case语句块

• 需求：根据author或title来查询博客，有谁查谁，如果都没有就按照views查询

• BlogMapper接口

  List<Blog> getBlog2(Map map);
  

• BlogMapper.xml

  <mapper>
      <select id="getBlog2" parameterType="map" resultType="Blog">
          select  * from blog where 1=1
          <choose>
              <when test="title != null">
                  and title = #{title}
              </when>
              <when test="author != null">
                  and author = #{author}
              </when>
              <otherwise>
                  and views = 9999
              </otherwise>
          </choose>
      </select>
  </mapper>
  

• 测试

  @Test
  public void getBlog2() {
      SqlSession session = MyBatisUtils.getSqlSession();
      BlogMapper mapper = session.getMapper(BlogMapper.class);
      Map<String, String> map = new HashMap<>();
  
  //        map.put("title", "Java如此简单");
  //        map.put("author", "BruceXu");
      List<Blog> blogList = mapper.getBlog2(map);
      for (Blog blog : blogList) {
          System.out.println(blog);
      }
      session.close();
  }
  

  

  

  

• 可以看到，如果when中的条件没有一个匹配上，那么拼接上otherwise中的内容。如果when中有多个匹配，那么则只拼接第一个匹配的

10.4 trim (where, set)

• 如果仔细看上面两个测试环境中使用的SQL语句，都会发现存在这么一点 where 1=1，这个是为了保证在进行拼接的时候不出现这样的错误：select * from blog where and author = ...

• 那么有没有这样的一种方法，能够自动判断是不是要加上and或者空格等符号以保证SQL语句的正确性？

• trim标签，当然，MyBatis也为我们提前封装好了where和set标签

  <trim prefix="前缀匹配" prefixOverrides="前缀替换" suffix="后缀匹配" suffixOverrides="后缀替换">...</trim>
  

• where和set示例

  • BlogMapper接口

  List<Blog> getBlog3(Map map);
  int updateBlog(Map map);
  

  • BlohMapper.xml

  <mapper>
      <select id="getBlog3" parameterType="map" resultType="Blog">
          select * from blog
          <where>
              <if test="title != null">
                  and title = #{title}
              </if>
              <if test="author != null">
                  and author = #{author}
              </if>
          </where>
      </select>
      
      <update id="updateBlog" parameterType="map">
          update blog
          <set>
              <if test="title != null">
                  title = #{title},
              </if>
              <if test="author != null">
                  author = #{author},
              </if>
              <if test="view != null">
                  views = #{view},
              </if>
          </set>
      </update>
  </mapper>
  

  • 测试

  @Test
  public void getBlog3() {
      SqlSession session = MyBatisUtils.getSqlSession();
      BlogMapper mapper = session.getMapper(BlogMapper.class);
      Map<String, String> map = new HashMap<>();
  //        map.put("title", "Mybatis如此简单");
      map.put("author", "BruceXu");
      List<Blog> blog3 = mapper.getBlog3(map);
      for (Blog blog : blog3) {
          System.out.println(blog);
      }
      session.close();
  }
  
  @Test
  public void updateBlog() {
      SqlSession session = MyBatisUtils.getSqlSession();
      BlogMapper mapper = session.getMapper(BlogMapper.class);
      Map<String, String> map = new HashMap<>();
      map.put("view", "6666");
      map.put("id", "ccfb770fc1b64dbf8fd7379a564dcd9f");
      map.put("title", "狂神Java说的是真的好");
      mapper.updateBlog(map);
  
      List<Blog> blog3 = mapper.getBlog3(map);
      for (Blog blog : blog3) {
          System.out.println(blog);
      }
      session.commit();
      session.close();
  }
  

  

  • 可以看到，MyBatis都帮助我们成功的完成了SQL拼接，并没有出现任何错误

10.5 SQL片段

• 有些时候，在业务的处理过程中，可能某一部分SQL语句使用比较频繁，所以我们可以将其抽取出来，单独成为一个片段。然后在需要的使用通过 <include>标签导入即可

• 提取SQL片段

  <sql id="if-title-author">
  	<if test="title != null">
  		title = #{title}
  	</if>
  	<if test="author != null">
  		and author = #{author}
  	</if>
  </sql>
  

• 引用SQL片段

  <select id="queryBlogIf" parameterType="map" resultType="blog">
  	select * from blog
  	<where>
  		<!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace-->
  		<include refid="if-title-author" />
  		<!-- 在这里还可以引用其他的 sql 片段 -->
  	</where>
  </select>
  

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