基本環(huán)境

語(yǔ)言：Java 8 數(shù)據(jù)庫(kù)：Oracle ORM 框架：MyBatis 3.4.5

需求

批量插入數(shù)據(jù)，數(shù)據(jù)需要有自增 id。每次插入有一個(gè)唯一的 sessionId 來(lái)標(biāo)記這些記錄，插入完成之后返回這個(gè) sessionId。

方案

循環(huán)插入單條記錄，偽代碼：

int sessionId = dao.querySessionId();
for (Record record : recordList) {
    dao.insertRecord(record, sessionId);
}
return sessionId;

上述解決方案很簡(jiǎn)明易懂，但是循環(huán)中每次 insert 操作都會(huì)與 DB 交互一次，當(dāng)數(shù)據(jù)量很大時(shí)，會(huì)花費(fèi)很多時(shí)間在網(wǎng)絡(luò)傳輸上，導(dǎo)致性能問(wèn)題。

改進(jìn)

問(wèn)題可以通過(guò)批量插入來(lái)改善。

帶自增 id 的批量插入

Oracle 中比較常見(jiàn)的批量插入模版是：

INSERT ALL
   INTO target_table (col1, col2, col3) VALUES ('id1_1', 'val1_2', 'val1_3')
   INTO target_table (col1, col2, col3) VALUES ('id2_1', 'val2_2', 'val2_3')
   ...
Subquery;

但是每條記錄都需要有一個(gè)自增 id，Oracle 中一般用 Sequence 來(lái)實(shí)現(xiàn)，于是比較容易想到的是用下面這種方式：

INSERT ALL
   INTO table (col1, col2, col3) VALUES (SEQUENCE.NEXTVAL, 'val1_2', 'val1_3')
   INTO table (col1, col2, col3) VALUES (SEQUENCE.NEXTVAL, 'val2_2', 'val2_3')
   ...
SELECT 1 FROM DUAL;

不過(guò)遺憾的是，上述方案行不通，因?yàn)樗械?SEQUENCE.NEXTVAL 都會(huì)是同一個(gè)值，會(huì)產(chǎn)生主鍵沖突。

接著嘗試其他的方案。Oracle 支持從一個(gè)表里批量選取數(shù)據(jù)插入另一個(gè)表中：

INSERT INTO target_table (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;

用上述方式，我們可以把被插入的數(shù)據(jù)用 UNION ALL 構(gòu)造一個(gè)子表，也就是上面的 source_table 來(lái)實(shí)現(xiàn)批量插入。跟 INSERT ALL INTO 相比的好處是，可以使用 Sequence 的自增值：

INSERT INTO target_table (col1, col2, col3)
SELECT SEQUENCE.NEXTVAL,
       col2,
       col3
FROM (
    SELECT 'val1_2' col2, 'val1_3' col3 FROM dual UNION ALL
    SELECT 'val2_2' col2, 'val2_3' col3 FROM dual UNION ALL
    ...
);

用 MyBatis 的 dynamic sql 來(lái)實(shí)現(xiàn)大致如下：

<insert id="sampleInsert" parameterType="java.util.List">
    INSERT INTO target_table (col1, col2, col3)
    SELECT SEQUENCE.NEXTVAL,, col2, col3 FROM
    <foreach collection="list" item="item" index="index" open="(" close=")" separator=" UNION ALL ">
        SELECT #{item.val2} col2, #{item.val2} col3 FROM dual
    </foreach>
</insert>

插入完成之后返回 sessionId

在 Mybatis 中，返回某個(gè) property 可以用 SelectKey。SelectKey 是 Insert 的子標(biāo)簽，實(shí)現(xiàn)原理是在執(zhí)行插入語(yǔ)句之前先做一次 SelectKey 的子查詢，此處，可以將子查詢的結(jié)果賦值到查詢的參數(shù)當(dāng)中，例如

public class Foo {
    private int id;
    private String col2;
    private String col3;
}
public interface FooDao {
    void sampleInsert(Foo foo);
}

<insert id="sampleInsert" useGeneratedKeys="true" parameterType="Foo" keyProperty="id">
    <selectKey keyProperty="id" order="BEFORE" resultType="int">
        SELECT SESSION_SEQUENCE.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO target_table (col1, col2, col3)
    VALUES (#{id}, #{col2}, #{col3})
</insert>

當(dāng)插入結(jié)束之后，參數(shù) foo.id 就會(huì)是 SESSION_SEQUENCE 的自增值（注意在 Dao 中不能用 @Param() 標(biāo)注參數(shù)）。

然而這種方式只支持單條記錄的插入，Oracle 中批量插入的情況就無(wú)法完成賦值了。所以此處只能分成兩步來(lái)做：

• 獲取 sessionId（SESSION_SEQUENCE.NEXTVAL）
• 批量插入帶 sessionId 的記錄

或者

• 用 SelectKey 選出 sessionId（SESSION_SEQUENCE.NEXTVAL）并批量插入記錄
• 獲取 sessionId（SESSION_SEQUENCE.CURRVAL）

注意上述第二種方式，需要保證兩個(gè)方法在同一個(gè) Transaction 里面，否則 Sequence 的值會(huì)不一致。

注意事項(xiàng)

oracle中用insert into xxx values (xxx,xxx),(xxx,xxx) 這種語(yǔ)法應(yīng)該通不過(guò)的，會(huì)報(bào)錯(cuò),ORA-00933: SQL 命令未正確結(jié)束，要用這種

insert into  t_test (id, prop_name)   
select 1,'aaa' from dual union all    
select 2,'bbb' from dual union all  
select 3,'ccc' from dual;

總結(jié)

本文簡(jiǎn)單總結(jié)了在 MyBatis 中往 Oracle 批量插入數(shù)據(jù)的方法，作一個(gè)快速筆記。

到此這篇關(guān)于Mybatis批量插入Oracle數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Mybatis批量插入Oracle內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

Reference

• The Ultimate Guide to Oracle INSERT ALL Statement
• Oracle INSERT INTO SELECT
• Inserting Multiple Rows Using a Single Statement
• Inserting multiple rows with sequence in Oracle

最新評(píng)論