java - Retrieving serial id from batch inserted rows in postgresql -

here code works:

        connection c = ds.getconnection();         c.setautocommit(false);         preparedstatement stmt = c.preparestatement("insert items (name, description) values(?, ?)");         while (!(items = bus.take()).isempty()) {           (item item : items) {             stmt.setstring(1, item.name);             stmt.setstring(2, item.description);             stmt.addbatch();           }           stmt.executebatch();           c.commit();         } 

but need populate table id foreign key. if use insert returning id executebatch fails "a result returned when none expected" error.

i see several ways solve this

• do individual insert rather batch insert.
• replace serial id client generated guid.
• use kind of stored procedure perform batch insert , return list of ids.

of 3 methods see last 1 seems preserve both efficiency of batch insert , return ids, complex me have never written stored procedures.

is there better way batch insert , ids? have no problem using postgresql specific api rather jdbc.

if not, 1 sketch such stored procedure?

here table schema:

create unlogged table items (   id serial,   name character varying(1000),   description character varying(10000) ) (   oids=false ); 

something should work:

// tell driver want generated keys stmt =  c.preparestatement("insert ... ", statement.return_generated_keys);  stmt.executebatch();  // retrieve generated keys resultset rs = stmt.getgeneratedkeys(); while (rs.next()) {  int id = rs.getinteger(1);  .. save id somewhere or update items list  } 

i think (i not sure!) keys returned in order generated. first row resultset should map first "item" list processing. verify that!

edit

if doesn't work, try specifying actual columns values generated:

stmt =  c.preparestatement("insert ... ", new string[] {"id"});