假設我們現在有這樣的需求，想知道某一位顧客購買了哪些商品，但是通過觀察customers表和products表，我們發現這兩張表並沒有關聯，那麼怎麼辦呢？通過前面的例子，我們可以發現，經由orders和orderitems這兩張表，customers表和products表能建立起間接聯絡。
我們以customer表作為主表，那麼查詢的語句應該是
SELECT c.*, o.*, oi.*, p.*
FROM customers AS c, orders AS o, orderitems AS oi, products AS p
WHERE c.cust_id=o.cust_id
AND o.order_num=oi.order_num
AND oi.prod_id=p.prod_id
複製程式碼根據上一節的解釋，如果我們使用resultType來定義返回型別，那麼定義的POJO類將會包含很多相似的部分，所以最好還是採用resultMap來定義返回的型別，這樣我們就可以在原有的Customer類上擴充套件屬性來儲存訂單，那麼訂單屬性內部就會包含訂單項，而訂單項就會跟具體的商品關聯，關聯關係看上面的圖很容易明白。
那麼我們具體來看看類被我們定義成什麼樣子了。
POJO定義
public class Customer {
private Integer custId;
private String custName;
private String custAddress;
private String custCity;
private String custState;
private String custZip;
private String custCountry;
private String custContact;
private String custEmail;
//新增的orderList用於儲存該客戶所下的訂單
List<Orders> ordersList;
複製程式碼Orders類中我們把原先的Customer屬性刪去，別的都不動
Orders中包含OrderItems屬性，OrderItems這麼定義：
public class OrderItems {
private Integer orderNum;
private Integer orderItem;
private String prodId;
private Integer quantity;
private Double itemPrice;
//新增的商品類，那麼就可以看到訂單項中包含的商品的具體資訊
private Products products;
複製程式碼定義resultMap
<resultMap id="CustomerAndProductsMap" type="com.shuqing28.pojo.Customer">
<id column="cust_id" property="custId"/>
<result column="cust_name" property="custName"/>
<result column="cust_address" property="custAddress"/>
<result column="cust_city" property="custCity"/>
<result column="cust_state" property="custState"/>
<result column="cust_zip" property="custZip"/>
<result column="cust_country" property="custCountry"/>
<result column="cust_contact" property="custContact"/>
<result column="cust_email" property="custEmail"/>
<collection property="ordersList" ofType="com.shuqing28.pojo.Orders">
<id column="order_num" property="orderNum"/>
<result column="order_date" property="orderDate"/>
<result column="cust_id" property="custId"/>
<collection property="orderItems" ofType="com.shuqing28.pojo.OrderItems">
<id column="order_num" property="orderNum"/>
<id column="order_item" property="orderItem"/>
<result column="prod_id" property="prodId"/>
<result column="quantity" property="quantity"/>
<result column="item_price" property="itemPrice"/>
<association property="products" javaType="com.shuqing28.pojo.Products">
<id column="prod_id" property="prodId"/>
<result column="vend_id" property="vendId"/>
<result column="prod_name" property="prodName"/>
<result column="prod_price" property="prodPrice"/>
<result column="prod_desc" property="prodDesc"/>
</association>
</collection>
</collection>
</resultMap>
複製程式碼我們從resultMap的定義中就可以看出從Customer到Products的巢狀關係。下面繼續看查詢語句定義：
<select id="findCustomerProducts" resultMap="CustomerAndProductsMap">
SELECT c.*, o.*, oi.*, p.*
FROM customers AS c, orders AS o, orderitems AS oi, products AS p
WHERE c.cust_id=o.cust_id
AND o.order_num=oi.order_num
AND oi.prod_id=p.prod_id
</select>
複製程式碼通過一系列內連線，查詢出與customer有關聯的products。
定義介面：
public List<Customer> findCustomerProducts();
複製程式碼測試程式碼：
@Test
public void findCustomersProductsMap(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class);
List<Customer> customerProducts = ordersDao.findCustomerProducts();
System.out.println(customerProducts);
} finally {
sqlSession.close();
}
}
複製程式碼查詢結果
這就是所謂的多對多查詢，巢狀關係多一些。