批量插入MySQL和Oracle的sql会有些差别,要注意!
这里是mysql插入:
<insert id="batchInsert" parameterType="java.util.List">
insert into user(name, sex) values
<foreach collection="list" item="item" index="index"
separator=",">
(#{item.name},
#{item.sex} )
</foreach>
</insert>
oracle:
<insert id="batchInsert" parameterType="java.util.List">
insert into user(name, sex)
<foreach close=")" collection="list" item="item" index="index" open="(" separator="union">
select
#{item.name,jdbcType=VARCHAR},
#{item.sex,jdbcType=VARCHAR}
from dual
</foreach>
</insert>
下面的是Oracle使用 Sequence来生成主键的批量插入写法:
<insert id="batchInsert" parameterType="java.util.List">
insert into user(id, name, sex)
select SEQ_USER_ID.NEXTVAL,T.* from(
<foreach collection="list" item="item" index="index"
separator="UNION">
SELECT
#{item.name,jdbcType=VARCHAR},
#{item.sex,jdbcType=VARCHAR}
from dual
</foreach>
) T
</insert>
批量删除MySql和Oracle数据库数据:
<delete id="batchDeleteByIdList" parameterType="java.util.List">
delete from user
where id in
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</delete>