动态SQL的学习笔记

一、动态sql的简介

动态SQL：根据不同的条件生成不同的SQL语句。动态SQL就是在拼接SQL语句，我们要保证SQL语句的正确（在sql层面执行逻辑代码，如if判断）

二、环境搭建

2.1 设计表blog

2.2 实体类Blog

package com.wen.pojo;

import jdk.jfr.DataAmount;
import lombok.Data;

import java.util.Date;

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;

}

三、动态sql使用的标签

3.1 if

<mapper namespace="com.wen.dao.BlogMapper">

    <select id="getBlog" 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>

</mapper>

3.2 choose (when, otherwise)

<mapper namespace="com.wen.dao.BlogMapper">

    <select id="getBlogChoose" parameterType="map" resultType="Blog">
        select * from 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>

</mapper>

3.3 set

<mapper namespace="com.wen.dao.BlogMapper">

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

</mapper>

SQL片段：将一些公共部分提取取来，方便复用
1、使用sql标签提取公共部分
2、使用include标签引用

<mapper namespace="com.wen.dao.BlogMapper">
     <!--使用sql标签-->
    <sql id="title-author">
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
    <select id="getBlog" parameterType="map" resultType="blog">
        select * from blog
        <where>
        <!--使用include标签引入-->
           <include refid="title-author"></include>
        </where>

    </select>

</mapper>

3.4 foreach

<mapper namespace="com.wen.dao.BlogMapper">
  <select id="getBlogForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" separator="or" close=")">
                id = #{id}
            </foreach>
        </where>
    </select>
</mapper>

测试

 @Test
    public void test2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        map.put("ids",ids);

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