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


博客分类： mybatis

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},