Fluent Queries

Query instances are SQLStatement instances specialized for SQL queries. They are created from Handle instances, and support fluent-style method chaining.

Queries are parameterized with the type each row in the result set will be mapped to, for example:

DBI dbi = new DBI("jdbc:h2:mem:test");
Handle h = dbi.open();
h.execute("create table something (id int primary key, name varchar(100))");
h.execute("insert into something (id, name) values (1, 'Brian')");
h.execute("insert into something (id, name) values (2, 'Keith')");


Query<Map<String, Object>> q =
    h.createQuery("select name from something order by id");
Query<String> q2 = q.map(StringMapper.FIRST);
List<String> rs = q2.list();

assertThat(rs, equalTo(asList("Brian", "Keith")));

h.close();

The default representation of a result row is a Map<String, Object>, as can be seen when the Query is first created. To map the rows to something else we use a ResultSetMapper. A number of mappers are included, such as the StringMapper static instance we use here to extract a String from position 1 (the first thing) in each row.

Finally, we execute the query via one of several methods, in this case list(), which eagerly maps all rows and stores them in a List.

This example uses intermediate assignment to make clear what is happening at each step, more a more idiommatic way of writing the above would be to chain the calls to the initial Query:

DBI dbi = new DBI("jdbc:h2:mem:test");
Handle h = dbi.open();
h.execute("create table something (id int primary key, name varchar(100))");
h.execute("insert into something (id, name) values (1, 'Brian')");
h.execute("insert into something (id, name) values (2, 'Keith')");


List<String> rs = h.createQuery("select name from something order by id")
    .map(StringMapper.FIRST)
    .list();

assertThat(rs, equalTo(asList("Brian", "Keith")));

h.close();

There are several other methods for executing the query, depending on how you want to retrieve the results:

String rs = h.createQuery("select name from something order by id")
    .map(StringMapper.FIRST)
    .first();

assertThat(rs, equalTo("Brian"));

The first() method sets the max rows to 1, then extracts the value from just that first row of the result set.

The iterator() method works a bit differently then first() and list(). It returns an Iterator which lazily traverses the result set, leaving them open until either the iterator is closed, or the end is reached.

ResultIterator<String> rs = h.createQuery("select name from something order by id")
    .map(StringMapper.FIRST)
    .iterator();

assertThat(rs.next(), equalTo("Brian"));
assertThat(rs.next(), equalTo("Keith"));
assertThat(rs.hasNext(), equalTo(false));

rs.close();

Here we can see we actually get back a sub-interface of Iterator called ResultIterator which defines a close() method. We can call the close() to close the result set and prepared statement underlying the query.

If we know we will traverse to the end of the result set, we don’t need to call close() explicitely. The call to Iterator#hasNext which returns false will automatically close the results, so we can write the above as

Iterator<String> rs = h.createQuery("select name from something order by id")
    .map(StringMapper.FIRST)
    .iterator();

assertThat(rs.next(), equalTo("Brian"));
assertThat(rs.next(), equalTo("Keith"));
assertThat(rs.hasNext(), equalTo(false));

Additionally, Query implements Iterable so we can do things like

for (String name : h.createQuery("select name from something order by id").map(StringMapper.FIRST))
{
    assertThat(name, equalsOneOf("Brian", "Keith"));
}

The final means of traversing a result set is to fold across it)

StringBuilder rs = h.createQuery("select name from something order by id")
                    .map(StringMapper.FIRST)
                    .fold(new StringBuilder(), new Folder2<StringBuilder>()
                    {
                        public StringBuilder fold(StringBuilder acc, ResultSet rs, StatementContext ctx) throws SQLException
                        {
                            acc.append(rs.getString(1)).append(", ");
                            return acc;
                        }
                    });

rs.delete(rs.length() - 2, rs.length()); // trim the extra ", "
assertThat(rs.toString(), equalTo("Mark, Tatu"));

We supply an implementation of Folder2 which receives the first argument and first row to the fold() call to the initial invocation, and returns a value to be passed to the next invocation, along with the next row, and so on until the last row in the result set, the value returned from the last invocation will be returned from the fold() call.