java - 合并使用 1 :* relationships into one query 的三个查询

Question

假设我有四个表要读取:

客户

customer_id,    customer_name
1               Joe Bolggs

客户订单

customer_id, order_no
----------------------------
1       1
1       2
1       3

客户地址

customer_id address
----------------------------
1       11 waterfall road
1       23 The broadway

customer_tel_no

customer_id number
----------------------------
1       523423423432
1       234342342343

上面显示的客户信息(对于 id=1 的客户)将存储在 Java 对象中，如下所示

public class Customer{

    String customer_id;
    String customerName;
    ArrayList<String> customerOrders;
    ArrayList<String> customerAddress;
    ArrayList<String> customerTelephoneNumbers;
} 

我能想到的获取上述信息的唯一方法是使用三个查询。原因是客户表和其他每个表之间存在 1:* 关系。为了获取数据，我正在做这样的事情:

Customer customer = new Customer()

String customerSQL = "Select * from customer where customer_id = ?";
statement = getConnection().prepareStatement(contactsQuery);
statement.setString(1,1);
resultSet = statement.executeQuery(); 

while (resultSet.next()){
    customer.customer_id = resultSet.get(1);    //No getters/setters in this example
    customer.customerName = resultSet.get(2);
}

String customerOrdersSQL = "Select * from customer_orders where customer_id = ?";
statement = getConnection().prepareStatement(customerOrdersSQL); 
statement.setString(1,1);
resultSet = statement.executeQuery(); 

customer.customerOrders = new ArrayList();
while (resultSet.next()){
    customer.customerOrders.add(resultSet.getString(2);  // all the order numbers
}


String customerAddressesSQL = "Select * from customer_addresses where customer_id = ?";
statement = getConnection().prepareStatement(customerAddressesSQL ); 
statement.setString(1,1);
resultSet = statement.executeQuery(); 

customer.customerAddresses = new ArrayList();
while (resultSet.next()){
    customer.customerAddresses.add(resultSet.getString(2);  // all the addresses
}


String customerTelSQL = "Select * from customer_tel_no where customer_id = ?";
statement = getConnection().prepareStatement(customerTelSQL); 
statement.setString(1,1);
resultSet = statement.executeQuery(); 

customer.customerTelephoneNumbers = new ArrayList();
while (resultSet.next()){
    customer.customerTelephoneNumbers.add(resultSet.getString(2);  // all the order numbers
}

上述问题是我对数据库进行了三次调用。有没有办法可以将以上内容合并到一个查询中？

我看不到联接如何工作，因为例如，customer 和 customer_orders 之间的联接将为 customer_orders 中的每一行返回一个客户行。无论如何，我可以将上述三个查询合并为一个吗？

Answer 1

我认为这样的事情会起作用:

SELECT c.customer_id, c.customer_name, o.order_no, a.address, t.number
FROM customer c LEFT JOIN customer_orders o ON c.customer_id = o.customer_id
LEFT JOIN customer_addresses a ON c.customer_id = a.customer_id
LEFT JOIN customer_tel_no t ON c.customer_id = t.customer_id
WHERE c.customer_id = ?

然后，在您的代码中，执行查询后:

while (resultSet.next())
{
    customer.customerOrders.add(resultSet.getString(3));
    customer.customerAddresses.add(resultSet.getString(4));
    customer.customerTelephoneNumbers.add(resultSet.getString(5));
}

当然，这并没有考虑到您在此过程中将会有空值的事实，因此我建议检查空值以确保您不会向数组列表中添加大量垃圾。尽管如此，这可能比 3 个单独查询的成本要低得多。