背景:拉取别人的数据,来更新到自己的库中。我原本是写的先查出库中所有数据,然后遍历拿到要插入的list集合,判断是否存在数据库中,不存在则放到一个新的insertList集合中,存在则放到updateList集合中,然后在分别执行插入和更新语句,但是考虑到库里的数据往后可能会很多,就放弃了这个思路。
····我在查阅资料后得知,oracle可以用 merge into(有则更新,无则插入)搞定这样的需求,这里以student表为例,mapper.xml中如下
<update id="updateBatch" parameterType="java.util.List">
MERGE INTO student t2
USING(
<foreach collection="list" item="stu" index="index" separator="union all">
SELECT
#{stu.id} id,
#{stu.name} name,
#{stu.age} age
from DUAL
</foreach>
)t1
ON (t2.id = t1.id)
WHEN MATCHED THEN
UPDATE SET
t2.NAME=t1.name, t2.AGE=t1.age where t2.age<12
WHEN NOT MATCHED THEN
INSERT (t2.id,t2.name,t2.age) VALUES (t1.id,t1.name,t1.age)
</update>
24-01-28修改 WHEN MATCHED and t2.age < 12 THEN。这里是判断原始数据,若存在该数据则更新,但又多个一个条件,必须是age<12,否则就跳过本次更新;此条件类似于在数据库中添加一个禁止更新标记,可防止数据修改
24-03修改,重装数据库后,加上这个条件会报错(缺失关键字),然后将语句再次修改【WHEN MATCHED THEN UPDATE SET t2.NAME=t1.name, t2.AGE=t1.age where t2.age<12】。我已经不确定之前那个写法对不对了
对应到的mapper文件
int updateBatch(List<Student> record);
转为可执行sql
MERGE INTO student t2
USING(
SELECT '1' id, '张三' name, '10' age from DUAL
union all
SELECT '2' id, '李四' name, '11' age from DUAL
)t1
ON (t2.id = t1.id)
WHEN MATCHED THEN
UPDATE SET
t2.NAME=t1.name, t2.AGE=t1.age
WHEN NOT MATCHED THEN
INSERT (t2.id,t2.name,t2.age) VALUES (t1.id,t1.name,t1.age)
直接调用,sql会判断该条数据是插入还是更新
⚠️ ON (t2.id = t1.id)是两个表的关联关系,所以在update set后面,id是不能有的,否则会报错。大数据的时候可以优先考虑merge into方式,缺点就是当字段很多时,需要在foreach、update和insert中分别写上对应的字段,会很长。
此方法对应mysql的是 (ON DUPLICATE KEY UPDATE)
<insert id="updateByid">
insert into student
(id,name,age)
values
<foreach collection="list" separator="," index="index" item="item">
(#{item.id},#{item.name},#{item.age})
</foreach>
ON duplicate KEY UPDATE
name=values(name),rate=values(age)
</insert>
转为可执行的sql(因为我本地是oracle,所以并没有测试)
INSERT INTO student (id,name,age)
VALUES
('1','章三','10') ,
('2','离四','12')
ON DUPLICATE KEY UPDATE
name = '章三',age='11';