使用 JDBC 进行批量插入的有效方法 [英] 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.

有没有其他让批量插入更快的技巧?

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 进行批量插入的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！