JavaWeb学习笔记-mybatis-21-多对多查询

查询用户以及用户所购买的商品信息
查询主表：用户表
关联表：订单，订单明细，商品
sql:

        select
          orders.*,
          user.username,
          user.sex,
          user.addressfrom,
          orderdetail.id orderdetail_id
          orderdetail.orders_id,
          orderdetail.items_id,
          orderdetail.items_name,
          items.id items_id,
          items.name items_name,
          items.price item_price
    from orders,user,orderdetail,items
        where
          orders.user_id = user.id
        and
          orderdetail.orders_id = orders.id
        and 
          orderdetail.items_id = items.id

将用户信息映射到user中
在user中添加订单列表属性List<Orders> ordersList
在orders中添加订单明细列表属性List<OrderDetails> oderdetails
在orderdetails中添加items属性

    <!--查询用户及关联的商品信息-->
    <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
        select
          orders.*,
          user.username,
          user.sex,
          user.addressfrom,
          orderdetail.id orderdetail_id
          orderdetail.orders_id,
          orderdetail.items_id,
          orderdetail.items_name,
          items.id items_id,
          items.name items_name,
          items.price item_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>

    <!--查询用户及相关联商品信息-->
    <resultMap id="UserAndItemsResultMap" type="com.sws.entity.User">
        <!--用户信息-->
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <!--订单信息
        一个用户对应多个订单-->
        <collection property="orderList" ofType="com.sws.entity.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="orderdetails" ofType="com.sws.entity.OrderDetails">
                <id property="id" column="orderdetail_id"/>
                <result column="item_id" property="itemId"/>
                <result column="item_name" property="itemName"/>
                <result column="orders_id" property="ordersId"/>
                <!--一个订单明细对于一个商品-->
                <association property="items" javaType="com.sws.entity.Items">
                    <!--id:关联查询用户的唯一标识
                    column:指定唯一标识用户信息的列
                    property:映射到user的哪个属性-->
                    <id column="items_id" property="id"/>
                    <result column="items_name" property="name"/>
                    <result column="items_price" property="price"/>
                    <result column="items_detail" property="detail"/>
                </association>
            </collection>
        </collection>
    </resultMap>

public List<User> findUserAndItemsResultMap() throws Exception;