Mybatis学习笔记-动态SQL

概念

根据不同环境生成不同SQL语句，摆脱SQL语句拼接的烦恼【doge】
本质：SQL语句的拼接

环境搭建

搭建数据库

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

创建一个基础Maven工程
- 编写配置文件（略）
- 编写实体类

package cn.iris.pojo;

import java.util.Date;

public class Blog {
    private String id;
    private String title;
    private String author;
    private Date creatTime;
    private int views;

    public Blog() {
    }

    public Blog(String id, String title, String author, Date creatTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.creatTime = creatTime;
        this.views = views;
    }

    Getter&Setter

    @Override
    public String toString()
}

- 编写实体类对应Mapper接口以及Mapper.xml文件

IF语句

Mapper.xml

    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        SELECT * FROM blog WHERE 1=1
        <if test="title != null">
            AND title like #{title}
        </if>
        <if test="author != null">
            AND author like #{author}
        </if>
        <if test="views != 0">
            AND views >= #{views}
        </if>
    </select>

常用标签

where&set

取代死板的关键字【WHERE】和【SET】，提供灵活的SQL语句拼接，在满足条件后才加上对应SQL关键字

choose&when&otherwise

类似于Java中的switch-case-default结构
匹配when则执行
Mapper.xml

    <select id="queryBlogChoose" parameterType="map" resultType="Blog">
        SELECT * FROM blog
        <where>
            <choose>
                <when test="title != null">
                    AND title like #{title}
                </when>
                <when test="author != null">
                    AND author like #{author}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

trim

提供自定义【WHERE】或【SET】标签内容

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

SQL片段

某些可复用的功能片段
定义SQL片段【sql标签抽取部分sql代码】

<sql id="if-title-author">
...   
</sql>

引用SQL片段【include标签引用对应id的sql代码片段】

<include refid="if-title-author"></include>

注意事项：
- 最好基于单表定义SQL片段
- 不要存在where标签

Foreach

对集合遍历，通常是构建IN语句时
open 第一个拼接的字符【'('】， separator 分隔符【','】，close 结尾拼接符【')'】，等同于IN(?,?,?)
collection对应传入的集合，index为下标，item则为集合内对应下标元素,从而遍历集合内元素
若传入类型为【Map】或者【Map.Entry 对象】的集合，index传入键，item传入值
Mapper.xml

    <!--
        sql: SELECT * FROM blog WHERE 1=1 AND (id=1 OR id=2 OR id=3)
        在传递Map时，Map中可存在集合
    -->
    <select id="queryByForeach" parameterType="map" resultType="Blog">
        SELECT * FROM blog
        <where>
            <foreach collection="ids" item="id" index=""
            open="AND (" separator="OR" close=")">
                id = #{id}
            </foreach>
        </where>
    </select>

测试方法

    @Test
    public void queryBlogByForeach() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<Object, Object> hashMap = new HashMap<>();
        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(4);
        hashMap.put("ids",ids);

        List<Blog> blogs = mapper.queryByForeach(hashMap);

        for ( Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }