Mybatis学习笔记:class-08

12 动态SQL

什么是动态SQL：动态SQL就是根据不同的条件生成不同的SQL语句

所谓的动态SQL，实质上还是SQL语句，只是我们可以在SQL层面，去执行一个逻辑代码

动态SQL是MyBatis的强大特性之一。若果你是用过JDBC或者其他框架，你应该能够理解根据不同的条件拼接SQL语句很是复杂，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态SQL。可以彻底摆脱这种痛苦。

搭建环境

-- 建表
CREATE TABLE `mybatis1`.`blog`  (
    `id` VARCHAR(50) NOT NULL COMMENT '博客id',
    `title` VARCHAR(30) 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

创建一个基础工程：

相关代码:

//生成随机数工具类
public class IDUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-", "");
    }

    @Test
    public void test(){
        System.out.println(IDUtils.getId());
    }
}

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    //属性名与字段名字不一样，有一个setting属性配置可转换驼峰
    private Date createTime;
    private int views;
}

public interface BlogMapper {
    //插入数据
    int addBlogs(Blog blog);
}

@Test
public void addBlog(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog = new Blog();
    blog.setId(IDUtils.getId());
    blog.setTitle("Mybatis");
    blog.setAuthor("狂神说");
    blog.setCreateTime(new Date());
    blog.setViews(9999);

    mapper.addBlogs(blog);

    blog.setId(IDUtils.getId());
    blog.setTitle("Java");
    mapper.addBlogs(blog);

    blog.setId(IDUtils.getId());
    blog.setTitle("Spring");
    mapper.addBlogs(blog);

    blog.setId(IDUtils.getId());
    blog.setTitle("微服务");
    mapper.addBlogs(blog);
    
    sqlSession.close();
}

<!-- mybatis-config.xml-->
<!-- 开启自动驼峰命名规则映射,当字段名与属性名不一致时-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<mappers>
    <mapper class="com.serene.dao.BlogMapper" />
</mappers>

<insert id="addBlogs" parameterType="blog">
    insert into mybatis1.blog (id, title, author, create_time, views)
    values (#{id}, #{title}, #{author}, #{createTime}, #{views});

</insert>

测试环境搭建完成，数据库数据如下：

12.1 IF where标签

输入参数条件，将满条件的if标签下的sql语句追加到初始sql语句末尾

where标签，若语句开头有or或and，会将其自动去除

<select id="queryBlogsIF" parameterType="map" resultType="blog">
    select * from mybatis1.blog
    <!-- where标签，若语句开头有or或and，会将其自动去除-->
    <where>
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

@Test
public void queryBlogsIF(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    map.put("title", "Java");
    map.put("title", "狂神说");
    List<Blog> blogList = mapper.queryBlogsIF(map);

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

    sqlSession.close();
}

12.2 choose-when-otherwise标签

在choose里面选择一个执行，优先级与顺序有关系，类似于case语句

<select id="queryBlogsIF" parameterType="map" resultType="blog">
    select * from mybatis1.blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and 1=1
            </otherwise>
        </choose>
    </where>
</select>

@Test
//select * from mybatis1.blog WHERE title = Java
public void queryBlogsIF(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
            map.put("title", "Java");
    //        map.put("author", "狂神说");
    //        map.put("views", "9999");
    List<Blog> blogList = mapper.queryBlogsIF(map);

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

    sqlSession.close();
}

12.3 update-set-if

更新数据库数据

<update id="updateBlogs" parameterType="map">
    update mybatis1.blog
    <set>
        <if test="title != null">
            title = #{title}, <!-- 注意这里的逗号-->
        </if>
        <if test="author != null">
            author = #{author},
        </if>
    </set>
    where views = #{views}
</update>

@Test
//update mybatis1.blog SET title = Java1 author = 晴朗 where views = 1111 
public void updateBlogs(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    map.put("title", "Java1");
    map.put("author", "晴朗");
    map.put("views", "1111");
    mapper.updateBlogs(map);
    sqlSession.close();
}

12.4 SQL片段

有的时候，我们可能会将一些功能的部分抽取出来，方便使用！如上面12.1和12.3个节中xml代码中都有代码

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

我们可以使用下面来实现

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

<update id="updateBlogs" parameterType="map">
    update mybatis1.blog
    <set>      
        <include refid="ti-and-au"></include>
    </set>
    where views = #{views}
</update>

最好基于单表来定于SQL片段，不要存在where标签

12.5 foreach标签

<!--
        select * from mybatis1.blog where 1=1 and (views=1 or views=2 or views=3)
        万能map传入一个集合
    -->
<select id="queryBlogsForeach" parameterType="map" resultType="blog">
    select * from mybatis1.blog
    <where>
        <foreach collection="viewss" item="views" open="and (" close=")" separator="or">
            views = #{views}
        </foreach>
    </where>
</select>

@Test
//select * from mybatis1.blog where 1=1 and (views=1 or views=3)
public void queryBlogsForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    ArrayList<Integer> viewss = new ArrayList<Integer>();
    viewss.add(1);
    viewss.add(3);
    map.put("viewss",viewss);
    List<Blog> blogList = mapper.queryBlogsForeach(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    sqlSession.close();
}

结果：

动态SQL就是在拼接SQL语句，我们只需要保证SQL的真确性，按照SQL的格式，去排列组合就可以了。

先在Mysql中写出完整的SQL，在对应的去修改成我们的动态SQL实现通用即可