Map jdbi query result to a map keyed on a column with a list of values

Question

Imagine a scenario where I am querying the orders table for a list of users and trying to find their orders. I am trying to get a map, with a keyset of user ids, and a list of order objects. return type is similar to this - Map<Integer, List>

 JdbiHelper.jdbi(readOnly).withHandle(
                handle -> handle.createQuery(query)
                        .bindList("user_id", userIds).?

The query itself is like :-

SELECT * FROM orders WHERE user_id in (<userIds>)

How do I get a map, with key as user ids and list of order objects as values?

I was trying something along these lines

    JdbiHelper.jdbi(readOnly).withHandle(
                handle -> handle.createQuery(query)
                        .bindList("user_id", userIds)
                        .map((rs, ctx) -> Pair.of(rs.getInt("user_id"), rs))
                        .collect(toMap(Pair::getLeft, Pair::getRight))

but, it didn't work.

Answer 1

You need a result mapper, e.g. an OrderMapper which turns a result set into an Order object.

Here is a solution using the Guava ImmutableListMultimap (which maps onto a list or results). Otherwise, you have to write your own Collector that collects the resulting Order objects into a Map<Integer, List<Order>>.

OrderMapper mapper = new OrderMapper();

Multimap<Integer, Order> results = JdbiHelper.jdbi(readOnly).withHandle(
                handle -> handle.createQuery("SELECT * from orders WHERE user_id IN (<user_id>)")
                        .bindList("user_id", userIds)
                        .mapTo(Order.class)
                        .collect(toImmutableListMultimap(Order::getUserId, Functions.identity())));

Or you can register the OrderMapper with Jdbi using Jdbi.registerColumnMapper(new OrderMapper()) and use .mapTo(Order.class) instead of the .map(mapper).

In any case you need a mapper to turn the result set into an Order object.

public class OrderMapper implements ColumnMapper<Order> {

    public Order map(ResultSet r, int columnNumber, StatementContext ctx) throws SQLException {
        return new Order().setUserId(rs.getInt("user_id"),
                ... additional columns ...
        }
    }