mybatis使用foreach批次插入，解决sequence只查询一次的问题

oracle的批量插入方式是：
insert  into db(id, zgbh, shbzh)
        select '1', '2', '3' from dual
        union all select '2', '3', '4' from dual
        union all select '3', '4', '5' from dual
        union all select '4', '5', '6' from dual
        union all select '5', '6', '7' from dual

由于项目使用到sequence生成id，刚开始的写法：
<!-- 批次插入，List-->
     <insert id="insertList" useGeneratedKeys="true" parameterType="java.util.List">
        <selectKey resultType="long" keyProperty="id" order="BEFORE">
            SELECT SEQ_xxx_DETAIL.NEXTVAL FROM DUAL
        </selectKey>
         insert into TBL_xxx_DETAIL
              (
                 <include refid="allColumns"/>
              )
              <foreach collection="list" item="item" index="index"  separator="UNION ALL" >
                  SELECT
                 #{id, jdbcType=NUMERIC javaType=long},
                 #{item.batchFundTitleId, jdbcType=NUMERIC javaType=long},
                 #{item.transactionRequestId, jdbcType=NUMERIC javaType=long},
                 #{item.arAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.bankServiceAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.receivedAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.realReceivedAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.verifyDate, jdbcType=DATE javaType=date},
                 #{item.verifyOp, jdbcType=VARCHAR javaType=string},
                 #{item.verifyStatus, jdbcType=INTEGER javaType=int},
                 #{item.created, jdbcType=VARCHAR javaType=string},
                 #{item.createdDate, jdbcType=DATE javaType=date},
                 #{item.createdIp, jdbcType=VARCHAR javaType=string},
                 #{item.modified, jdbcType=VARCHAR javaType=string},
                 #{item.modifiedDate, jdbcType=DATE javaType=date},
                 #{item.modifiedIp, jdbcType=VARCHAR javaType=string}
                  from dual
             </foreach>
     </insert>
这样的写法sequence的查询方法只查询一次，造成list中的对象的再插入时id都会一样，违反主键的唯一性约束。

所以修改为如下的形式：
<insert id="insertList" useGeneratedKeys="true" parameterType="java.util.List">
        <selectKey resultType="long" keyProperty="id" order="BEFORE">
            SELECT SEQ_xxx_DETAIL.NEXTVAL FROM DUAL
        </selectKey>
         insert into TBL_xxx_DETAIL
              (
                 <include refid="allColumns"/>
              ) select  SEQ_xxx_DETAIL.NEXTVAL,A.*  from(
              <foreach collection="list" item="item" index="index"  separator="UNION ALL" >
                  SELECT
                 #{item.batchFundTitleId, jdbcType=NUMERIC javaType=long},
                 #{item.transactionRequestId, jdbcType=NUMERIC javaType=long},
                 #{item.arAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.bankServiceAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.receivedAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.realReceivedAmt, jdbcType=DECIMAL javaType=java.math.BigDecimal},
                 #{item.verifyDate, jdbcType=DATE javaType=date},
                 #{item.verifyOp, jdbcType=VARCHAR javaType=string},
                 #{item.verifyStatus, jdbcType=INTEGER javaType=int},
                 #{item.created, jdbcType=VARCHAR javaType=string},
                 #{item.createdDate, jdbcType=DATE javaType=date},
                 #{item.createdIp, jdbcType=VARCHAR javaType=string},
                 #{item.modified, jdbcType=VARCHAR javaType=string},
                 #{item.modifiedDate, jdbcType=DATE javaType=date},
                 #{item.modifiedIp, jdbcType=VARCHAR javaType=string}
                  from dual
             </foreach>) A
     </insert>
把foreach中的id去掉，foreach拼出来的数据作为一张表A，然后从表A中查询数据，再接上从sequence中读取的值作为id。这样sequence的值就会多次读取，id就会不一样。
<selectKey resultType="long" keyProperty="id" order="BEFORE">
            SELECT SEQ_xxx_DETAIL.NEXTVAL FROM DUAL
        </selectKey>删除后会报错：SQL command not properly ended，暂时没有查出来原因，有知道的原因的请告诉我哈。
以此文抛砖引玉吧，希望能有更合适的方式。

mysql的批量插入如下：
INSERT INTO MyTable(ID,NAME) VALUES(7,'003'),(8,'004'),(9,'005')
而且mysql有自增字段，可以把id设置为自增的，这样的话就不存在id一致的情况。

<insert id="insertBatch" > 
    insert into student ( NAME,SEX,ADDRESS,TELEPHONE,TID)  
    values  
    <foreach collection="list" item="item" index="index" open="(" separator=","  close=")"> 
         #{item.name},
         #{item.sex},
         #{item.address},
         #{item.telephone},
         #{item.tId} 
    </foreach> 
</insert> 

评论

6 楼 baozhutang 2015-01-13  

我们这边的解决方法是，用一个trigger生成id，不通过sql来。

5 楼 csakurai 2014-11-11  

非常感谢，搞定了！之前找了很多个都不行。  

4 楼 关刀十甫村 2014-05-19  

<selectKey resultType="long" keyProperty="id" order="BEFORE">
            SELECT SEQ_xxx_DETAIL.NEXTVAL FROM DUAL
        </selectKey>删除后会报错：SQL command not properly ended

这个错误 可以把useGeneratedKeys="true"删掉后 再删掉selectKey标签
就不会报：SQL command not properly ended的错误

3 楼 mylaughing 2014-01-26  

<include refid="allColumns"/> 这个 "allColumns"的内容是什么呀

2 楼 biristone 2012-11-16  

改了改，能搞定，LZ厉害。

1 楼 biristone 2012-11-16  

<select id="insertBatch" parameterType="java.util.List">
insert into TD_S_PROTOCOL_NODE (node_id,node_code,defaultvalue,nodetype,regex,remark,from_system)(
<foreach collection="list" item="obj" index="index" separator="union ">
select #{obj.nodeId,jdbcType=DECIMAL},#{obj.nodeCode,jdbcType=VARCHAR},#{obj.defaultvalue,jdbcType=VARCHAR},#{obj.nodetype,jdbcType=VARCHAR},#{obj.regex,jdbcType=VARCHAR},#{obj.remark,jdbcType=VARCHAR},#{obj.fromSystem,jdbcType=VARCHAR} from dual
</foreach>)
</select>

我写的不行啊，报ora-00933