批量处理JDBC语句提高处理速度
1）当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据批量处理。通常情况下比单独提交处理更有效率
2）JDBC的批量处理语句包括下面两个方法：
addBatch(String)添加需要批量处理的SQL语句或参数
executeBatch()执行批量处理语句
clearBatch() 清空SQL
3）通常我们会遇到两种批量执行SQL语句的情况：1⃣️多条SQL语句的批量处理、2⃣️一个SQL语句的批量传参
多条SQL语句的批量处理
代码语言：javascript复制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();
}
}
