oracle批量插入数据语法：

Sql代码

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

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

  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>

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

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

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

  9.  </foreach>

  10.  ) A

  11. </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
     
       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);}