racle批量插入:
- <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>