12.动态SQL

12.动态SQL

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

利用动态SQL这一特性可以彻底摆脱这种痛苦

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

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

12.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

创建一个基础工程

• 导包
• 编写核心配置文件
• 编写实体类

注意：java中创建实体类中的时间属性需选择.util,选择.sql不生效

 

@Data
public class Blog {
    private int id;
    private String title;
    private String author;
    private Date createTime;//属性名和字段名不一致
    private int views;
}

如何解决属性名和字段名不一致：

在核心配置文件的Setting方法里加入如下代码：

<settings>
        <!--开启驼峰命名转换-->
        <setting name="mapUnderscoreToCamelCase" value="true "/>
    </settings>

• 编写工厂类（utils—sqlSessionFactory）
• 编写实体类对应的Mapper接口和Mapper.xml文件

IF标签

BlogMapper

//查询博客
    List<Blog> queryBlogIF(Map map);

BlogMapper.xml

<mapper namespace="com.itxiaofei.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into mybatis.blog (id, title, author, create_time, views)
        values(#{id},#{title}, #{author}, #{createTime}, #{views});
    </insert>
		<!--if标签是固定的格式-->
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog <!--where 1=1-->
	<!--不使用where也可以在后面加where1=1，但是不正规-->
			<where>
        <if test="title != null">
            and title like #{title}
        </if>
        <if test="author != null">
            and author like #{author}
        </if>
			</where>
    </select>
</mapper>

使用where标签的原因：（where 1=1不正规！）

测试：

//测试动态SQL中if
    @Test
    public void queryBlogIF(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        /*map.put("title","Java并不难");*/
        map.put("author","if小飞");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

choose (when, otherwise)标签

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员精选的 Blog）。

1.BlogMapper

List<Blog> queryBlogChoose(Map map)

2.BlogMapper.xml

<select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null">
                    and title like #{title}
                </when>
                <when test="author != null">
                    and author like #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

3.测试

//测试动态SQL中choose (when, otherwise)标签
    @Test
    public void queryBlogChoose(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("views","9999");
        /*map.put("title","Java并不难");*/
        /*map.put("author","if小飞");*/
        List<Blog> blogs = mapper.queryBlogChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

trim、where、set标签

where标签：（自动去除“AND” 或 “OR”）

<select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null">
                    and title like #{title}
                </when>
                <when test="author != null">
                    and author like #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

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>

set测试：

//测试动态SQL中的set标签
    @Test
    public void updateBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        //id后面的参数那么一长串是因为之前加了UUID防止数据太多导致重复
        map.put("id","f125088b7dd74826a6b4eddc42132ad4");
        map.put("title","Python并不难");
        map.put("author","it小飞呀");
        mapper.updateBlog(map);
        sqlSession.close();
    }

trim标签：

trim标签你可以理解为是set和where标签的整合

<!--prefix：前缀 ，prefixOverrides：前缀覆盖内容
            suffix：后缀 ，suffixOverrides：后缀覆盖内容
 -->
  <trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
            <!--写拼接的sql语句-->
  </trim>

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

Foreach（遍历）

 

 

1.编写接口

List<Blog> queryBlogForeach(Map map);

2.Foreach标签的使用

<!--Foreach标签
    select * from blog where and(id=1 or id=2 or id=3)
    下面Foreach标签的作用就是拼接这段话
    我们传递一个万能的map，这个map中可以存在一个集合
    -->
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

3.测试

//测试动态SQL中的Foreach标签
    @Test
    public void queryBlogForeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        //我们不知道到底有多少个数据元素的时候，就可使用ArrayList；<>里面放的是：泛型
        //<>作用就是确定到底存放什么类型
        // 如果知道数据集合有多少个元素，就用数组。
        ArrayList<Integer> ids = new ArrayList<Integer>();
        //Java Map.put()方法：获取Map集合的所有键名
        map.put("ids",ids);
        ids.add(1);
        ids.add(2);
        ids.add(3);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

SQL片段

有些时候，我们可能会将一些公共的部分抽取出来，方便复用

1.使用sql标签抽取公共的部分

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

2.在需要使用的地方使用Include标签引用即可

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

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

建议：

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