问题描述
假设我有一个具有两个不同的一对多关系的对象。非常类似于：
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.
我读过这个例子：
，它解释了如何使用单一的一对多关系来完成它。为方便起见，这里的 ResultSetExtractor 覆盖：
I've read this example:http://forum.springsource.org/showthread.php?50617-rowmapper-with-one-to-many-querywhich 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?
推荐答案
从您的问题来看，我假设您有三个表：客户，品牌，订单。如果您想获取品牌和订单属性客户对您的客户对象，品牌和订单之间没有关系，我建议使用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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！