Most of our interactions with SQL databases are done directly, without any ORM due performance issues. Only recently I got to know JDBI and after a while I felt in love. Follow this quick walkthrough how I’m using it.

## Get your DBI instance

// Using MySQL backend
Class.forName("com.mysql.jdbc.Driver");
DBI dbi = new DBI("jdbc:mysql://localhost/test", "testuser", "testpass");

## Get your Handler

Handle h = null;
try {
  h = dbi.open();

  ...
  ...

} finally {
  // don't forget to close it later!
  if(h!=null) h.close();
}

Mmmmm! I never liked this kind of statement because if you forget to close ResutlSet or Statement or Connection bad things happens. JDBI has an interface to solve this, similar to C# Using.

Integer employeesCount = dbi.withHandle(new HandleCallback<Integer>(final Integer id) {
  public Integer withHandle(Handle h) {

    // query goes here

}});

## Execute your Query

Integer employeesCount = dbi.withHandle(new HandleCallback<Integer>(final Integer id) {
  public Integer withHandle(Handle h) {
    return h.createQuery("select count(1) from employees WHERE contractor_id = :id")
      .map(IntegerMapper.FIRST) // [1]
      .bind("id", id) // [2]
      .first(); // [3]
}});

A lot of awesomeness happened here, let’s check it in detail:

• [1]: It defines a Mapper, how the ResultSet will be mapped to an object, on this case a Integer (built-in class) that will contains the first field of the recordset count(1) and this field is type Integer.
• [2]: Allows parameter name binding, which makes code more clear than using position (although they are available too)
• [3]: Get the first (and unique) line returned from this statement

## Custom Mappers

If you have a more complex object, you can create Custom Mappers also. On this example I have a Profile and I want to return it using a similar statement:

import lombok.Data;

public @Data class Profile {
    private final long id;
    private final String firstName;
    private final String lastName;
}

Pro tip: Check this great library lombok for reducing boilerplate

public class ProfileMapper implements ResultSetMapper<Profile> {

    @Override
    public Profile map(int idx, ResultSet rs, StatementContext ctx) throws SQLException {
	      return new Profile(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"));
    }
}

Now I can get my object the way I need!

Profile myProfile = dbi.withHandle(new HandleCallback<Integer>(final Integer profileId) {
  public Profile withHandle(Handle h) {
    return h.createQuery("select * from profile WHERE id = :id")
      .map(new ProfileMapper())
      .bind("id", profileId)
      .first();
}});

I realize above samples are very simple and probably you’re thinking why not just use a ORM and write less code, conversely this solution is easier and allow fine grained control of sql statements with JOINS and SUB-QUERIES.

## A lot more …

This is just a quick intro! There are SQL Object Batching, Transactions and more on the documentation.

## ACK

Thanks to Brian McCallister for this awesome library and to José Diaz @__joselo for telling me about it.