Retrieving serial id from batch inserted rows in postgresql

Question

Here is the code that works:

        Connection c = ds.getConnection();
        c.setAutoCommit(false);
        PreparedStatement stmt = c.prepareStatement("INSERT INTO items (name, description) VALUES(?, ?)");
        while (!(items = bus.take()).isEmpty()) {
          for (Item item : items) {
            stmt.setString(1, item.name);
            stmt.setString(2, item.description);
            stmt.addBatch();
          }
          stmt.executeBatch();
          c.commit();
        }

But now I need to populate another table where id is a foreign key. If I use INSERT with RETURNING id then executeBatch fails with "A result was returned when none was expected" error.

I see several ways to solve this

• Do individual insert rather than the batch insert.
• Replace serial id with client generated guid.
• Use some kind of a stored procedure to perform the batch insert and return a list of ids.

Of the three methods that I see the last one seems to preserve both the efficiency of batch insert and return the ids, but it is also the most complex for me as I have never written stored procedures.

Is there a better way to batch insert and get the IDs? I have no problem using postgresql specific API rather than jdbc.

If not, could any one sketch such a stored procedure?

Here is the table schema:

CREATE UNLOGGED TABLE items
(
  id serial,
  name character varying(1000),
  description character varying(10000)
)
WITH (
  OIDS=FALSE
);

Answer 1

Something like this should work:

// tell the driver you want the generated keys
stmt =  c.prepareStatement("INSERT ... ", Statement.RETURN_GENERATED_KEYS);

stmt.executeBatch();

// now retrieve the generated keys
ResultSet rs = stmt.getGeneratedKeys();
while (rs.next()) {
 int id = rs.getInt(1);
 .. save the id somewhere or update the items list 
}

I think (I am not sure!) that the keys are returned in the order they were generated. So the first row from the ResultSet should map to the first "item" from the list you are processing. But do verify that!

Edit

If that doesn't work, try specifying the actual columns for which the values are generated:

stmt =  c.prepareStatement("INSERT ... ", new String[] {"id"});