Introduction
It is mentioned in the article Where tag of MyBatis dynamic SQL:
In the case of the if tag and the choose-when-otherwise tag, a condition ‘1=1’ is added to the SQL statement, which not only ensures the success of the condition after where, but also avoids that the first word after where is and or keywords like or.
Assuming that the condition ‘1=1’ is removed, the following statement can appear
select * from t_customer where and username like concat('%','#{username}','%')
The above statement is directly followed by and because where appears, and a syntax error will be reported when SQL is running.
At this time, you can use the where tag to process, and here you can also use the trim tag to process
trim tag has four attributes: prefix, prefixOverrides, suffix, suffixOverrides
| prefix | to sql The prefix for statement splicing |
| suffix | The suffix for sql statement splicing |
| prefixOverrides | Remove the keyword or character in front of the sql statement, the keyword or character is specified by the prefixOverrides attribute, assuming that the attribute is specified as “AND”, when the beginning of the sql statement is “AND”, the trim tag will remove the ” AND” |
| suffixOverrides | Remove the keywords or characters behind the sql statement, which are specified by the suffixOverrides attribute |
Grammar format
Trim is generally used to remove redundant AND keywords and commas in SQL statements, or to add suffixes such as where and set to SQL statements, and can be used for selective insert, update, delete, or conditional queries. The trim syntax is as follows.
<trim prefix="prefix" suffix="suffix" prefixOverrides="ignore prefix characters" suffixOverrides="ignore suffix characters">
SQL statement
</trim>
Internet case
<select id="selectWebsite" resultType="net.biancheng.po.Website">
SELECT id,name,url,age,country
FROM website
<trim prefix="where" prefixOverrides="and">
<if test="name != null and name !=''">
AND name LIKE CONCAT ('%',#{name},'%')
</if>
<if test="url != null">
AND url like concat ('%',#{url},'%')
</if>
</trim>
</select>
Basic environment
1. Database preparation
# Create a table named t_customer
CREATE TABLE t_customer (
id int(32) PRIMARY KEY AUTO_INCREMENT,
username varchar(50),
jobs varchar(50),
phone varchar(16)
);
# Insert 3 pieces of data
INSERT INTO t_customer VALUES ('1', 'joy', 'teacher', '13733333333');
INSERT INTO t_customer VALUES ('2', 'jack', 'teacher', '13522222222');
INSERT INTO t_customer VALUES ('3', 'tom', 'worker', '15111111111');
2. Create a new project or Module
Add in
3 pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>mybatis</artifactId>
<groupId>com.example</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<groupId>com.biem</groupId>
<artifactId>dynamaicSql</artifactId>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<!--1. Introduce mybatis package -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!--2. Unit test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!--3.mysql driver-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
<scope>runtime</scope>
</dependency>
<!--4.log4j log -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
</dependencies>
</project>
4. Create package and folder
Create package under src/main/java/
com.biem.pojo
com.biem.mapper
com.biem.util
Create a folder under src/main/resources/
com/biem/mapper
Create package under src/test/java
com.biem.test
5 framework configuration file
5.1 mybatis core configuration file mybatis-config.xml
<?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>
<!--Introduce properties file-->
<properties resource="jdbc.properties"></properties>
<!--Map underscores to camel case-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--Set type alias -->
<typeAliases>
<!--
In package units, set all type settings under the package to the default type alias, that is, the class name and case-insensitive
-->
<package name="com.biem.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- import mapping file -->
<mappers>
<!--
Introduce mapping files in units of packages
Require:
1. The package where the mapper interface is located must be consistent with the package where the mapping file is located
2. The mapper interface should be consistent with the name of the mapping file
-->
<package name="com.biem.mapper"/>
</mappers>
</configuration>
5.2 mybatis property file jdbc.properties
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC jdbc.username=root jdbc.password=root
5.3 log4j.xml file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \
"/>
</layout>
</appender>
<logger name="java.sql">
<level value="debug"/>
</logger>
<logger name="org.apache.ibatis">
<level value="info"/>
</logger>
<root>
<level value="debug"/>
<appender-ref ref="STDOUT"/>
</root>
</log4j:configuration>
6 User Profile
6.1 Entity class
package com.biem.pojo;
import lombok.*;
/**
* ClassName: Customer
* Package: com.biem.pojo
* Description:
*
* @Create 2023/4/5 22:17
* @Version 1.0
*/
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class Customer {<!-- -->
private Integer id;
private String username;
private String jobs;
private String phone;
}
It is necessary to introduce lombok in pom.xml to simplify the code of the original entity class
6.2 mybatis interface class
package com.biem.mapper;
/**
* ClassName: CustomerMapper
* Package: com.biem.mapper
* Description:
*
* @Create 2023/4/5 22:19
* @Version 1.0
*/
public interface CustomerMapper {<!-- -->
}
6.3 mybatis user configuration file
<?xml version="1.0" encoding="UTF-8" ?>
<! DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.CustomerMapper">
<!-- The namespace should be consistent with the full class name of the mapper interface, such as com.biem.mybatis.mapper.xxxMapper -->
<!-- The sql statement should be consistent with the method name of the interface -->
</mapper>
6.4 mybatis tool class
package com.biem.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
* ClassName: MybatisUtil
* Package: com.biem.utils
* Description:
*
* @Create 2023/4/5 22:23
* @Version 1.0
*/
public class MybatisUtil {<!-- -->
//Use static (static) to belong to the class and not to the object, and it is globally unique
private static SqlSessionFactory sqlSessionFactory = null;
//Use the static block to instantiate sqlSessionFactory when initializing the class
static {<!-- -->
InputStream is = null;
try {<!-- -->
is = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {<!-- -->
e.printStackTrace();
throw new ExceptionInInitializerError(e);
}
}
/**
* openSession creates a new SqlSession object
* @return SqlSession object
*/
public static SqlSession openSession(boolean autoCommit){<!-- -->
return sqlSessionFactory. openSession(autoCommit);
}
public static SqlSession openSession(){<!-- -->
return sqlSessionFactory. openSession();
}
/**
* Release a valid SqlSession object
* @param session is ready to release the SqlSession object
*/
public static void closeSession(SqlSession session){<!-- -->
if(session != null){<!-- -->
session. close();
}
}
}
The project structure is as follows
Use the trim tag to remove redundant and keywords
Add in 1 com.biem.mapper.CustomerMapper.class
public List<Customer> findCustomerByTrim(Customer customer);
Add in
2 com/biem/mapper/CustomerMapper.xml
<!--public List<Customer> findCustomerByTrim(Customer customer);-->
<select id="findCustomerByTrim" parameterType="customer" resultType="customer">
select * from t_customer
<trim prefix="where" prefixOverrides="and">
<if test="username !=null and username != ''">
and username like concat('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs != ''">
and jobs=#{jobs}
</if>
</trim>
</select>
3. Functional test com.biem.test.TestMybatis.java
@Test
public void testFindCustomerByTrim(){<!-- -->
// Obtain the SqlSession object through the tool class
SqlSession session = MybatisUtil. openSession();
// Create a Customer object and encapsulate the conditions that need to be combined to query
Customer customer = new Customer();
customer.setJobs("teacher");
CustomerMapper mapper = session. getMapper(CustomerMapper. class);
List<Customer> customers = mapper. findCustomerByTrim(customer);
System.out.println("customers = " + customers);
// Close SqlSession
session. close();
}