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: