How to select an aggregate query result into tuples using JDBI annotations?

Question

I'm using JDBI and I need to run a query using aggregate functions.

How would I go about reading the result from this query? What return type can I use to make it convenient?

@SqlQuery("select count(*), location from Customers group by location")
public Type getCustomersCountByLocation();

I could possibly add an alias to the aggregate function result and write a matching POJO

@SqlQuery("select count(*) as customerCount, location from Customers group by location")
public List<CustomerLocation> getCustomersCountByLocation();

The POJO being:

public class CustomerLocation {

    private int customerCount;

    private String location;

    public CustomerLocation(int customerCount, String location) {
        this.customerCount = customerCount;
        this.location = location;
    }

    //getters
}

But that seems like a lot of unnecessary boilerplate. I could write a one-size-fits-all object for this kind of queries but that would introduce unnecessary coupling.

Does JDBI support any type OOTB that would allow me to select the result of my query into an arbitrary n-tuple parametrized with the right types?

Pseudocode:

@SqlQuery("select count(*) as customerCount, location from Customers group by location")
public List<Tuple<Integer, String>> getCustomersCountByLocation();

score 2 · Answer 1 · edited May 23 '17 at 11:45

The other answer is a very good one but I just wanted to post one answering the specific question is someone's wondering.

What Manikandan suggested can be done with an org.apache.commons.lang3.tuple.Pair.

@SqlQuery("select count(*) as customerCount, location from Customers group by location")
@Mapper(CustomerCountByLocationMapper.class)
public List<Pair<String, Integer>> getCustomersCountByLocation();

Then in the mapper class:

public class CustomerCountByLocationMapper implements ResultSetMapper<Pair<String, Integer>> {

    @Override
    public Pair<String, Integer> map(int index, ResultSet r, StatementContext ctx) throws SQLException {
        String location = r.getString("location");
        Integer customerCount = r.getInt("customerCount");
        return Pair.of(source, count);
    }
}

In this case, the getCustomersCountByLocation method will return a List<Pair<String,Integer>>, which, as pointed out by the other answer, is a silly type and a list of pairs with this kind of semantics is effectively a map.

At the same time, the ResultSetMapper interface is flexible enough to allow mapping to completely arbitrary types. In a more fitting context, a Pair can be used with just several lines of code.

score 1 · Accepted Answer · answered Feb 05 '16 at 18:15

You could use Map instead. You need to write mapper once and it can be used for all aggregation queries and can be used for other use cases as well.

@SqlQuery("select count(*) as customerCount, location from Customers group by location")
@Mapper(MapMapper.class)
public Map getCustomersCountByLocation();

And define mapper like this.

public class MapMapper implements ResultSetMapper<Map<String, Integer>> {
    @Override
    public Map<String, Integer> map(int index, ResultSet r, StatementContext ctx) throws SQLException {
        HashMap<String, Integer> result = new HashMap<>();
        for(int i =1; i <= r.getMetaData().getColumnCount(); i++) {
            String columnName = r.getMetaData().getColumnName(i);
            Integer value = r.getInt(i);
            result.put(columnName, value);
        }
        return result;
    }
}

Good suggestion, a map might be easier to interpret. I completely forgot about the existence of these mappers, even though I had one for the POJOs already. Also didn't realize I could assign a mapper to a specific query. — toniedzwiedz, Feb 07 '16 at 09:06
Thanks. You can actually assign mapper in multiple levels. Method level has higher priority, then class level and then application level mapper. — Manikandan, Feb 07 '16 at 15:41

How to select an aggregate query result into tuples using JDBI annotations?

2 Answers2