MyBatis中批量插入、删除写法(MySql、Oracle)

利用MyBatis动态SQL的特性，我们可以做一些批量的操作，本文将介绍MySQL、Oracle SQL方言的批量插入、删除写法，更多详细情况请查看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>