在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>
