MyBatis根据条件批量修改字段
背景:
给学生改作业,只要是对的都批量进行数据库的修改
代码以及注释
- conttoller
1
2
3
4
5
6
7
8
9
10
11
12
|
@RestController @RequestMapping ( "/work" ) public class WorkController { @Autowired private WorkService workService; @PutMapping ( "/examine" ) public HttpResponse examine(Integer[] id) { Integer total = workService.examine(id); return HttpResponse.ok( "共批改[ " +total+ " ]条道题" , "" ); } } |
- service
1
2
3
4
5
6
7
8
9
|
@Service public class WorkService { @Autowired private WorkMapper workMapper; public Integer examine(Integer[] id) { return workMapper.examine(id); } } |
- mapper
1
2
3
4
|
@Mapper public interface WorkMapper { Integer examine( @Param ( "id" )Integer[] id); } |
- xml
1
2
3
4
5
6
7
|
<!--根据id来批量修改WORK表里面的isEnable字段--> < update id = "examine" > UPDATE WORK SET isEnable=TRUE WHERE id IN < foreach collection = "id" item = "id" separator = "," open = "(" close = ")" > #{id} </ foreach > </ update > |
- 工具类HttpResponse(此需求中无关紧要的类)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
public class HttpResponse { private Integer status; private String message; private Object object; public static HttpResponse ok(String message) { return new HttpResponse( 200 , message, null ); } public static HttpResponse ok(Object object) { return new HttpResponse( 200 , null , object); } public static HttpResponse ok(String message,Object object) { return new HttpResponse( 200 , message, object); } public static HttpResponse error(Integer status, String message) { return new HttpResponse( 500 , message, null ); } public static HttpResponse error(String message) { return new HttpResponse( 500 , message, null ); } public static HttpResponse error(String message,Object object) { return new HttpResponse( 500 , message, object); } protected HttpResponse() { super (); } private HttpResponse(Integer status, String message, Object object) { super (); this .status = status; this .message = message; this .object = object; } public Integer getStatus() { return status; } public void setStatus(Integer status) { this .status = status; } public String getMessage() { return message; } public void setMessage(String message) { this .message = message; } public Object getObject() { return object; } public void setObject(Object object) { this .object = object; } } |
重点:
如果mapper没加@Param("id")注解会报错找不到参数"id"
至此MyBatis根据id批量修改数据库的某字段需求完成!
MyBatis多条件批量修改
简单记录下
想要修改一张表,是联合主键,也就是where后两个以上条件才能唯一确定一条数据。
如果有唯一键,那么foreach中 用in就可以解决。
现在没办法用in,不然 A in (#{})and B in (#{})感觉成笛卡尔了。
简单点就是要实现执行多条语句。对update做循环。传入参数为 List,ATest类中字段A,B为联合主键,修改C的值
1
2
3
4
5
6
7
8
9
10
|
< update id = "update" parameterType = "java.util.List" > < foreach collection = "list" item = "val" separator = ";" open = "begin" close = ";end;" > update table_ABC < set > < if test = "val.C!= null" >C = #{val.C},</ if > </ set > where A=#{val.A} and B= #{val.B} </ foreach > </ update > |
执行出来效果为:
begin
update table_ABC set C = '2' where A = '1' and B= '1';
update table_ABC set C = '2' where A = '1' and B= '2';
update table_ABC set C = '4' where A = '3' and B= '2';
end;
这里使用的数据库为oracle,oracle执行认为begin和end之前为一条语句
当然,也可以用or 的形式拼接,不过还没测试。
网上查有的说是mysql数据库 mybatis一次执行一条语句。支持多条,可以MySQL连接数据库时,添加语句:“allowMultiQueries=true”
作用:
1.可以在sql语句后携带分号,实现多语句执行。
2.可以执行批处理,同时发出多个SQL语句。
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/qq_42426937/article/details/105018123