SQL Object Queries

Queries are denoted on sql object interfaces via the @SqlQuery annotation on the query methods. The return type of the method indicates what to do with the result set. Take the following queries:

public interface SomeQueries
{
  @SqlQuery("select name from something where id = :id")
  String findName(@Bind("id") int id);

  @SqlQuery("select name from something where id > :from and id < :to")
  List<String> findNamesBetween(@Bind("from") int from, @Bind("to") int to);

  @SqlQuery("select name from something")
  Iterator<String> findAllNames();
}

The first method, findName infers that you want only the first result, and it will be mapped into a String. It will take the first element in the first row of the result set to return.

The second method, findNamesBetween will again infer that we are looking for a String, and pull the first string from each row of the result set. Because it returns a java.util.List it will eagerly map each row to a String and return the full result set.

The third method, findAllNames does the same String extraction from each row, but because the method returns a java.util.Iterator it loads results lazily, only traversing the result set as Iterator#next or Iterator#hasNext is called. The iterator returned is actually a ResultIterator. The underlying result set will be closed when the ResultIterator#close method is invoked, or when the end of the result set is reached.

As with String, mappings for singular primitive types in the first position of the result set are provided out of the box by JDBI. For more sophisticated mappings you can register ResultSetMapper or ResultSetMapperFactory instances with either the DBI, the Handle, or on the sql object or individual method. Take for example the following result set mapper and class it maps to:

public class Something
{
  private int id;
  private String name;
  
  public Something() { }

  public Something(int id, String name)
  {
    this.id = id;
    this.name = name;
  }

  public int getId()
  {
    return id;
  }

  public void setId(int id)
  {
    this.id = id;
  }

  public String getName()
  {
    return name;
  }

  public void setName(String name)
  {
    this.name = name;
  }
}

public class SomethingMapper implements ResultSetMapper<Something>
{
  public Something map(int index, ResultSet r, StatementContext ctx) throws SQLException
  {
    return new Something(r.getInt("id"), r.getString("name"));
  }
}

We can now make use of the SomethingMapper in a couple ways. The first is to register it on the sql object itself:

@RegisterMapper(SomethingMapper.class)
public interface AnotherQuery
{
  @SqlQuery("select id, name from something where id = :id")
  Something findById(@Bind("id") int id);
}

In this case whenever a query needs to map to Something it will find the registered mapper and try to use that.

Alternately, we can specify a result mapper to use for a specific method:

public interface YetAnotherQuery
{
  @SqlQuery("select id, name from something where id = :id")
  @Mapper(SomethingMapper.class)
  Something findById(@Bind("id") int id);
}

You can also register result set mappers on the DBI or Handle instance the sql object is attached to.