Mybatis11——UUID、动态SQL(choose(when...,otherwise)、where、set、trim、foreach、sql片段)

一、UUID

UUID 是指Universally Unique Identifier，翻译为中文是通用唯一识别码，UUID 的目的是让分布式系统中的所有元素都能有唯一的识别信息。如此一来，每个人都可以创建不与其它人冲突的 UUID，就不需考虑数据库创建时的名称重复问题。

UUID 是由一组32位数的16进制数字所构成，是故 UUID 理论上的总数为16^32=2^128，约等于3.4 x 10^123。

也就是说若每纳秒产生1百万个 UUID，要花100亿年才会将所有 UUID 用完

public class IDUtil { 
    public static String genId(){ 
        return UUID.randomUUID().toString().replaceAll("-",""); 
    } 
}

二、动态sql

数据准备

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

2、实体类

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;

}

3、接口和Blogmapper.xml

public interface BlogMapper { 
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">


<!--namespace需要绑定一个对应的DAO/Maper接口-->
<mapper namespace="com.jia.mapper.BlogMapper">

</mapper>

4、开启下划线驼峰自动转换mybatis-config.xml配置

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

5、插入部分数据

public class InitBlog {
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);


        Blog blog = new Blog();
        blog.setId(IDUtil.genId());
        blog.setTitle("title2");
        blog.setAuthor("aaa");
        blog.setCreateTime(new Date());
        blog.setViews(200);
        mapper.addBlog(blog);

        sqlSession.commit();
        sqlSession.close();

    }
}

6、普通查询

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

    <select id="selectBlog" parameterType="map" resultType="com.jia.pojo.Blog">
        select * from Blog where title = #{title} and author = #{author}
    </select>


        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<String, Object> map = new HashMap<>();

        map.put("title","title2");
        map.put("author","aaa");

        List<Blog> blogs = mapper.selectBlog(map);

        System.out.println(blogs);

7、动态查询——if

//接口    
List<Blog> selectBlogD(Map<String,Object> map);

//mapper.xml
    <select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
        select * from Blog where 1=1

        <if test="title != null">
            and title = #{title}
        </if>

        <if test="author != null">
            and author = #{author}
        </if>
    </select>

//测试
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<String, Object> map = new HashMap<>();
//
        map.put("title","title2");
        map.put("author","aaa");

        List<Blog> blogsList = mapper.selectBlogD(map);

        for (Blog blog : blogsList) {
            System.out.println(blog);
        }
    }

8、choose、when、otherwise

    <select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
        select * from Blog
        <where>
        选择一个实现
        <choose>  
        
            <when test="title!=null">
                title = #{title}
            </when>

            <when test="author!=null">
                author = #{author}
            </when>
            
            上面两个都不满足，实现otherwise
            <otherwise>
                views = #{views}
            </otherwise>
        </choose>

        </where>

    </select>

9、where(自动添加删除where、and和or)

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

    <select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
        select * from Blog
        <where>

        <if test="title != null">
            and title = #{title}
        </if>

        <if test="author != null">
            and author = #{author}
        </if>
        </where>

    </select>

10、Set(自动添加set、删除额外的，)

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

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

11、trim（定制where和set）

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

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

12、foreach（循环拼接）

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

<select id="selectBlogForeache" parameterType="map" resultType="com.jia.pojo.Blog">
    select * from Blog
    <where>
        <foreach item="id"  collection="ids" open="(" close=")" separator="or">
           id = #{id}
        </foreach>
    </where>
</select>

    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<String, Object> map = new HashMap<>();

        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(3);
        ids.add(3);

        map.put("ids",ids);

        List<Blog> blogs = mapper.selectBlogForeache(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
    }

13、sql片段（对一下共有的脚本片段进行抽取）

    <sql id="select_or_title_or_author_othorwise_views">
        <choose>
            <when test="title!=null">
                title = #{title}
            </when>

            <when test="author!=null">
                author = #{author}
            </when>

            <otherwise>
                views = #{views}
            </otherwise>
        </choose>
    </sql>

使用include进行拼接

    <select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
        select * from Blog
        <where>
        <include refid="select_or_title_or_author_othorwise_views"></include>
        </where>

    </select>