Skip to main content

Mutable Ideas

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

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

## Get your Handler

Handle h = null;
try {
  h =;


} 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> {

    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)

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.