数据库批量 之 Oracle

mybatis批量插入oracle时需要显式指定为 useGeneratedKeys="false" 不然报错~~~

 

正确的mybatis的mapper的sql配置如下：

 1 <insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
 2         INSERT ALL
 3         <foreach item="item" index="index" collection="list">
 4         INTO T_APPLAUD
 5         (
 6             ID,
 7             USER_ID,
 8             BUSINESS_TYPE,
 9             PRODUCT_ID,
10             CREATE_TIME
11         ) VALUES
12         (
13             #{item.id, jdbcType=NUMERIC},
14             #{item.userId, jdbcType=VARCHAR},
15             #{item.businessType, jdbcType=VARCHAR},
16             #{item.productId, jdbcType=VARCHAR},
17             #{item.createdTime, jdbcType=NUMERIC} 
18         )
19         </foreach>
20         SELECT 1 FROM DUAL
21     </insert>

 

另外一种方法是 insert into table(...) (select ... from dual) union all (select ... from dual)

 1     <insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
 2         INSERT INTO T_APPLAUD
 3         (
 4             ID,
 5             USER_ID,
 6             BUSINESS_TYPE,
 7             PRODUCT_ID,
 8             CREATE_TIME
 9         )
10         <foreach item="item" index="index" collection="list" separator="union all">
11         (
12             SELECT 
13                 #{item.id},
14                 #{item.userId},
15                 #{item.businessType},
16                 #{item.productId},
17                 #{item.createdTime} 
18             FROM DUAL
19         )
20         </foreach>
21     </insert>