mybatis一对多Collection使用

场景：

订单主表：order_main

CREATE TABLE `order_main` (
  `order_id` bigint(20) unsigned NOT NULL COMMENT '订单ID',
  `order_show_no` varchar(64) DEFAULT '' COMMENT '订单展示id',
  `third_order_id` bigint(20) DEFAULT NULL COMMENT '第三方订单ID',
  `order_source` varchar(64) NOT NULL COMMENT '订单来源（web，app，h5，applet，third,other）',
  `order_channel_type` varchar(64) NOT NULL COMMENT '订单渠道类型 online:线上渠道 offline:线下渠道',
  `business_type` int(10) DEFAULT NULL COMMENT '业务类型 10 复诊 11中日复诊 20 教学 30 处方流转 31 中日处方流转 41 智慧就医（住院缴费）42 智慧就医（门诊缴费）43 挂号预约 50 护理照护 60 医学咨询',
  `order_class` varchar(64) NOT NULL COMMENT '订单种类（object:实体商品 service:服务商品 virtual:虚拟商品）',
  `order_category` varchar(64) DEFAULT NULL COMMENT '订单大类: 销售，退货(已收已付)，换货送新(出库)，换货拖旧(意向)，拒收',
  `order_related_origin` bigint(20) DEFAULT NULL COMMENT '退/换货关联的原单号，换货关联的最近换货记录或原单',
  `order_time` datetime NOT NULL COMMENT '订单产生时间',
  `status_code` int(10) NOT NULL COMMENT '主订单状态编码（01:待支付 05:待确认 10:待出库 15:待发货 20:待收货 25:已完成）',
  `merger` tinyint(4) DEFAULT '0' COMMENT '是否合并（0:否 1:是）',
  `split` tinyint(4) DEFAULT '0' COMMENT '是否拆单（0:否 1:是）',
  `pay_on_arrival` tinyint(4) DEFAULT '0' COMMENT '是否货到付款（0:否 1:是）',
  `need_invoice` tinyint(4) DEFAULT '0' COMMENT '是否需要发票（0:否 1:是）',
  `merchant_id` varchar(64) NOT NULL COMMENT '商家ID',
  `merchant_name` varchar(64) NOT NULL COMMENT '商家名称',
  `user_id` bigint(64) NOT NULL COMMENT '用户id',
  `user_name` varchar(64) NOT NULL COMMENT '用户姓名',
  `delivery_type` int(11) DEFAULT NULL COMMENT '0:物流 1:快递 2:自提 3:线下直配 4:系统发送（虚拟商品、服务商品）',
  `delivery_confirm` tinyint(4) DEFAULT '0' COMMENT '是否送货前电话确认（0:否 1:是）',
  `delivery_no` varchar(64) DEFAULT NULL COMMENT '订单物流单号',
  `shipper_code` varchar(20) DEFAULT NULL COMMENT '订单物流公司',
  `delivery_time_flag` int(11) DEFAULT NULL COMMENT '送货时间（0:白天 1:晚上）',
  `delivery_date_flag` int(11) DEFAULT NULL COMMENT '1:工作日 2:双休日 3:节假日 4:均可送货',
  `recipients_name` varchar(64) DEFAULT NULL COMMENT '收件人姓名',
  `recipients_email` varchar(64) DEFAULT NULL COMMENT '收件人邮箱',
  `recipients_phone` varchar(64) DEFAULT NULL COMMENT '收件人电话(固定电话)',
  `recipients_mobile` varchar(64) DEFAULT NULL COMMENT '收件人手机号',
  `recipients_post_code` varchar(64) DEFAULT NULL COMMENT '收件人邮编',
  `recipients_area_code` varchar(64) DEFAULT NULL COMMENT '收件人区域信息编码',
  `recipients_area_name` varchar(64) DEFAULT NULL COMMENT '收件人区域信息名称',
  `recipients_address` varchar(64) DEFAULT NULL COMMENT '收件人详细地址',
  `recipients_remark` varchar(64) DEFAULT NULL COMMENT '收件人备注',
  `product_weight_total` decimal(10,2) DEFAULT NULL COMMENT '商品总重量',
  `transport_price_total` decimal(10,2) DEFAULT NULL COMMENT '运费总额',
  `union_pay` tinyint(4) DEFAULT '0' COMMENT '是否组合支付（0:否 1:是）',
  `pay_effective_time` datetime DEFAULT NULL COMMENT '支付有效时间',
  `pay_amount_total` decimal(10,2) DEFAULT NULL COMMENT '实际支付总金额',
  `pay_status_code` int(11) NOT NULL COMMENT '支付状态编码（0:待支付 1:部分支付 2:已支付）',
  `cancel` tinyint(4) DEFAULT '0' COMMENT '是否取消订单（0:否 1:是）',
  `cancel_time` datetime DEFAULT NULL COMMENT '取消时间',
  `cancel_reason` varchar(64) DEFAULT NULL COMMENT '取消订单原因编号',
  `need_refund` bigint(20) DEFAULT NULL COMMENT '取消订单是否需退款（0:否 1:是）',
  `refund_type` int(11) DEFAULT NULL COMMENT '取消订单退款类型（0:全部退款 1:部分退款）',
  `refund_pay_amount_total` decimal(10,2) DEFAULT NULL COMMENT '取消订单实际退款支付总金额（商品折前总金额 - 商品优惠总金额 + 运费 - 运费优惠 - 优惠卷）',
  `refund_pay_no` varchar(64) DEFAULT NULL COMMENT '取消订单退款支付编码',
  `refund_pay_status` varchar(64) DEFAULT NULL COMMENT '取消订单退款支付状态（0:待退款 1:部分退款 2:已退款）',
  `refund_pay_time` datetime DEFAULT NULL COMMENT '取消订单退款支付时间',
  `review_status` varchar(64) DEFAULT NULL COMMENT '审核状态(如：DRAFT:待审核 /PROCESSING:审核中 /REJECTED:审核驳回 /COMPLETED:审核通过 )',
  `evaluate_status` int(11) DEFAULT '0' COMMENT '评价状态（0:待评价 1:已评价）',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标识，0（未删除）1（删除）',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(20) DEFAULT NULL COMMENT '创建用户id',
  `create_user_name` varchar(64) DEFAULT NULL COMMENT '创建用户名称',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `update_user_id` bigint(20) DEFAULT NULL COMMENT '更新用户id',
  `update_user_name` varchar(64) DEFAULT NULL COMMENT '更新用户名称',
  `project_id` bigint(32) NOT NULL COMMENT '所属项目ID',
  PRIMARY KEY (`order_id`),
  KEY `order_main_index_id` (`order_id`),
  KEY `order_main_index_user_id` (`user_id`),
  KEY `index_order_main_status_code` (`status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

订单商品表

CREATE TABLE `order_item` (
  `order_item_id` bigint(20) unsigned NOT NULL COMMENT '订单item ID',
  `order_id` bigint(20) NOT NULL COMMENT '主订单ID',
  `order_source` varchar(64) NOT NULL COMMENT '订单来源（web，app，h5，applet，third,other）',
  `order_channel_type` varchar(64) NOT NULL COMMENT '订单渠道类型 online:线上渠道 offline:线下渠道',
  `business_type` int(10) DEFAULT NULL COMMENT '业务类型 10 复诊 20 教学 30 处方流转',
  `order_class` varchar(64) NOT NULL COMMENT '订单种类（object:实体商品 service:服务商品 virtual:虚拟商品）',
  `order_category` varchar(64) DEFAULT NULL COMMENT '订单大类: 销售，退货(已收已付)，换货送新(出库)，换货拖旧(意向)，拒收',
  `item_inventory_price` decimal(10,2) DEFAULT NULL COMMENT '商品库存成本价',
  `item_unit_price` decimal(10,2) NOT NULL COMMENT '商品单价',
  `item_unit_amount` decimal(10,2) NOT NULL COMMENT '商品单价合计(商品单价x商品数量)',
  `amount` int(10) DEFAULT NULL COMMENT '商品数量',
  `sku_id` bigint(20) DEFAULT NULL COMMENT '商品ID',
  `sku_code` varchar(64) DEFAULT NULL COMMENT '商品SKU编号',
  `sku_name` varchar(64) DEFAULT NULL COMMENT '商品名称',
  `sku_image` varchar(255) DEFAULT NULL COMMENT '商品图片地址',
  `spu_id` bigint(20) DEFAULT NULL COMMENT '商品SPU的ID',
  `spu_code` varchar(64) DEFAULT NULL COMMENT '商品编码',
  `spu_name` varchar(64) DEFAULT NULL COMMENT '商品名称',
  `spu_type` varchar(64) DEFAULT NULL COMMENT '商品类型（object:实体商品 service:服务商品 virtual:虚拟商品）',
  `bar_code` varchar(64) DEFAULT NULL COMMENT '商品条形码（国标码）',
  `instore_bar_code` varchar(64) DEFAULT NULL COMMENT '商品店内码（国标码）',
  `batch_number` varchar(64) DEFAULT NULL COMMENT '商品批号',
  `spec_name` varchar(64) DEFAULT NULL COMMENT '商品Sku规格',
  `spec_value` varchar(64) DEFAULT NULL COMMENT '商品Sku规格值',
  `single_gross_weight` decimal(10,2) DEFAULT NULL COMMENT '单件商品毛重',
  `single_net_weight` decimal(10,2) DEFAULT NULL COMMENT '单件商品净重',
  `merchant_id` varchar(64) DEFAULT NULL COMMENT '商家ID',
  `merchant_no` varchar(64) DEFAULT NULL COMMENT '商家编号',
  `merchant_name` varchar(64) DEFAULT NULL COMMENT '商家名称',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标识，0（未删除）1（删除）',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(20) DEFAULT NULL COMMENT '创建用户id',
  `create_user_name` varchar(64) DEFAULT NULL COMMENT '创建用户名称',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `update_user_id` bigint(20) DEFAULT NULL COMMENT '更新用户id',
  `update_user_name` varchar(64) DEFAULT NULL COMMENT '更新用户名称',
  PRIMARY KEY (`order_item_id`),
  KEY `order_item_index_id` (`order_id`),
  KEY `order_item_index_item_id` (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单item表，订单商品信息';

分页列表中既有主表字段，又有扩展表字段，形成一对多关系

列表实现方式：

<resultMap id="orderListMap" type="com.health.order.entity.vo.OrderListVO">
        <id column="order_id" property="orderId"/>
        <result column="order_source" property="orderSource" jdbcType="VARCHAR"/>
        <result column="order_show_no" property="orderShowNo" jdbcType="VARCHAR"/>
        <result column="order_channel_type" property="orderChannelType" jdbcType="VARCHAR"/>
        <result column="business_type" property="businessType" jdbcType="INTEGER"/>
        <result column="order_class" property="orderClass" jdbcType="VARCHAR"/>
        <result column="order_category" property="orderCategory" jdbcType="VARCHAR"/>
        <result column="order_time" property="orderTime" jdbcType="TIMESTAMP"/>
        <result column="status_code" property="statusCode" jdbcType="INTEGER"/>
        <result column="merchant_id" property="merchantId" jdbcType="VARCHAR"/>
        <result column="merchant_name" property="merchantId" jdbcType="VARCHAR"/>
        <result column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="user_name" property="userName" jdbcType="VARCHAR"/>
        <result column="delivery_type" property="deliveryType" jdbcType="INTEGER"/>
        <result column="delivery_no" property="deliveryNo" jdbcType="VARCHAR"/>
        <result column="recipients_name" property="recipientsName" jdbcType="VARCHAR"/>
        <result column="recipients_mobile" property="recipientsMobile" jdbcType="VARCHAR"/>
        <result column="recipients_post_code" property="recipientsPostCode" jdbcType="VARCHAR"/>
        <result column="recipients_address" property="recipientsAddress" jdbcType="VARCHAR"/>
        <result column="recipients_remark" property="recipientsRemark" jdbcType="VARCHAR"/>
        <result column="transport_price_total" property="transportPriceTotal" jdbcType="VARCHAR"/>
        <result column="pay_status_code" property="payStatusCode" jdbcType="VARCHAR"/>
        <result column="pay_amount_total" property="payAmountTotal" jdbcType="VARCHAR"/>
        <result column="pay_effective_time" property="payEffectiveTime" jdbcType="VARCHAR"/>
        <result column="cancel" property="cancel" jdbcType="INTEGER"/>
        <result column="cancel_time" property="cancelTime" jdbcType="TIMESTAMP"/>
        <result column="cancel_reason" property="cancelReason" jdbcType="VARCHAR"/>
        <result column="evaluate_status" property="evaluateStatus" jdbcType="INTEGER"/>
        <result column="remark" property="remark" jdbcType="VARCHAR"/>
        <result column="shipper_code" property="shipperCode" jdbcType="VARCHAR"/>
<!--
     column="order_id" 通过order_id关联扩展表，返回类型为OrderItemListVO
     select指定在扩展表中的查询语句，返回类型与OrderItemListVO对应
-->
        <collection property="orderItemList" column="order_id" ofType="com.health.order.entity.vo.OrderItemListVO" select="getOrderListCount"
                    javaType="java.util.ArrayList">
            <id column="order_item_id" property="orderItemId" jdbcType="INTEGER"/>
            <result column="sku_id" property="skuId" jdbcType="INTEGER"/>
            <result column="sku_name" property="skuName" jdbcType="VARCHAR"/>
            <result column="sku_image" property="skuImage" jdbcType="VARCHAR"/>
            <result column="item_unit_price" property="itemUnitPrice" jdbcType="DECIMAL"/>
            <result column="amount" property="amount" jdbcType="INTEGER"/>
            <result column="sku_code" property="skuCode" jdbcType="VARCHAR"/>
            <result column="item_unit_amount" property="itemUnitAmount" jdbcType="DECIMAL"/>
        </collection>
    </resultMap>

    <select id="getOrderListCount" parameterType="long" resultType="com.health.order.entity.vo.OrderItemListVO">
    select
        i.order_item_id,
        i.sku_id,
        i.sku_image,
        i.sku_name,
        i.sku_code,
        i.item_unit_price,
        i.amount,
        i.item_unit_amount
        from order_item i where i.order_id = #{orderId}
    </select>

<select id="selectOrderListPage" resultMap="orderListMap">
        select o.order_id,
        o.order_source,
        o.order_show_no,
        o.order_channel_type,
        o.business_type,
        o.order_class,
        o.order_category,
        o.order_time,
        o.status_code,
        o.merchant_id,
        o.merchant_name,
        o.user_id,
        o.user_name,
        o.delivery_type,
        o.delivery_no,
        o.recipients_name,
        o.recipients_mobile,
        o.recipients_post_code,
        o.recipients_address,
        o.recipients_remark,
        o.transport_price_total,
        o.pay_status_code,
        o.pay_amount_total,
        o.pay_effective_time,
        o.cancel,
        o.cancel_time,
        o.cancel_reason,
        o.evaluate_status,
        o.remark,
        o.shipper_code
        from order_main o
       where 1 = 1 and o.status_code != 25 and o.deleted = 0 and o.cancel != 1
<!--查询条件-->
        <if test="params.orderSource != null and params.orderSource != '' ">
            and o.order_source = #{params.orderSource}
        </if>
        <if test="params.orderChannelType != null and params.orderChannelType != '' ">
            and o.order_channel_type = #{params.orderChannelType}
        </if>
        <if test="params.businessType != null and params.businessType != '' and params.businessType != 0 ">
            and o.business_type = #{params.businessType}
        </if>
        <if test="params.orderClass != null and params.orderClass != ''">
            and o.order_class = #{params.orderClass}
        </if>
        <if test="params.orderCategory != null and params.orderCategory != ''">
            and o.order_category = #{params.orderCategory}
        </if>
        <if test="params.statusCode != null and params.statusCode != '' and params.statusCode != 0 ">
            and o.status_code = #{params.statusCode}
        </if>
        <if test="params.deliveryType != null and params.deliveryType != '' and params.deliveryType != 0 ">
            and o.delivery_type = #{params.deliveryType}
        </if>
        <if test="params.cancel != null and params.cancel != '' and params.cancel != 0 ">
            and o.is_cancel = #{params.isCancel}
        </if>
        <if test="params.startTime != null and params.endTime != null">
            and date(o.order_time) BETWEEN date(#{params.startTime}) AND date(#{params.endTime})
        </if>
        and o.user_id = #{userId} order by o.create_time desc
    </select>

public class OrderListVO {

    @ApiModelProperty(value = "订单ID")
    private String orderId;

    @ApiModelProperty(value = "订单展示id")
    private String orderShowNo;

    @ApiModelProperty(value = "订单来源（web，app，h5，applet，third,other）")
    private String orderSource;

    @ApiModelProperty(value = "订单渠道类型 online:线上渠道 offline:线下渠道")
    private String orderChannelType;

    @ApiModelProperty(value = "业务类型 10 复诊 20 教学 30 处方流转")
    private Integer businessType;

    @ApiModelProperty(value = "订单种类（object:实体商品 service:服务商品 virtual:虚拟商品）")
    private String orderClass;

    @ApiModelProperty(value = "订单大类: 销售，退货(已收已付)，换货送新(出库)，换货拖旧(意向)，拒收")
    private String orderCategory;

    @ApiModelProperty(value = "订单产生时间")
    private LocalDateTime orderTime;

    @ApiModelProperty(value = "主订单状态编码（01:待支付 05:待确认 10:待出库 15:待发货 20:待收货 25:已完成）")
    private Integer statusCode;

    @ApiModelProperty(value = "商家ID")
    private String merchantId;

    @ApiModelProperty(value = "商家名称")
    private String merchantName;

    @ApiModelProperty(value = "用户id")
    private Long userId;

    @ApiModelProperty(value = "用户姓名")
    private String userName;

    @ApiModelProperty(value = "0:物流 1:快递 2:自提 3:线下直配 4:系统发送（虚拟商品、服务商品）")
    private Integer deliveryType;

    @ApiModelProperty(value = "订单物流单号")
    private String deliveryNo;

    @ApiModelProperty(value = "订单物流公司")
    private String shipperCode;

    @ApiModelProperty(value = "收件人姓名")
    private String recipientsName;

    @ApiModelProperty(value = "收件人手机号")
    private String recipientsMobile;

    @ApiModelProperty(value = "收件人邮编")
    private String recipientsPostCode;

    @ApiModelProperty(value = "收件人详细地址")
    private String recipientsAddress;

    @ApiModelProperty(value = "收件人备注")
    private String recipientsRemark;

    @ApiModelProperty(value = "运费总额")
    private BigDecimal transportPriceTotal;

    @ApiModelProperty(value = "支付状态编码（0:待支付 1:部分支付 2:已支付 3:退款中 4:已退款）")
    private Integer payStatusCode;

    @ApiModelProperty(value = "实际支付总金额")
    private BigDecimal payAmountTotal;

    @ApiModelProperty(value = "支付有效时间")
    private LocalDateTime payEffectiveTime;

    @ApiModelProperty(value = "是否取消订单（0:否 1:是）")
    private Long cancel;

    @ApiModelProperty(value = "取消时间")
    private LocalDateTime cancelTime;

    @ApiModelProperty(value = "取消订单原因编号")
    private String cancelReason;

    @ApiModelProperty(value = "评价状态（0:待评价 1:已评价）")
    private Integer evaluateStatus;

    @ApiModelProperty(value = "备注")
    private String remark;

    @ApiModelProperty(value = "商品列表")
    private List<OrderItemListVO> orderItemList;

}



public class OrderItemListVO {

    @ApiModelProperty(value = "订单item ID")
    private Long orderItemId;

    @ApiModelProperty(value = "订单ID")
    private Long orderId;

    @ApiModelProperty(value = "商品id")
    private String skuId;

    @ApiModelProperty(value = "商品名称")
    private String skuName;

    @ApiModelProperty(value = "商品编码")
    private String skuCode;

    @ApiModelProperty(value = "商品图片地址")
    private String skuImage;

    @ApiModelProperty(value = "商品单价")
    private BigDecimal itemUnitPrice;

    @ApiModelProperty(value = "商品单价合计(商品单价x商品数量)")
    private BigDecimal itemUnitAmount;

    @ApiModelProperty(value = "商品数量")
    private Integer amount;

}

需要注意mapper.xml中orderListMap的id跟select，通过ID关联订单主表与商品表，select部分，会在查询到主表的一个集合后，循环执行select部分sql，在进行查询结果的组装，达到最终一条订单信息对应多条商品信息的目的。分页查询的时候一定要注意记录总数。