Mybatis一对多关联查询

1.需求

有一个需求是这样的，我要查一个订单列表，列表中的一个订单中可能有多个商品，是一对多的关系，然后需要在列表中同时查出来。结果大概是下面这样的：

2.实现

没时间了，直接上xml代码，基本思路是将分页和条件查询放在取一端的id集的操作中，将符合条件的订单id先全部拿出来，再去查这些订单的订单基本信息和多个商品信息，条件查询可以查询订单（一方）和订单商品和卖家姓名（多端）关键字检索。碰到的问题有总数不对、每页实际返回条数不对（Mybatis会将相同的键的拼到同一个result中）、多端有数据填充List时不完整、做分页的时候最后一条只取了多端的一部分，另一部分丢失了。

    //select 语句
    <select id="selectOrderListWithCondition" resultMap="mOrders">
        select
        <include refid="mOrdersPkSql"/>
        ,
        muser.id AS Muser_id,
        muser.user_name AS Muser_userName,
        order_goods.id AS OrderGoods_id,
        order_goods.sku_count AS OrderGoods_skuCount,
        order_goods.goods_id AS OrderGoods_goodsId,
        order_goods.sku_id AS OrderGoods_skuId,
        goods.id AS Goods_id,
        goods.goods_name AS Goods_goodsName,
        goods.goods_url AS Goods_goodsUrl,
        sku.id AS Sku_id,
        sku.values_str AS Sku_valuesStr,
        sku.price AS Sku_price,
        sku.inventory AS Sku_inventory,
        sku.if_uniform_spec AS Sku_ifUniformSpec
        from
        orders,
        muser,
        order_goods ,
        goods,
        sku
        WHERE
        orders.id=order_goods.order_id
        AND order_goods.goods_id=goods.id
        AND order_goods.sku_id=sku.id
        AND orders.muser_id=muser.id
        AND orders.id in ( SELECT
        t.orderId
        FROM
        (
        select orders.id orderId
        from
        orders,
        muser,
        order_goods ,
        goods
        WHERE
        orders.id=order_goods.order_id
        AND order_goods.goods_id=goods.id
        AND orders.muser_id=muser.id
        <if test="searchText!=null and searchText!=''">
            AND orders.order_number like concat('%',#{searchText},'%')
        </if>
        <if test="sourceType!=null and sourceType!=''">
            AND orders.source_type=#{sourceType}
        </if>
        <if test="orderType!=null and orderType!=''">
            AND orders.order_type=#{orderType}
        </if>
        <if test="muserId!=null and muserId!=''">
            AND orders.muser_id=#{muserId}
        </if>
        <if test="orderStatus!=null and orderStatus!='' and orderStatus==99 ">
            AND orders.order_status in (4,5)
        </if>
        <if test="orderStatus!=null and orderStatus!='' and orderStatus!=99 ">
            AND orders.order_status = #{orderStatus}
        </if>
        <if test="goodsName!=null and goodsName!=''">
            AND goods.goods_name like concat('%',#{goodsName},'%')
        </if>
        <if test="userName!=null and userName!=''">
            AND muser.user_name like concat('%',#{userName},'%')
        </if>
        GROUP BY orders.id
        ORDER BY orders.gmt_datetime desc
        limit #{startIndex},#{size}
        ) t)
        ORDER BY orders.gmt_datetime desc
    </select>

    //统计分页总条数
    <select id="selectOrderListSize" resultType="java.lang.Integer">
        SELECT count(*) FROM
        (SELECT
        orders.id
        FROM
        orders,
        muser,
        order_goods ,
        goods
        WHERE
        orders.id=order_goods.order_id
        AND order_goods.goods_id=goods.id
        AND orders.muser_id=muser.id
        <if test="searchText!=null and searchText!=''">
            AND orders.order_number like concat('%',#{searchText},'%')
        </if>
        <if test="sourceType!=null and sourceType!=''">
            AND orders.source_type=#{sourceType}
        </if>
        <if test="orderType!=null and orderType!=''">
            AND orders.order_type=#{orderType}
        </if>
        <if test="muserId!=null and muserId!=''">
            AND orders.muser_id=#{muserId}
        </if>
        <if test="orderStatus!=null and orderStatus!='' and orderStatus==99 ">
            AND orders.order_status in (4,5)
        </if>
        <if test="orderStatus!=null and orderStatus!='' and orderStatus!=99 ">
            AND orders.order_status = #{orderStatus}
        </if>
        <if test="goodsName!=null and goodsName!=''">
            AND goods.goods_name like concat('%',#{goodsName},'%')
        </if>
        <if test="userName!=null and userName!=''">
            AND muser.user_name like concat('%',#{userName},'%')
        </if>
        GROUP BY orders.id) t
    </select>

---

下面是resultMap,用collection标签接收list，association 接收实体：

    <resultMap type="com.rongke.model.Orders" id="mOrders">
        <id property="id" column="Orders_id"/>
        <result property="muserId" column="Orders_muserId"/>
        <result property="addressId" column="Orders_addressId"/>
        <result property="orderNumber" column="Orders_orderNumber"/>
        <result property="outTradeNo" column="Orders_outTradeNo"/>
        <result property="sourceType" column="Orders_sourceType"/>
        <result property="totalPrice" column="Orders_totalPrice"/>
        <result property="returnDuobi" column="Orders_returnDuobi"/>
        <result property="returnYabi" column="Orders_returnYabi"/>
        <result property="cashPayPrice" column="Orders_cashPayPrice"/>
        <result property="allowPayType" column="Orders_allowPayType"/>
        <result property="orderType" column="Orders_orderType"/>
        <result property="orderStatus" column="Orders_orderStatus"/>
        <result property="deliveryMoney" column="Orders_deliveryMoney"/>
        <result property="useCoupon" column="Orders_useCoupon"/>
        <result property="couponVal" column="Orders_couponVal"/>
        <result property="leavingMessage" column="Orders_leavingMessage"/>
        <result property="isLogistics" column="Orders_isLogistics"/>
        <result property="receiverPhone" column="Orders_receiverPhone"/>
        <result property="receiverName" column="Orders_receiverName"/>
        <result property="receiverAddress" column="Orders_receiverAddress"/>
        <result property="logiCom" column="Orders_logiCom"/>
        <result property="logisticsNumber" column="Orders_logisticsNumber"/>
        <result property="payDatetime" column="Orders_payDatetime"/>
        <result property="completeDatetime" column="Orders_completeDatetime"/>
        <result property="payType" column="Orders_payType"/>
        <result property="merchantRemarks" column="Orders_merchantRemarks"/>
        <result property="cusServiceRemarks" column="Orders_cusServiceRemarks"/>
        <result property="gmtDatetime" column="Orders_gmtDatetime"/>
        <result property="uptDatetime" column="Orders_uptDatetime"/>
        <association property="muser" javaType="com.rongke.model.Muser">
            <id property="id" column="Muser_id"/>
            <result property="userName" column="Muser_userName"/>
        </association>
        <collection property="orderGoodsList" ofType="com.rongke.model.OrderGoods" column="OrderGoods_id">
            <result property="id" column="OrderGoods_id"/>
            <result property="goodsId" column="OrderGoods_goodsId"/>
            <result property="skuId" column="OrderGoods_skuId"/>
            <result property="skuCount" column="OrderGoods_skuCount"/>
            <association property="sku" javaType="com.rongke.model.Sku" column="OrderGoods_skuId">
                <id property="id" column="Sku_id"/>
                <result property="price" column="Sku_price"/>
                <result property="valuesStr" column="Sku_valuesStr"/>
                <result property="inventory" column="Sku_inventory"/>
                <result property="ifUniformSpec" column="Sku_ifUniformSpec"/>
            </association>
            <association property="goods" javaType="com.rongke.model.Goods" column="OrderGoods_goodsId">
                <id property="id" column="Goods_id"/>
                <result property="goodsName" column="Goods_goodsName"/>
                <result property="goodsUrl" column="Goods_goodsUrl"/>
            </association>
        </collection>
    </resultMap>