一、动态 SQL

官网:https://mybatis.org/mybatis-3/zh/dynamic-sql.html

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。

动态SQL元素总类:

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

二、搭建环境

2.1 数据库

数据库:postgresql

新建一个blog表:属性有id,title,author,created_at,views

1
-- ----------------------------
2
-- Table structure for blog
3
-- ----------------------------
4
DROP TABLE IF EXISTS "public"."blog";
5
CREATE TABLE "public"."blog" (
6
  "id" int4 NOT NULL,
7
  "title" varchar(255) COLLATE "pg_catalog"."default",
8
  "author" varchar(255) COLLATE "pg_catalog"."default",
9
  "created_at" timestamptz(6),
10
  "views" int4
11
)
12
;
13
14
-- ----------------------------
15
-- Primary Key structure for table blog
16
-- ----------------------------
17
ALTER TABLE "public"."blog" ADD CONSTRAINT "blog_pkey" PRIMARY KEY ("id");

插入测试数据:

1
INSERT INTO "public"."blog"("id", "title", "author", "created_at", "views") VALUES (1, 'mybatis学习笔记', '张三', '2021-12-05 10:44:27+08', 2);
2
INSERT INTO "public"."blog"("id", "title", "author", "created_at", "views") VALUES (2, 'mysql', '李四', '2021-12-01 10:44:44+08', 6);
3
INSERT INTO "public"."blog"("id", "title", "author", "created_at", "views") VALUES (3, 'postgresql', '王五', '2021-12-06 10:45:04+08', 9);

2.2 maven工程

与之前的学习笔记一样,新建一个module,搭建基础框架。

Blog实体类:

1
@Data
2
public class Blog {
3
    private int id;
4
    private String title;
5
    private String author;
6
    private Timestamp createdAt;
7
    private int views;
8
}

三、if

if标签是实现动态查询条件的关键,比如当我的title不为空的时候,就要加一个条件来查询我输入的这个title值。

mapper接口:

1
List<Blog> queryBlogIF(Map map);

xml实现:

1
<select id="queryBlogIF" resultType="mybatis08.domain.Blog" parameterType="map">
2
    select * from blog
3
    where 1=1
4
    <if test="title != null">
5
        and title = #{title}
6
    </if>
7
    <if test="author != null">
8
        and author = #{author}
9
    </if>
10
</select>

junit测试:

1
@Test
2
public void selectStudentList2() {
3
    SqlSession sqlSession = MybatisUtils.getSqlSession();
4
    try {
5
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
6
        Map map = new HashMap<String,String>();
7
        map.put("title","mybatis学习笔记");
8
        List<Blog> blogList = blogMapper.queryBlogIF(map);
9
        for (Blog blog : blogList) {
10
            System.out.println(blog);
11
        }
12
    } catch (Exception e) {
13
        e.printStackTrace();
14
    } finally {
15
        sqlSession.close();
16
    }
17
}

如果map中不传属性的值,就会查出所有的博客列表,加了map.put("title","mybatis学习笔记");之后,只会查询标题为mybatis学习笔记的博客。

四、choose (when, otherwise)

有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。

xml实现举例:

1
<select id="queryBlogChoose" resultType="mybatis08.domain.Blog" parameterType="map">
2
    SELECT * FROM blog WHERE 1=1
3
    <choose>
4
        <when test="title != null">
5
            AND title like #{title}
6
        </when>
7
        <when test="author != null">
8
            AND author like #{author}
9
        </when>
10
        <otherwise>
11
            AND views > 3
12
        </otherwise>
13
    </choose>
14
</select>

也就是自上而下,选择第一个符合条件的语句,只选择一个,如果都不符合,那就选择<otherwise>语句。

五、trim (where, set)

5.1 where

回到之前的 if示例,如果我们把1=1去掉,而当所有的动态条件都不符合的时候,sql拼接就会变成这个样子:

1
SELECT * FROM blog WHERE

后面没有条件了,但还是有where语句,就会报错。MyBatis 有一个简单且适合大多数场景的解决办法:也就是将where改成<where>标签,将上面的语句改成:

1
<select id="queryBlogIF" resultType="mybatis08.domain.Blog" parameterType="map">
2
    select * from blog
3
    <where>
4
        <if test="title != null">
5
            and title = #{title}
6
        </if>
7
        <if test="author != null">
8
            and author = #{author}
9
        </if>
10
    </where>
11
</select>

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

5.2 set

用于动态更新语句的类似解决方案叫做 setset 元素可以用于动态包含需要更新的列,忽略其它不更新的列。

1
<update id="updateBlogSet" parameterType="map">
2
    update blog
3
    <set>
4
        <if test = "title!=null">
5
            title = #{title},
6
        </if>
7
        <if test = "author!=null">
8
            author = #{author},
9
        </if>
10
    </set>
11
    where id = #{id}
12
</update>

这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。

5.3 trim

上面的where, set都派生于<trim>元素,其中,<where>可以表示为:

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

prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。

<set>可以表示为:

1
<trim prefix="SET" suffixOverrides=",">
2
  ...
3
</trim>

六、foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如我们要拼接:

1
SELECT * FROM blog where id in ( 1 , 2 )

xml实现:

1
<select id="queryBlogForeach" parameterType="map" resultType="mybatis08.domain.Blog">
2
    SELECT * FROM blog where id in
3
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
4
        #{id}
5
    </foreach>
6
</select>

其中,collection="ids"就是传进来的集合名字,item="id"就是遍历用的变量,open="(" close=")" separator=","这是开头,结尾和分割。

七、SQL片段

这个元素可以用来定义可重用的 SQL 代码片段,以便在其它语句中使用。

比如我们定义一下字段:

1
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

使用的时候:

1
<include refid="userColumns"><property name="alias" value="t1"/></include>

八、总结

动态SQL也就是使用各种元素标签类拼接sql语句,本质上还是sql语句。