oracle批量插入数据语法:


Sql代码 mybatis-oracle批量插入数据的简单学习_批量插入

  1. INSERTINTO table_name

  2. (column1, column2, column3)

  3. (SELECT ?, ?, ?

  4. FROM dual

  5. UNIONALL

  6. SELECT ?, ?, ?

  7. FROM dual

  8. UNIONALL

  9. SELECT ?, ?, ? FROM dual)




添加两条数据到emp表



Sql代码 mybatis-oracle批量插入数据的简单学习_批量插入

  1. INSERTINTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)

  2. (

  3. SELECT'9527' ,'XINGXING','CLERK', 7902 ,TO_DATE('2012-03-14','yyyy-mm-dd'), 1000.00 , 20

  4. FROM DUAL

  5. UNION ALL

  6. SELECT'8888' ,'Jason','CLERK', 7902 ,TO_DATE('2012-03-15','yyyy-mm-dd'), 1500.00 , 20

  7. FROM DUAL

  8. )

mybatis 中的emp.xml配置如下

需求:批量向雇员表插入数据,参数为List,List中存放的是Map,Map中则以Key-Value形式存放数据信息

 如:Map {EMPNO="9527",ENAME="XINGXING",JOB="CLERK",MGR="7902",HIREDATE="2012-03-14",SAL="1000.00",DEPTNO="20"}


Xml代码 mybatis-oracle批量插入数据的简单学习_批量插入

  1. <insertid="insertData"parameterType="java.util.List">

  2.  INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) (

  3. <foreachcollection="list"item="item"index="index"separator="union all">

  4.   select

  5.    #{item.EMPNO,jdbcType=VARCHAR},

  6.    #{item.ENAME,jdbcType=VARCHAR},

  7.    #{item.JOB,jdbcType=VARCHAR},

  8.    #{item.MGR,jdbcType=NUMERIC},

  9.    #{item.MGR,jdbcType=NUMERIC},

  10.    TO_DATE(#{item.HIREDATE,jdbcType=VARCHAR},'yyyy-mm-dd')

  11.     from dual

  12. </foreach>

  13.  )

  14. </insert>



==================如果插入的表中有序列,按下面的代码写==================


  1. <insert id="insertBatch" parameterType="ArrayList" useGeneratedKeys="true">

  2.  <selectKey keyProperty="deptno" order="BEFORE" resultType="int">

  3.   SELECT S_FORUM_USERID.NEXTVAL FROM DUAL

  4.  </selectKey>

  5.  INSERT INTO DEPT(DEPTNO , DNAME , LOC) SELECT S_FORUM_USERID.NEXTVAL , A.* FROM (

  6.  <foreach collection="list" item="item" index="index" separator="UNION">

  7.   SELECT #{item.dname} , #{item.loc} FROM DUAL

  8.  </foreach>

  9.  ) A

  10. </insert>



**************************************************************************************

if(size > 0) {
    int batchSize = 100;
    if(size <= batchSize) {
        //不够100个一批
        saveResult = taobaoShopMapper.batchInsert(payTaobaoshopList);
    } else {
        int count = size / batchSize;
        if(size % batchSize != 0) {
            count += 1;
        }
           
        List<PayTaobaoshop> tempList;
        int startIndex;
        int endIndex;
           
        for (int i = 0; i < count; i++) {
            startIndex = i * batchSize;
            endIndex = startIndex + batchSize;
            if(endIndex > size) {
                endIndex = size;
            }
            log.info(" =========== 插入批次:" + (i+1) + ", startIndex:" + startIndex + ", endIndex:" + endIndex);
            tempList = payTaobaoshopList.subList(startIndex, endIndex);
            saveResult += taobaoShopMapper.batchInsert(tempList);
        }
    }
    //2013-11-15-注释掉下面一行换成上面一段
    //saveResult = taobaoShopMapper.batchInsert(payTaobaoshopList);
}