Mybatis系列九之动态sql

动态sql

动态sql：根据不同的条件生成不同的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
	
	
insert into blog values("001","java","小明","2021-1-11",45);
insert into blog values("002","python","小亮","2021-2-11",100);	
insert into blog values("003","c++","小红","2021-3-11",99);	
insert into blog values("004","c","小张","2021-1-18",46);	
insert into blog values("005","php","小辉","2021-1-21",87);	
insert into blog values("006","js","小刘","2021-7-21",68);

2 实体类

随机生成随机的id编号

import java.util.UUID;

@SuppressWarnings("all")
public class IDutils {

    public static String getId(){
        return UUID.randomUUID().toString().replace("-","");   }

}

测试接口

import com.xgh.pojo.Blog;

import java.util.List;

public interface BlogMapper {
    //测试添加数据
    int addBlog(Blog blog);

    //测试查询所有方法
    List<Blog> findAll();
}

BlogMapper.xml文件

<?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">

<mapper namespace="com.xgh.dao.BlogMapper">
    <insert id="addBlog" parameterType="Blog">
        insert into blog (id, title, author, create_time, views) values
        (#{id}, #{title}, #{author}, #{create_time}, #{views});
    </insert>

    <select id="findAll" resultType="Blog">
        select * from blog;
    </select>
</mapper>

3 分类

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

3.1 if语句

格式：

<if test="判断语句">

</if>

<select id="findBlogIF" resultType="Blog" parameterType="map">
    select * from blog where 1 = 1 
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

解析此段：如果map集合为空，则查出来的结果为所有，select * from blog where 1 = 1

如果map集合中 title含有值 则where条件中为此条件

3.2 choose (when, otherwise)

相当于java中的switch case语句

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

3.3 trim (where, set)

where 解决不能匹配的问题

匹配的只是第二个条件又会怎样？这条 SQL 会是这样:

SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’

正确格式

    <select id="findBlogIF" resultType="Blog" parameterType="map">
        select * from blog 
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>

set设置更新条件

用于动态更新语句的类似解决方案叫做 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 id=#{id}
    </update>

trm是where和set的父标签

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

来看看与 set 元素等价的自定义 trim 元素吧：

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

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

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

3.4 foreach

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

    <!--动态sqlForeach遍历-->
<!-- 拼接成的动态sql语句 SELECT * FROM blog WHERE ID in ( ? ) -->
    <select id="findBlogForeach" resultType="Blog" parameterType="map">
        SELECT *
        FROM blog
        WHERE ID in
        <foreach item="id" collection="ids"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

测试类

@Test
    public void findBlogForeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();

        ArrayList<String> ids = new ArrayList<String>();
        ids.add("001");
        ids.add("002");

        map.put("ids",ids);

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

    }

4 SQL片段

有些时候我们有一些公共部分

1. 使用sql便签抽取公共部分
2. 在使用的地方使用include标签

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

<select id="queryBlogIF" parameterType="map" resultType="Blog">
    select * from mybatis.blog
    <where>
        <include refid="if-title-author"/>
    </where>
</select>

注意：

• 最好基于单表
• sql里不要存在where标签