使用JDBC进行批量INSERTS的高效方法 [英] Efficient way to do batch INSERTS with JDBC

问题描述

在我的应用中，我需要做很多插入。它是一个Java应用程序，我使用普通的JDBC来执行查询。数据库是Oracle。我已启用批处理，因此它节省了执行查询的网络延迟。但是查询作为单独的INSERT串行执行：

In my app I need to do a lot of INSERTS. Its a Java app and I am using plain JDBC to execute the queries. The DB being Oracle. I have enabled batching though, so it saves me network latencies to execute queries. But the queries execute serially as separate INSERTs:

insert into some_table (col1, col2) values (val1, val2)
insert into some_table (col1, col2) values (val3, val4)
insert into some_table (col1, col2) values (val5, val6)

我想知道以下形式的INSERT是否会更有效：

I was wondering if the following form of INSERT might be more efficient:

insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)

即将多个INSERT折叠成一个。

i.e. collapsing multiple INSERTs into one.

使批量INSERT更快的任何其他提示？

Any other tips for making batch INSERTs faster?

推荐答案

这是前两个答案的混合：

This is a mix of the two previous answers:

  PreparedStatement ps = c.prepareStatement("INSERT INTO employees VALUES (?, ?)");

  ps.setString(1, "John");
  ps.setString(2,"Doe");
  ps.addBatch();

  ps.clearParameters();
  ps.setString(1, "Dave");
  ps.setString(2,"Smith");
  ps.addBatch();

  ps.clearParameters();
  int[] results = ps.executeBatch();

这篇关于使用JDBC进行批量INSERTS的高效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！