Mybatis dynamic SQL – use trim tags to replace where and set tags

In the previous articles, we introduced the use of where and set tags; in this article we continue to introduce how to use the tirm tag provided by Mybatis to replace the where and set tags.

If you don’t know much about where and set tags, it is recommended that you understand it first before reading this article. You can refer to:

Mybatis dynamic SQL – use if, where tags to dynamically generate conditional statements

Mybatis dynamic SQL – use if, set tags to dynamically generate update statements

1. Data preparation

Here we directly use the script to initialize the data in the database

--Create the database if it does not exist
CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARSET utf8;
--Switch database
USE demo;
-- Create user table
CREATE TABLE IF NOT EXISTS T_TEACHER(
  ID INT PRIMARY KEY COMMENT 'teacher number',
  TEACHER_NAME VARCHAR(64) NOT NULL COMMENT 'teacher's name',
  DEPARTMENT VARCHAR(16) NOT NULL COMMENT 'Department',
  BIRTH DATE NOT NULL COMMENT 'birth date',
  DEGREE VARCHAR(16) NOT NULL COMMENT 'Education (ZK: junior college, BK: undergraduate, YJS: graduate, BS: doctorate)'
);
--Insert user data
INSERT INTO T_TEACHER(ID, TEACHER_NAME, DEPARTMENT, BIRTH, DEGREE)
VALUES(1, 'Zhang San1', '001', '1990-06-12', 'BK'),
      (2, '李四1', '002', '1992-05-10', 'BK'),
      (3, 'Zhang San 2', '003', '1988-01-15', 'YJS'),
      (4, '李思2', '001', '1979-03-10', 'BK'),
      (5, '李思3', '003', '1995-08-16', 'YJS');

Created a database named demo; created a teacher table named T_TEACHER in the library and inserted data into the table

2. Environment construction

1. Create entity class

Create the TeacherInfo entity class under cn.horse.demo:

TeacherInfoClass:

package cn.horse.demo;

import java.time.LocalDate;

public class TeacherInfo {
    private Integer id;
    private String name;
    private String department;
    private LocalDate birth;
    private String degree;

    public void setId(Integer id) {
        this.id = id;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public void setDegree(String degree) {
        this.degree = degree;
    }

    @Override
    public String toString() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("{ ");
        stringBuilder.append("id: ");
        stringBuilder.append(this.id);
        stringBuilder.append(", ");
        stringBuilder.append("name: ");
        stringBuilder.append(this.name);
        stringBuilder.append(", ");
        stringBuilder.append("department: ");
        stringBuilder.append(this.department);
        stringBuilder.append(", ");
        stringBuilder.append("birth: ");
        stringBuilder.append(this.birth);
        stringBuilder.append(", ");
        stringBuilder.append("degree: ");
        stringBuilder.append(this.degree);
        stringBuilder.append(" }");
        return stringBuilder.toString();
    }
}

2. Mapper configuration file

Create a new TeacherInfoMapper.xml configuration file in the resources directory

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.horse.demo.TeacherInfoMapper">

    <select id="findById" resultType="cn.horse.demo.TeacherInfo">
        SELECT
            ID,
            TEACHER_NAME name,
            DEPARTMENT,
            BIRTH,
            DEGREE
        FROM T_TEACHER
        WHERE ID = #{id}
    </select>

    <select id="findByQuery" parameterType="cn.horse.demo.TeacherInfoQuery" resultType="cn.horse.demo.TeacherInfo">
        SELECT
            ID,
            TEACHER_NAME name,
            DEPARTMENT,
            BIRTH,
            DEGREE
        FROM T_TEACHER
        <where>
            <if test="null != degree and '' != degree">
                AND DEGREE = #{degree}
            </if>
            <if test="null != department and '' != department">
                AND DEPARTMENT = #{department}
            </if>
        </where>
    </select>

    <update id="updateByEntity" parameterType="cn.horse.demo.TeacherInfo">
        UPDATE T_TEACHER
        <set>
            <if test="null != department and '' != department">
                DEPARTMENT = #{department},
            </if>
            <if test="null != degree and '' != degree">
                DEGREE = #{degree},
            </if>
        </set>
        WHERE ID = #{id}
    </update>
</mapper>

3. Introduce configuration files

Create a new mybatis-config.xml configuration file under resources and introduce the TeacherInfoMapper.xml configuration file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="logImpl" value="JDK_LOGGING"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="org.gjt.mm.mysql.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/demo?useUnicode=true & amp;amp;useSSL=false & amp;amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="horse"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="demo/TeacherInfoMapper.xml" />
    </mappers>
</configuration>

4. Conversation tool class

Create a new SqlSessionUtils tool class under the cn.horse.demo package

package cn.horse.demo;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.Objects;

public class SqlSessionUtils {

    private static final SqlSessionFactory sqlSessionFactory;
    static {
        //Read mybatis configuration file
        InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("mybatis-config.xml");
        // Create SqlSession factory according to configuration
        sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(inputStream);
    }

    /**
     * Start session
     * @return
     */
    public static SqlSession openSession() {
        return sqlSessionFactory.openSession();
    }

    /**
     * Close session
     * @param sqlSession
     */
    public static void closeSession(SqlSession sqlSession) {
        if(Objects.nonNull(sqlSession)) {
            sqlSession.close();
        }
    }
}

5. JDK log system configuration

Create a new logging.properties configuration file in the resources directory

handlers=java.util.logging.ConsoleHandler
.level=INFO

cn.horse.demo.TeacherInfoMapper.level=FINER
java.util.logging.ConsoleHandler.level=ALL
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter
java.util.logging.SimpleFormatter.format=%1$tY-%1$tm-%1$td %1$tT.%1$tL %4$s %3$s - %5$s%6$s %n

Create a new JdkLogConfig class under cn.horse.demo:

JdkLogConfigClass:

package cn.horse.demo;

import java.io.IOException;
import java.io.InputStream;
import java.util.logging.LogManager;

public class JdkLogConfig {

    public JdkLogConfig() {
        try {
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("logging.properties");
            LogManager.getLogManager().readConfiguration(inputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

6. Start the program

package cn.horse.demo;

import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Main {
    public static void main(String[] args) {
        //Introduce JDK log configuration
        System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");

        // Query teachers with bachelor's degree
        TeacherInfoQuery query = new TeacherInfoQuery();
        query.setDegree("BK");
        findAll("cn.horse.demo.TeacherInfoMapper.findByQuery", query);

        //Update teacher number 2
        TeacherInfo teacherInfo = new TeacherInfo();
        teacherInfo.setId(2);
        teacherInfo.setDegree("BK");
        updateByEntity("cn.horse.demo.TeacherInfoMapper.updateByEntity", teacherInfo);
        // Query the teacher with number 2
        findById("cn.horse.demo.TeacherInfoMapper.findById", 2);
    }

    private static void findAll(String statement, TeacherInfoQuery query) {
        SqlSession sqlSession = null;
        try {
            sqlSession = SqlSessionUtils.openSession();
            List<TeacherInfo> teacherInfoList = sqlSession.selectList(statement, query);
            for (TeacherInfo teacherInfo: teacherInfoList) {
                System.out.println(teacherInfo);
            }
        } finally {
            SqlSessionUtils.closeSession(sqlSession);
        }
    }

    private static void updateByEntity(String statement, TeacherInfo teacherInfo) {
        SqlSession sqlSession = null;
        try {
            sqlSession = SqlSessionUtils.openSession();
            sqlSession.update(statement, teacherInfo);
            sqlSession.commit();
        } finally {
            SqlSessionUtils.closeSession(sqlSession);
        }
    }

    private static void findById(String statement, Integer id) {
        SqlSession sqlSession = null;
        try {
            sqlSession = SqlSessionUtils.openSession();
            TeacherInfo teacherInfo = sqlSession.selectOne(statement, id);
            System.out.println(teacherInfo);
        } finally {
            SqlSessionUtils.closeSession(sqlSession);
        }
    }
}

The result after execution is as follows:

3. Trim tag replaces where tag

Previously we introduced the two functions of the where tag:

(1) Determine whether the condition exists. If the condition does not exist, then where is no longer spliced; if the condition exists, where is spliced.

(2) Eliminate the redundant AND or OR after where

Below we give the equivalent trim tag configuration of where:

<trim prefix="where" prefixOverrides="AND | OR">
    
</trim>

prefix attribute: When the content in the trim tag is not empty, the prefix attribute value is spliced

prefixOverrides attribute: When the content in the trim tag begins with AND or OR, AND or OR is removed

Next we modify the findByQuery query statement in the TeacherInfoMapper.xml configuration file:

<select id="findByQuery" parameterType="cn.horse.demo.TeacherInfoQuery" resultType="cn.horse.demo.TeacherInfo">
    SELECT
        ID,
        TEACHER_NAME name,
        DEPARTMENT,
        BIRTH,
        DEGREE
    FROM T_TEACHER
    <trim prefix="where" prefixOverrides="AND | OR">
        <if test="null != degree and '' != degree">
            AND DEGREE = #{degree}
        </if>
        <if test="null != department and '' != department">
            AND DEPARTMENT = #{department}
        </if>
    </trim>
</select>

Test:

//Introduce JDK log configuration
System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");

// Query teachers with graduate degree
TeacherInfoQuery query = new TeacherInfoQuery();
query.setDegree("YJS");
findAll("cn.horse.demo.TeacherInfoMapper.findByQuery", query);

The result after execution is as follows:

4. The trim tag replaces the set tag

Previously we introduced that the set tag can be used to remove extra commas in update statements.

Below we give the equivalent trim tag configuration of set:

<trim prefix="set" suffixOverrides=",">
    
</trim>

prefix attribute: When the content in the trim tag is not empty, the prefix attribute value is spliced

suffixOverrides attribute: When the content in the trim tag ends with a comma, the comma is removed

Next we modify the updateByEntity query statement in the TeacherInfoMapper.xml configuration file:

<update id="updateByEntity" parameterType="cn.horse.demo.TeacherInfo">
    UPDATE T_TEACHER
    <trim prefix="set" suffixOverrides=",">
        <if test="null != department and '' != department">
            DEPARTMENT = #{department},
        </if>
        <if test="null != degree and '' != degree">
            DEGREE = #{degree},
        </if>
    </trim>
    WHERE ID = #{id}
</update>

Test:

//Introduce JDK log configuration
System.setProperty("java.util.logging.config.class", "cn.horse.demo.JdkLogConfig");

//Update teacher number 2
TeacherInfo teacherInfo = new TeacherInfo();
teacherInfo.setId(2);
teacherInfo.setDegree("BK");
updateByEntity("cn.horse.demo.TeacherInfoMapper.updateByEntity", teacherInfo);
// Query the teacher with number 2
findById("cn.horse.demo.TeacherInfoMapper.findById", 2);

The results of the query are as follows: