多个一对多关系 ResultSetExtractor [英] multiple one-to-many relations ResultSetExtractor

问题描述

假设我有一个对象有两种不同的一对多关系.很像:

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

客户 1<->M 个品牌和客户 1<->M 个订单

假设我的对象 Customer 有两个与这两个对象相关的列表.

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

我读过这个例子:http://forum.springsource.org/showthread.php?50617-rowmapper-with-one-to-many-query这解释了如何使用单个一对多关系来做到这一点.为方便起见，这里是 ResultSetExtractor 覆盖:

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?

推荐答案

从你的问题来看，我假设你有三个表；客户、品牌、订单.如果您想将 Customer 的 Brands 和 Orders 属性提取到您的客户对象中，其中 Brands 和 Orders 之间没有关系，我建议使用 UNION 查询.像这样:

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

查询:

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)

您的 ResultSetExtractor 将变成:

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

这篇关于多个一对多关系 ResultSetExtractor的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！