Java JDBC - 多个预准备语句批量插入 [英] Java JDBC - Multiple prepared statement bulk insert

问题描述

使用JDBC（Oracle）我需要在两个表中的每一个中插入大约一千行。这样的事情：

Using JDBC (Oracle) I need to insert about thousand rows into each of two tables. Something like this:

"INSERT INTO TABLE_A (A_ID, A_NAME, A_LAST_NAME) VALUES (MY_SEQUENCE.NEXTVAL, ?, ?)";
"INSERT INTO TABLE_B (B_ID, B_DESCRIPTION) VALUES (MY_SEQUENCE.CURRVAL, ?)";

问题是两个表都是通过公共序列连接的，所以语句顺序很重要。

The problem is that both tables are connected through common sequence, so that order of statements is important.

如果我只有一张桌子那就很容易了。在这种情况下，我使用了代码：

It would be quite easy if I had only one table. In that case I used code:

String insert = "Insert into TABLE_A(A_ID, A_NAME, A_LAST_NAME) values(MY_SEQUENCE.NEXTVAL, ?, ?)";
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(insert);
for(MyObject obj : myCollection) {
    ps.setString(1, obj.getName());
    ps.setString(2, obj.getLastName());
    ps.addBatch();
}
ps.executeBatch();
conn.commit();
ps.close();

但这种方法只适用于一个准备好的语句，因此只有一个插入。我如何为这个问题提供解决方案？

But this approach can work only with one prepared statment and thus with only one Insert. How can I provide a solution for this problem?

推荐答案

你可以尝试

PreparedStatement ps = conn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
...
ps.executeBatch();

然后

ResultSet rs = ps.getGeneratedKeys();
ps = conn.prepareStatement("INSERT INTO TABLE_B (B_ID, B_DESCRIPTION) VALUES (?, ?)");

for ( int counter =0;rs.next(); counter++ ) { 
  ps.setInt(1,rs.getInt(0));
  ps.setString(2, myCollection.get(counter).getDescription());
  ps.addBatch();
}
...

这篇关于Java JDBC - 多个预准备语句批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！