17

I am using JDBC and want to batch insert, but I need the generated keys for the next round of inserts - is there anyway to accomplish this?

MapSqlParameterSource[] batchArgs ....

DAL.getNamedParameterTemplate().batchUpdate("INSERT INTO...", batchArgs);

Thanks

MalcomTucker
  • 7,407
  • 14
  • 72
  • 93
  • I found your question because I am looking to do the same thing, but the Spring batchUpdate takes a BatchPreparedStatementSetter and I can't figure out how to make that return the generated keys. I'm wondering if somehow you have to do this yourself using jdbc addBatch funtions with the Spring jdbcTemplate functions. Did you find an answer to your question? – titania424 Jun 16 '11 at 17:40

2 Answers2

11

Spring framework folks attempted a solution to this problem. But they abandoned the attempt when it became apparent that there is no way to guarantee that the solution will work with all JDBC Drivers. This is because the JDBC spec doesn't guarantee that the generated keys will be made available after a batch update. JDBC drivers are free to implement this feature as they see fit. In some cases the underlying database might not return the generated keys making it impossible for the driver to support this feature.

So even if you are directly working with JDBC, you will need to check whether your database and JDBC driver makes the generated keys available.

I remember I was able to achieve this with MySQL 5.0 JDBC driver with some effort but never integrated the solution in our production application as we had to support older versions of MySQL as well.

Tahir Akhtar
  • 11,385
  • 7
  • 42
  • 69
0

Use db sequences to grab the next primary key value and use it in the insert statement.

Boris Pavlović
  • 63,078
  • 28
  • 122
  • 148