07. jdbc 批量操作

jdbc 系列文章列表, 请查看目录: 《jdbc学习笔记》

1. 批量操作

当需要执行批量插入或批量更新操作时, 普通的一条sql一提交方式, sql执行效率很低. 对此我们可以借助于JDBC 的批量提交机制, 可以大幅提升批量操作性能. 对于JDBC 支持两种批量操作方式:

单条sql批量传参: 借助于PreparedStatment 实现, 最常用多条sql批量操作: 借助于Statment实现, 不常用

1.1 批量传参模式

Connection 需要设置手工提交事务, 不能采用自动提交事务方式借助于PreparedStatment 实现, 相关API 有: addBatch(): 将当前参数列表田clearBatch(): 清空当前已添加到批量命令队列中的命令, 其实底层就是一个List结构, 执行的是List.clear()方法.executeBatch(): 批量执行当前命令队列中的所有命令, 执行之后会调用clearBatch()方法. 需要注意的是并为提交事务, 提交事务是由Connection 对象控制的. public void batchTemplate(){ Connection connection = null; PreparedStatement preparedStatement = null; try{ // 1. 获取数据库连接, 并指定自动提交事务为false connection = DbConnUtil.getConnection(false); // 2. 指定sql String sql = ""; // 3. 获取PreparedStatement preparedStatement = connection.prepareStatement(sql); // 4. 批量分组设置参数, 每一条sql独立设置参数 for (int i = 0; i < BATCH_NUM; i++) { preparedStatement.setString(1, ""); // 省略多个参数设置... // 将当前参数和sql添加到批量执行队列中, 底层为ArrayList. preparedStatement.addBatch(); // 可选: 防止一次性提交命令太多, 可先进行部分提交. if ((i % 100 == 0)) preparedStatement.executeBatch(); } // 5. 批量执行sql preparedStatement.executeBatch(); // 6. 手工提交事务 connection.commit(); } catch (SQLException e) { try { // 出异常后, 回滚事务 connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } }finally { // 关闭连接,释放资源 DbConnUtil.release(preparedStatement, connection); } }

1.2 批量sql方式

批量sql 方式, 也需要设置Connection的事务提交为手动方式批量sql 方式 采用Statment 来设置, 可能会有sql注入风险.Statment 相关批量API: addBatch(sql): 批量命令中添加sql, sql不能包含占位符, 必须为可执行的sqlclearBatch(): 清空当前批量执行队列中的sql命令executeBatch(): 批量执行sql public void batchSqlTemplate(){ Connection connection = null; PreparedStatement preparedStatement = null; try { // 1. 获取数据库连接, 并指定手工提交事务 connection = DbConnUtil.getConnection(false); // 2. 获取Statement 对象 Statement statement = connection.createStatement(); // 3. 批量添加sql statement.addBatch("insert into t_user values (null , 'zhangsan', '123456')"); statement.addBatch("update t_user set password = name"); // 4. 执行批量sql statement.executeBatch(); // 5. 提交事务 connection.commit(); } catch (SQLException e) { try { // 出异常后, 回滚事务 connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } }finally { // 关闭连接,释放资源 DbConnUtil.release(preparedStatement, connection); } }

2 批量性能测试

由于不同的数据库对于sql优化的方式和对JDBC的实现有所不同, 因此不同的数据库对于不同的批量操作方式, 表现也略有差异. 笔者比较熟悉Oracle 和mysql, 因此主要对比测试mysql 和 Oracle 对于不同的批量操作的性能差异.综合对比, 笔者推荐使用批量API方式进行批量操作.

2.1 执行批量操作的三种方式

对于常见的批量操作, 有如下几种方式:

每条sql独立提交事务: 类似于使用hibernate, mybatis 等封装好的API. 因为默认使用的Connection为自动提交事务, 因此也无法回滚事务.一次性提交事务: 需要设置Connection事务提交方式为手工, 然后最后一次性提交一次性提交事务, 通过批量API: 需要设置Connection事务提交方式为手工, 然后使用批量API

2.2 批量性能测试结果

可能是由于笔者的Oracle为单位开发库的问题, 一直在使用, 因此执行效率很慢. 所以笔者对于oracle 只拿出1000条进行批量测试.对于Oracle: 在不使用批量API的情况下, 独立提交事务和一次性提交事务, 性能差别不大. 但是批量提交事务性能有大幅提升, 因此Oracle 的批量操作应该优先考虑批量API对于mysql: 一次性提交事务和借助于批量API方式, 性能差异不大, 但和单独提交事务性能差异很大. 数据库批量插入条数每条记录独立提交事务一次提交事务一次提交事务, 批量APIoracle/1,000条28658ms27724 ms622 msmysql/100,000条146114ms5531 ms5421 ms

2.3 准备表

对于性能测试, 笔者只创建一个简单的user表即可.需要注意的是, 不要使用自增主键方式. 因为oracle 无自增主键方式, mysql 无序列化主键方式.笔者准备表t_user, 表结构如下 字段类型idintnamevarchar(20)passwordvarchar(20)

2.4 测试用例

对于获取数据库连接, 实现方式较多, 对测试并无影响, 所以笔者封装了一个工具类为DbConnUtil.

public class TestBatch { // 批量数量 private static final int BATCH_NUM = 100000; // 执行之前清空表 @Before public void setUp() throws Exception { // 定义sql String sql = "truncate table t_user"; // 1. 获取连接 Connection connection = DbConnUtil.getConnection(); // 2. 创建PreparedStatement对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); // 4. 执行sql preparedStatement.executeUpdate(); } // 批量操作: 每条sql 单独提交事务 @Test public void test_batch1() throws Exception { long start = System.currentTimeMillis(); // 定义sql String sql = "insert into t_user values (? , ?, ?)"; // 1. 获取连接 Connection connection = DbConnUtil.getConnection(true); // 2. 创建PreparedStatement对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); // 3. 批量操作 for (int i = 0; i < BATCH_NUM; i++) { // 3.1 为每条sql(相同sql)设置参数 preparedStatement.setInt(1, i); preparedStatement.setString(2, "usr_" + i); preparedStatement.setString(3, "pwd_" + i); // 3.2 单独执行每条sql preparedStatement.executeUpdate(); } // 5. 释放连接 DbConnUtil.release(preparedStatement, connection); long end = System.currentTimeMillis(); System.out.println("共耗时:" + (end - start)); } // 批量操作: 手工事务, 一次性提交 @Test public void test_batch2() throws Exception { long start = System.currentTimeMillis(); // 定义sql String sql = "insert into t_user values (? , ?, ?)"; // 1. 获取连接 Connection connection = DbConnUtil.getConnection(false); // 2. 创建PreparedStatement对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); // 3. 批量操作 for (int i = 0; i < BATCH_NUM; i++) { // 3.1 为每条sql(相同sql)设置参数 preparedStatement.setInt(1, i); preparedStatement.setString(2, "usr_" + i); preparedStatement.setString(3, "pwd_" + i); // 3.2 单独执行每条sql preparedStatement.executeUpdate(); } // 4. 提交事务 connection.commit(); // 5. 释放连接 DbConnUtil.release(preparedStatement, connection); long end = System.currentTimeMillis(); System.out.println("共耗时:" + (end - start)); // 共耗时:25053 } // 批量操作: 一次性提交事务, 批量API @Test public void testBatch3() throws Exception{ long start = System.currentTimeMillis(); // 定义sql String sql = "insert into t_user values (? , ?, ?)"; // 1. 获取数据库连接 Connection connection = DbConnUtil.getConnection(false); // 2. 创建PreparedStatement对象 PreparedStatement preStat = connection.prepareStatement(sql); // 3. 批量设置参数 for (int i = 0; i < BATCH_NUM; i++) { preStat.setInt(1, i); preStat.setString(2, "usr_" + i); preStat.setString(3, "pwd_" + i); // 将当前sql和参数作为一条命令添加到命令列表中 preStat.addBatch(); // 每1000条命令发送一条sql if ((i + 1) % 100 == 0) { preStat.executeBatch(); // int a = 1/0; 模拟异常, 测试事务回滚 } } // 4. 执行批量队列中剩余的sql preStat.executeBatch(); // 5.提交事务 connection.commit(); // 6.关闭连接 DbConnUtil.release(preStat, connection); long end = System.currentTimeMillis(); System.out.println("共耗时:" + (end - start)); } }