在mybatis中，对于oracle的批量操作有点特殊，用到了就记一下。

批量插入

• 批量插入（带序列）

<insert id="batchInsert"  parameterType="java.util.List" useGeneratedKeys="false">
      INSERT TABLE(
      ID,
      FIELD1,
      FIELD2
      )
      SELECT ID.NEXTVAL,X.* FROM(
      <foreach collection="lsit" item="item" index="index" separator="union all">
        SELECT
        #{item.field1},
        #{item.field2}
        FROM DUAL
      </foreach>
      ) X
</insert>

• 批量插入（不带序列）

<insert id="batchInsert"  parameterType="java.util.List" useGeneratedKeys="false">
    INSERT ALL
    <foreach collection="list" item="item" index="index">
      INTO TABLE
      (ID,FIELD1,FIELD2)
      values(#{item.id},#{item.field1},#{item.field2})
    </foreach>
    SELECT 1 FROM DUAL
</insert>

注：必须加 useGeneratedKeys="false" ，不信可以试试。。。
大概什么原因偶也不清楚，就是批量插入的时候不能返回主键吧。

批量更新

  <update id="batchUpdate" >
    <foreach collection="list" item="item" index="index" separator=";"  open="begin" close=";end;">
      UPDATE TABLE
      <set >
        <if test="item.field1!= null" >
          ADDING_OPENNESS = #{item.field1},
        </if>
        <if test="item.field2!= null" >
          NORMALLY_OPEN = #{item.field2},
        </if>
      </set>
      where ID= #{item.id}
    </foreach>
  </update>

批量删除

<delete id="batchDelete" parameterType="java.util.List">
DELETE FROM TABLE where ID in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</delete>

批量查询

<select id="batchQuery" resultMap="BaseMap">
select ID,FIELD1,FIELD2
FROM TABLE
where ID in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item.id}
</foreach>
</select>