一、mybatis+mysql数据库
单个新增或编辑:
<insert id="insert" parameterType="com.pagoda.batchprocessingdata.dto.user.User">
insert into admin.t_user (ID, NAME, AGE, PASSWORD)
values (#{id}, #{name}, #{age}, #{password})
ON DUPLICATE KEY UPDATE
NAME = values(NAME),
AGE = values(AGE),
PASSWORD = values(PASSWORD)
</insert>
连接mysql数据库执行测试结果:
批量新增或编辑:
<insert id="multi_insert" parameterType="com.pagoda.batchprocessingdata.dto.user.User">
insert into t_user ( ID, NAME, AGE, PASSWORD )
values
<foreach collection="list" item="item" index="index" separator="," >
(#{item.id}, #{item.name}, #{item.password})
</foreach>
ON DUPLICATE KEY UPDATE
NAME = VALUES( NAME ),
AGE = VALUES( AGE ),
PASSWORD = VALUES( PASSWORD )
</insert>
测试结果:
INSERT INTO t_user ( ID, NAME, AGE, PASSWORD )
VALUES
('1', '悟空','20', '2'),
('2', '八戒','20', 's'),
('3', '唐僧','20', 's'),
('4', '沙僧','20', '2'),
('5', '白龙马','20', '2')
ON DUPLICATE KEY UPDATE
NAME = VALUES( NAME ),
AGE = VALUES( AGE ),
PASSWORD = VALUES( PASSWORD )
二、mybatis+oracle数据库
单个新增或编辑:单个是我根据批量改的(可以用)
<!-- 插入数据 -->
<!-- 入参定义:实体类,会自动解析属性到对应的值-->
<insert id="insert" parameterType="com.pagoda.batchprocessingdata.dto.user.User">
merge into admin.t_user t
<!-- insert 和update中所有的数据都需要从using中获取 -->
using(
select #{id} as ID, #{name} as NAME, #{age} as AGE, #{password} as PASSWORD
from dual
) tmp
<!-- on后面的括弧不能省 -->
on ( tmp.ID = t.ID)
when matched THEN
update set
<!-- ORA-38104: 在on条件中的列是不可以更新的 t.ID = tmp.ID, -->
t.NAME = tmp.NAME,
t.AGE = tmp.AGE,
t.PASSWORD = tmp.PASSWORD
when not matched THEN
insert
<trim prefix="(" suffix=")" suffixOverrides=",">
ID, NAME, AGE, PASSWORD
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
tmp.ID, tmp.NAME, tmp.AGE, tmp.PASSWORD
</trim>
</insert>
批量新增或编辑:
<!-- 入参定义:使用List集合对象 -->
<insert id="multiInsert" parameterType="java.util.List">
merge into admin.t_user t
<!-- insert 和update中所有的数据都需要从using中获取 -->
using(
<!-- item:list中的具体对象 -->
<foreach collection="list" index="index" item="item" open="" close="" separator="union">
select
#{item.id,jdbcType=INTEGER} as ID,
#{item.name,jdbcType=VARCHAR} as NAME,
#{item.age,jdbcType=VARCHAR} as AGE,
#{item.password,jdbcType=INTEGER} as PASSWORD
from dual
</foreach>
) tmp
<!-- on后面的括弧不能省 -->
on ( tmp.ID = t.ID)
when matched THEN
update set
<!-- ORA-38104: 在on条件中的列是不可以更新的 t.ID = tmp.ID -->
t.NAME = tmp.NAME,t.AGE = tmp.AGE,t.PASSWORD = tmp.PASSWORD
when not matched THEN
insert
<trim prefix="(" suffix=")" suffixOverrides=",">
ID,NAME,AGE, PASSWORD
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
tmp.ID,tmp.NAME,tmp.AGE,tmp.PASSWORD
</trim>
</insert>