Java JDBC - Multiple prepared statement bulk insert

Question

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?

Clint · Accepted Answer · 2011-08-25 19:21:36Z

You can try

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

then

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(); } ...

IncrediApp · Accepted Answer · 2011-08-25 19:03:20Z

1

If I understand your problem correctly, you have a problem with NEXTVAL and CURRVAL since CURRVAL might change due to other DB use? If so, you can change your code to this order:

currentNextVal = select NEXTVAL INSERT into table_a with currentNextVal as the id INSERT into table_b with the same currentNextVal

Did I understand your problem correctly?

answered Aug 25, 2011 at 19:03

IncrediApp

10.4k2 gold badges35 silver badges24 bronze badges

2 Comments

agav Over a year ago

Partially you are right, but more complex problem is to execute both insert statement IN JAVA one after another about 1000 times so that each row in one table would correspond to one row in another table (A_ID = B_ID). If it was only one insert than we can use addBatch() as I show in example and this would increase performance. But seems like it is not possible with two prepared statements in java, and this might cause performance problems.

IncrediApp Over a year ago

If you use the same "currentNextVal" from my pseudo code, you will achieve it. You don't have to perform it in a batch, although you can do it in 2 batches...

Collectives™ on Stack Overflow

Java JDBC - Multiple prepared statement bulk insert

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related