利用MyBatis動(dòng)態(tài)SQL的特性谣膳，我們可以做一些批量的操作竿报，本文將介紹MySQL、Oracle SQL方言的批量插入参歹、刪除寫法仰楚，更多詳細(xì)情況請查看MyBatis官方文檔。
-
批量插入
mysql：
<insert id="batchInsert" parameterType="java.util.List">
insert into user(username, password) values
<foreach collection="list" item="item" index="index"
separator=",">
(#{item.username},
#{item.password} )
</foreach>
</insert>
oracle：
<insert id="batchInsert" parameterType="java.util.List">
insert into user(username, password)
<foreach close=")" collection="list" item="item" index="index" open="(" separator="union">
select
#{item.username,jdbcType=VARCHAR},
#{item.password,jdbcType=VARCHAR}
from dual
</foreach>
</insert>
oracle使用 sequence主鍵策略
<insert id="batchInsert" parameterType="java.util.List">
insert into user(id, username, password)
select SEQ_USER_ID.NEXTVAL,T.* from(
<foreach collection="list" item="item" index="index"
separator="UNION">
SELECT
#{item.username,jdbcType=VARCHAR},
#{item.password,jdbcType=VARCHAR}
from dual
</foreach>
) T
</insert>
-
批量刪除
<delete id="batchDeleteByIdList" parameterType="java.util.List">
delete from user
where id in
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
