Batch insert via fluent API & returning generated keys...

[jcubeta]

Hello everyone! I am trying to write a method that will do a batch insert of objects, and then get back all the primary keys from the insert and put them in the respective beans. Is this possible?

I noticed that the PreparedBatch object has an "executeAndReturnGeneratedKeys()" method, but I cannot figure out how to use it.

Here's a simplified example of what I'm trying to do:

public void insertBatch(List<Order> orders) { 
   jdbi.useHandle(handle -> {
      PreparedBatch batch = handle.prepareBatch("insert into orders (cols...) values (:values...)");
      for (Order order: orders) { 
         batch.bindBean(order).add();
      }
      batch.executeAndReturnGeneratedKeys("id");
      batch.execute();
   });
}

My table (and my Order class) has an "id" column which is the auto-generated primary key.

The batch insert does work, but the 'id' attribute in the beans is never populated. To be fair, I could be going about this completely wrong/inefficiently, so please feel free to correct me.

Any help you can provide will be greatly appreciated!

Thanks!
James

[hgschmie]

So this works fine with Postgres:

@Test
    void testBatchInsertReturningIds() {
        List<String> names = Arrays.asList(
            "Brian",
            "Steven",
            "Matthew",
            "Artem",
            "Marnick",
            "Henning");


        PreparedBatch batch = handle.prepareBatch("insert into something (name) values (?) returning id");
        names.forEach(batch::add);

        BatchResultBearing batchResult = batch.executePreparedBatch("id");
        List<Integer> ids = batchResult.mapTo(Integer.class).list();

        assertThat(ids).hasSize(6);
        assertThat(ids).containsExactly(1, 2, 3, 4, 5, 6);
    }

The challenge is that returning data from the database is highly dependent on the database itself. E.g. for Postgres you need the returning id clause in insert. Could you talk a bit more about which database you are using and which driver?

[jcubeta]

Thank you for this. I am using Postgres as my database and Hikari connection pooling, but I am not sure what versions I am using. I am out of the office for the holidays but will post a response when I get back to work!

[jcubeta]

Hi @hgschmie - following up on this thread.

This worked! I had to upgrade my JDBI to v3.42.0 (I was back at 3.29.0). Also, as for other versions, I am using v42.2.19 of the Postgres driver, v4.0.2 of Hikari, and my Postgres server is v13.5 (it's an AWS RDS).

So my code now basically reads:

public void insertBatch(List<Order> orders) { 
   jdbi.useHandle(handle -> {
      PreparedBatch batch = handle.prepareBatch("insert into orders (cols...) values (:values...) returning id");
      for (Order order: orders) { 
         batch.bindBean(order).add();
      }
      BatchResultBearing batchResult = batch.executePreparedBatch("id");
      List<Integer> ids = batchResult.mapTo(Integer.class).list();
      assert ids.size() == orders.size();

      int i = 0;
      for (Order order: orders) {
         order.setId(ids.get(i++));
      }
   });
}

I feel like there should be a cleaner way to do the setting of the IDs after the insert, but I'm not that savvy of a programmer. :/

Thank you so much for the help!

[hgschmie]

do "INSERT INTO ... RETURNING *" and

BatchResultBearing batchResult = batch.executePreparedBatch("*");
      List<Order> ids = batchResult.mapTo(Order.class).list();

This assumes you have a mapper for the Order object registered. Postgres is probably the best database for any types of returning statements.

[jcubeta]

Awesome - thank you again for your help!