Usual style of writing queries with JDBI is to embed them in method level annotations like below

1
2
3
4
    public interface WhyDao {
        @SqlQuery("select * from whytable where name = :name order by created_date")
        public abstract List<Why> getWhys(@Bind("name") String name);
    }

This type of embedding queries is good for small ones. Since java does not (yet) support multiline strings, this style gets messy with long queries (I am talking about queries that goes beyond 3 lines with sensible line widths).

Fortunately JDBI offers an alternate if you really wanted to keep the queries easily readable and editable.

1
2
3
4
5
    @OverrideStatementLocatorWith(QueryLocator.class)
    public interface WhyDao {
        @SqlQuery
        public abstract List<Why> getWhys(@Bind("name") String name);
    }

Above is the same class rewritten by externalizing the query string. The key element in this code is the annotation @OverrideStatementLocatorWith. It takes in a subclass of StatementLocator class (QueryLocator here) which will have the strategy to find the sql string to use.

An example implementation of StatementLocator might look like this

1
2
3
4
5
6
7
8
9
10
    public class QueryLocator implements StatementLocator {
        @Override
        public String locate(String name, StatementContext ctx) throws Exception {
            String query = Queries.get(name);
            if (query == null) {
                throw new RuntimeException("Unable to find any query for '" + name + "'");
            }
            return query;
        }
    }

name parameter will contain the method name (in our case ‘getWhys’) which can be use to find the appropriate query string. Queries class is my custom singleton class which loads all the queries from external text file(s) containing sqls during bootstrap.