MyBatis series (6) : MyBatis dynamic Sql if tag usage

This blog post focuses on using the if tag to generate dynamic Sql, including the following three scenarios:

Implement dynamic query according to query conditions
Dynamically update some columns based on parameter values
Dynamically insert columns based on parameter values

1. Use if tag to implement dynamic query

Suppose there is a requirement to query the user list according to the user’s input criteria. If the user name is entered, fuzzy query is performed according to the user name; if the mailbox is entered, precise query is performed according to the mailbox; if both the user name and mailbox are entered, the two criteria are used to match the user.

First, we add the following methods to the interface SysUserMapper:

/** * Query user information based on dynamic conditions **@param sysUser
 * @return* /
List<SysUser> selectByUser(SysUser sysUser);
Copy the code

Then add the following code to the corresponding sysusermapper.xml:

<select id="selectByUser" resultType="com.zwwhnly.mybatisaction.model.SysUser">
    SELECT  id,
            user_name,
            user_password,
            user_email,
            create_time
    FROM sys_user
    WHERE 1 = 1
    <if test="userName ! = null and userName ! = "">
        AND user_name LIKE CONCAT('%',#{userName},'%')
    </if>
    <if test="userEmail ! = null and userEmail ! = "">
        AND user_email = #{userEmail}
    </if>
</select>
Copy the code

Code simple explanation:

1) The test attribute of the if tag is mandatory. The value of this attribute is a judgment expression that meets OGNL requirements. Generally, only true or false is used as the result.

2) Property! = null or property == null, which applies to any type of field and is used to determine whether an attribute value is null.

3) Property! = “or property ==”, only for String fields, used to determine whether an empty String is present.

4) when there are multiple judgment conditions, use and/or connect, nested judgment can use parentheses group, and is equivalent to in Java and (&), or relevant in Java or (| |).

So the code above means that the field is null and then the field is an empty string.

Finally, add the following test methods to the SysUserMapperTest test class:

@Test
public void testSelectByUser(a) {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        // Query by user name only
        SysUser query = new SysUser();
        query.setUserName("ad");
        List<SysUser> sysUserList = sysUserMapper.selectByUser(query);
        Assert.assertTrue(sysUserList.size() > 0);

        // Query by email only
        query = new SysUser();
        query.setUserEmail("[email protected]");
        sysUserList = sysUserMapper.selectByUser(query);
        Assert.assertTrue(sysUserList.size() > 0);

        // Query by user's home and email address
        query = new SysUser();
        query.setUserName("ad");
        query.setUserEmail("[email protected]");
        sysUserList = sysUserMapper.selectByUser(query);
        // There is no user that matches both conditions, so the number of query results is 0
        Assert.assertTrue(sysUserList.size() == 0);
    } finally{ sqlSession.close(); }}Copy the code

Run the test code, the test passes, the output log is as follows:

DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE 1 = 1 AND user_name LIKE CONCAT(‘%’,? , ‘%’)

DEBUG [main] – ==> Parameters: ad(String)

TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time

TRACE [main] – <== Row: 1, admin, 123456, [email protected], 2019-06-27 18:21:07.0

DEBUG [main] – <== Total: 1

DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE 1 = 1 AND user_email = ?

DEBUG [main] – ==> Parameters: [email protected](String)

TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time

TRACE [main] – <== Row: 1001, test, 123456, [email protected], 2019-06-27 18:21:07.0

DEBUG [main] – <== Total: 1

DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE 1 = 1 AND user_name LIKE CONCAT(‘%’,? ,’%’) AND user_email = ?

DEBUG [main] – ==> Parameters: ad(String), [email protected](String)

DEBUG [main] – <== Total: 0

2. Use the if tag to implement dynamic updates

Assume that there is a requirement: when updating user information, fields that have values but have not changed cannot be updated to null or null, that is, only fields that have values can be updated.

First, we add the following methods to the interface SysUserMapper:

/** * Selectively update user information based on primary key **@param sysUser
 * @return* /
int updateByIdSelective(SysUser sysUser);
Copy the code

Then add the following code to the corresponding sysusermapper.xml:

<update id="updateByIdSelective">
    UPDATE sys_user
    SET
    <if test="userName ! = null and userName ! = "">
        user_name = #{userName},
    </if>
    <if test="userPassword ! = null and userPassword ! = "">
        user_password = #{userPassword},
    </if>
    <if test="userEmail ! = null and userEmail ! = "">
        user_email = #{userEmail},
    </if>
    <if test="userInfo ! = null and userInfo ! = "">
        user_info = #{userInfo},
    </if>
    <if test="headImg ! = null">
        head_img = #{headImg,jdbcType=BLOB},
    </if>
    <if test="createTime ! = null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
    </if>
    id = #{id}
    WHERE id = #{id}
</update>
Copy the code

Finally, add the following test methods to the SysUserMapperTest test class:

@Test
public void testUpdateByIdSelective(a) {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        SysUser sysUser = new SysUser();
        // Update user id=1
        sysUser.setId(1L);
        // Modify the mailbox
        sysUser.setUserEmail("[email protected]");

        int result = sysUserMapper.updateByIdSelective(sysUser);
        Assert.assertEquals(1, result);

        // Query user id 1
        sysUser = sysUserMapper.selectById(1L);
        // The modified name remains the same, but the mailbox is new
        Assert.assertEquals("admin", sysUser.getUserName());
        Assert.assertEquals("[email protected]", sysUser.getUserEmail());
    } finally{ sqlSession.close(); }}Copy the code

Run the test code, the test passes, the output log is as follows:

DEBUG [main] – ==> Preparing: UPDATE sys_user SET user_email = ? , id = ? WHERE id = ?

DEBUG [main] – ==> Parameters: [email protected](String), 1(Long), 1(Long)

DEBUG [main] – <== Updates: 1

DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id = ?

DEBUG [main] – ==> Parameters: 1(Long)

TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time

TRACE [main] – <== Row: 1, admin, 123456, [email protected], 2019-06-27 18:21:07.0

DEBUG [main] – <== Total: 1

3. Use the IF tag to implement dynamic insertion

Suppose you have a requirement that when inserting data into a database table, if the parameter value in a column is not null, the value passed in is used, and if the parameter value is null, the default value in the database (which is usually null) is used instead of the null value passed in.

To better understand this example, let’s set the user_email field of the sys_user table to the default value [email protected]. The Sql statement is as follows:

ALTER TABLE sys_user MODIFY COLUMN user_email VARCHAR(50) NULL DEFAULT '[email protected]' COMMENT 'mailbox' AFTER user_password;Copy the code

First, we add the following methods to the interface SysUserMapper:

/** * dynamically inserts columns ** based on the parameter values passed in@param sysUser
 * @return* /
int insertSelective(SysUser sysUser);
Copy the code

Then add the following code to the corresponding sysusermapper.xml:

<insert id="insertSelective" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO sys_user(user_name, user_password,
    <if test="userEmail ! = null and userEmail ! = "">
        user_email,
    </if>
    user_info, head_img, create_time)
    VALUES (#{userName},#{userPassword},
    <if test="userEmail ! = null and userEmail ! = "">
        #{userEmail},
    </if>
    #{userInfo},#{headImg,jdbcType=BLOB},#{createTime,jdbcType=TIMESTAMP})
</insert>
Copy the code

Finally, add the following test methods to the SysUserMapperTest test class:

@Test
public void testInsertSelective(a) {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        SysUser sysUser = new SysUser();
        sysUser.setUserName("test-selective");
        sysUser.setUserPassword("123456");
        sysUser.setUserInfo("test info");
        sysUser.setCreateTime(new Date());

        sysUserMapper.insertSelective(sysUser);

        // Get the data just inserted
        sysUser = sysUserMapper.selectById(sysUser.getId());
        // Since userEmail is not specified, the database default is used
        Assert.assertEquals("[email protected]", sysUser.getUserEmail());
    } finally{ sqlSession.close(); }}Copy the code

Run the test code, the test passes, the output log is as follows:

DEBUG [main] – ==> Preparing: INSERT INTO sys_user(user_name, user_password, user_info, head_img, create_time) VALUES (? ,? ,? ,? ,?)

DEBUG [main] – ==> Parameters: Test-selective (String), 123456(String), Test Info (String), NULL, 2019-07-08 11:40:362.927 (Timestamp)

DEBUG [main] – <== Updates: 1

DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id = ?

DEBUG [main] – ==> Parameters: 1021(Long)

TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time

TRACE [main] – <== Row: 1021, test-selective, 123456, [email protected], 2019-07-08 11:40:37.0

DEBUG [main] – <== Total: 1

4. Source code and reference

Source code address: github.com/zwwhnly/myb… Welcome to download.

MyBatis from Entry to Mastery by Liu Zenghui

MyBatis series (6) : MyBatis dynamic Sql if tag usage

1. Use if tag to implement dynamic query

2. Use the if tag to implement dynamic updates

3. Use the IF tag to implement dynamic insertion

4. Source code and reference

Related Posts

Mysql series: storage engine

From Layered Architecture to Microservices Architecture (1)

Http persistent connections and HttpClient connection pools