oracle批量插入数据语法:
INSERTINTO table_name
(column1, column2, column3)
(SELECT ?, ?, ?
FROM dual
UNIONALL
SELECT ?, ?, ?
FROM dual
UNIONALL
SELECT ?, ?, ? FROM dual)
添加两条数据到emp表
INSERTINTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)
(
SELECT'9527' ,'XINGXING','CLERK', 7902 ,TO_DATE('2012-03-14','yyyy-mm-dd'), 1000.00 , 20
FROM DUAL
UNION ALL
SELECT'8888' ,'Jason','CLERK', 7902 ,TO_DATE('2012-03-15','yyyy-mm-dd'), 1500.00 , 20
FROM DUAL
)
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"}
<insertid="insertData"parameterType="java.util.List">
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) (
<foreachcollection="list"item="item"index="index"separator="union all">
select
#{item.EMPNO,jdbcType=VARCHAR},
#{item.ENAME,jdbcType=VARCHAR},
#{item.JOB,jdbcType=VARCHAR},
#{item.MGR,jdbcType=NUMERIC},
#{item.MGR,jdbcType=NUMERIC},
TO_DATE(#{item.HIREDATE,jdbcType=VARCHAR},'yyyy-mm-dd')
from dual
</foreach>
)
</insert>
==================如果插入的表中有序列,按下面的代码写==================
<insert id="insertBatch" parameterType="ArrayList" useGeneratedKeys="true">
<selectKey keyProperty="deptno" order="BEFORE" resultType="int">
SELECT S_FORUM_USERID.NEXTVAL FROM DUAL
</selectKey>
INSERT INTO DEPT(DEPTNO , DNAME , LOC) SELECT S_FORUM_USERID.NEXTVAL , A.* FROM (
<foreach collection="list" item="item" index="index" separator="UNION">
SELECT #{item.dname} , #{item.loc} FROM DUAL
</foreach>
) A
</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); }