How to return deleted rows using jdbi?

How do I return the deleted rows using jdbi? For example, I’d like to do something like this:

public List<User> deleteAndReturnUsers() {
  return jdbi.withHandle(handle -> {
    return handle.createQuery("DELETE FROM mytable where id = :id")
      .bind("id", "someid")
      .map(new UserMapper())
      .list();
  });
}

Answer

Postgresql has a returning keyword…

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=25d284309745e40c8ea29945481d1aa2

DELETE FROM data WHERE val >= 2 RETURNING *;

You can then execute that query in the same way as you would a normal SELECT statement, and get a result set back; containing the records deleted.