Mybatis多表查询

根据一个简单的订单案列理解resultMap
表中一个订单对应一个唯一用户
查询到的字段名与带有user属性的Orders的映射结果集未OrdersCustomMapOfUser，表中查询字段名与属性一一对应
association – 一个复杂类型的关联;许多结果将包装成这种类型
嵌套结果映射 – 关联可以指定为一个 resultMap 元素，或者引用一个

<resultMap type="com.lin.mybatisdemo.po.Orders" id="OrdersCustomMapOfUser">
		<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="order_num" property="orderNum" />
		<result column="createTime" property="createTime"/>
		<result column="note" property="note"/>
		<association property="user" javaType="com.lin.mybatisdemo.po.User" >
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
		</association>
	</resultMap>
	<!-- 查询订单扩展（含有orders信息扩展用户信息和订单明细-->
	<select id="queryOrdersListResultMap" resultMap="OrdersCustomMapOfUser">
		SELECT
			orders.id,orders.user_id,
			orders.order_num,orders.createTime,orders.note,
			user.username,user.sex
		FROM
			orders,
			user
		WHERE
			orders.user_id = user.id
	</select>

表中一个订单对应一个唯一用户，且一个订单对应多个订单明细，所以映射结果集resultMap如下，一个继承属性调用resultMap
collection – 一个复杂类型的集合
嵌套结果映射 – 集合可以指定为一个 resultMap 元素，或者引用一个

<!-- 该resultMap 映射orders带有user和orderdetails -->
	<!-- 使用extend属性 引用带有user的orders的resultMap 减少重复代码 -->
	<resultMap type="com.lin.mybatisdemo.po.Orders" id="OrdersCustomMapOfUserAndDetails" extends="OrdersCustomMapOfUser">
		<collection property="orderDetails" ofType="com.lin.mybatisdemo.po.OrderDetail">
			<id column="ordertail_id" property="id"/>
			<result column="id" property="orderId"/>
			<result column="item_id" property="itemId"/>
			<result column="item_num" property="itemNum"/>
		</collection>
	</resultMap>
	<!-- 查询订单扩展包括user，orderdetail 
		一个订单只有一个user，一个订单对应多个订单明细
		user在Orders中属于单例对象
		ordertails在Orders中属于集合对象
	-->
	<select id="querOrdersCustomList" resultMap="OrdersCustomMapOfUserAndDetails">
		SELECT
		orders.id,orders.user_id userId,orders.order_num,orders.createTime,
		user.username,user.sex,
		orderdetail.id ordertail_id,
		orderdetail.item_id,orderdetail.item_num
		FROM
			orders,
			user,
			orderdetail
		WHERE
			orders.user_id = user.id
		AND orderdetail.order_id = orders.id
	</select>
查询一对多，多对多

<!-- 查询订单扩展包括user，orderdetail,item
		一对一使用 association 
		一对多使用collection
		订单与用户关系一对一，订单与订单详细关系一对多
		订单与货物一对一
		所以使用结构可以预测为
		<resultMap>
			<association/> 用户
			<collection>  订单详细
				<association/> 货物
			</collection>
		</resultMap>
	 -->
	 <resultMap type="com.lin.mybatisdemo.po.Orders" id="OrdersOfAllInfo">
	 	<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="order_num" property="orderNum" />
		<result column="createTime" property="createTime"/>
		<result column="note" property="note"/>
		<association property="user" javaType="com.lin.mybatisdemo.po.User" >
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
		</association>
	 	<collection property="orderDetails" ofType="com.lin.mybatisdemo.po.OrderDetail">
			<id column="ordertail_id" property="id"/>
			<result column="id" property="orderId"/>
			<result column="item_id" property="itemId"/>
			<result column="item_num" property="itemNum"/>
			<association property="item" javaType="com.lin.mybatisdemo.po.Items">
				<id column="item_id" property="id"/>
				<result column="name" property="name"/>
				<result column="price" property="price"/>
				<result column="picture" property="picture"/>
			</association>
		</collection>
	 </resultMap>
	 <select id="queryOrdersListAllInfo" resultMap="OrdersOfAllInfo">
		SELECT
			orders.id,
			orders.user_id,
			orders.order_num,
			orders.createTime,
			USER .username,
			USER .sex,
			orderdetail.id ordertail_id,
			orderdetail.item_id,
			orderdetail.item_num,
			items. NAME,
			items.price,
			items.picture
		FROM
			orders,
			USER,
			orderdetail,
			items
		WHERE
			orders.user_id = USER .id
		AND orderdetail.order_id = orders.id
		AND orderdetail.item_id = items.id
	 </select>
查询所有用户每个用户的所有订单

<!-- 
		该表对应一个用户有多个订单，每个订单有多个明细，每个明细有一个商品信息
		一对多，多对多，然后一对一关系
		所以预测resultMap结构
		<resultMap>
			<id/>
			<result/>
			<collection> ordersList
				<id/>
				<result/>
				<collection> ordersDetailList
					<id/>
					<result/>
					<association>  item
						<id/>
						<result/>
					</association>
				</collection>
			</collection>
		</resultMap>
	 -->
	<resultMap type="com.lin.mybatisdemo.dto.UserQueryVo" id="UserAllOrders">
		<id column="id" property="id"/>
		<result column="username" property="username"/>
		<result column="sex" property="sex"/>
		<collection property="ordersList" ofType="com.lin.mybatisdemo.po.Orders">
			<id column="order_id" property="id"/>
			<result column="id" property="userId"/>
			<result column="order_num" property="orderNum" />
			<result column="createTime" property="createTime"/>
			<collection property="orderDetails" ofType="com.lin.mybatisdemo.po.OrderDetail">
				<id column="ordertail_id" property="id"/>
				<result column="order_id" property="orderId"/>
				<result column="item_id" property="itemId"/>
				<result column="item_num" property="itemNum"/>
				<association property="item" javaType="com.lin.mybatisdemo.po.Items">
					<id column="item_id" property="id"/>
					<result column="name" property="name"/>
					<result column="price" property="price"/>
					<result column="picture" property="picture"/>
				</association>
			</collection>
		</collection>
	</resultMap>
	<select id="queryUserAllOrders" resultMap="UserAllOrders">
		SELECT
			`user`.id,`user`.username,`user`.sex,
			orders.id order_id,
			orders.order_num,
			orders.createTime,
			orderdetail.id ordertail_id,
			orderdetail.item_id,
			orderdetail.item_num,
			items. NAME,
			items.price,
			items.picture
		FROM
			user,
			orders,
			orderdetail,
			items
		WHERE
			orders.user_id = USER .id
		AND orderdetail.order_id = orders.id
		AND orderdetail.item_id = items.id
	</select>
Orders（订单实体扩展带有user，orderDetails）

public class Orders {
	
	private Integer id; //订单id
	private Integer userId; //用户id
	private String orderNum; // 订单编号
	private Date createTime; //订单生成时间
	private String note;  //订单备注
	
	// 使用resultMap接收数据选择主类型，扩展内容变为成员变量
	// 一个订单对应一个用户 所以创建单列成员变量
	private User user;
	
	// 一个订单对应多个订单详细 所以创建订单详细集合对象
	private List<OrderDetail> orderDetails;
	
	//这里省列了setget方法
}
User属性

    private Integer id;
    private String username;
    private String password;
    private Date createTime;
    private String sex;

OrderDetail属性

    private Integer id; //订单详细id
    private Integer orderId; // 订单的id
    private Integer itemId; //货物id
    private Integer itemNum; //货物数量

查询一对多，多对多

<!-- 查询订单扩展包括user，orderdetail,item
		一对一使用 association 
		一对多使用collection
		订单与用户关系一对一，订单与订单详细关系一对多
		订单与货物一对一
		所以使用结构可以预测为
		<resultMap>
			<association/> 用户
			<collection>  订单详细
				<association/> 货物
			</collection>
		</resultMap>
	 -->
	 <resultMap type="com.lin.mybatisdemo.po.Orders" id="OrdersOfAllInfo">
	 	<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="order_num" property="orderNum" />
		<result column="createTime" property="createTime"/>
		<result column="note" property="note"/>
		<association property="user" javaType="com.lin.mybatisdemo.po.User" >
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
		</association>
	 	<collection property="orderDetails" ofType="com.lin.mybatisdemo.po.OrderDetail">
			<id column="ordertail_id" property="id"/>
			<result column="id" property="orderId"/>
			<result column="item_id" property="itemId"/>
			<result column="item_num" property="itemNum"/>
			<association property="item" javaType="com.lin.mybatisdemo.po.Items">
				<id column="item_id" property="id"/>
				<result column="name" property="name"/>
				<result column="price" property="price"/>
				<result column="picture" property="picture"/>
			</association>
		</collection>
	 </resultMap>
	 <select id="queryOrdersListAllInfo" resultMap="OrdersOfAllInfo">
		SELECT
			orders.id,
			orders.user_id,
			orders.order_num,
			orders.createTime,
			USER .username,
			USER .sex,
			orderdetail.id ordertail_id,
			orderdetail.item_id,
			orderdetail.item_num,
			items. NAME,
			items.price,
			items.picture
		FROM
			orders,
			USER,
			orderdetail,
			items
		WHERE
			orders.user_id = USER .id
		AND orderdetail.order_id = orders.id
		AND orderdetail.item_id = items.id
	 </select>

查询所有用户每个用户的所有订单

<!-- 
		该表对应一个用户有多个订单，每个订单有多个明细，每个明细有一个商品信息
		一对多，多对多，然后一对一关系
		所以预测resultMap结构
		<resultMap>
			<id/>
			<result/>
			<collection> ordersList
				<id/>
				<result/>
				<collection> ordersDetailList
					<id/>
					<result/>
					<association>  item
						<id/>
						<result/>
					</association>
				</collection>
			</collection>
		</resultMap>
	 -->
	<resultMap type="com.lin.mybatisdemo.dto.UserQueryVo" id="UserAllOrders">
		<id column="id" property="id"/>
		<result column="username" property="username"/>
		<result column="sex" property="sex"/>
		<collection property="ordersList" ofType="com.lin.mybatisdemo.po.Orders">
			<id column="order_id" property="id"/>
			<result column="id" property="userId"/>
			<result column="order_num" property="orderNum" />
			<result column="createTime" property="createTime"/>
			<collection property="orderDetails" ofType="com.lin.mybatisdemo.po.OrderDetail">
				<id column="ordertail_id" property="id"/>
				<result column="order_id" property="orderId"/>
				<result column="item_id" property="itemId"/>
				<result column="item_num" property="itemNum"/>
				<association property="item" javaType="com.lin.mybatisdemo.po.Items">
					<id column="item_id" property="id"/>
					<result column="name" property="name"/>
					<result column="price" property="price"/>
					<result column="picture" property="picture"/>
				</association>
			</collection>
		</collection>
	</resultMap>
	<select id="queryUserAllOrders" resultMap="UserAllOrders">
		SELECT
			`user`.id,`user`.username,`user`.sex,
			orders.id order_id,
			orders.order_num,
			orders.createTime,
			orderdetail.id ordertail_id,
			orderdetail.item_id,
			orderdetail.item_num,
			items. NAME,
			items.price,
			items.picture
		FROM
			user,
			orders,
			orderdetail,
			items
		WHERE
			orders.user_id = USER .id
		AND orderdetail.order_id = orders.id
		AND orderdetail.item_id = items.id
	</select>

Orders（订单实体扩展带有user，orderDetails）

public class Orders {
	
	private Integer id; //订单id
	private Integer userId; //用户id
	private String orderNum; // 订单编号
	private Date createTime; //订单生成时间
	private String note;  //订单备注
	
	// 使用resultMap接收数据选择主类型，扩展内容变为成员变量
	// 一个订单对应一个用户 所以创建单列成员变量
	private User user;
	
	// 一个订单对应多个订单详细 所以创建订单详细集合对象
	private List<OrderDetail> orderDetails;
	
	//这里省列了setget方法
}

User属性

private Integer id;
private String username;
private String password;
private Date createTime;
private String sex;

OrderDetail属性

private Integer id; //订单详细id
private Integer orderId; // 订单的id
private Integer itemId; //货物id
private Integer itemNum; //货物数量