【Mybatis】九、动态SQL

动态SQL

什么是动态SQL?

就是根据不同的条件生成不同的SQL语句，写动态SQL就是在sql语句中插入一些逻辑判断

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

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

数据库

实体类

package com.wanjie.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private Integer views;
}

1、IF

接口方法

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

Mapper文件

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


<!--使用where标签去除1 = 1 -->
<select id="queryBlogIF" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

测试类

@Test
public void queryBlogIF(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<String, Object> map = new HashMap<>();
    map.put("title","54321");
    map.put("author","王五");
    List<Blog> blogs = mapper.queryBlogIF(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

2、choose (when, otherwise)

​ 有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句，由上到下依次判断，符合条件就停止向下判断。

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

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

@Test
public void queryBlogChoose(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<String, Object> map = new HashMap<>();
    map.put("title","54321");
    map.put("author","王五");
    map.put("views",3000);
    List<Blog> blogs = mapper.queryBlogChoose(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

3、trim (where, set)

​ 用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。

int updateBlogBySet(Map<String,Object> map);

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

@Test
public void updateBlogBySet(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
    map.put("title","123456");
    map.put("id","44de7bb3475847999abded5d913ad4ff");
    map.put("views",110);
    mapper.updateBlogBySet(map);
    sqlSession.close();
}

trim

<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">

</trim>

<update id="updateBlogBySet" parameterType="map">
    update blog
    <trim prefix="set" suffixOverrides=",">
        <if test="title != null">title = #{title},</if>
        <if test="views != null">views = #{views},</if>
    </trim>
    where id = #{id};
</update>

4、SQL片段

​ SQL片段是可以通过标签来将sql语句中的通用sql提取出来，使用时只需用引用即可。

<sql id="selectAll">
        select * from blog
</sql>

<select id="queryBlogChoose" parameterType="map" resultType="Blog">
    <include refid="selectAll"/>
    <where>
        <choose>
            <when test="title != null">
                and title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

注意事项：

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

5、Foreach

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

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

<select id="queryBolgForeach" parameterType="map" resultType="blog">
    select * from blog where id in
    <foreach collection="list" index="index" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

@Test
public void queryBolgForeach(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<String, Object> map = new HashMap<>();
    ArrayList<Integer> list = new ArrayList<>();
    list.add(1);
    list.add(2);
    list.add(3);
    map.put("list",list);
    List<Blog> blogs = mapper.queryBolgForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

另一种方法：

<select id="queryBolgForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach collection="list" item="id"  separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

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

建议：

• 现在Mysql中写出sql语句，再在Mapper文件中拼接，实现动态SQL