动态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(50) NOT NULL COMMENT '作者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`views` int(11) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

1、导包

2、编写配置文件

3、创建实体类

package com.sw.pojo;

import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @Author suaxi
 * @Date 2020/12/13 16:20
 */
@Data
public class blog implements Serializable {

    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

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

IF

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

chose(when,otherwise)

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

trim(where,set)

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

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

SQL片段

将部分SQl抽取出来，方便复用

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

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

2、在需要使用的地方用include标签引用、

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

注：最好基于单表查询，不要存在where标签

Foreach

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

==动态SQL就是在拼接SQL语句，先确保SQL的正确性，再按照SQL格式去排列组合==

先在MySQL中写出完整的sql，再去修改成动态sql实现通用