Mybatis if, set, where 动态sql和sql片段的使用

不念不忘少年蓝@ 2021-09-21 15:32 307阅读 0赞

### 目录 ###

*  动态SQL
 *  if
 *  trim (where, set)
 *  choose (when, otherwise)
 *  SQL片段
 *  foreach

**Mybatis 官方文档：** [https://mybatis.org/mybatis-3/zh/dynamic-sql.html][https_mybatis.org_mybatis-3_zh_dynamic-sql.html]

# 动态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 CHARSET=utf8 COLLATE=utf8_general_ci;

实体类

public class Blog { 
        private String id;
        private String title;
        private String auther;
        private java.util.Date createTime;
        private int views;
    }

开启驼峰命名自动映射

# if #

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

# trim (where, set) #

> trim

*  prefix：在包裹的代码块前面添加一个 `xxx`
 *  prefixOverrides：属性会忽略通过管道符分隔的文本序列（**注意此例中的空格是必要的**）
 *  suffixOverrides： 忽略最后一个 `xxx`

## 等价于 where 标签
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      ...
    </trim>
    
    ## 等价于 set 标签
    <trim prefix="SET" suffixOverrides=",">
      ...
    </trim>

> where

**若子句的开头为 “AND” 或 “OR”，*where* 元素也会将它们去除。**

HashMap hashMap = new HashMap();
    hashMap.put("title","java");
    hashMap.put("author","自己");
    
    <select id="queryBlogIf" 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>

> set

***set* 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号**

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

# choose (when, otherwise) #

> choose

**类似 Java 中的 switch**

HashMap hashMap = new HashMap();
    hashMap.put("title","java");
    //        hashMap.put("author","自己");
    hashMap.put("views", 1000);
    
    <select id="queryBlogChoose" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    author = #{author}
                </when>
                <otherwise>
                    views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

# SQL片段 #

我们可以把一些功能抽取出来，方便复用

*  sql：抽取代码片段
 *  include： 引用sql抽取的代码片段

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

注意事项

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

# foreach #

*  collection：遍历对象
 *  item：每一项
 *  index：索引
 *  open：开头
 *  separator：分隔符
 *  close：结尾

int[] array = new int[]{10, 5000, 9999};
    List<Integer> list = new ArrayList<>();
    for (int i : array) {
        list.add(i);
    }
    
    <select id="getBlogIn" parameterType="list" resultType="Blog">
        select * from blog
        <where>
            <if test="list != null and list.size() > 0">
                views in
                <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

[https_mybatis.org_mybatis-3_zh_dynamic-sql.html]: https://mybatis.org/mybatis-3/zh/dynamic-sql.html