CODEWITHSUNDEEP
javamysqljdbc
ducati1212
ducati1212

Reputation: 855

MySQL batch stmt with Statement.RETURN_GENERATED_KEYS

I am trying to execute 2 sql statements in a batch. the first statement is an insert that uses a auto generated value for its ID. the second statement is an insert to another table but it needs to use the auto generated value from above as part of the insert value

something like (where id is just to show the auto generated field its not defined in sql

stmt.addbatch(insert into table1("id_auto_generated", "foo"));
stmt.addbatch(insert into table2("table1_id", "boo"));

the way I do it now is by using this in my second sql

insert into table2(LAST_INSERT_ID(), "boo");

Problem is its slow even in batch statements its very slow as my batch can be 50,000 inserts.

I wanted to switch to prepared statements but do not know how to use Statement.RETURN_GENERATED_KEYS or LAST_INSERT_ID() with prepared statements.

Upvotes: 1

Views: 1962

Answers (1)

Gray
Gray

Reputation: 116908

I'm not sure this is a way you can do this with addBatch except in the manner that you are using. Another thing to try is to abandon the addBatch() method and try turning off auto-commit instead. Then you can use the stmt.getGeneratedKeys();. Something like:

connection.setAutoCommit(false);
stmt.executeUpdate("insert into table1(\"id_auto_generated\", \"foo\") ...");
DatabaseResults results = stmt.getGeneratedKeys();
// extract the id from the results
stmt.executeUpdate("insert into table2(\"table1_id\", \"boo\") ...");
... many more stmts here
connection.commit();
connection.setAutoCommit(true);

Hope this helps.

Upvotes: 2

Related Questions