Search code examples
javamysqljdbi

Can I get back subsets of fields from JDBI?


I need to get the latest few distinct values from a particular table, and I'm doing it like so:

@SqlQuery("SELECT DISTINCT object_id, MAX(created) FROM my_table WHERE user_id= :userId AND type= :type GROUP BY object_id ORDER BY MAX(created) DESC, object_id LIMIT :count")
??? getLatestDistinctObjects(@Bind("userId") long userId, @Bind("type") int type, @Bind("count") int count);

The query works fine, but I can't work out what the return type should be. If I'm returning one column that's fine (I get a List<int>, but I can't exclude MAX(created) here for the query to work. What return type should I use?

I have tried just returning Object and inspecting the result, but it just returns null.


Solution

  • The default mapper in JDBI will construct a object using reflection and set fields using setter methods. When you used Object as return type, it wouldn't have any setter for the fields present in the result set.

    Ideally we should have some object which is representing the result set. If you don't want to write boilerplate code only for this query, next best option in my opinion is Map in which you can have key, value pairs.

    @SqlQuery(....)
    @Mapper(MapMapper.class)
    public Map getLatestDistinctObjects(params ...);
    

    But you need to define mapper for constructing Map. You can write this mapper generically so that you can use that for any map and other use case as well. One way of having generic mapper could be,

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