实现InsertOrUpdate功能
需求
最近在项目开发中遇到这样一个需求:每天需要对相同的数据(也有可能是不同的)进行两次入库操作,数据不存在便insert,存在则update。于是就用到了Mybatis的InsertOrUpdate功能。
实现
每次操作数据库之前,先根据id查询有没有记录,有则进行update操作,没有则进行insert操作。
model类代码如下。其中count为非业务字段(也不是表sheet中的字段),只是方便Mybatis进行insertOrUpdate操作的附加字段。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
import lombok.Data; @Data public class Sheet { /** * 主键 */ private String id; /** * 客户姓名 */ private String customerName; /** * 。。。省略其他字段 */ /** * 该字段为非业务字段。Mybatis配置文件需要要到该字段,方便进行insertOrUpdate操作 */ private int count; } |
Mybatis的mapper.xml配置文件代码如下。
代码含义:先执行selectKey语句,把结果赋值给Sheet类的count属性。
- 如果count大于0,表示记录已存在,则进行update操作。
- 如果count等于0,表示没有记录,则进行insert操作。
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
|
< update id = "insertOrUpdate" parameterType = "Sheet" > < selectKey keyProperty = "count" resultType = "int" order = "BEFORE" > select count(1) from sheet where ID= #{id} </ selectKey > < if test="count > 0"> update sheet < set > < if test = "customerName != null and customerName != ''" > CUSTOMER_NAME= #{customerName}, </ if > </ set > where ID = #{id} </ if > < if test = "count==0" > insert into sheet < trim prefix = "(" suffix = ")" suffixOverrides = "," > ID, < if test = "customerName != null and customerName != ''" > CUSTOMER_NAME, </ if > </ trim > < trim prefix = "values (" suffix = ")" suffixOverrides = "," > #{id}, < if test = "customerName != null and customerName != ''" > #{customerName}, </ if > </ trim > </ if > </ update > |
selectKey标签可以给update标签中的parameterType属性(model类)对应的对象设置属性值。selectKey标签的属性描述:
-
keyProperty
:selectKey 语句结果应该被设置的目标属性。此处对应的就是Sheet类的count属性。 -
resultType
:结果的类型,此处为属性count的类型。 -
order
:可以被设置为 BEFORE 或 AFTER。BEFORE表示先执行selectKey语句,后执行update语句;AFTER表示先执行update语句,后执行selectKey语句。
Mybatis学习笔记:InsertOrUpdate
环境
- Intellij IDEA : 2021.3
- Mysql:8+
- java:1.8+
前言
以前使用mongodb、JOOQ组件的时候都是有insertOrUpdate的功能,现在使用mybatis似乎没有提供这种功能。
最近研究了,这个功能其实是mysql提供的,利用的是duplicate key update;
假设,我们有这么一张表:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE TABLE `relation` ( `id` int (11) NOT NULL AUTO_INCREMENT COMMENT '主键' , ` name ` varchar (64) NOT NULL DEFAULT '' COMMENT '名称' , `relation_id` varchar (64) NOT NULL DEFAULT '' COMMENT '关联id' , `type` int (11) NOT NULL DEFAULT '0' COMMENT '0:默认' , `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT ' 状态值' , `create_at` varchar (64) NOT NULL DEFAULT '' COMMENT '创建人' , `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' , `update_at` varchar (64) NOT NULL DEFAULT '' COMMENT '更新人' , `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新人' , PRIMARY KEY (`id`), UNIQUE KEY `ix_relation_id_type` (`relation_id`,`type`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
注意: ix_relation_id_type:唯一索引
Dao
1
2
3
4
|
@Mapper public interface FlowModelMapper { void insertOrUpdate(List<FlowModel> flowModel); } |
Mapper XML文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
< insert id = "insertOrUpdate" > insert into flow_model(name, relation_id, type, is_delete,create_at,update_at) values < foreach collection = "list" item = "p" index = "index" separator = "," > ( #{p.name}, #{p.relationId}, #{p.type}, #{p.isDelete}, #{p.createAt}, #{p.updateAt} ) </ foreach > on duplicate key update name=values(name), update_at=values(update_at) </ insert > |
说明:
- on duplicate key update这个是非常关键的地方,需要有唯一键和主键。
- on duplicate key update后面跟着的name=values(name)算是一个固定写法,作用:动态的传入要修改的值。
在MySQL 8.0.20之后,VALUES()在mysql未来的版本会被删除。
官方建议,使用列别名的方式来写:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
< insert id = "insertOrUpdate" > insert into flow_model(name, relation_id, type, is_delete,create_at,update_at) values < foreach collection = "list" item = "p" index = "index" separator = "," > ( #{p.name}, #{p.relationId}, #{p.type}, #{p.isDelete}, #{p.createAt}, #{p.updateAt} ) </ foreach > AS fm on duplicate key update name=fm.name, update_at=fm.update_at </ insert > |
行别名
insert into …values
语法:insert into ...values(...) AS 行别名 ON DUPLICATE KEY UPDATE 使用行别名。
例如:下面的 new就是行别名。
1
2
|
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new ON DUPLICATE KEY UPDATE c = new.a+new.b; |
列别名
或者是:insert into ...values(...) AS 行别名(列别名,列别名,列别名) ON DUPLICATE KEY UPDATE 使用别名
下面的m,n,p是随便取的列别名
1
2
|
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p) ON DUPLICATE KEY UPDATE c = m+n; |
注意:
当使用列别名时,必须在VALUES子句后面使用行别名,即使在后面的子句中不使用行别名。
除了insert into … values 场景,insert into …set场景也适用。
语法和上面是一样的:
1
2
3
4
|
INSERT INTO t1 SET a=1,b=2,c=3 AS new ON DUPLICATE KEY UPDATE c = new.a+new.b; INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p) ON DUPLICATE KEY UPDATE c = m+n; |
主键和唯一索引
现在假设我们有这些索引:
唯一索引:biz_id、name、code
主键:id
1
2
3
4
5
6
7
8
9
10
11
12
13
|
insert into template_url(id, name , code, url, scope, description, biz_id, create_by, create_user_id, update_by, update_user_id) values ( 1, 'yutao' , 'yutao' , 'www.baidu.com' , 'yutao' , 'yutao' ,0, 'yutao' ,0, 'yutao' ,0 ) ON DUPLICATE KEY UPDATE name = values ( name ), description= values (description), url= values (url), scope= values (scope), update_by= values (update_by), update_user_id= values (update_user_id) |
主键冲突
假设这时,主键冲突,那么MySQL就会接着判断是否 唯一索引冲突:
① 唯一索引不冲突,那么久执行更新
② 唯一索引冲突,就会报错:
1062 - Duplicate entry '0-yutao-yutao111' for key 'template_url.uk_biz_id_code_name', Time: 0.004000s
编辑时,唯一索引的字段不要修改
小结一下:insertOrUpdate的实现是基于mysql的on duplicate key update 来实现的。
使用ON DUPLICATE KEY UPDATE,如果行作为新行插入,则每行受影响的行值为1。如果更新现有行,则每行受影响的行值为2;如果将现有行设置为其当前值,则每行受影响的行值为0(可以通过配置,使其受影响的行值为1)。
官方地址:
13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE Statement
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/qq_36065688/article/details/105328511