jDBI 2.12 and the SQL Object API 16 Mar 2011

The latest release of jDBI, 2.12, includes a new set of APIs I have been mulling over for a couple of years now, ever since JDBC 4.0 dropped the “ease of development” features.

The sql object API lets you define annotated interfaces which generate all the needed rigamarole for you. Take, for example:

interface TheBasics
{
    @SqlUpdate("insert into something (id, name) values (:id, :name)")
    int insert(@BindBean Something something);
    
    @SqlQuery("select id, name from something where id = :id")
    Something findById(@Bind("id") long id);
}

This snippet defines two methods, and annotates them with the SQL and how to bind the arguments into the generated prepared statements. Using them is equally easy,

DBI dbi = new DBI(dataSource);
dbi.registerMapper(new SomethingMapper());

TheBasics dao = dbi.onDemand(TheBasics.class);

dao.insert(new Something(7, "Martin"));

Something martin = dao.findById(7);

In this case, we open an on-demand sql object. On demand means that it will obtain and release connections as needed, generally immediately before and after each method call. We then just call methods and we get database interactions.

We used a registered result set mapper here as well. This is also a new feature in 2.12, available on both the fluent api, and in the sql object api. Basically it just lets you register result set mappers which will be used to transform each row of the result set into some object, one to one. You can add an explicit mapper as well, via the @Mapper annotation, or by defining your own mapping annotation.

To get access to additional functionality, such as tranactions or access to the underlying handle, the sql object API has the idea of mixin interfaces. These are interfaces defined as part of the library which will be implemented on the sql object, for instance to use transactions with a sql object you would define your object as

interface UsesTransactions extends Transactional<UsesTransactions> 
{
  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@BindBean Something something);

  @SqlUpdate("update something set name = :name where id = :id")
  int update(@BindBean Something s);

  @SqlQuery("select id, name from something where id = :it")
  Something findById(@Bind int id);
}

The Transactional interface defines begin(), commit(), rollback(), and checkpoint related friends, as well as a callback receiver which wraps the callback in a transaction:

public void testExerciseTransactional() throws Exception
{
  UsesTransactions one = dbi.onDemand(UsesTransactions.class);
  UsesTransactions two = dbi.onDemand(UsesTransactions.class);

  one.insert(new Something(8, "Mike"));

  one.begin();
  one.update(new Something(8, "Michael"));

  assertEquals("Mike", two.findById(8).getName());

  one.commit();

  assertEquals("Michael", two.findById(8).getName());
}

public void testExerciseTransactionalWithCallback() throws Exception
{
  UsesTransactions dao = dbi.onDemand(UsesTransactions.class);
  dao.insert(new Something(8, "Mike"));

  int rows_updated = dao.inTransaction(new Transaction<Integer, UsesTransactions>()
  {
    public Integer inTransaction(UsesTransactions tx, TransactionStatus status) throws Exception
    {
      Something current = tx.findById(8);
      if ("Mike".equals(current.getName())) {
         return tx.update(new Something(8, "Michael"));
      }
      else {
        return 0;
      }
    }
  });

  assertEquals(1, rows_updated);
}

The whole thing is designed to, hopefully, be fully tunable and extensable, in that you can define your own binding annotations, statement customizer annotations, and so on. It translates the interfaces into regular jDBI elements, so the various techniques folks already use for statement rewriting, externalizing sql, etc all work the same as before. Now, hopefully, we have just managed to reduce a whole lot of boilerplate, and make stubbing or mocking the databases a smidge easier!

The easiest way to get started using jDBI is through maven (or ivy, or sbt, everything supports the maven2 repo layout now) via org.jdbi:jdbi:2.12, or grabbing the jar if you aren’t using one of those.