Catalog
2.1 Use if tag in WHERE condition
2.2 Use if tag in UPDATE update column
2.3 Use if tag in INSERT dynamic insertion
Several properties of 4.3.3 trim
5.2 foreach for batch insertion
7. Method of Wrapper in Mybatis-Plus
One of the favorite features of MyBatis is dynamic SQL. In the process of using JDBC, splicing SQL according to conditions is cumbersome and error-prone. The advent of MyBatis dynamic SQL solves this problem.
MyBatis uses OGNL for dynamic SQL.
Currently, dynamic SQL supports the following tags
| element | Effect | Remarks |
|---|---|---|
| if | Judgement statement | Single Conditional Branch |
| choose(when,otherwise) | Equivalent to if else in Java | Multi-Conditional Branch |
| trim(where,set) | Auxiliary Elements | Used to handle SQL splicing problems |
| foreach | Loop statement | Bulk insert, update, often used in queries |
| bind | Create a variable and bind it to the context | For compatibility with different databases, preventing SQL injection, etc. |
1 Data preparation
For later demonstrations, a Maven project, mybatis-dynamic, was created with corresponding databases and tables
DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'number', `name` varchar(20) DEFAULT NULL COMMENT 'Full name', `phone` varchar(20) DEFAULT NULL COMMENT 'Telephone', `email` varchar(50) DEFAULT NULL COMMENT 'mailbox', `sex` tinyint(4) DEFAULT NULL COMMENT 'Gender', `locked` tinyint(4) DEFAULT NULL COMMENT 'state(0:normal,1:locking)', `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Time to store in database', `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified time', `delete` int(11) DEFAULT NULL, PRIMARY KEY (`student_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Student table';
Corresponding project structure
Project structure
2 if tag
The if tag is the one we use most often. It is likely to be used when querying, deleting, updating. Must be used in conjunction with the test attribute.
2.1 Use if tag in WHERE condition
This is a common phenomenon, and there may be a variety of situations when we make conditional queries.
2.1.1 Query Criteria
Conditional retrieval based on input student information
-
When only the user name is entered, the user name is used for fuzzy retrieval.
-
Use gender for exact matching when only gender is entered
-
Query matching queries using both user name and gender
2.1.2 Dynamic SQL
Application program interface
/** * Conditional retrieval based on input student information * 1. When only the user name is entered, the user name is used for fuzzy retrieval. * 2. Use gender for exact matching when only mailboxes are entered * 3. Use these two criteria for query matching when both user name and gender exist * @param student * @return */ List<Student> selectByStudentSelective(Student student);
Corresponding Dynamic SQL
<select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
where 1=1
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</select>
In this SQL statement, where 1=1 is a trick when splicing multiple conditions so that all subsequent conditional queries can use and.
At the same time, we added if tags to handle dynamic SQL
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
<if test="sex != null">
and sex=#{sex}
</if>
The test attribute value of this if tag is an OGNL-compliant expression that can be true or false. If the expression returns a numeric value, 0 is false and non-0 is true.
2.1.3 Test
@Test
public void selectByStudent() {
SqlSession sqlSession = null;
sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student search = new Student();
search.setName("bright");
System.out.println("Queries with name only");
List<Student> studentsByName = studentMapper.selectByStudentSelective(search);
for (int i = 0; i < studentsByName.size(); i++) {
System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
}
search.setName(null);
search.setSex((byte) 1);
System.out.println("Gender Only Queries");
List<Student> studentsBySex = studentMapper.selectByStudentSelective(search);
for (int i = 0; i < studentsBySex.size(); i++) {
System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
}
System.out.println("Queries with both name and gender");
search.setName("bright");
List<Student> studentsByNameAndSex = studentMapper.selectByStudentSelective(search);
for (int i = 0; i < studentsByNameAndSex.size(); i++) {
System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE));
}
sqlSession.commit();
sqlSession.close();
}
Name-only queries, sent statements, and results
Queries with name only
Query criteria sent only
where 1=1 and name like concat('%', ?, '%')
Queries, Sent Statements and Results for Gender Only
Gender Only Queries
Query criteria sent only
where 1=1 and sex=?
Queries, Sent Statements and Results with Name and Gender
Queries with both name and gender
query criteria
where 1=1 and name like concat('%', ?, '%') and sex=?
2.2 Use if tag in UPDATE update column
Sometimes we don't want to update all the fields, just the fields that have changed.
2.2.1 Update Conditions
Only fields with changes are updated, and null values are not updated.
2.2.1 Dynamic SQL
Interface Method
/** * Update non-empty properties */ int updateByPrimaryKeySelective(Student record);
Corresponding SQL
<update id="updateByPrimaryKeySelective" parameterType="com.homejim.mybatis.entity.Student">
update student
<set>
<if test="name != null">
`name` = #{name,jdbcType=VARCHAR},
</if>
<if test="phone != null">
phone = #{phone,jdbcType=VARCHAR},
</if>
<if test="email != null">
email = #{email,jdbcType=VARCHAR},
</if>
<if test="sex != null">
sex = #{sex,jdbcType=TINYINT},
</if>
<if test="locked != null">
locked = #{locked,jdbcType=TINYINT},
</if>
<if test="gmtCreated != null">
gmt_created = #{gmtCreated,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
</if>
</set>
where student_id = #{studentId,jdbcType=INTEGER}
2.2.3 Test
@Test
public void updateByStudentSelective() {
SqlSession sqlSession = null;
sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = new Student();
student.setStudentId(1);
student.setName("Mingming");
student.setPhone("13838438888");
System.out.println(studentMapper.updateByPrimaryKeySelective(student));
sqlSession.commit();
sqlSession.close();
}
give the result as follows
Use if tag in UPDATE update column
2.3 Use if tag in INSERT dynamic insertion
When we insert a record into a database, not every field has a value, it changes dynamically. Using the if tag at this time can help us solve this problem.
2.3.1 Insertion Conditions
Only non-empty attributes are inserted.
2.3.2 Dynamic SQL
Interface Method
/** * Non-empty fields are inserted */ int insertSelective(Student record);
Corresponding SQL
<insert id="insertSelective" parameterType="com.homejim.mybatis.entity.Student">
insert into student
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="studentId != null">
student_id,
</if>
<if test="name != null">
`name`,
</if>
<if test="phone != null">
phone,
</if>
<if test="email != null">
email,
</if>
<if test="sex != null">
sex,
</if>
<if test="locked != null">
locked,
</if>
<if test="gmtCreated != null">
gmt_created,
</if>
<if test="gmtModified != null">
gmt_modified,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="studentId != null">
#{studentId,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="phone != null">
#{phone,jdbcType=VARCHAR},
</if>
<if test="email != null">
#{email,jdbcType=VARCHAR},
</if>
<if test="sex != null">
#{sex,jdbcType=TINYINT},
</if>
<if test="locked != null">
#{locked,jdbcType=TINYINT},
</if>
<if test="gmtCreated != null">
#{gmtCreated,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
#{gmtModified,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
This SQL should be familiar to everyone, after all, it is automatically generated.
2.3.3 Test
@Test
public void insertByStudentSelective() {
SqlSession sqlSession = null;
sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = new Student();
student.setName("Small Airplane");
student.setPhone("13838438899");
student.setEmail("xiaofeiji@qq.com");
student.setLocked((byte) 0);
System.out.println(studentMapper.insertSelective(student));
sqlSession.commit();
sqlSession.close();
}
Corresponding results
Use if tag in INSERT dynamic insertion
In SQL, only non-empty fields are inserted.
3 choose tag
The choose when otherwise tag helps us implement if else logic.
A choose tag has at least one when and at most one otherwise
The following is an example of a query.
3.1 Query Criteria
Assuming name is unique, query a student
-
When studen_ Use studen_when ID has a value ID for query;
-
When studen_ When ID has no value, use name to query;
-
Otherwise return to null
3.2 Dynamic SQL
Interface Method
/** * - When studen_ Use studen_when ID has a value ID for query; * - When studen_ Use name to query if Id has no value; * - Otherwise return to null */ Student selectByIdOrName(Student record);
Corresponding SQL
<select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
where 1=1
<choose>
<when test="studentId != null">
and student_id=#{studentId}
</when>
<when test="name != null and name != ''">
and name=#{name}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
3.3 Test
@Test
public void selectByIdOrName() {
SqlSession sqlSession = null;
sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = new Student();
student.setName("Small Airplane");
student.setStudentId(1);
Student studentById = studentMapper.selectByIdOrName(student);
System.out.println("Yes ID Then according to ID Obtain");
System.out.println(ToStringBuilder.reflectionToString(studentById, ToStringStyle.MULTI_LINE_STYLE));
student.setStudentId(null);
Student studentByName = studentMapper.selectByIdOrName(student);
System.out.println("No, ID Then according to name Obtain");
System.out.println(ToStringBuilder.reflectionToString(studentByName, ToStringStyle.MULTI_LINE_STYLE));
student.setName(null);
Student studentNull = studentMapper.selectByIdOrName(student);
System.out.println("No, ID and name, Return null");
Assert.assertNull(studentNull);
sqlSession.commit();
sqlSession.close();
}
If there is an ID, it is obtained based on the ID, and the result is
If there is an ID, get it by ID
Get by name without ID
Get by name without ID
No ID and name, return null
No ID and name, return null
4 trim(set,where)
These three actually solve similar problems. When we write the previous [if tag in WHERE condition] SQL, where 1=1 is a condition we don't want to have.
4.1 where
4.1.1 Query Criteria
Conditional retrieval based on the input student information.
-
When only the user name is entered, the user name is used for fuzzy retrieval.
-
Use gender for exact matching when only gender is entered
-
Query matching queries using both user name and gender
Do not use where 1=1.
4.1.2 Dynamic SQL
Clearly, we need to address these issues
-
When conditions are not met: should there be no where in SQL at this time, otherwise an error will occur
-
When if is conditionally satisfied: where is required in SQL, and | or etc. under the first established if tag is removed
At this point, we can use the where tag.
Interface Method
/** * Conditional retrieval based on input student information * 1. When only the user name is entered, the user name is used for fuzzy retrieval. * 2. Use gender for exact matching when only mailboxes are entered * 3. Use these two criteria for query matching when both user name and gender exist */ List<Student> selectByStudentSelectiveWhereTag(Student student);
Corresponding SQL
<select id="selectByStudentSelectiveWhereTag" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
<where>
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where>
</select>
4.1.3 Test
@Test
public void selectByStudentWhereTag() {
SqlSession sqlSession = null;
sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student search = new Student();
search.setName("bright");
System.out.println("Queries with name only");
List<Student> studentsByName = studentMapper.selectByStudentSelectiveWhereTag(search);
for (int i = 0; i < studentsByName.size(); i++) {
System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
}
search.setSex((byte) 1);
System.out.println("Queries with both name and gender");
List<Student> studentsBySex = studentMapper.selectByStudentSelectiveWhereTag(search);
for (int i = 0; i < studentsBySex.size(); i++) {
System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
}
System.out.println("Query when neither name nor gender exists");
search.setName(null);
search.setSex(null);
List<Student> studentsByNameAndSex = studentMapper.selectByStudentSelectiveWhereTag(search);
for (int i = 0; i < studentsByNameAndSex.size(); i++) {
System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE));
}
sqlSession.commit();
sqlSession.close();
}
Name only query, where
Queries with name only
Queries with both name and gender, where
Queries with both name and gender
When neither name nor gender exists, the query does not occur anymore.
Query when neither name nor gender exists
4.2 set
The set tag is similar, in [2.2 uses the if tag in the UPDATE update column], if our method updateByPrimaryKeySelective does not use a tag, then we have to figure out how to handle conditions where the field is completely empty, conditions where the field is not empty, and so on. With this, we just need to write the if tag instead of dealing with similar problems.
4.3 trim
Both set and where are actually a type of trim tag, and both functions can be implemented using trim tags.
4.3.1 trim to indicate where
As with the where tag above, we can also write
<trim prefix="where" prefixOverrides="AND |OR"> </trim>
Indicates that when a trim contains content, where is added and the first is an and or, it is removed. If there is no content, don't add where.
4.3.2 trim to represent set
Correspondingly, the set tag can be expressed as follows
<trim prefix="SET" suffixOverrides=","> </trim>
Indicates that when a trim contains content, a set is added and the last content is, it is removed. No content, no set added
Several properties of 4.3.3 trim
-
Prefix: When a trim element contains content, increase the prefix specified by prefix
-
PrefixOverrides: Removes the prefix specified by prefixOverrides when the trim element contains content
-
Suffix: Increases the suffix specified by suffix when the trim element contains content
-
SuffixOverrides: Removes the suffix specified by suffixOverrides when the trim element contains content
5 foreach tag
The foreach tag can be used on arrays, maps, or implements the Iterable interface.
There are several properties in foreach
-
Collection: required, name of collection/array/Map.
-
item: Variable name. That is, each value taken from the iterated object
-
Index: The property name of the index. When the iterated object is a Map, the value is a Key in the Map.
-
open:The string at the beginning of the loop
-
close:The string at the end of the loop
-
Separator: separator for each loop
Others understand better, how should the values in the collection be set?
Related to parameters in interface methods.
1. Only one array or set parameter
Default: collection=list, array is collection=array
Recommendation: Use @Param to specify the name of the parameter. If we use @Param("ids") before the parameter, fill in collection=ids
2. Multiple parameters
Use @Param to specify multiple parameters, otherwise it will be inconvenient in SQL
3. The parameter is Map
Specify the corresponding Key in the Map. In fact, the @Param above is also converted to Map in the end.
4. Parameters are objects
Use attributes. Attributes will do.
5.1 where to use foreach
Used in where conditions, such as querying by id set, deleting by id set, etc.
5.1.1 Query Criteria
We want to query all user information in the user id collection.
5.1.2 Dynamic SQL
Function Interface
/** * Get user information in the id collection * @param ids * @return */ List<Student> selectByStudentIdList(List<Integer> ids);
Corresponding SQL
<select id="selectByStudentIdList" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from student
where student_id in
<foreach collection="list" item="id" open="(" close=")" separator="," index="i">
#{id}
</foreach>
</select>
5.1.3 Test
@Test
public void selectByStudentIdList() {
SqlSession sqlSession = null;
sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Integer> ids = new LinkedList<>();
ids.add(1);
ids.add(3);
List<Student> students = studentMapper.selectByStudentIdList(ids);
for (int i = 0; i < students.size(); i++) {
System.out.println(ToStringBuilder.reflectionToString(students.get(i), ToStringStyle.MULTI_LINE_STYLE));
}
sqlSession.commit();
sqlSession.close();
}
Result
where to use foreach
5.2 foreach for batch insertion
Batch insertions can be accomplished through foreach.
5.2.1 Dynamic SQL
Interface Method
/** * Bulk Insert Student */ int insertList(List<Student> students);
Corresponding SQL
<insert id="insertList">
insert into student(name, phone, email, sex, locked)
values
<foreach collection="list" item="student" separator=",">
(
#{student.name}, #{student.phone},#{student.email},
#{student.sex},#{student.locked}
)
</foreach>
</insert>
5.2.2 Test
Result
foreach implements bulk insertion
6 bind tag
The bind tag defines a context variable through an OGNL expression, which is convenient for us to use.
As in the selectByStudentSelective method, there are
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
In MySQL, this function supports multiple parameters, but only two parameters are supported in Oracle. So we can use bind to make this SQL work to support two databases
<if test="name != null and name !=''">
<bind name="nameLike" value="'%'+name+'%'"/>
and name like #{nameLike}
</if>
The changed query results are as follows
Use of bind Tags
7. Method of Wrapper in Mybatis-Plus
public interface EntityService extends IService<TbEntity>{
}
entityService.update(entity,Condition.create().like("name","hello").eq("status","0"));| Query mode | Explain |
|---|---|
| setSqlSelect | Set SELECT Query Field |
| where | WHERE statement, splicing + WHERE condition |
| and | AND statement, splice + AND field = value |
| andNew | AND statement, stitching + AND (field = value) |
| or | OR statement, splice + OR field = value |
| orNew | OR statement, splice + OR (field = value) |
| eq | Equal to= |
| allEq | Based on map content equal to= |
| ne | Not equal to <> |
| gt | Greater than> |
| ge | Greater than or equal to >= |
| lt | Less than < |
| le | Less than or equal to <= |
| like | Fuzzy Query LIKE |
| notLike | Fuzzy Query NOT LIKE |
| in | IN Query |
| notIn | NOT IN Query |
| isNull | NULL Value Query |
| isNotNull | IS NOT NULL |
| groupBy | Grouping GROUP BY |
| having | HAVING keywords |
| orderBy | Sort ORDER BY |
| orderAsc | ASC Sort ORDER BY |
| orderDesc | DESC Sort ORDER BY |
| exists | EXISTS Conditional Statement |
| notExists | NOT EXISTS conditional statement |
| between | BETWEEN conditional statement |
| notBetween | NOT BETWEEN conditional statement |
| addFilter | Free Splicing SQL |
| last | Stitch together at the end, for example: last("LIMIT 1") |
addFilterIfNeed | Where you can write sql:e.g. .addFilterIfNeed(StringUtils.equals(userId,"1"),"date_format(FOLLOW_TIME,'%Y-%m-%d')=date_format(NOW(),'%Y-%m-%d')") |