基本介绍
- 当需要成批插入或者更新记录时。可以采用
Java
的批量更新机制,这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。 JDBC
的批量处理语句包括下面方法:
1.addBatch()
:添加需要批量处理的 SQL
语句或参数
2.executeBatch()
:执行批量处理语句
3.clearBatch()
:清空批处理包的语句JDBC
连接 MySQL
时,如果要使用批处理功能,请再 url
中加参数?rewriteBatchedStatements=true
- 批处理往往和
PreparedStatement
一起搭配使用,可以既减少编译次数,又减少运行次数,效率大大提高
案例:批量插入 5000 条数据,比较耗时
package Test;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test14 {
@Test
public void noBatch(){
Connection connection = null;
String sql = "insert into admin2 values(null, ?, ?)";
PreparedStatement preparedStatement = null;
try{
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行:");
long start = System.currentTimeMillis();
for(int i = 0; i < 5000; i++){
preparedStatement.setString(1, "Gin" + i);
preparedStatement.setString(2, "4869");
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("传统方式耗时:" + (end - start) + " ms");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
@Test
public void useBatch(){
Connection connection = null;
String sql = "insert into admin2 values(null, ?, ?)";
PreparedStatement preparedStatement = null;
try{
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
System.out.println("开始执行:");
long start = System.currentTimeMillis();
for(int i = 0; i < 5000; i++){
preparedStatement.setString(1, "Gin" + i);
preparedStatement.setString(2, "4869");
preparedStatement.addBatch();
if((i + 1) % 1000 ==0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("使用Batch耗时:" + (end - start) + " ms");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
}