Concurrently retrieve or create new row using JDBI SQL Object API

Question

I'm using JDBI and would like to use the SQL Object API in order to, given a field value, search if a row exists for this value, return its key if found, create it if not found and return the generated key (talking to a MySQL database).

Is it possible to do this using SQL Object API without any stored procedure?

Answer 1

There is no magic in the JDBI SQL Object API whch will do this for you. You will need several SQL statements, just as you would in a stored procedure.

If you want to combine it in a single method in your DAO class, you can make it an abstract class rather than an interface, then it can have a concrete method containing the logic you need, and annotate that to make it occur within a transaction:

  public abstract class MyDAO {

    @Transaction
    public long getId(String value) {
      Thing thing = findByValue(value);
      if (thing != null) {
        return thing.getId();
      } else {
        return addNewRecord(value);
      } 

    }

    @SqlQuery("select * from mytable where value = :value")
    public Thing findByValue(@Bind("value") String value);

    @SqlUpdate("insert into mytable (value) values (:value)")
    @GetGeneratedKeys
    public long addNewRecord(String value);

  }