Anthony O. Anthony O. - 6 months ago 27
MySQL Question

Concurrently retrieve or create new row using JDBI SQL Object API

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

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);

  }