oracle批量插入数据语法:
INSERTINTO table_name
(column1, column2, column3)
(SELECT ?, ?, ?
FROM dual
UNIONALL
SELECT ?, ?, ?
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
)
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},
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
**************************************************************************************
<code>if</code><code>(size > </code><code>0</code><code>) {</code>
<code> </code><code>int</code> <code>batchSize = </code><code>100</code><code>;</code>
<code> </code><code>if</code><code>(size <= batchSize) {</code>
<code> </code><code>//不够100个一批</code>
<code> </code><code>saveResult = taobaoShopMapper.batchInsert(payTaobaoshopList);</code>
<code> </code><code>} </code><code>else</code> <code>{</code>
<code> </code><code>int</code> <code>count = size / batchSize;</code>
<code> </code><code>if</code><code>(size % batchSize != </code><code>0</code><code>) {</code>
<code> </code><code>count += </code><code>1</code><code>;</code>
<code> </code><code>}</code>
<code> </code>
<code> </code><code>List<PayTaobaoshop> tempList;</code>
<code> </code><code>int</code> <code>startIndex;</code>
<code> </code><code>int</code> <code>endIndex;</code>
<code> </code><code>for</code> <code>(</code><code>int</code> <code>i = </code><code>0</code><code>; i < count; i++) {</code>
<code> </code><code>startIndex = i * batchSize;</code>
<code> </code><code>endIndex = startIndex + batchSize;</code>
<code> </code><code>if</code><code>(endIndex > size) {</code>
<code> </code><code>endIndex = size;</code>
<code> </code><code>}</code>
<code> </code><code>log.info(</code><code>" =========== 插入批次:"</code> <code>+ (i+</code><code>1</code><code>) + </code><code>", startIndex:"</code> <code>+ startIndex + </code><code>", endIndex:"</code> <code>+ endIndex);</code>
<code> </code><code>tempList = payTaobaoshopList.subList(startIndex, endIndex);</code>
<code> </code><code>saveResult += taobaoShopMapper.batchInsert(tempList);</code>
<code> </code><code>}</code>
<code> </code><code>//2013-11-15-注释掉下面一行换成上面一段</code>
<code> </code><code>//saveResult = taobaoShopMapper.batchInsert(payTaobaoshopList);</code>
<code>}</code>
本文转自韩立伟 51CTO博客,原文链接:http://blog.51cto.com/hanchaohan/1322359,如需转载请自行联系原作者