Using getGeneratedKeys with batch inserts in MySQL with Connector/J

Question

Using Connector/J, I would like to do a batch insert into a master table followed by a batch insert into a details table (PreparedStatement.executeBatch() for both). I haven't found much information online, so I'm looking for any feedback from people that have experience with this.

1. Can I use Statement.getGeneratedKeys() to get the IDs of the newly inserted rows in the master table so that I can use them as foreign keys in the detail inserts?

2. What if not every query resulted in an insert (e.g. there was an insert ignore or insert ... on duplicate key update query)? Will I get a row in Statement.getGeneratedKeys() for every statement, or only for the new ones?

3. What will Statement.getGeneratedKeys() return there is an error with one of the inserted master records, and continueBatchOnError is set to true in the connection string?

4. Are there any differences in the related behavior between Connector/J versions 5.0.x vs 5.5.x? What about MySQL 5.0 vs 5.1?

5. Any there any other issues or gotchas that I should be aware of?

6. Is there a better way to do this?

Answer 1

Well, I ran some tests. With Connector/J 5.1 and MySQL 5.1.42, I observe the following:

1. Statement.getGeneratedKeys() works as expected for inserts

2. If a row was inserted or updated (the update count array returned by executeBatch() returns '1' or '2'), Statement.getGeneratedKeys() will have the key for that row. If the row was not modified (insert ignore or insert ... on duplicate key update that results in a no-op, executeBatch() returns 3), there is no key.

3. The ResultSet returned by getGeneratedKeys will have the entries for successfully inserted rows, as per (2). There will not be a generated key row for the failed inserts (where update count value is Statement.EXECUTE_FAILED)

4. ?

5. Be careful with rewriteBatchedStatements in the JDBC connection string. If it is set to true, any failures will result in every row in the rewritten "chunk" to be treated as though it had failed. One way to handle this is to iterate the failed rows and retry them without batching.

6. ?