搜尋
首頁Javajava教程Java如何實作JDBC批次插入

Java如何實作JDBC批次插入

May 18, 2023 am 10:02 AM
javajdbc

    一、說明

    在JDBC中,executeBatch這個方法可以將多條dml語句批次執行,效率比單一執行executeUpdate高很多,這是什麼原理呢?在mysql和oracle中又是如何實現批次執行的呢?本文將為大家介紹背後的原理。

    二、實驗介紹

    本實驗將透過以下三步驟進行

    a. 記錄jdbc在mysql中批次執行和單一執行的耗時

    b. 記錄jdbc在oracle中批次執行和單一執行的耗時

    c. 記錄oracle plsql批次執行和單一執行的耗時

    相關java和資料庫版本如下:Java17, Mysql8,Oracle11G

    三、正式實驗

    在mysql和oracle中分別建立一張表格

    create table t (  -- mysql中创建表的语句
        id    int,
        name1 varchar(100),
        name2 varchar(100),
        name3 varchar(100),
        name4 varchar(100)
    );
    create table t (  -- oracle中创建表的语句
        id    number,
        name1 varchar2(100),
        name2 varchar2(100),
        name3 varchar2(100),
        name4 varchar2(100)
    );

    在實驗前需要開啟資料庫的稽核

    mysql開啟審計:

    set global general_log = 1;

    oracle開啟審計:

    alter system set audit_trail=db, extended;  
    audit insert table by scott;  -- 实验采用scott用户批量执行insert的方式

    java程式碼如下:

    import java.sql.*;
    
    public class JdbcBatchTest {
    
        /**
         * @param dbType 数据库类型,oracle或mysql
         * @param totalCnt 插入的总行数
         * @param batchCnt 每批次插入的行数,0表示单条插入
         */
        public static void exec(String dbType, int totalCnt, int batchCnt) throws SQLException, ClassNotFoundException {
            String user = "scott";
            String password = "xxxx";
            String driver;
            String url;
            if (dbType.equals("mysql")) {
                driver = "com.mysql.cj.jdbc.Driver";
                url = "jdbc:mysql://ip/hello?useServerPrepStmts=true&rewriteBatchedStatements=true";
            } else {
                driver = "oracle.jdbc.OracleDriver";
                url = "jdbc:oracle:thin:@ip:orcl";
            }
    
            long l1 = System.currentTimeMillis();
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url, user, password);
            connection.setAutoCommit(false);
            String sql = "insert into t values (?, ?, ?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= totalCnt; i++) {
                preparedStatement.setInt(1, i);
                preparedStatement.setString(2, "red" + i);
                preparedStatement.setString(3, "yel" + i);
                preparedStatement.setString(4, "bal" + i);
                preparedStatement.setString(5, "pin" + i);
    
                if (batchCnt > 0) {
                    // 批量执行
                    preparedStatement.addBatch();
                    if (i % batchCnt == 0) {
                        preparedStatement.executeBatch();
                    } else if (i == totalCnt) {
                        preparedStatement.executeBatch();
                    }
                } else {
                    // 单条执行
                    preparedStatement.executeUpdate();
                }
            }
            connection.commit();
            connection.close();
            long l2 = System.currentTimeMillis();
            System.out.println("总条数:" + totalCnt + (batchCnt>0? (",每批插入:"+batchCnt) : ",单条插入") + ",一共耗时:"+ (l2-l1) + " 毫秒");
        }
    
        public static void main(String[] args) throws SQLException, ClassNotFoundException {
            exec("mysql", 10000, 50);
        }
    }

    程式碼中幾個注意的點,

    • #mysql的url需要加入useServerPrepStmts=true&rewriteBatchedStatements=true參數。

    • batchCnt表示每次批次執行的sql條數,0表示單一執行。

    先測試mysql

    exec("mysql", 10000, batchCnt);

    代入不同的batchCnt值看執行時長

    batchCnt=50 總條數:10000,每批插入:50,共耗時:4369 毫秒
    batchCnt=100 總條數:10000,每批插入:100,共耗時:2598 毫秒
    batchCnt=200 總條數:10000,每批插入: 200,共耗時:2211 毫秒
    batchCnt=1000 總條數:10000,每批插入:1000,共耗時:2099 毫秒
    batchCnt=10000 總條數:10000,每批插入:10000共耗時:2418 毫秒
    batchCnt=0 總條數:10000,單一插入,共耗時:59620 毫秒

    ##查看general log

    batchCnt=5

    batchCnt=0

    可以得到幾個結論:

    • #批次執行的效率比起單條執行大幅提升。

    • mysql的批次執行其實是改寫了sql,將多條insert合併成了insert xx values(),()...的方式去執行。

    • 將batchCnt由50改到100的時候,時間基本上縮短了一半,但是再擴大這個值的時候,時間縮短並不明顯,執行的時間甚至還會升高。

    分析原因:

    客戶端將要執行的SQL語句傳送給資料庫伺服器後,資料庫執行該SQL語句並將結果傳回給客戶端。總耗時 = 資料庫執行時間 網路傳輸時間。透過大量執行減少往返次數可以降低網路傳輸時間,從而縮短總時間。然而,當batchCnt變大時,即使網路傳輸時間不再是最主要的瓶頸,總時間的降低也不會那麼明顯。特別是當batchCnt=10000,即一次性把1萬條語句全部執行完,時間反而變多了,這可能是由於程式和資料庫在準備這些入參時需要申請更大的內存,所以耗時更多(我猜的)。

    再來說一句,batchCnt這個值是不是能無限大呢,假設我需要插入的是1億條,那麼我能一次批量插入1億條嗎?當然不行,我們不考慮undo的空間問題,首先你電腦就沒有這麼大的內存一次性把這1億條sql的入參全部保存下來,其次mysql還有個參數max_allowed_pa​​cket限制單條語句的長度,最大為1G位元組。當語句過長的時候就會報"Packet for query is too large (1,773,901 > 1,599,488). You can change this value on the server by setting the 'max_allowed_pa​​cket' variable"。

    接下來測試oracle

    exec("oracle", 10000, batchCnt);

    代入不同的batchCnt值看執行時長

    batchCnt=50 總條數:10000,每批插入:50,一共耗時:2055 毫秒

    batchCnt=100 總條數:10000,每批插入:100,共耗時:1324 毫秒
    batchCnt=200 總條數:10000,每批插入:200,總共耗時:856 毫秒
    batchCnt=1000 總條數:10000,每批插入:1000,總共耗時:785 毫秒
    batchCnt=10000 總條數:10000,每批插入:10000,總共耗時:804毫秒
    batchCnt=0 總條數:10000,單一插入,總共耗時:60830 毫秒

    在Oracle中執行的效果跟MySQL中基本上一致,批次操作的效率明顯高於單條執行。問題就來了,oracle中並沒有這種insert xx values(),()..語法呀,那它是怎麼做到批量執行的呢?

    查看當執行batchCnt=50的稽核檢視dba_audit_trail

    从审计的结果中可以看到,batchCnt=50的时候,审计记录只有200条(扣除登入和登出),也就是sql只执行了200次。sql_text没有发生改写,仍然是"insert into t values (:1 , :2 , :3 , :4 , :5 )",而且sql_bind只记录了批量执行的最后一个参数,即50的倍数。根据awr报告可以看出,实际只执行了200次(由于篇幅限制,省略了awr截图)。那么oracle是怎么做到只执行200次但插入1万条记录的呢?我们来看看oracle中使用存储过程的批量插入。

    四、存储过程

    准备数据:

    首先将t表清空 truncate table t;

    用java往t表灌10万数据 exec("oracle", 100000, 1000);

    创建t1表 create table t1 as select * from t where 1 = 0;

    以下两个过程的意图一致,均为将t表中的数据导入t1表。nobatch是单次执行,usebatch是批量执行。

    create or replace procedure nobatch is
    begin
      for x in (select * from t)
      loop
        insert into t1 (id, name1, name2, name3, name4)
        values (x.id, x.name1, x.name2, x.name3, x.name4);
      end loop;
      commit;
    end nobatch;
    /
    create or replace procedure usebatch (p_array_size in pls_integer)
    is
      type array is table of t%rowtype;
      l_data array;
      cursor c is select * from t;
    begin
      open c;
      loop
        fetch c bulk collect into l_data limit p_array_size;
        forall i in 1..l_data.count insert into t1 values l_data(i);
        exit when c%notfound;
      end loop;
      commit;
      close c;
    end usebatch;
    /

    执行上述存储过程

    SQL> exec nobatch;  
    Elapsed: 00:00:32.92

    SQL> exec usebatch(50);
    Elapsed: 00:00:00.77

    SQL> exec usebatch(100);
    Elapsed: 00:00:00.47

    SQL> exec usebatch(1000);
    Elapsed: 00:00:00.19

    SQL> exec usebatch(100000);
    Elapsed: 00:00:00.26

    存储过程批量执行效率也远远高于单条执行。查看usebatch(50)执行时的审计日志,sql_bind也只记录了批量执行的最后一个参数,即50的倍数。与使用executeBatch方法在记录内容方面相同。因此可以推断,JDBC的executeBatch和存储过程的批量执行都采用了相同的方法

    存储过程的这个关键点就是forall。查阅相关文档。

    The FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE clauses.
    The different values come from existing, populated collections or host arrays. The FORALL statement is usually much faster than an equivalent FOR LOOP statement.
    The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time.

    翻译过来就是forall很快,原因就是不需要每次执行的时候等待参数。

    以上是Java如何實作JDBC批次插入的詳細內容。更多資訊請關注PHP中文網其他相關文章!

    陳述
    本文轉載於:亿速云。如有侵權,請聯絡admin@php.cn刪除
    是否有任何威脅或增強Java平台獨立性的新興技術?是否有任何威脅或增強Java平台獨立性的新興技術?Apr 24, 2025 am 12:11 AM

    新興技術對Java的平台獨立性既有威脅也有增強。 1)雲計算和容器化技術如Docker增強了Java的平台獨立性,但需要優化以適應不同雲環境。 2)WebAssembly通過GraalVM編譯Java代碼,擴展了其平台獨立性,但需與其他語言競爭性能。

    JVM的實現是什麼,它們都提供了相同的平台獨立性?JVM的實現是什麼,它們都提供了相同的平台獨立性?Apr 24, 2025 am 12:10 AM

    不同JVM實現都能提供平台獨立性,但表現略有不同。 1.OracleHotSpot和OpenJDKJVM在平台獨立性上表現相似,但OpenJDK可能需額外配置。 2.IBMJ9JVM在特定操作系統上表現優化。 3.GraalVM支持多語言,需額外配置。 4.AzulZingJVM需特定平台調整。

    平台獨立性如何降低發展成本和時間?平台獨立性如何降低發展成本和時間?Apr 24, 2025 am 12:08 AM

    平台獨立性通過在多種操作系統上運行同一套代碼,降低開發成本和縮短開發時間。具體表現為:1.減少開發時間,只需維護一套代碼;2.降低維護成本,統一測試流程;3.快速迭代和團隊協作,簡化部署過程。

    Java的平台獨立性如何促進代碼重用?Java的平台獨立性如何促進代碼重用?Apr 24, 2025 am 12:05 AM

    Java'splatformindependencefacilitatescodereusebyallowingbytecodetorunonanyplatformwithaJVM.1)Developerscanwritecodeonceforconsistentbehavioracrossplatforms.2)Maintenanceisreducedascodedoesn'tneedrewriting.3)Librariesandframeworkscanbesharedacrossproj

    您如何在Java應用程序中對平台特定問題進行故障排除?您如何在Java應用程序中對平台特定問題進行故障排除?Apr 24, 2025 am 12:04 AM

    要解決Java應用程序中的平台特定問題,可以採取以下步驟:1.使用Java的System類查看系統屬性以了解運行環境。 2.利用File類或java.nio.file包處理文件路徑。 3.根據操作系統條件加載本地庫。 4.使用VisualVM或JProfiler優化跨平台性能。 5.通過Docker容器化確保測試環境與生產環境一致。 6.利用GitHubActions在多個平台上進行自動化測試。這些方法有助於有效地解決Java應用程序中的平台特定問題。

    JVM中的類加載程序子系統如何促進平台獨立性?JVM中的類加載程序子系統如何促進平台獨立性?Apr 23, 2025 am 12:14 AM

    類加載器通過統一的類文件格式、動態加載、雙親委派模型和平台無關的字節碼,確保Java程序在不同平台上的一致性和兼容性,實現平台獨立性。

    Java編譯器會產生特定於平台的代碼嗎?解釋。Java編譯器會產生特定於平台的代碼嗎?解釋。Apr 23, 2025 am 12:09 AM

    Java編譯器生成的代碼是平台無關的,但最終執行的代碼是平台特定的。 1.Java源代碼編譯成平台無關的字節碼。 2.JVM將字節碼轉換為特定平台的機器碼,確保跨平台運行但性能可能不同。

    JVM如何處理不同操作系統的多線程?JVM如何處理不同操作系統的多線程?Apr 23, 2025 am 12:07 AM

    多線程在現代編程中重要,因為它能提高程序的響應性和資源利用率,並處理複雜的並發任務。 JVM通過線程映射、調度機制和同步鎖機制,在不同操作系統上確保多線程的一致性和高效性。

    See all articles

    熱AI工具

    Undresser.AI Undress

    Undresser.AI Undress

    人工智慧驅動的應用程序,用於創建逼真的裸體照片

    AI Clothes Remover

    AI Clothes Remover

    用於從照片中去除衣服的線上人工智慧工具。

    Undress AI Tool

    Undress AI Tool

    免費脫衣圖片

    Clothoff.io

    Clothoff.io

    AI脫衣器

    Video Face Swap

    Video Face Swap

    使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

    熱工具

    SecLists

    SecLists

    SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合,這些清單在安全評估過程中經常使用,而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表,幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上,他就可以存取所需的每種類型的清單。

    PhpStorm Mac 版本

    PhpStorm Mac 版本

    最新(2018.2.1 )專業的PHP整合開發工具

    WebStorm Mac版

    WebStorm Mac版

    好用的JavaScript開發工具

    記事本++7.3.1

    記事本++7.3.1

    好用且免費的程式碼編輯器

    DVWA

    DVWA

    Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中