preface
Work, often encounter a lot of batch operation requirements: batch add, batch update, batch delete, batch import, batch audit and so on, the following article we will repeat one by one, first of all, we first understand mybatis tag foreach loop:
MybatIs tags foreach
Foreach is primarily used to build in conditions, which can iterate over a collection in an SQL statement.
The main attributes of foreach element are item, index, collection, open, separator, and close.
1. Alias for iterating each element in the item collection
Index indicates the position reached during each iteration
What does the statement start with
What symbol does separator use as a separator between each iteration
5. What does close end with
1. There are three main cases of collection attributes:
1.1 If a single argument is passed and the argument type is a List, the collection property value is List
1.2 If a single parameter is passed and the parameter type is array, the collection property value is array
1.3 If multiple parameters are passed in, we need to encapsulate them into a Map
Two, batch add
When passed as a list collection:
2.1 Mapper XML
<! --> <insert id="saveEmp" parameterType="java.util.List">
INSERT INTO t_employee(id, name, age, salary, department_id,update_time)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},#{item.name},#{item.age},#{item.salary},#{item.departmentId},now())
</foreach>
</insert>
Copy the code2.2 the Controller layer
@PostMapping("saveBath")
@ResponseBody
public CommonResult<Employee> saveBath(@RequestBody List<Employee> employeeList){
return employeeService.saveEmp(employeeList);
}
Copy the code@responseBody: returns Json data @requestBody: accepts Json data
2.3 Json Array Collection data
[{"id": 1."name": DT test "1"."age": 26."salary": 10000.0."departmentId": 1
},
{
"id": 2."name": "DT test 2"."age": 26."salary": 10000.0."departmentId": 2}]Copy the codeThird, batch update
1.Mapper.xml
1.1 Batch update the first method
<update id="updateBatch" parameterType="java.util.List" >
<foreach collection="list" item="item" index="index" separator=";">
UPDATE t_employee
<set>
<if test="item.name ! = null and item.name ! = "" >
name = #{item.name},
</if>
<if test="item.age ! = null" >
age = #{item.age},
</if>
<if test="item.salary ! = null" >
salary = #{item.salary},
</if>
<if test="item.salary ! = null" >
salary = #{item.departmentId},
</if>
</set>
where id = #{item.id}
</foreach>
</update>
Copy the codeRemember to connect to the database plus:
allowMultiQueries=true
Copy the codeOtherwise, the following error will be reported:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘UPDATE t_employee
AllowMultiQueries =true allowMultiQueries=true
1. You can carry semicolons (;) after SQL statements to implement multi-statement execution. 2. Execute batch processing and issue multiple SQL statements at the same time.
This way is through SQL splicing, single update.
1.2 Batch update the second method
<update id="updateBatch" parameterType="java.util.List" >
update t_employee
<trim prefix="set" suffixOverrides=",">
<trim prefix="name=case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.name ! = null and i.name ! = "">
when id=#{i.id} then #{i.name}
</if>
</foreach>
</trim>
<trim prefix="age=case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.age ! = null">
when id=#{i.id} then #{i.age}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index" >
id = #{i.id}
</foreach>
</update>
Copy the codeThe bulk update is actually done through the case WHEN statement with a single SQL statement:
Of course, in addition to the above two ways, can also be through the following:
Batch UPDATE The third method, ON DUPLICATE KEY UPDATE, is a batch insert operation. If the DUPLICATE KEY UPDATE already exists during the insert, it is updated. Therefore, the batch modification effect can be achieved.Copy the codeIt is generally not recommended to update SQL with large amounts of data. In this way, xiaobian’s previous article also has said how to use it, which is no longer described here.
Note: the above method is for the case of multiple fields, if only a single field, can be written as:
<! The second method is to batch update a single field --> <update id="updateBatch" parameterType="java.util.List">
UPDATE t_employee
SET name = CASE
<foreach collection="list" item="item" index="index">
WHEN id = #{item.id} THEN #{item.name}
</foreach>
END
WHERE id IN
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>
Copy the code2. The Controller layer
@PostMapping("updateBatch")
@ResponseBody
public CommonResult<Employee> updateBatch(@RequestBody List<Employee> employeeList){
return employeeService.updateBatch(employeeList);
}
Copy the code3.Json collection data
[{"id": 1."name": DT test "111"."age": 2611
},
{
"id": 2."name": DT test "211"."age": 2611}]Copy the code4. Batch delete
1. Pass in the List array object
1.Mapper.xml
<delete id="deleteBath" parameterType="java.util.List">
DELETE FROM t_employee WHERE id IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</delete>
Copy the code2. The Controller layer
@PostMapping("deleteBath")
@ResponseBody
public CommonResult<Employee> deleteBath(@RequestBody List<Employee> employeeList){
return employeeService.deleteBath(employeeList);
}
Copy the code3.Json collection data
[{"id": 1
},
{
"id": 2}]Copy the code2. An array is passed in
1.Mapper.xml
<delete id="deleteBath" parameterType="java.util.Arrays">
DELETE FROM t_employee WHERE id IN
<foreach collection="array" item="ids" open="(" separator="," close=")">
#{ids}
</foreach>
</delete>
Copy the code2. The Controller layer
@PostMapping("deleteBath")
@ResponseBody
public CommonResult<Employee> deleteBath(@RequestBody int[] ids){
return employeeService.deleteBath(ids);
}
Copy the code3. The Json array
[1.2]
Copy the code2. The Map collection is passed in
1.Mapper.xml
<delete id="deleteBath" parameterType="java.util.Map">
DELETE FROM t_employee WHERE id IN
<foreach collection="ids" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
Copy the codeint deleteBath(@Param("ids") Map<String, Integer> ids);
Copy the code2. The Controller layer
@PostMapping("deleteBath")
@ResponseBody
public CommonResult<Employee> deleteBath(@RequestBody Map<String,Object> map){
/ / receive the List
List<Integer> ids = (List<Integer>) map.get("ids");
Map<String, Integer> stringMap = new HashMap<>();
ids.forEach(id -> stringMap.put("ids", id));
return employeeService.deleteBath(stringMap);
}
Copy the code3. The map data
{
"ids": [1.2]}Copy the codeFive, batch query
1.Mapper.xml
<select id="findBath" resultType="com.dt.springbootdemo.entity.Employee" parameterType="com.dt.springbootdemo.entity.Employee">
SELECT * FROM t_employee WHERE id IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</select>
Copy the code2. The Controller layer
@GetMapping("findBath")
@ResponseBody
public CommonResult<List<Employee>> findBath(@RequestBody List<Employee> employeeList){
return employeeService.findBath(employeeList);
}
Copy the code3.Json collection data
[{"id": 1
},
{
"id": 2}]Copy the codeOther data formats will not be described, it is very simple, change the data format can be:
conclusion
This is the end! Stay up late dry goods, creation is not easy, move small hands point praise ！！！！ Behind will continue to output more dry goods to you, like please pay attention to xiaobian CSDN: blog.csdn.net/qq_41107231 and nuggets: juejin.cn/user/394024…