JavaWeb学习笔记-mybatis-20-一对多查询

需求
查询订单及订单明细的信息

sql语句
确定主查询表：订单表
关联查询表：订单明细表

select orders.*,user.username,user.sex,user.addressfrom,orderdetail.orders_id,orderdetail.item_id,orderdetail.item_name from orders,user,orderdetail where orders.user_id = user.id and 
orderdetial.orders_id = orders.id

分析：使用resultType将上述查询结果映射到POJO中，订单信息会重复（一个订单信息对应多个订单明细）
要求：对order映射不能出现重复记录
在order.java中添加List<OrderDetail> orderDetails属性，最终会将订单信息映射到orders中，订单信息的订单明细映射到orders的orderDeatils中
在Order中添加属性

    //订单明细
    private List<OrderDetail> orderDetails;

    public List<OrderDetail> getOrderDetails() {
        return orderDetails;
    }

    public void setOrderDetails(List<OrderDetail> orderDetails) {
        this.orderDetails = orderDetails;
    }

    <!--查询订单关联用户信息及用户明细-->
    <select id="findOrdersUserAndOrderDetailResultMap" resultMap="OrdersUserAndOrderDetailResultMap">
        select
          orders.*,
          user.username,
          user.sex,
          user.addressfrom,
          orderdetail.id orderdetail_id
          orderdetail.orders_id,
          orderdetail.item_id,
          orderdetail.item_name
        from orders,user,orderdetail
        where
          orders.user_id = user.id
        and
          orderdetial.orders_id = orders.id
    </select>

resultMap定义

    <!--订单及订单明细的resultMap-->
    <resultMap id="OrdersUserAndOrderDetailResultMap" type="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"/>
        <!--用户信息-->
        <association property="user" javaType="com.sws.entity.User">
            <!--id:关联查询用户的唯一标识
            column:指定唯一标识用户信息的列
            property:映射到user的哪个属性-->
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
        <!--订单明细，一个订单对应多个明细
        collection:关联查询多条记录映射到结果集中
        property:将关联查询到的多条记录映射到order属性中
        ofTYpe:指定映射到list集合属性中的pojo类型-->
        <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"/>
        </collection>
    </resultMap>

    <!--订单及订单明细的resultMap
    使用继承，不用配置订单信息以及用户信息-->
    <resultMap id="OrdersUserAndOrderDetailResultMap" type="com.sws.entity.Orders" extends="OrdersUserResultMap">
        <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"/>
        </collection>
    </resultMap>

public List<Orders> OrdersUserAndOrderDetailResultMap() throws Exception;