<!-- 多对多查询:一个用户创建多个订单,一个订单包含多个订单明细,一个订单明细包含一个商品 -->
<resultMap type="com.hbut.po.User" id="findUserItemsMap">
<!-- 一个用户 -->
<id column="usersId" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 多个订单 -->
<collection property="orderList" ofType="com.hbut.po.Orders">
<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"/>
<!-- 多个明细 -->
<collection property="orders" ofType="com.hbut.po.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<!--一个订单明细包含一个商品-->
<association property="items" javaType="com.hbut.po.Items">
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="detail" property="detail"/>
</association>
</collection>
</collection>
</resultMap>
<select id="findUserItems" resultMap="findUserItemsMap">
SELECT
orders.*,
`user`.id usersId,
USER.username,
USER.sex,
USER.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id,
items.name,
items.price,
items.detail
FROM
orders,
USER,
orderdetail,
items
WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id=items.id
</select>
User类:
//属性名和数据库表的字段对应
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
//一个用户可以创建多个订单
private List<Orders> orderList;
Orders类:
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//一个订单包含多个订单明细
private List<Orderdetail> orders;
Orderdetail类
private Integer id;
private Integer ordersId;
private Integer itemsId;
private Integer itemsNum;
//一个订单明细对应一个商品
private Items items;
Items类(商品类)
private Integer id;
private String name;
private Float price;
private String pic;
private Date createtime;
private String detail;
运行结果:一个用户创建的多个订单,每个订单包含多个订单明细,每个订单明细包含一个商品