Mybatis之一对多，多对多查询

association是用于一对一和多对一，而collection是用于一对多的关系

一对一（订单和用户）：

<select id="findOrdersUser"
		resultType="com.mybatis.xdw.po.OrdersCustom">
		select orders.*,user.username,user.sex,user.address from
		orders,user where orders.user_id = user.id
</select>

<resultMap type="com.mybatis.xdw.po.Orders" id="OrdersUserResultMap">
	<id column="id" property="id" />
	<result column="user_id" property="userId" />
	<result column="number" property="number" />
	<result column="createtime" property="createtime" />
	<result column="note" property="note" />
<association property="user" javaType="com.mybatis.xdw.po.User">
	<id column="id" property="id" />
	<result column="username" property="username" />
	<result column="sex" property="sex" />
	<result column="birthday" property="birthday" />
	<result column="address" property="address" />
</association>
</resultMap>

 

一对多（用户对订单）：

<select id="findOrdersAndOrderDetailResultMap"
resultMap="OrdersAndOrderDetailResultMap">
	SELECT
        u.id as u_id,
		u.username,
		u.sex,
		u.address,
		o.*
		FROM
		USER u,
		orders o
		WHERE u.id = o.user_id
</select>

<resultMap type="com.mybatis.xdw.po.User" id="UserandOrdersResultMap" >
		<!-- property  实体类的属性  -->
		<id column="id" property="id" />
		<result column="username" property="username" />
		<result column="sex" property="sex" />
		<result column="birthday" property="birthday" />
		<result column="address" property="address" />
		<collection property="ordersList"
			ofType="com.mybatis.xdw.po.Orders">
			<id column="id" property="id" />
			<result column="user_id" property="userId" />
			<result column="number" property="number"/>
			<result column="createtime" property="createtime"/>
		</collection>
</resultMap>

多对多（用户对商品）： 

<resultMap type="com.mybatis.xdw.po.User" id="UserAndItemsResultMap">
			<id column="id" property="id" />
			<result column="username" property="username" />
			<result column="sex" property="sex" />
			<result column="birthday" property="birthday" />
			<result column="address" property="address" />
		<collection property="ordersList" ofType="com.mybatis.xdw.po.Orders">
				<id column="id" property="id" />
				<result column="user_id" property="userId" />
				<result column="number" property="number"/>
				<result column="createtime" property="createtime"/>
			<collection property="orderdetails" ofType="com.mybatis.xdw.po.Orderdetail">
					<id column="orderdetail_id" property="ordersId" />
					<result column="items_id" property="itemsId" />
					<result column="items_num" property="itemsNum" />
				<association property="items" javaType="com.mybatis.xdw.po.Items">
					<id column="id" property="id" />
					<result column="items_name" property="name" />
					<result column="items_price" property="price" />
					<result column="pic" property="pic" />
					<result column="items_detail" property="detail"/>
				</association>
			</collection>
		</collection>
	</resultMap>
	<select id="findUserAndItemsResultMap"
		resultMap="UserAndItemsResultMap">
		SELECT
		orders.*,
		USER.username,
		USER.sex,
		USER.address,
		orderdetail.id orderdetail_id,
		orderdetail.items_id,
		orderdetail.items_num,
		orderdetail.orders_id,
		items.name items_name,
		items.detail items_detail,
		items.price items_price 
		FROM
		orders,
		USER,
		orderdetail,
		items
		WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND
		orderdetail.items_id = items.id
	</select>