Using @Transaction in JDBI / Dropwizard application

Question

I hava two jdbi dao like these:

public interface dao1 {
  @Query("insert into table1 ...")
  findByid(myBean1);
}

public interface dao2 {
  @Query("insert into table2 ...)
  save(myBean2;
  }
}

i want to execute save of two dao in one transaction like:

dao1.save();
dao2.save();

With spring i have used @transactional annotation. What can i do with dropwizard and jdbi ?

Lyricist

Answer 1

You can use @Transaction in JDBI. I have blogged about it here. http://manikandan-k.github.io/2015/05/10/Transactions_in_jdbi.html

Aldora

Hi, thanks this is what i need, but how can i test the class that implements GetHandle interface? Or if a need to use different repository object in the same transaction? – Lyricist

We can't use two different repository in same transaction. If you want use two different Dao's create a repository containing those two Daos. For testing the class, What is the difficulty you are facing ? – Aldora

we can't mock daos in repository 's unit test, because they are injected by jdbi @SqlCreateObject annotation and jdbi.onDemand method. It's true? – Lyricist

Since repository is abstract class, you need to have a implementation when writing unit test. When you give implementation you can inject mocks for abstract methods – Aldora

Hi @Manikandan, I went through your blog and tried to save the transaction using Repository class but no luck. This is my run method : – Bobine

public void run(ServiceConfiguration configuration, Environment environment) throws Exception { final DBIFactory factory = new DBIFactory(); final DBI jdbi = factory.build(environment, configuration.dataBase(), "mysql"); final AppsMessageDao appsMessageDao = jdbi.onDemand(AppsMessageDao.class); final MessageDao messageDao = jdbi.onDemand(MessageDao.class); } When I use the repository Class , I get error connection already closed . – Bobine

I am quite surprised why such this important information is not a part of official document form JDBI. – Quadruped

This doesn't seem to work for me. The first method always goes through (no-rollbacks) whenever the next/last method throws exceptions. I was hoping @Transaction annotation would solve that. – Archibald

Answer 2

The SQL Object API Overview shows the possibility to bind two instances to the same handle. This way you can both save() calls as part of the same transaction:

// TODO: add try/catch/finally to close things properly
DBI dbi = new DBI("jdbc:h2:mem:test");
Handle h = dbi.open();
h.begin();
Dao1 dao1 = h.attach(Dao1.class);
Dao2 dao2 = h.attach(Dao2.class);
dao1.save(myBean1);
dao2.save(myBean2);
h.commit();
h.close();

If you use onDemand instead of open and hesitate to get try/catch right, you might want to consider something like this:

// add some more interfaces
public interface Dao1 extends GetHandle, Transactional<Dao1> {
   @Query("insert into table1 ...")
   save(myBean1);
}

DBI dbi = new DBI("jdbc:h2:mem:test");
Dao1 dao1 = dbi.onDemand(Dao1.class);

// no try/catch necessary here
dao1.inTransaction(transactional, status) -> {
    transactional.save(myBean1);
    transactional.withHandle((h) -> h.attach(Dao2.class)
       .save(myBean2));
    return null; // return is enforced by the interface
});

Please double-check the functionality with a unit test.

Lawford

The question was about the JDBI @Transaction annotation. It is not used here... – Panfish

Answer 3

As per the jdbi docs,

Update, insert, and data definition statements are indicated in the SQL Object API via the @SqlUpdate annotation. The methods for these statements must have either void or int return types. If the return type is int, the the value will be the number of rows changed.

Annotate with @SqlUpdate. Here's a sample usage using h2 as the DB.

DBI dbi = new DBI("jdbc:h2:mem:test");
Handle h = dbi.open();
Dao1 dao1 = h.attach(Dao1.class);
dao1.save(myBean1);

Reference: http://jdbi.org/jdbi2/sql_object_api_dml

Harlequinade

Answer 4

You can use transaction callback on DBI:

dbi.useTransaction((handle, transactionStatus) -> {
    Dao1 dao1 = handle.attach(Dao1.class);
    Dao2 dao2 = handle.attach(Dao2.class);
    dao1.save();
    dao2.save();
});

Assuming you're using JDBI v2.x

Arcuation

Answer 5

Use @UnitOfWork

Example:

@POST
@UnitOfWork
public Role create(@Valid RoleApi roleApi) {
    return roleService.create(roleApi);
}

Attu

@UnitOfWork annotation is enabled only with hibernate bundle and not with jdbi one. – Lyricist