动态SQL

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

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

搭建环境

创建一个基础工程
1：导包
2：编写配置文件

<mappers>
    <mapper class="com.kuang.dao.BlogMapper"/>
</mappers>

3：创建一个实体类

@Data
@SuppressWarnings("all")  //镇压警告
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;  //属性名和字段名不一致
    private int views;
}

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

public interface BlogMapper {

    //插入数据；
    int addBlog(Blog blog);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.BlogMapper">

    <insert id="addBlog" parameterType="Blog">

        insert into mybatis.blog (id, title, author, create_time, views)
        values (#{id}, #{title}, #{author}, #{createTime}, #{views});

    </insert>

</mapper>

5：测试运行OK

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>

choose (when, otherwise)

MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != author">
                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>

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

所谓的动态的SQL，本质还是SQL语句，只不过我们是在SQL层面，去执行一个逻辑代码；
If
where
set
when
choose

SQL片段

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