1, Dynamic SQL

Official website: https://mybatis.org/mybatis-3/zh/dynamic-sql.html

Dynamic SQL is one of the powerful features of MyBatis. If you have used JDBC or other similar frameworks, you should be able to understand how painful it is to splice SQL statements according to different conditions. For example, when splicing, make sure you can't forget to add the necessary spaces and remove the comma of the last column name in the list. Using dynamic SQL, you can completely get rid of this pain.

Dynamic SQL element class:

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

2, Build environment

2.1 database

Database: postgresql

Create a new blog table: the attributes include ID, title, author and created_ at,views:

-- ----------------------------
-- Table structure for blog
-- ----------------------------
DROP TABLE IF EXISTS "public"."blog";
CREATE TABLE "public"."blog" (
  "id" int4 NOT NULL,
  "title" varchar(255) COLLATE "pg_catalog"."default",
  "author" varchar(255) COLLATE "pg_catalog"."default",
  "created_at" timestamptz(6),
  "views" int4
)
;

-- ----------------------------
-- Primary Key structure for table blog
-- ----------------------------
ALTER TABLE "public"."blog" ADD CONSTRAINT "blog_pkey" PRIMARY KEY ("id");

Insert test data:

INSERT INTO "public"."blog"("id", "title", "author", "created_at", "views") VALUES (1, 'mybatis Study notes', 'Zhang San', '2021-12-05 10:44:27+08', 2);
INSERT INTO "public"."blog"("id", "title", "author", "created_at", "views") VALUES (2, 'mysql', 'Li Si', '2021-12-01 10:44:44+08', 6);
INSERT INTO "public"."blog"("id", "title", "author", "created_at", "views") VALUES (3, 'postgresql', 'Wang Wu', '2021-12-06 10:45:04+08', 9);

2.2 maven project

Like the previous study notes, create a new module to build the basic framework.

Blog entity class:

@Data
public class Blog {
    private int id;
    private String title;
    private String author;
    private Timestamp createdAt;
    private int views;
}

3, if

The if tag is the key to realizing dynamic query conditions. For example, when my title is not empty, a condition should be added to query the Title Value I entered.

mapper interface:

List<Blog> queryBlogIF(Map map);

xml implementation:

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

junit test:

@Test
public void selectStudentList2() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap<String,String>();
        map.put("title","mybatis Study notes");
        List<Blog> blogList = blogMapper.queryBlogIF(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

If you do not pass the attribute value in the map, you will find out all the blog lists and add map.put("title","mybatis learning notes"); After that, only the blog titled mybatis learning notes will be queried.

4, choose (when, otherwise)

Sometimes, we don't want to use all the conditions, but just want to choose one from multiple conditions. In this case, MyBatis provides a choose element, which is a bit like a switch statement in Java.

xml implementation example:

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

That is, from top to bottom, select the first qualified statement and only one. If none of them meet the requirements, select the < otherwise > statement.

5, trim (where, set)

5.1 where

Returning to the previous if example, if we remove 1 = 1 and all dynamic conditions are not met, sql splicing will become like this:

SELECT * FROM blog WHERE

If there is no condition, but there is still a where statement, an error will be reported. MyBatis has a simple and suitable solution for most scenarios: that is, change the where to the < where > tag and the above statement to:

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

The where element inserts the "where" clause only if the child element returns anything. Moreover, if clause starts with the "AND" OR ", where element removes them.

5.2 set

A similar solution for dynamically updating statements is called set. The set element can be used to dynamically include columns that need to be updated and ignore other columns that do not need to be updated.

<update id="updateBlogSet" parameterType="map">
    update blog
    <set>
        <if test = "title!=null">
            title = #{title},
        </if>
        <if test = "author!=null">
            author = #{author},
        </if>
    </set>
    where id = #{id}
</update>

In this example, the SET element will dynamically insert the SET keyword at the beginning of the line and delete additional commas (which are introduced when assigning values to columns using conditional statements).

5.3 trim

The above where and set are derived from the < trim > element, where < where > can be expressed as:

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

The prefixOverrides property ignores text sequences separated by pipe characters (note that spaces are necessary in this example). The above example will remove all the contents specified in the prefixOverrides attribute and insert the contents specified in the prefix attribute.

< set > can be expressed as:

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

6, Summary

Dynamic SQL is to use various element label classes to splice SQL statements. In essence, it is still SQL statements.