一、批量执行SQL语句
当需要成批插入或者更新记录时,可以采用Java的批量更新机制,这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
JDBC的批量处理语句包括下面三个方法:
addBatch(String)
:添加需要批量处理的SQL语句或是参数;executeBatch()
:执行批量处理语句;clearBatch()
:清空缓存的数据
通常我们会遇到两种批量执行SQL语句的情况:
- 多条SQL语句的批量处理;
- 一个SQL语句的批量传参;
二、使用PreparedStatement批量插入数据
@Test
public void testInsert1(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 20000; i++) {
preparedStatement.setObject(1,"name_" + i);
preparedStatement.execute();
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
花费时间为
优化插入操作,减少磁盘IO操作。使用addBatch()
、executeBatch()
、clearBatch()
三个方法实现批处理。mysql服务器默认是关闭批处理的,我们需要通过一个参数,让mysql开启批处理的支持。将?rewriteBatchedStatements=true
写在配置文件的url
后面。
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
@Test
public void testInsert2(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 20000; i++) {
preparedStatement.setObject(1,"name_" + i);
//1、暂时缓存sql,缓存一定数量之后再与数据库交互,进行插入
preparedStatement.addBatch();
if(i % 500 == 0){ //缓存500个sql,执行一次数据库插入的交互
//2、执行batch
preparedStatement.executeBatch();
//3、清空batch
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
花费时间
还可以再优化,设置不自动提交,等到所有数据都传输完成以后,最后一块提交。
@Test
public void testInsert3(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
//设置不允许自动提交数据
connection.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 2000000; i++) {
preparedStatement.setObject(1,"name_" + i);
//1、暂时缓存sql,缓存一定数量之后再与数据库交互,进行插入
preparedStatement.addBatch();
if(i % 500 == 0){ //缓存500个sql,执行一次数据库插入的交互
//2、执行batch
preparedStatement.executeBatch();
//3、清空batch
preparedStatement.clearBatch();
}
}
//统一提交数据
connection.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end - start));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}