情景分析
一个用户(User)有多个订单(Orders),一个订单包含多个订单详情(Orderdetail),一个订单详情有多个订单项(Items)
多对多映射:
在OrdersMapper,
配置一个resultMap,resultMap的返回类型为User
user对Orders为一对多
Orders对OrdersDetail为一对多,
OrdersDetail对Items为一对一
OrdersMapper.xml
<!-- type属性为需要映射的POJO对象 -->
<resultMap type="com.gec.pojo.User" id="UserAndItemsInfoResultMap">
<id column="userId" property="id" jdbcType="INTEGER" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="birthday" property="birthday" jdbcType="DATE" />
<result column="address" property="address" jdbcType="VARCHAR" />
<!-- collection配置一对多关系,ofType属性为关联的属性的jdbc类型,
如:User有多个订单Orders,则这里的ofType类型为“com.gec.pojo.Orders” -->
<collection property="ordersList" ofType="com.gec.pojo.Orders">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="userId" property="userid" jdbcType="INTEGER" />
<result column="orderId" property="orderid" jdbcType="VARCHAR" />
<result column="createtime" property="createtime" jdbcType="DATE" />
<result column="note" property="note" jdbcType="VARCHAR" />
<collection property="detaiList" ofType="orderdetail">
<id column="detail_id" property="id" jdbcType="INTEGER" />
<result column="itemsNum" property="itemsnum" jdbcType="INTEGER" />
<association property="item" javaType="items">
<id column="items_id" property="id" jdbcType="INTEGER" />
<result column="items_name" property="name" jdbcType="VARCHAR" />
<result column="price" property="price" jdbcType="INTEGER" />
<result column="pic" property="pic" jdbcType="VARCHAR" />
<result column="items_createtime" property="createtime" jdbcType="DATE" />
<result column="items_detail" property="detail" jdbcType="VARCHAR" />
</association>
</collection>
</collection>
</resultMap>
<!-- select查询语句 -->
<select id="findUserAndItemsInfo" resultMap="UserAndItemsInfoResultMap">
SELECT orders.*,
u.`address`,u.`birthday`,u.`username`,
detail.id detail_id, detail.itemsNum,
items.`id` items_id, items.`NAME` items_name, items.`price`, items.pic, items.`createtime` items_createtime, items.`detail` items_detail
FROM t_user u, orders , orderdetail detail, items
WHERE u.id=orders.`userId` AND orders.`id`=detail.ordersId AND detail.itemsId=items.`id`
</select>
OrdersMapper.java
public interface OrdersMapper {
List<User> findUserAndItemsInfo();
}