multiple one-to-many relations ResultSetExtractor

Question

Let's say I have an object with two different one-to-many relations. Much like:

Customer 1<->M Brands and Customer 1<->M Orders

And let's say that the my object Customer has two lists related to those two objects.

I've read this example: http://forum.springsource.org/showthread.php?50617-rowmapper-with-one-to-many-query which explains how to do it with a single one-to-many relationship. For your convenience here's the ResultSetExtractor override:

private class MyObjectExtractor implements ResultSetExtractor{

    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
        Map<Integer, MyObject> map = new HashMap<Integer, MyObject>();
        MyObject myObject = null;
        while (rs.next()) {
            Integer id = rs.getInt("ID);
            myObject = map.get(id);
          if(myObject == null){
              String description = rs,getString("Description");
              myObject = new MyObject(id, description);
              map.put(id, myObject);
          }
      MyFoo foo = new MyFoo(rs.getString("Foo"), rs.getString("Bar"));
      myObject.add(myFoo);
        }
        return new ArrayList<MyObject>(map.values());;
    }
}

I don't think it covers how to work with both. What would be the cleanest approach? Is there a simpler way than to iterate with conditions? Would sets be better off than lists in this case?

Solution 1

From your question, I assume that you have three tables; Customer, Brands, Orders. If you want to fetch the Brands and Orders properties of the Customer to your customer object, where there is no relationship between Brands and Orders, what I suggest is to use a UNION query. Something like this:

TBL_CUSTOMER
------------
CUSTOMER_ID
CUSTOMER_ACCOUNT_NO
CUSTOMER_NAME

TBL_CUSTOMER_BRANDS
-------------------
CUSTOMER_BRAND_ID            - UK
BRAND_NAME
CUSTOMER_ID                  - FK

TBL_ORDERS
-------------------
ORDER_ID                     - UK
CUSTOMER_ID                  - FK

Query:

SELECT CUS.*, BRANDS.CUSTOMER_BRAND_ID COL_A, BRANDS.BRAND_NAME COL_B, 1 IS_BRAND FROM TBL_CUSTOMER CUS JOIN TBL_CUSTOMER_BRANDS BRANDS ON (CUS.CUSTOMER_ID = BRANDS.CUSTOMER_ID)
UNION ALL
SELECT CUS.*, ORDERS.ORDER_ID, '', 0 IS_BRAND FROM TBL_CUSTOMER CUS JOIN TBL_ORDERS ORDERS ON (CUS.CUSTOMER_ID = ORDERS.CUSTOMER_ID)

Your ResultSetExtractor will become:

private class MyObjectExtractor implements ResultSetExtractor{

    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            Map<Long, Customer> map = new HashMap<Long, Customer>();

        while (rs.next()) {
            Long id = rs.getLong("CUSTOMER_ID");
            Customer customer = map.get(id);
            if(customer == null){
                customer = new Customer();
                customer.setId(id);
                customer.setName(rs.getString("CUSTOMER_NAME"));
                customer.setAccountNumber(rs.getLong("CUSTOMER_ACCOUNT_NO"));
                map.put(id, customer);
                    }

            int type = rs.getInt("IS_BRAND");
            if(type == 1) {
                List brandList = customer.getBrands();
                if(brandsList == null) {
                    brandsList = new ArrayList<Brand>();
                    customer.setBrands(brandsList);
                }
                Brand brand = new Brand();
                brand.setId(rs.getLong("COL_A"));
                brand.setName(rs.getString("COL_B"));
                brandsList.add(brand);
            } else if(type == 0) {
                List ordersList = customer.getOrders();
                if(ordersList == null) {
                    ordersList = new ArrayList<Order>();
                    customer.setOrders(ordersList);
                }
                Order order = new Order();
                order.setId(rs.getLong("COL_A"));
                ordersList.add(order);
            }
        }
        return new ArrayList<Customer>(map.values());
    }
}

Solution 2

I think there is no better way than to iterate over all rows, extract the two different objects and add it to a List<Brand> and List<Order> within the Customer object.

So you would end up in a customer object:

public class Customer {
     private List<Brand> brands;
     private List<Order> orders;
....
}

There was an issue on SpringSource regarding a mutliple rowmapper: https://jira.springsource.org/browse/SPR-7698

but there's only one comment linking to a one-to-many resultset extractor: https://github.com/SpringSource/spring-data-jdbc-ext/blob/master/spring-data-jdbc-core/src/main/java/org/springframework/data/jdbc/core/OneToManyResultSetExtractor.java

I think you're doing it right if you really need eager fetching. If you'd need lazy fetching you could load the respective orders and brands on access during runtime. That's how Hibernate and other ORM frameworks do it. It depends on your scenario and what you do with the object.

Solution 3

I assume the model described by James Jithin in his answer:

TBL_CUSTOMER
------------
CUSTOMER_ID
CUSTOMER_ACCOUNT_NO
CUSTOMER_NAME

TBL_CUSTOMER_BRANDS
-------------------
CUSTOMER_BRAND_ID            - UK
BRAND_NAME
CUSTOMER_ID                  - FK

TBL_ORDERS
-------------------
ORDER_ID                     - UK
CUSTOMER_ID                  - FK

Instead of going for one Query, I would suggest the following three:

SELECT CUS.* FROM TBL_CUSTOMER CUS 

SELECT BRANDS.CUSTOMER_ID, BRANDS.CUSTOMER_BRAND_ID, BRANDS.BRAND_NAME FROM TBL_CUSTOMER_BRANDS BRANDS

SELECT ORDERS.CUSTOMER_ID, ORDERS.ORDER_ID FROM TBL_ORDERS ORDERS 

Your RowCallbackHandlers would become:

private class CustomerRowCallbackHandler  implements RowCallbackHandler {

    private final Map<Long, Customer> customerMap;

    public BrandRowCallbackHandler(Map<Long, Customer> customerMap) { this.customerMap = customerMap}

    public void processRow(ResultSet rs) throws SQLException {
            Long id = rs.getLong("CUSTOMER_ID");
            Customer customer = map.get(id);
            if(customer == null){
                customer = new Customer();
                customer.setId(id);
                customer.setName(rs.getString("CUSTOMER_NAME"));
                customer.setAccountNumber(rs.getLong("CUSTOMER_ACCOUNT_NO"));
                map.put(id, customer);
                    }
    }
}

private class BrandRowCallbackHandler implements RowCallbackHandler {

    private final Map<Long, Customer> customerMap;

    public BrandRowCallbackHandler(Map<Long, Customer> customerMap) { this.customerMap = customerMap}

    public void processRow(ResultSet rs) throws SQLException {
            Long id = rs.getLong("CUSTOMER_ID");
            Customer customer = map.get(id);
            if(customer != null){
                List brandList = customer.getBrands();
                if(brandsList == null) {
                    brandsList = new ArrayList<Brand>();
                    customer.setBrands(brandsList);
                }
                Brand brand = new Brand();
                brand.setId(rs.getLong("CUSTOMER_BRAND_ID"));
                brand.setName(rs.getString("CUSTOMER_BRAND_NAME"));
                brandsList.add(brand);
            } 
    }
}

private class OrderRowCallbackHandler implements RowCallbackHandler {

    private final Map<Long, Customer> customerMap;

    public OrderRowCallbackHandler(Map<Long, Customer> customerMap) { this.customerMap = customerMap}

    public void processRow(ResultSet rs) throws SQLException {
            Long id = rs.getLong("CUSTOMER_ID");
            Customer customer = map.get(id);
            if(customer != null){
                List ordersList = customer.getOrders();
                if(ordersList == null) {
                    ordersList = new ArrayList<Order>();
                    customer.setOrders(ordersList);
                }
                Order order = new Order();
                order.setId(rs.getLong("ORDER_ID"));
                ordersList.add(order);
            }
    }
}

Solution 4

If I really had to do it, I would prefer RowCallbackHandler over ResultSetExtractor. See RowCallbackHandler api and JDBCTemplate api.

In this case you need to collect the resulting Customers collection yourself in the handler. Sets can help to filter out duplicates.

View more solutions

Comments

[tkr]

This is what I understood from the question. Still I wonder why not to separate the queries and resultset extractors. It would make them much more comprehensible.

[Nimchip]

Thank you, this is precisely what i was looking for. I forgot all about Unions working with ORMs and decently normalized tables.

[Nimchip]

@tkr what do you mean by separating them?

[tkr]

It is a little bit big for a comment, see my answer below. Please be aware, that I do not have an IDE here, so probabaly the code contains some errors. I also used RowCallbackHandler as suggested by Vasyl as it removes the necessarity of calling rs.next() and returning an object.

[Anton Belev]

A bit of recommendation here from a immutable objects fan. Instead of checking if brandList is null every time -> create a all field constructor in the Customer class and initialize the brandList there. Also I'd prefer to initialize my Customer object using constructor instead setting each field separately (personal preference).