Anthony O. Anthony O. - 1 year ago 87
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 Source

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 {

    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)")
    public long addNewRecord(String value);

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download