java: retrieve keys after executeBatch() in H2

Question

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

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: Apologies for not mentioning that I use H2 (http://www.h2database.com/html/main.html) database in embedded mode.

Answer 1

According to H2 jdbc driver javadocs, this is the normal behaviour:

Return a result set that contains the last generated auto-increment key for this connection, if there was one. If no key was generated by the last modification statement, then an empty result set is returned. The returned result set only contains the data for the very last row.

Answer 2

You must iterate 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();        

Answer 3

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

For now use inserts/updates without batch, or query generated keys somehow through select.