第一周：JDBC中批量插入資料問題

在向資料庫中添加資料時，難免會遇到批量添加資料的問題，下面就是使用JDBC來實作批量插入的幾種方法，

準備作業：

• 在MySQL5資料庫中創建一個names表
• 表中就兩個欄位
  • id：主鍵，自增
  • name：varchar(25)，保證長度夠用就行

CREATE TABLE names(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(25)
);

方法一：

最直接的頻繁執行SQL陳述句來插入

long start = System.currentTimeMillis();

// 獲取資料庫連接 
Connection conn= DriverManager.getConnection(url, user, password);
// SQL陳述句
String sql = "insert into names(name) values(?);";
// 預編譯SQL陳述句
PreparedStatement ps = conn.prepareStatement(sql);

// 批量插入 2萬 條資料
for (int i = 0; i < 20000; i++) {
    ps.setObject(1, "name_"+i); // 填充占位符?
    ps.execute(); // 每一條資料都執行一次
}

long end = System.currentTimeMillis();

System.out.println("花費的時間為：" + (end - start)); // 花費的時間為：794551

// 關閉資源
ps.close();
conn.close();

方式二：

使用executeBatch()來批量插入資料

需要在資料庫連接的url中添加rewriteBatchedStatements=true欄位，讓資料庫開啟批處理默認

long start = System.currentTimeMillis();

// 獲取資料庫連接 
Connection conn= DriverManager.getConnection(url, user, password);
// SQL陳述句
String sql = "insert into names(name) values(?)"; // 注意這里！ 一定不要加結尾的分號;
// 預編譯SQL陳述句
PreparedStatement ps = conn.prepareStatement(sql);

// 批量插入 100萬 條資料
for (int i = 1; i <= 1000000; i++) {
    ps.setObject(1, "name_"+i);

    // 添加到同一batch中
    ps.addBatch();

    if (i % 500 == 0) { // 每批次夠500條才執行 控制這個數也可以提高點速度
        // 執行該batch的插入操作
        ps.executeBatch();

        // 清空已執行的batch
        ps.clearBatch();
    }
}

long end = System.currentTimeMillis();

System.out.println("花費的時間為：" + (end - start)); // 花費的時間為：5177

// 關閉資源
ps.close();
conn.close();

注意：一定不要給SQL陳述句添加結尾的;，否則會拋例外，

java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('name_2'),('name_3'),('name_4'),('name_5'),('name_6'),('name_7'),('name_8'),('n' at line 1...

至于原因，JDBC的原始碼實在太晦澀了，看一會就前后連接不上了，所以筆者認為應該是由于在MySQL中批量插入的SQL陳述句的問題，

就是對于names表，直接在MySQL中用SQL陳述句來批量添加資料時，可以這樣

insert into names(`name`) values("name_1"), ("name_2"), ("name_3");

這行去掉;也能正常運行

但是如果這樣，注意分號

insert into names(`name`) values("name_1");, ("name_2"), ("name_3")

那么"name_1"插入表中，后面2和3沒有，并且MySQL拋例外，

? 那么或許在JDBC中，每次addBatch()，都是將要放在占位符?的資料先存在ArrayList中，當執行executeBatch()時，遍歷ArrayList將第一個資料"name_1"放在SQL陳述句的?處，后續的全部構造成,("name_2")、,("name_3")的形式連接在這條SQL陳述句后面，最終構造成一個長的插入SQL陳述句，再執行，完成批量插入，

即：

insert into names(`name`) values("name_1")
insert into names(`name`) values("name_1"), ("name_2")
insert into names(`name`) values("name_1"), ("name_2"), ("name_3")
insert into names(`name`) values("name_1"), ("name_2"), ("name_3")..., ("name_batchSize")

這樣由于執行拼在一起的SQL就可以完成批量插入，

但是如果insert into names(name) values(?);結尾有個;，就變成這樣：

insert into names(`name`) values("name_1");
insert into names(`name`) values("name_1");, ("name_2")
insert into names(`name`) values("name_1");, ("name_2"), ("name_3")
insert into names(`name`) values("name_1");, ("name_2"), ("name_3")..., ("name_batchSize")

那么JDBC的對SQL陳述句的語法檢查或語意檢查無法通過，就會拋例外，

資料庫中也不會有"name_1"這條資料，

以上是筆者的推測，并沒有通過JDBC原始碼驗證，

方式三：

在方式二的基礎上再進一步優化，除了改變一批次的容量（上面是500）外，還可以設定不允許自動提交資料，改為手動提交資料，

// 設定不允許自動提交資料
conn.setAutoCommit(false); // 該行代碼放在獲取資料庫連接后

// ... 批量插入操作同上

// 提交資料
conn.commit(); // 在批量插入的for回圈后

// 花費時間為：3954

另外，還有個executeLargeBatch()方法

當要總共要插入1億條資料，并且一個batch為100萬

executeBatch()花費了413635毫秒

executeLargeBatch()花費了386389毫秒

emmm...可能不是單純替換著用的，哈哈哈！

標籤：其他

上一篇：五子棋原生JavaScript分享

下一篇：我的splash頁面怎么少東西