Oracle并不支持values(),()的形式批量操作,用begin ;;end;(相当于一次执行多条语句)的方式去批量插入,使用MERGE如果主键冲突则执行update语句
<insert id="save" parameterType="List" >
begin
<foreach collection="list" item="item" index="index" separator=";">
MERGE INTO Users_sys us1
USING (SELECT '${item.userid}' userid ,'${item.username}' username FROM dual) us2
ON ( us1.userid=us2.userid)
WHEN MATCHED THEN
UPDATE SET us1.username = us2.username
WHEN NOT MATCHED THEN
INSERT (us1.userid,us1.username) VALUES(us2.userid,us2.username)
</foreach>
;end;
</insert>
MySQL不支持 MERGE INTO 语法,使用 on duplicate key update 语句,当违反唯一性约束则是执行更新语句,(主键约束也是唯一性约束),执行速度较慢,不推荐使用
<insert id="batchInsert" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" separator=";">
insert into tb_user(user_id,user_name,user_pwd)
values (#{item.userId,jdbcType=BIGINT},#{item.userName,jdbcType=VARCHAR},#{item.userPwd,jdbcType=VARCHAR})
on duplicate key update
user_name= values(user_name),
user_pwd= values(user_pwd)
</foreach>
;
</insert>
--个人成长笔记