Getting serial id from 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 the foreign key. If I use INSERT with a RETURNING id , then executeBatch does not work with the error "The result was returned when no one expected it."

I see several ways to solve this problem.

• Make a separate insert, not a batch insert.
• Replace the serial identifier with the key generated by the client.
• Use some stored procedure to perform batch insertion and return a list of identifiers.

Of the three methods that I see in the latter, it seems that both the efficiency of batch insertion and the return of identifiers are preserved, but it is also the most difficult for me, since I never wrote stored procedures.

Is there a better way to insert a package and get identifiers? I have no problem using postgresql specific API, not jdbc.

If not, can someone sketch out such a stored procedure?

Here is the table layout:

 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'm not sure!) That the keys are returned in the order in which they were generated. Thus, the first line from the ResultSet must match the first "item" from the list that you are processing. But make sure!

Edit

If this does not work, try specifying the actual columns for which the values ​​are generated:

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