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

Bertha 。 2022-04-22 14:32 249阅读 0赞

博客分类： 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，刚开始的写法：

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

也可以编写函数来处理：

create or replace function fetchSeq(seqName in varchar) return number is 
      result number; 
      str_query_seq_sql varchar2(100) := ''; 
      begin 
         str_query_seq_sql := 'select ' ||  seqName || '.nextval from dual'; 
         dbms_output.put_line('str_query_seq_sql= ' || str_query_seq_sql); 
         execute immediate str_query_seq_sql into result; 
      return(result); 
    end fetchSeq;

<insert id="test" parameterType="list"> 
    
         insert into test(id,name) 
        <foreach collection="list" separator="union all" index="index" item="obj"> 
            select  fetchSeq('TEST_SEQ'),#{obj.name} from dual 
        </foreach> 
      </insert>