假设我们现在有这样的需求，想知道某一位顾客购买了哪些商品，但是通过观察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();
}
}
查询结果
从图中可以发现，在customer的orderList属性下有2个订单，每个订单里会包含一个或多个订单项orderItems，每个orderItem又会对应一个product。
这就是所谓的多对多查询，嵌套关系多一些。