通过使用addBatch()和executeBatch()这一对方法可以实现批量处理数据。
手动打开mysql批量插入的开关,性能才能表现出来,大家试试就知道啦。。
加上“?useServerPrepStmts=false&rewriteBatchedStatements=true”
package com.xcfcky.demo;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DbStoreHelper {
private String insert_sql;
private String charset;
private boolean debug;
private String connectStr;
private String username;
private String password;
public DbStoreHelper() {
connectStr = "jdbc:mysql://localhost:3306/db_ip";
connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";
insert_sql = "INSERT INTO tb_ipinfos (iplong1,iplong2,ipstr1,ipstr2,ipdesc) VALUES (?,?,?,?,?)";
charset = "gbk";
debug = true;
username = "root";
password = "****";
}
private void doStore() throws ClassNotFoundException, SQLException, IOException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(connectStr, username,password);
conn.setAutoCommit(false); // 设置手动提交
PreparedStatement psts = conn.prepareStatement(insert_sql);
String line = null;
//开始执行时间
long begin = System.currentTimeMillis();
for(int i=0; i<500000; i++){
psts.setInt(1, i);
psts.setInt(2, i);
psts.setString(3, i + "ipstr1");
psts.setString(4, i + "ipstr2");
psts.setString(5, i + "ipstr3");
psts.addBatch(); // 加入批量处理
}
psts.executeBatch(); // 执行批量处理
conn.commit(); // 提交
System.out.println("共用去时间" + (System.currentTimeMillis() - begin));
conn.close();
}
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException{
DbStoreHelper dbsh = new DbStoreHelper();
dbsh.doStore();
}
}
插入50万条数据只需要14秒,性能明显提高
在MySQL JDBC连接字符串中还可以加入参数,
rewriteBatchedStatements=true,mysql默认关闭了batch处理,通过此参数进行打开,这个参数可以重写向数据库提交的SQL语句,具体参见:http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html
useServerPrepStmts=false,如果不开启(useServerPrepStmts=false),使用com.mysql.jdbc.PreparedStatement进行本地SQL拼装,最后送到db上就是已经替换了