ParameterizedRowMapper That Maps Object List to Object

Question

I am trying to set the Parent List in a ParameterizedRowMapper how is this written or approached. I have two Objects one for parent and one for children however children contains a ListThe parents for each child are stored in a separate table in the database and the mapping is 1 - many.

The select for the records for the parents will be done in a separate ResultSet. Will the mapping have to be done separately (separate ParameterizedRowMapper), if so how will i have to write the ParameterizedRowMapper this is the major concern how ParameterizedRowMapper is written to accommodate a list items.

ParameterizedRowMapper

public static class ChildrenMapper implements ParameterizedRowMapper<Children>{         

         public Children mapRow(ResultSet rs, int rowNum) throws SQLException {
                Children child = new Children();
                child.setFirstName(rs.getString("firstName"));
                child.setLastName(rs.getString("lastName"));
                    //a child can have many Parents or gaurdians
                child.setParent(List<Parent>);                  

                return child;
            }       
    }

Based on my research i have found that i need to use ResultSetExtractor, however i have a questions on the use of that. Do i integrate it into the class at the point of setting the Parent? Can someone guide me on how it can be done the correct way

Children.java

Public class Children(){
int cid;
String firstName;
String lastName;
List<Parent>parents;
..

//getters/setters

}

Parent.java

Public class Parent(){
 int pid;
String firstName;
String lastName;
..

//setters/getters
}

Joeljoela

Answer 1

I will show how to do this for a canonical 1-to-many example, you can adapt it to your vo class / table.

Order class

public class Order {
    private Long orderId;
    private String user;
    private List<LineItem> items;
    // Getter / setter omitted
}

Item class

public class LineItem {
    private Long lineItemId;
    private String product;
    private int quantity;
    // Getter / setter omitted
}

Use two rowmappers one for each class and then use a result set extractor to convert multiple rows into one order + line items

OrderRepository

public final static RowMapper<Order> orderMapper = ParameterizedBeanPropertyRowMapper.newInstance(Order.class);
public final static RowMapper<LineItem> lineItemMapper = ParameterizedBeanPropertyRowMapper.newInstance(LineItem.class);

public Order findOrderWithItems(Long orderId) {
    return jdbcTemplate.query("select * from orders, line_item "
            + " where orders.order_id = line_item.order_id and orders.order_id = ?", 
            new ResultSetExtractor<Order>() {
        public Order extractData(ResultSet rs) throws SQLException, DataAccessException {
            Order order = null;
            int row = 0;
            while (rs.next()) {
                if (order == null) {
                    order = orderMapper.mapRow(rs, row);
                }
                order.addItem(lineItemMapper.mapRow(rs, row));
                row++;
            }
            return order;
        }

    }, orderId);
}

public List<Order> findAllOrderWithItmes() {
    return jdbcTemplate.query("select * from orders, line_item "
            + " where orders.order_id = line_item.order_id order by orders.order_id",
            new ResultSetExtractor<List<Order>>() {
                public List<Order> extractData(ResultSet rs) throws SQLException, DataAccessException {
                    List<Order> orders = new ArrayList<Order>();
                    Long orderId = null;
                    Order currentOrder = null;
                    int orderIdx = 0;
                    int itemIdx = 0;
                    while (rs.next()) {
                        // first row or when order changes
                        if (currentOrder == null || !orderId.equals(rs.getLong("order_id"))) {
                            orderId = rs.getLong("order_id");
                            currentOrder = orderMapper.mapRow(rs, orderIdx++);
                            itemIdx = 0;
                            orders.add(currentOrder);
                        }
                        currentOrder.addItem(lineItemMapper.mapRow(rs, itemIdx++));
                    }
                    return orders;
                }

            });
}

Windywindzer

I haven't tested it. It should work with some minor tweaks - especially if have columns with same name in both tables.. – Windywindzer

You can find a working version of this at - github.com/gkamal/jdbc-template-one-to-many – Windywindzer

won't compile. RowMapper isn't generic – Dominion

Use the 3.0 or later version of spring or get rid of generics and replace with casts. – Windywindzer