Mybatis对Oracle的批量操作 增加，更新，删除

bgvrucswsp

 

oracle批量插入：

<insert id="insertBatch" parameterType="java.util.List">
insert into table_name(id, name,updateTime)
<foreach collection="list" item="item" index="index" separator="union all" >
(select  
#{item.id,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, #{item.updateTime,jdbcType=TIMESTAMP}
from dual)
</foreach>
</insert>

 需要注意的是sql中没有values；<foreach>标签中语句 (select ..... from dual)

　　

 

 

oracle批量更新：

<update id="updateBatch" parameterType="java.util.List">
begin  
<foreach collection="list" item="item" index="index" separator=";" >
update table_name
<set>
name= #{item.name}
</set>
where id = #{item.id}
</foreach>
;end;
</update>

或者 

 

<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">
update table_name
<set>
name= #{item.name}
</set>
where id = #{item.id}
</foreach>
</update>

 

 　　oracle批量删除：

<delete id="deleteBatch" parameterType="java.util.List">
delete from table_name
<where>
<foreach collection="list" index="index" item="item" open="(" separator="or" close=")">     
id=#{item.id}
</foreach>
</where>
</delete>