Geek Answers Handbook

Java: retrieve keys after executeBatch () in H2

I am trying to get the generated keys from an executeBatch() transaction, but I only get the last key.

this is my code:

  PreparedStatement ps_insert = conn.prepareStatement(insertQuery, PreparedStatement.RETURN_GENERATED_KEYS); for (int i = 0 ; i < adding_dates.length ; i++){ ps_insert.setInt(1, Integer.parseInt(consultant_id)); ps_insert.setDate(2, adding_dates[i]); ps_insert.setInt(3, Integer.parseInt(room_id)); ps_insert.addBatch(); } ps_insert.executeBatch(); ResultSet rs = ps_insert.getGeneratedKeys(); //<-- Only the last key retrieved conn.commit();

What am I doing wrong?

EDIT: Sorry, I don't mention that I use H2 ( http://www.h2database.com/html/main.html ) in native mode.

+6
source share
4 answers

According to H2 jdbc driver javadocs , this is normal behavior:

Returns a result set that contains the last generated auto-increment key for this connection, if any. If the key has not generated the last modification statement, then an empty result set is returned. The returned result set contains only data for the last row.

+3
source

This is a limitation of the implementation of H2. This is an issue .

Now use insertions / updates without batch processing or generated generated keys in some way via select.

+1
source

You must iterate through the ResultSet to retrieve the keys.

  PreparedStatement ps_insert = conn.prepareStatement(insertQuery, PreparedStatement.RETURN_GENERATED_KEYS); for (int i = 0 ; i < adding_dates.length ; i++){ ps_insert.setInt(1, Integer.parseInt(consultant_id)); ps_insert.setDate(2, adding_dates[i]); ps_insert.setInt(3, Integer.parseInt(room_id)); ps_insert.addBatch(); } ps_insert.executeBatch(); ResultSet rs = ps_insert.getGeneratedKeys(); //<-- Only the last key retrieved if (rs.next()) { ResultSetMetaData rsmd = rs.getMetaData(); int colCount = rsmd.getColumnCount(); do { for (int i = 1; i <= colCount; i++) { String key = rs.getString(i); System.out.println("key " + i + "is " + key); } } while (rs.next();) } conn.commit();
0
source

If you are sharing a session / connection between two threads and two of these threads are trying to execute instructions at the same time, you may see this problem.

You probably need to either (a) use the connection pool, or (b) synchronize all your access to the database.

e.g. for option (b)

put a synchronize token infront of your method to make it thread safe

Just a thought, because I don’t know that you are executing the execution context

0
source

More articles:


All Articles