MyBatis集合嵌套查询

1.定义实体类成员属性包含集合

public class ReportOrders {

    private String orderId;//订单号
    private String sellerOrderId;//商户订单号
    private String totalAmount;//商品金额
    private String finalAmount;//订单金额
    private String payStatus;//付款状态
    private String shipStatus;//发货状态
    private String createTime;//下单时间
    private String merchantBn;//商户号
    private String source;//订单分类

    private List<ReportOrderItems> reportItemsList;//订单详情集合


    public String getOrderId() {
        return orderId;
    }

    public void setOrderId(String orderId) {
        this.orderId = orderId;
    }

    public String getSellerOrderId() {
        return sellerOrderId;
    }

    public void setSellerOrderId(String sellerOrderId) {
        this.sellerOrderId = sellerOrderId;
    }

    public String getTotalAmount() {
        return totalAmount;
    }

    public void setTotalAmount(String totalAmount) {
        this.totalAmount = totalAmount;
    }

    public String getFinalAmount() {
        return finalAmount;
    }

    public void setFinalAmount(String finalAmount) {
        this.finalAmount = finalAmount;
    }

    public String getPayStatus() {
        return payStatus;
    }

    public void setPayStatus(String payStatus) {
        this.payStatus = payStatus;
    }

    public String getShipStatus() {
        return shipStatus;
    }

    public void setShipStatus(String shipStatus) {
        this.shipStatus = shipStatus;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }

    public String getMerchantBn() {
        return merchantBn;
    }

    public void setMerchantBn(String merchantBn) {
        this.merchantBn = merchantBn;
    }

    public String getSource() {
        return source;
    }

    public void setSource(String source) {
        this.source = source;
    }

    public List<ReportOrderItems> getReportItemsList() {
        return reportItemsList;
    }

    public void setReportItemsList(List<ReportOrderItems> reportItemsList) {
        this.reportItemsList = reportItemsList;
    }
}

2.BasicOrdersMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.etone.dao.platform.mapper.db1.BasicOrdersMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.etone.dao.platform.entity.BasicOrders">
        <id column="id" property="id" />
        <result column="order_id" property="orderId" />
        <result column="bn_id" property="bnId" />
        <result column="shop_id" property="shopId" />
        <result column="address" property="address" />
        <result column="phone" property="phone" />
        <result column="user_name" property="userName" />
        <result column="total_price" property="totalPrice" />
        <result column="create_time"  property="createTime" />
        <result column="finsh_time"   property="finshTime" />
        <result column="reserve_time" property="reserveTime"/>
        <result column="order_status" property="orderStatus" />
        <result column="source" property="source"/>
        <result column="pre_name" property="preName" />
        <result column="description" property="description"/>
        <result column="deliver_name" property="deliverName" />
        <result column="deliver_phone" property="deliverPhone"/>
        <result column="deliver_fee" property="deliverFee"/>
        <result column="bn_shop" property="bnShop"/>
        <result column="mess_type" property="messType"/>
        <result column="oper_sys" property="operSys"/>
        <result column="oper_time" property="operTime"/>
        <result column="launch_status" property="launchStatus"/>
        <result column="launch_time" property="launchTime"/>
        <result column="refund_total_price" property="refundTotalPrice" />
        <result column="refund_goods" property="refundGoods" />
        <result column="is_call" property="isCall"/>
        <result column="send_type" property="sendType"/>
        <result column="maxOffsetId" property="max_offset_id" />
        <result column="daySeq" property="day_Seq"/>

        <!--property表示返回类型Clazz的属性itemsList
            column表示将order_id作为参数进行之后的查询
            fetchtype表示懒加载
            javaType表示属性对应的类型
            ofType表示集合当中的类型
        -->
        <collection property="itemsList" column="order_id" fetchType="lazy"
                    javaType="ArrayList" ofType="com.etone.dao.platform.entity.BasicOrderItems"
                    select="com.etone.dao.platform.mapper.db1.BasicOrderItemsMapper.selectOrderItemsList">
        </collection>
    </resultMap>


    <sql id="sqlParam">
        <if test="map.orderStatus != null and map.orderStatus != ''">
            <choose>
                <when test=' map.orderStatus=="valid_r" '>
                    and order_status in ('valid_r','valid_rb')
                </when>
                <when test=' map.orderStatus=="unprocessed" '>
                    and order_status in ('unprocessed','pending')
                </when>
                <otherwise>
                    AND order_status=#{map.orderStatus}
                </otherwise>
            </choose>
        </if>
        <if test="map.phone != null and map.phone != ''">
            AND phone=#{map.phone}
        </if>
        <if test="map.createTime !=null and map.createTime != ''">
            AND create_time>=#{map.createTime}
        </if>
        <if test="map.endTime!=null and  map.endTime!=''">
            AND create_time&lt;=#{map.endTime}
        </if>
    </sql>

<!--查询订单信息 -->
    <select id="selectOrdersList" resultMap="BaseResultMap" parameterType="map">
       SELECT id,order_id,bn_id,shop_id,address,phone,user_name,total_price,
        date_format(create_time,'%m-%d %H:%i:%s') as create_time,
        (case when finsh_time!='' then date_format(finsh_time,'%m-%d %H:%i:%s') else '' end) as finsh_time,
        order_status,pre_name,description,reserve_time,
        bn_shop,mess_type,oper_sys,oper_time,launch_status,deliver_phone,deliver_fee,launch_time,
        refund_total_price,refund_goods,deliver_name,is_call,send_type,source,max_offset_id,day_Seq
       FROM sdb_basic_orders WHERE bn_shop=#{map.bnShop}
        <include refid="sqlParam" />
         ORDER BY create_time DESC LIMIT ${map.pageCount},${map.number}
    </select>


    <!-- 同步报表数据使用 -->
    <resultMap id="BaseReportResultMap" type="com.etone.dao.platform.entity.ReportOrders">
        <id column="id" property="id" />
        <result column="order_id" property="orderId" />
        <result column="seller_order" property="sellerOrderId" />
        <result column="total_price" property="totalAmount" />
        <result column="final_amount" property="finalAmount" />
        <result column="pay_status" property="payStatus" />
        <result column="ship_status" property="shipStatus" />
        <result column="create_time" property="createTime" />
        <result column="merchant_bn"  property="merchantBn" />
        <result column="source"   property="source" />

        <collection property="reportItemsList" column="{orderId=order_id,merchantBn=merchant_bn}" fetchType="lazy"
                    javaType="ArrayList" ofType="com.etone.dao.platform.entity.ReportOrderItems"
                    select="com.etone.dao.platform.mapper.db1.BasicOrderItemsMapper.selectReportOrderItemsList">
        </collection>
    </resultMap>

    <!--查询报表需要订单数据-->
    <select id="selectReportOrdersList" resultMap="BaseReportResultMap">
        select  order_id,concat((case when bn_shop like '888%' then substr(bn_shop,1,15) when bn_shop like '999%' then substr(bn_shop,1,14) end),order_id) as seller_order,
			    total_price,total_price as final_amount,'1' as pay_status,'1' as ship_status,UNIX_TIMESTAMP(create_time) as create_time,
                (case when bn_shop like '888%' then substr(bn_shop,1,15) when bn_shop like '999%' then substr(bn_shop,1,14) end) as merchant_bn,source
        from sdb_basic_orders
        where substr(create_time,1,10)=#{createTime} AND  order_status in ('settled','refunding','refunding_b') and
              (case when bn_shop like '888%' then substr(bn_shop,1,15) when bn_shop like '999%' then substr(bn_shop,1,14) end)
              in
            <foreach collection="merList" index="index" item="item"
                     separator="," open="(" close=")">
                   #{item,jdbcType=BIGINT}
            </foreach>
    </select>

</mapper>

3.BasicOrderItemsMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.etone.dao.platform.mapper.db1.BasicOrderItemsMapper">

  
    <select id="selectOrderItemsList" resultType="com.etone.dao.platform.entity.BasicOrderItems">
      SELECT id,order_id AS orderId,goods_name AS goodsName,goods_num AS goodsNum,total_price AS totalPrice,sku_id AS skuId,
              des_name AS desName,basic_type AS basicType,food_property as foodProperty
      FROM sdb_basic_order_items
      WHERE order_id=#{orderId}
    </select>



    <select id="selectReportOrderItemsList" resultType="com.etone.dao.platform.entity.ReportOrderItems">
       SELECT order_id AS orderId,goods_name AS `name`,0 as price,total_price AS amount,
              goods_num AS nums,#{merchantBn} AS merchantBn
       FROM sdb_basic_order_items
       WHERE basic_type='normal' and order_id=#{orderId}
    </select>


</mapper>

• column 代表会拿父节点 id ，作为参数获取 next 对象
• javaType 代表 next 对象是个列表，其实可以省略不写
• ofType 集合包含的类型
• select 是用来执行循环哪个 SQL