动态SQL

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

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

搭建环境

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

1. 导包
2. 编写配置文件
3. 编写实体类

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

}

4. 编写实体类对应的Mapper接口 和 Mapper.xml文件

IF

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

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

choose (when, otherwise)

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

trim (where, set)

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

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

SQL片段

有时候，可能需要将一些公共的部分抽取出来，方便复用。

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

    <sql id="if-title-author">
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{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片段
• 不要存在where标签

Foreach

    <!--
        select * from blog where 1 = 1 and (id=1 or id=2 or id=3)
    -->
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

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

动态SQL实际上就是在拼接SQL语句，只要保证SQL的准确性，按照SQL的格式，排列组合即可

思路：先写出完整的SQL，再去改写为动态SQL