批量处理JDBC语句提高处理速度
1)当需要成批插入或者更新记录时,可以采用Java的批量更新机制,这一机制允许多条语句一次性提交给数据批量处理。通常情况下比单独提交处理更有效率
2)JDBC的批量处理语句包括下面两个方法:
addBatch(String)
添加需要批量处理的SQL语句或参数
executeBatch()
执行批量处理语句
clearBatch()
清空SQL
3)通常我们会遇到两种批量执行SQL语句的情况:1⃣️多条SQL语句的批量处理、2⃣️一个SQL语句的批量传参
多条SQL语句的批量处理
public class JDBCTest05 {
// 使用Statement的addBatch()批处理
public void testBatchWithStatement() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydb";
String sql = null;
Connection connection = DriverManager.getConnection(url,"user","password");
Statement statement = connection.createStatement();
long begin = System.currentTimeMillis();
for(int i = 0;i < 100000;i++){
sql = "INSERT INTO customers VALUES(" + (i + 1)
+ ", ' name_" + i + "', '2010-Ô1-13')";
statement.addBatch(sql); // statement的批量处理
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin));
statement.executeUpdate(sql);
statement.close();
connection.close();
// PreparedStatement()的executeUpdate分条处理
public void testBatchWithPreparedStatement() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydb";
String sql = "INSERT INTO customers VALUES(?,?,?)";
Connection connection = DriverManager.getConnection(url,"user","password");
PreparedStatement preparedStatement = connection.prepareStatement(sql);
Date date = new Date(new java.util.Date().getTime());
long begin = System.currentTimeMillis();
for(int i = 0;i < 100000;i++){
preparedStatement.setInt(1,i+1);
preparedStatement.setString(2,"name_" + i);
preparedStatement.setDate(3,date);
preparedStatement.executeUpdate();
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin));
preparedStatement.close();
connection.close();
// PreparedStatement()的executeBatch批量处理
public void testBatch() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydb";
String sql = "INSERT INTO customers VALUES(?,?,?)";
Connection connection = DriverManager.getConnection(url,"user","password");
PreparedStatement preparedStatement = connection.prepareStatement(sql);
Date date = new Date(new java.util.Date().getTime());
long begin = System.currentTimeMillis();
for(int i = 0;i < 100000;i++){
preparedStatement.setInt(1,i+1);
preparedStatement.setString(2,"name_ " + i);
preparedStatement.setDate(3,date);
preparedStatement.addBatch();
if((i + 1) % 300 == 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
if(100000 % 300 != 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin));
preparedStatement.close();
connection.close();