Mutable Ideas

Notes and ideas about Java, Scala, Big Data, NoSQL, Quality and Software Deploy

SQL Love With JDBI

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

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

Get your Handler

1
2
3
4
5
6
7
8
9
10
11
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.

1
2
3
4
5
6
Integer employeesCount = dbi.withHandle(new HandleCallback<Integer>(final Integer id) {
  public Integer withHandle(Handle h) {

    // query goes here

}});

Execute your Query

1
2
3
4
5
6
7
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:

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
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!

1
2
3
4
5
6
7
Profile myProfile = dbi.withHandle(new HandleCallback<Integer>(final Integer profileId) {
  public Profile withHandle(Handle h) {
    return h.createQuery("select count(1) 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.