spring jdbc template mapping multiple rows to parent child entity

Question

We are using spring jdbc template (4.1.x) and we have nesting relationship where our parent is Customer. Customer can have 0 or more orders. Each order can have 1 or many lineitems. Each line item can have extactly one detail and status.

What we are looking for is map sql query result(one query with multiple join) to map this entities.

Does anyone know how it can be done on a row level?

Answer 1

You can use a ResultSetExtractor. Then you can process the ResultSet the usual way by iterating through it. However, you can reuse your RowMapper classes to avoid duplicating that code.

Example:

public class OrderExtractor implements ResultSetExtractor {

public List<Customer> extractData(ResultSet rs) throws SQLException, DataAccessException {

    Map<Number, Customer> customers = new HashMap<>();

    CustomerMapper customerMapper = new CustomerMapper();
    OrderMapper orderMapper = new OrderMapper();
    LineItemMapper lineItemMapper = new LineItemMapper();

    while (rs.next()) {

        Customer customer = customerMapper.mapRow(rs, -1);
        if (!customers.containsKey(customer.getCustomerId())) {
            customers.put(customer.getCustomerId(), customer);
        } else {
            customer = customers.get(customer.getCustomerId());
        }

        Order order = orderMapper.mapRow(rs, -1);
        if (!customer.hasOrder(order.getNumber())) {
            customer.addOrder(order);
        } else {
            order = customer.getOrder(order.getNumber());
        }

        LineItem lineItem = lineItemMapper.mapRow(rs, -1);
        if (!order.hasLineItem(lineItem.getNumber())) {
            order.addLineItem(lineItem);
        }

        // Add other child entities here
    }

    return new ArrayList<>(customers.values());
}

}