|
在向數(shù)據(jù)庫(kù)中添加數(shù)據(jù)時(shí)，難免會(huì)遇到批量添加數(shù)據(jù)的問題。下面就是使用JDBC來(lái)實(shí)現(xiàn)批量插入的幾種方法。
準(zhǔn)備工作：
- 在MySQL5數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)
names表
- 表中就兩個(gè)字段
id：主鍵，自增name：varchar(25)，保證長(zhǎng)度夠用就行
CREATE TABLE names(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25)
);
方法一：
最直接的頻繁執(zhí)行SQL語(yǔ)句來(lái)插入
long start = System.currentTimeMillis();
// 獲取數(shù)據(jù)庫(kù)連接
Connection conn= DriverManager.getConnection(url, user, password);
// SQL語(yǔ)句
String sql = "insert into names(name) values(?);";
// 預(yù)編譯SQL語(yǔ)句
PreparedStatement ps = conn.prepareStatement(sql);
// 批量插入 2萬(wàn) 條數(shù)據(jù)
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name_"+i); // 填充占位符?
ps.execute(); // 每一條數(shù)據(jù)都執(zhí)行一次
}
long end = System.currentTimeMillis();
System.out.println("花費(fèi)的時(shí)間為：" + (end - start)); // 花費(fèi)的時(shí)間為：794551
// 關(guān)閉資源
ps.close();
conn.close();
方式二：
使用executeBatch()來(lái)批量插入數(shù)據(jù)
需要在數(shù)據(jù)庫(kù)連接的url中添加rewriteBatchedStatements=true字段，讓數(shù)據(jù)庫(kù)開啟批處理默認(rèn)
long start = System.currentTimeMillis();
// 獲取數(shù)據(jù)庫(kù)連接
Connection conn= DriverManager.getConnection(url, user, password);
// SQL語(yǔ)句
String sql = "insert into names(name) values(?)"; // 注意這里！ 一定不要加結(jié)尾的分號(hào);
// 預(yù)編譯SQL語(yǔ)句
PreparedStatement ps = conn.prepareStatement(sql);
// 批量插入 100萬(wàn) 條數(shù)據(jù)
for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, "name_"+i);
// 添加到同一batch中
ps.addBatch();
if (i % 500 == 0) { // 每批次夠500條才執(zhí)行 控制這個(gè)數(shù)也可以提高點(diǎn)速度
// 執(zhí)行該batch的插入操作
ps.executeBatch();
// 清空已執(zhí)行的batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花費(fèi)的時(shí)間為：" + (end - start)); // 花費(fèi)的時(shí)間為：5177
// 關(guān)閉資源
ps.close();
conn.close();
注意：一定不要給SQL語(yǔ)句添加結(jié)尾的;。否則會(huì)拋異常。
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的源碼實(shí)在太晦澀了，看一會(huì)就前后連接不上了，所以筆者認(rèn)為應(yīng)該是由于在MySQL中批量插入的SQL語(yǔ)句的問題。
就是對(duì)于names表，直接在MySQL中用SQL語(yǔ)句來(lái)批量添加數(shù)據(jù)時(shí)，可以這樣
insert into names(`name`) values("name_1"), ("name_2"), ("name_3");
這行去掉;也能正常運(yùn)行
但是如果這樣，注意分號(hào)
insert into names(`name`) values("name_1");, ("name_2"), ("name_3")
那么"name_1"插入表中，后面2和3沒有，并且MySQL拋異常。
? 那么或許在JDBC中，每次addBatch()，都是將要放在占位符?的數(shù)據(jù)先存在ArrayList中，當(dāng)執(zhí)行executeBatch()時(shí)，遍歷ArrayList將第一個(gè)數(shù)據(jù)"name_1"放在SQL語(yǔ)句的?處，后續(xù)的全部構(gòu)造成,("name_2")、,("name_3")的形式連接在這條SQL語(yǔ)句后面，最終構(gòu)造成一個(gè)長(zhǎng)的插入SQL語(yǔ)句，再執(zhí)行，完成批量插入。
即：
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")
這樣由于執(zhí)行拼在一起的SQL就可以完成批量插入。
但是如果insert into names(name) values(?);結(jié)尾有個(gè);，就變成這樣：
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的對(duì)SQL語(yǔ)句的語(yǔ)法檢查或語(yǔ)義檢查無(wú)法通過，就會(huì)拋異常。
數(shù)據(jù)庫(kù)中也不會(huì)有"name_1"這條數(shù)據(jù)。
以上是筆者的推測(cè)，并沒有通過JDBC源碼驗(yàn)證。
方式三：
在方式二的基礎(chǔ)上再進(jìn)一步優(yōu)化，除了改變一批次的容量（上面是500）外，還可以設(shè)置不允許自動(dòng)提交數(shù)據(jù)，改為手動(dòng)提交數(shù)據(jù)。
// 設(shè)置不允許自動(dòng)提交數(shù)據(jù)
conn.setAutoCommit(false); // 該行代碼放在獲取數(shù)據(jù)庫(kù)連接后
// ... 批量插入操作同上
// 提交數(shù)據(jù)
conn.commit(); // 在批量插入的for循環(huán)后
// 花費(fèi)時(shí)間為：3954
另外，還有個(gè)executeLargeBatch()方法
當(dāng)要總共要插入1億條數(shù)據(jù)，并且一個(gè)batch為100萬(wàn)
executeBatch()花費(fèi)了413635毫秒
executeLargeBatch()花費(fèi)了386389毫秒
emmm...可能不是單純替換著用的，哈哈哈！
|
