Dynamic SQL Foreach batch operation
- foreword
- prerequisite knowledge
-
- MySQL batch insert
- MySQL batch query
- MySQL batch modification
- MySQL batch delete
- Use foreach in mybatis for batch operations
-
- Attributes of the foreach tag
- batch insert
- batch query
- Batch Edit
- batch deletion
- Summarize
Foreword
Recently, I am studying the dynamic SQL of Mybatis, and I just learned the foreach element. In the past, the foreach element of Mybatis was often used for batch operations in project development. But sometimes it will be used incorrectly, so I sorted out and summarized the method of using foreach to add, delete, modify and check. Through this blog, the use of foreach will be more proficient and efficient.
Prerequisite knowledge
Before learning Mybatis’s foreach, we need to know how to write mysql batch statements. If you don’t know how to write mysql batch statements, then using the foreach element of Mybatis to perform batch operations is like a headless fly. The principle is very simple. Mybatis is a persistence layer framework. One of its functions is to splice SQL and hand it over to the database to execute SQL. So in mybatis, we need to splice batches of SQL statements successfully, so we will use how to write SQL for batch operations, and then how to combine it with the syntax of Mybatis.
Let’s take a look at how to write and execute the results of batch CRUD SQL statements.
MySQL batch insert
INSERT INTO tar_course_content_info ( id, course_assembly_id, assembly_content, create_time, created_id, created_by, update_time, updated_id, updated_by, is_delete )
VALUES
(
301906655392563202,
301906577433034752,
'Chinese class',
'2022-07-12 19:13:44',
'EmE6TKu4okhu3qK5M1AGQ4',
'Zhang San',
'2022-07-12 19:13:44',
'PWeDZyRPADjsdxCNWnSWxZ',
'Wang Wu',
0
),(
301906655392563203,
301906577433034752,
'math class',
'2022-07-12 19:13:44',
'EmE6TKu4okhu3qK5M1AGQ4',
'Zhang San',
'2022-07-12 19:13:44',
'PWeDZyRPADjsdxCNWnSWxZ',
'Wang Wu',
0
)
Execution result
The number of rows already affected is 2 rows
MySQL batch query
Use the in keyword, the function of the in keyword is to query data within a certain range
SELECT * FROM tar_course_content_info WHERE is_delete = 0 AND ( created_by, course_assembly_id ) IN (( 'Zhang San', 305107474690605056 ),( 'Li Si', 308290117053710337 ))
Execution result
MySQL batch modification
UPDATE tar_course_content_info SET created_by='Wang Wu' WHERE course_assembly_id IN( 305107474690605056,308290117053710337)
Execution result
The number of rows already affected is 13 rows
MySQL batch delete
DELETE FROM tar_course_content_info where (created_by,id) in (('Zhang San',301906655392563202),('Zhang San',301906655392563203))
Execution result
After knowing the batch operation of mysql sql statement, let’s see how to use foreach in mybatis to perform batch operation in the actual project development process.
Use foreach in mybatis for batch operations
Attributes of the foreach tag
collection indicates the name of the iteration collection
item indicates the element obtained in this iteration. If the collection is List, Set, or Array, it indicates the element in it; if the collection is may, it indicates the value in the key-value. The parameter is required
open Indicates what the statement starts with. The commonly used one is the left bracket “(“. Mybatis will splice the string before the sql statement wrapped by foreach, and splice it only once. This parameter is optional.
close Indicates what the statement is based on. The commonly used right bracket is “)”. Mybatis will splice the string after the SQL statement wrapped by foreach, and splice it only once. This parameter is optional.
separator mybatis will add the characters specified by the separate attribute to sql after each iteration. This parameter is optional.
index In List, Set and Array, it indicates the position of the current iteration. In Map, index indicates the key in the key-value. This parameter is optional.
nullable Indicates whether the collection can be null, the default is false, when set to true, the collection is null and no exception is thrown
Batch Insert
<!--Batch Insert-->
<insert id="insertBatch">
INSERT INTO tar_course_content_info (
id,
course_assembly_id,
assembly_content,
create_time,
created_id,
created_by,
update_time,
updated_id,
updated_by,
is_delete
) values
<foreach collection="list" item="item" separator=",">
( #{item.id},
#{item. courseAssemblyId},
#{item. assemblyContent},
#{item. createTime},
#{item.createdId},
#{item.createdBy},
#{item. updateTime},
#{item.updatedId},
#{item.updatedBy},
#{item.iselete}
)
</foreach>
</insert>
Execution result
Batch query
<!--Batch query based on creator and course component id -->
<select id="queryAllCourseContentByCreatedByAndCourseAssemblyId" resultMap="courseContentMap">
SELECT *
FROM tar_course_content_info
WhERE
is_delete=0
and
(created_by, course_assembly_id)
in
<foreach collection="list" item="item" open="(" close=")" separator="," nullable="false">
(#{item. createdBy}, #{item. courseAssemblyId})
</foreach>
</select>
Execution result
Batch editing
The first case is that the values to be updated are the same:
<!-- Batch false deletion based on creator and content id -->
<update id="updateAllByCreatedByAndContentId">
UPDATE tar_course_content_info SET is_delete=1 WHERE (created_by,id) IN
<foreach collection="list" item="item" open="(" close=")" separator=",">
(#{item.createdBy},#{item.id})
</foreach>
</update>
Execution result
The second case is that the values to be updated are different:
It should be noted here that you need to add & amp;allowMultiQueries=true in the connection data
Function: Batch processing can be executed and multiple SQL statements can be issued at the same time. That is, you can carry a semicolon after the SQL statement to implement multi-statement execution.
<!-- Modify the name of the creator in batches according to the course component id -->
<update id="updateAllCreatedByByCourseAssemblyId">
<foreach collection="list" item="item" separator=";">
UPDATE tar_course_content_info
SET
created_by = #{item.createdBy}
WHERE
course_assembly_id = #{item. courseAssemblyId}
</foreach>
</update>
Execution result
Bulk delete
<!-- Batch delete based on creator and content id -->
<delete id="deleteAllByCreatedByAndContentId">
DELETE FROM tar_course_content_info where (created_by,id) in
<foreach collection="list" item="item" open="(" close=")" separator="," nullable="false" index="index">
(#{item.createdBy},#{item.id})
</foreach>
</delete>
Execution result
Summary
- When learning a new thing, what I need to think about is the minimum necessary pre-knowledge that I must master, otherwise the efficiency will be extremely slow. Learning new things after mastering the minimum necessary knowledge will get twice the result with half the effort.
- Combining theory and practice. What is learned on paper is always shallow, and if you want to know this, you must do it yourself.