MyBatis-高级映射：多对多

数据库表的实现请参照：MyBatis-高级映射:一对一
Map映射文件中collection实现对多，association实现对一。

1.resultType和resultMap的选择

其实很多查询结果都是可以用resultType来实现，只有当想实现某个查询结果中包含一个子list，即出现list嵌套时，才会用到resultMap。resutType的方法相对比较简单，建议除非有嵌套list需求，其他都使用resultType方式。以下是用resultMap方式进行多对多查询的例子。

2.数据表关系分析

查询用户表，找出用户的所有订单，按照订单查找所有的订单商品明细。
订单和订单商品明细之间构成了多对多的关系。OrderDetail表和Item之间是一对一的关系。

3.构建POJO类

用户映射用户-所有订单的POJO类

package pojo;

import java.util.List;

public class UserOrderDetailItem extends User {
	private List<UserOrder> userOrderList;

	public List<UserOrder> getUserOrderList() {
		return userOrderList;
	}

	public void setUserOrderList(List<UserOrder> userOrderList) {
		this.userOrderList = userOrderList;
	}

	@Override
	public String toString() {
		String headString;
		for (UserOrder userOrder : userOrderList) {
			System.out.println("userid : " + this.getId());
			headString = "";
			headString = "    " + headString;
			System.out.println(headString + "orderid : " + userOrder.getId());
			headString = headString + "    ";
			for (UserOrderDetail userOrderDetail : userOrder.getUserOrderDetailList()) {
				System.out.println(headString + "OrderDetail:" + userOrderDetail.getId());
				
				System.out.println(headString + "    " + "ItemId:" + userOrderDetail.getItem().getId());
				System.out.println(headString + "    " + "ItemName:" + userOrderDetail.getItem().getName());
			}
		}
		return "";
	}
}

映射订单-订单详细的类

package pojo;

import java.util.List;

public class UserOrder extends Order{
	private List<UserOrderDetail> userOrderDetailList;

	public List<UserOrderDetail> getUserOrderDetailList() {
		return userOrderDetailList;
	}

	public void setUserOrderDetailList(List<UserOrderDetail> userOrderDetailList) {
		this.userOrderDetailList = userOrderDetailList;
	}

	@Override
	public String toString() {
		return "UserOrder [UserOrderDetail=" + userOrderDetailList + ":" + super.toString() + "]";
	}

}

映射订单详细-商品Item的类

package pojo;

public class UserOrderDetail extends OrderDetail{
	private Item item;

	public Item getItem() {
		return item;
	}

	public void setItem(Item item) {
		this.item = item;
	}
}

4.完成mapper映射文件

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper  
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
<mapper namespace="mapper.UserOrderDetailItemMapper"> 
	<resultMap id="UserOrderDetailItemMap" type="pojo.UserOrderDetailItem">
		<id column="id" property="id"/>
		<result column="username" property="username" />
		<result column="birthday" property="birthday"/>
		
		<collection property="userOrderList" ofType="pojo.UserOrder">
			<id column="order_id" property="id" />
			<collection property="userOrderDetailList" ofType="pojo.UserOrderDetail">
				<id column="detail_id" property="id"/>
				<association property="item" javaType="pojo.Item"><!-- 一个订单明细只有一个商品 -->
					<id column="item_id" property="id"/>
					<result column="name" property="name"/>
				</association>
			</collection>
		</collection>
	</resultMap> 
	
	<select id="selectUserOrderDetailItem" resultMap="UserOrderDetailItemMap">
		select user.*, `order`.id as order_id, orderdetail.id as detail_id, item.id as item_id, item.name
		from user, `order`, orderdetail, item 
		where (`order`.user_id=user.id) and (orderdetail.order_id=`order`.id) and (orderdetail.item_id = item.id);
	</select>
</mapper>  

5.测试类

package test;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import mapper.UserOrderDetailItemMapper;
import pojo.UserOrderDetailItem;

public class UserOrderTest {
	public static void main(String[] args) throws Exception{
		InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
		SqlSession session = sessionFactory.openSession(true);
		UserOrderDetailItemMapper userOrderDetailItemMapper = session.getMapper(UserOrderDetailItemMapper.class);
		
		List<UserOrderDetailItem> list = userOrderDetailItemMapper.selectUserOrderDetailItem();
		System.out.println(list);
	}
}

<完>