MyBatis动态sql

11.动态sql

11.1 简介

• 动态sql是根据不同的条件生成不同的sql

• 和使用 JSTL 类似。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

  if

  choose (when, otherwise)

  trim (where, set)

  foreach

11.2 使用

• 搭建环境

• 创建数据表

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(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

• 创建基础工程

导包

编写配置文件

编写实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Blog {
    private int id;
    private String title;
    private  String author;
    /**
     * java中尽量用java.util.Date
     *  sql.date会由问题
     *  注意跟数据库的字段名不对应
     * */
    private Date createTime;
    private int views;

}

编写实体类对应的mapper接口和mapper.xml文件

@SuppressWarnings("all") //类上抑制警告注解

• 使用UUID生成Blog表主键
• 拓展:Twitter的Snowflake简单了解使用

Twitter的Snowflake 算法 分布式系统中，有一些需要使用全局唯一ID的场景，有些时候我们希望能使用一种简单一些的ID，并且希望ID能够按照时间有序生成。
snowflake的结构如下(每部分用-分开):
符号位（1bit）- 时间戳相对值（41bit）- 数据中心标志（5bit）- 机器标志（5bit）- 递增序号（12bit）
0 - 0000000000 0000000000 0000000000 0000000000 0 - 00000 - 00000 - 000000000000

第一位为未使用(符号位表示正数)，接下来的41位为毫秒级时间(41位的长度可以使用69年) 然后是5位datacenterId和5位workerId(10位的长度最多支持部署1024个节点） 最后12位是毫秒内的计数（12位的计数顺序号支持每个节点每毫秒产生4096个ID序号）
并且可以通过生成的id反推出生成时间,datacenterId和workerId

• 导包：Twitter的Snowflake 算法 依赖，里面包含了UUID的生成算法

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.3</version>
</dependency>

import cn.hutool.core.util.IdUtil;
@Test
public void testID(){
    System.out.println(IdUtil.getSnowflakeNextId());
    System.out.println(IdUtil.fastUUID());  //.fastSimpleUUID()生成的uuid不带“-”
}
/*1538039768955478016
38a47591-ae8b-41ee-a13e-ada3dcfa7812*/

• 2者区别和优缺点
• 原文见https://blog.csdn.net/qq_40950903/article/details/108589837

需求：

在分布式系统需要对大量的数据和消息进行唯一标识而UUID 和 Snowflake 都可以生成全局唯一标识，数据分库分表后需要有一个唯一ID来标识一条数据。

UUID 简单无序，适合生成 requestID， Snowflake 里面包含时间序列等，可以用于排序，效率

优秀ID的标准

全局唯一：

有序递增：MySQL的InnoDB引擎中使用的是聚集索引，多数RDBMS使用BTree的数据结构来存储索引数据。因此在主键的选择上我们应该尽量使用有序的主键保证写入性能

MySQL InnoDB 存储引擎使用 B+ 树存储索引数据，主键也是一种索引。索引数据在 B+ 树中是有序排列的，有序保证了插入数据的有序性，否则造成了多余的数据移动的开销（磁盘IO）

安全：mysql的步长为n的自增主键容易被破解

业务相关性:UUID仅仅是由 32 个 16 进制数字组成的字符串，不具备业务相关性。Snowflake 却存在可以在后12位递增序号加入业务相关id.这种方案的好处是业务代码在使用的时候不需要跨网络调用，性能上会好一些，但是就需要更多的机器 ID 位数来支持更多的业务服务器。另外，由于业务服务器的数量很多，我们很难保证机器 ID 的唯一性，所以就需要引入 ZooKeeper 等分布式一致性组件来保证每次机器重启时都能获得唯一的机器 ID

时间戳：开发中快速了解分布式ID的生成时间。

uuid:UUID是基于当前时间、计数器（counter）和硬件标识（通常为无线网卡的MAC地址）等数据计算生成的

优点：UUID性能非常高:本地生成,没有网络消耗，如果只考虑唯一性UUID是ok的。但是入数据库的性能较差

缺点：UUID 是由 32 个 16 进制数字组成的字符串。UUID太长,如果作为数据库主键使用比较耗费空间.查询的效率比较低。传输数据量大，且不可读

缺点：无序:无法预测他的生成顺序,不能生成递增有序的数字。而且每一次新的UUID数据的插入,为了查询的优化,都会对索引"底层的B+树进行修改

数据库自增主键：基于数据库自增ID和mysql数据库的replace into实现的。这里的replace into 跟insert功能类似，不同点在于replace into首先尝试把数据插入数据列表中,如果发现表中已经有此行数据(根据主键或唯一索引判断)则先删除,再插入，否则直接插入新数据。

缺点：系统水平扩展比较困难，添加机器该怎么做？

缺点：数据库压力很大,每次获取ID都要读一次数据库,非常影响性能,不符合分布式ID里面的延迟低和要高QPS的规则(在高并发下,如果都去数据库里面获取id,那是非常影响性能的)

snowflake

优点：SnowFlake生成ID能够按照时间有序生成

优点：SnowFlake算法生成id的结果是一个64bit大小的整数,为一个Long型(转换成字符串后长度最多19)

优点：分布式系统内不会产生ID碰撞(由datacenter-数据中心码数据和workerld-机器码作区分)并且效率较高。

优点：毫秒数在高位,自增序列在低位,整个ID都是按时间趋势递增的。整个分布式系统内不会产重复id (因为有datacenterld和workerld来做区分)
优点：不依赖数据库等第三方系统,以服务的方式部署,稳定性更高,生成ID的性能也是非常高的。可以根据自身业务特性分配bit位,非常灵活。

缺点：依赖机器时钟,如果机器时钟回拨,会导致重复ID生成。
缺点：可能在单机上是递增的,但是由于涉及到分布式环境,每台机器上的时钟不可能完全同步,有时候会出现不是全局递增的情况(此缺点可以忽略, 一般分布式ID只要求趋势递增,并不会严格要求递增, 90%的需求都只要求趋势递增)

基于redis的id生成策略
因为Redis是单线程的天生保证原子性，可以使用原子操作INCR和INCRBY来实现。
注意:在Redis集群情况下,同样和MySQL一样需要设置不同的增长步长,同时key一定要设置有效期，可以使用Redis集群来获取更高的吞吐量。假如一个集群中有5台Redis,可以初始化每台Redis的值分别是1,2,3,4,5,然后步长都是5
各个Redis生成的ID为:
A: 1,6,11,16,21
B: 2,7,12,17,22
C:3.8.13.18.23
D: 4,9,14,19,24
E: 5,10,15,20,25

分布式系统对id可用性要求

1. 高可用:发一个获取分布式ID的请求，服务器就可以保证99.999%的情况下给我创建一个唯一的分布式ID
2. 低延迟:发一个获取分布式ID的请求，服务器响应速度要快
3. 高QPS:假如并发10万个创建分布式ID请求，服务器要顶得住并能成功创建10万个唯一的分布式ID

• 下mybatis-config.xml文件setting中开启驼峰命名

<!--跟数据库字段不一致时，开启驼峰命名只限制在这一种格式create_time/createTime-->
<setting name="mapUnderscoreToCamelCase" value="true"/>

• 技巧：mapper.xml中写sql时，无法自动补全sql的请按住ALT+ENTER,选择“uninject language…”然后选择MYSQL，就可以自动补全sql语句了

11.2.1 if

• 接口方法

List<Blog> selectBlogIF(Map map);

• mapper.xml文件

<select id="selectBlogIF" parameterType="map" resultType="blog">
    select * from school.blog where 1=1
    <if test="title != null">
        and title like concat('%', #{title},'%')
    </if>
    <if test="author != null">
        and author like concat('%', #{author},'%')
    </if>
</select>

• 测试

@Test
public void TestBlog02(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<String, Object> map = new HashMap<>();
    //map.put("title","诗");
    map.put("author","王");
    List<Blog> blogs = mapper.selectBlogIF(map);
    System.out.println("结果："+blogs.toString());
}

11.2.2 choose、when、otherwise

• 有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
• 多个满足的条件只会选择第一个条件执行，其他条件被省略
• 接口方法
• if 是拼接，choose是选择

List<Blog> selectBlogChoose(Map map);

• mapper.xml文件

<select id="selectBlogChoose" parameterType="map" resultType="blog">
    select * from school.blog where 1=1
    <choose>
        <when test="title != null">
            and title like concat('%', #{title},'%')
        </when>
        <when test="author != null">
            and author like concat('%', #{author},'%')
        </when>
        <otherwise>
            and views &gt; #{views}
        </otherwise>
    </choose>
</select>

• 测试

@Test
public void TestBlog03(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<String, Object> map = new HashMap<>();
    //map.put("title","诗");
    //map.put("author","王");
    map.put("views",200);
    List<Blog> blogs = mapper.selectBlogChoose(map);
    System.out.println("结果："+blogs.toString());
}

11.2.3 trim、where、set

• where：where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
• 改造上句

<select id="selectBlogChoose" parameterType="map" resultType="blog">
    select * from school.blog
    <where>
        <choose>
            <when test="title != null">
                and title like concat('%', #{title},'%')
            </when>
            <when test="author != null">
                and author like concat('%', #{author},'%')
            </when>
            <otherwise>
                and views &gt; #{views}
            </otherwise>
        </choose>
    </where>
</select>

• 以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

• set：set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）

<update id="updBlog" parameterType="map">
    update school.blog
    <set>
        <if test="title != null">
            title  = #{title},
        </if>
        <if test="author != null">
            author  = #{author},
        </if>
    </set>
    <where>
        <if test="author != null">
            views  = #{views}
        </if>
    </where>
</update>

• 你可以通过使用trim元素来达到同样的效果：

<trim prefix="SET" suffixOverrides=",">  
  ...插入前缀，并将后缀是，的用suffix（后缀代替）suffix没写即替换为空
</trim>

• 动态sql,只是在sql上执行一个逻辑代码

11.2.4 foreach

• 动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

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

• 编写接口

List<Blog> selectBlogForeach(Map map);

• 编写mapper.xml

<select id="selectBlogForeach" parameterType="map" resultType="blog">
    select * from school.blog
    <where>
        <foreach collection="viewsList" item="views" open="and (" close=")" separator="or">
            views = #{views}
        </foreach>
    </where>
</select>

• 测试

@Test
public void TestBlog05(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<String, Object> map = new HashMap<>();
    ArrayList<Integer> views = new ArrayList<Integer>();
    views.add(30);
    views.add(200);
    views.add(500);
    map.put("viewsList",views);
    List<Blog> result = mapper.selectBlogForeach(map);
    System.out.println("结果："+result.toString());
}

11.2.5 Sql片段

• 将一些功能部分抽取出来，方便复用
• 使用sql标签将公共部分抽取出来
• 使用include在需要的位置进行引用

<sql id="sql-condition">
    <if test="title != null">
        and title like concat('%', #{title},'%')
    </if>
    <if test="author != null">
        and author like concat('%', #{author},'%')
    </if>
</sql>
<select id="selectBlogIF" parameterType="map" resultType="blog">
    select * from school.blog where 1=1
    <include refid="sql-condition"></include>
</select>

• 注意

最好基于单表来定义sql片段

不要存在where标签

11.3 小结

• 动态sql就是在拼接sq语句，只要保证sql正确性，按照sql的格式，排列组合即可
• 最好实在mysql中写出完整sql,在对应修改为需要的动态sql实现通用即可。

本专栏下一篇：MyBatis缓存