【Bug日记】在MyBatis中使用resultMap一对多查询时，查询结果List集合只有一条数据

 问题描述

我在数据库中定义了几张表 

其中 orders 和 order_detail 的关系是 1:n ，然后我就在 Order 实体类中定义了存储 OrderDetail 实体类的 List 集合

@Data
public class Orders {
    private String orderId;
    private String employeeId;
    private String customerId;
    private String orderTime;
    private Customer customer;
    private Employee employee;
    /**
     * 一次订单可以有多条商品交易信息
     */
    private List<OrderDetail> orderDetailList;
}

xml 文件配置

<resultMap id="comprehensiveQueryMap" type="Orders">
    <id column="order_id" property="orderId"/>
    <result column="employee_id" property="employeeId"/>
    <result column="customer_id" property="customerId"/>
    <result column="order_time" property="orderTime"/>
    <association property="customer" javaType="Customer">
        <id column="customer_id" property="customerId"/>
        <result column="customer_name" property="customerName"/>
    </association>
    <association property="employee" javaType="Employee">
        <id column="employee_id" property="employeeId"/>
        <result column="employee_name" property="employeeName"/>
    </association>
    <!-- 一次订单对应多条商品交易信息 -->
    <collection property="orderDetailList" ofType="OrderDetail">
        <id column="order_id" property="orderId"/>
        <id column="goods_id" property="goodsId"/>
        <result column="amount" property="amount"/>
        <result column="sum" property="sum"/>
        <association property="goods" javaType="Goods">
            <id column="goods_id" property="goodsId"/>
            <result column="goods_name" property="goodsName"/>
            <result column="goods_price" property="goodsPrice"/>
        </association>
    </collection>
</resultMap>
<select id="comprehensiveQuery" resultMap="comprehensiveQueryMap">
    SELECT 
        o.order_id, 
        c.customer_name, 
        e.employee_name, 
        g.goods_name, 
        g.goods_price, 
        od.amount, 
        od.sum, 
        o.order_time
    FROM 
        orders o, 
        order_detail od, 
        goods g, 
        customer c, 
        employee e
    WHERE 
        o.order_id = od.order_id
        AND o.customer_id = c.customer_id
        AND o.employee_id = e.employee_id
        AND od.goods_id = g.goods_id
</select>

运行后发现查询结果每条订单编号只有一条商品交易的记录

order_detail 表里面一条订单应该有好几条商品交易的记录

问题原因

因为返回的列没有用于区分权限的 id ，导致 MyBatis 不知道如何区分，就只输出了第一列记录映射的对象

解决方法

给 order_detail 表添加一个 id 列

然后更新实体类和 xml 文件

<resultMap id="comprehensiveQueryMap" type="Orders">
    <id column="order_id" property="orderId"/>
    <result column="employee_id" property="employeeId"/>
    <result column="customer_id" property="customerId"/>
    <result column="order_time" property="orderTime"/>
    <association property="customer" javaType="Customer">
        <id column="customer_id" property="customerId"/>
        <result column="customer_name" property="customerName"/>
    </association>
    <association property="employee" javaType="Employee">
        <id column="employee_id" property="employeeId"/>
        <result column="employee_name" property="employeeName"/>
    </association>
    <collection property="orderDetailList" ofType="OrderDetail">
        <!-- 加上 order_detail_id 字段 -->
        <id column="order_detail_id" property="orderDetailId"/>
        <id column="order_id" property="orderId"/>
        <id column="goods_id" property="goodsId"/>
        <result column="amount" property="amount"/>
        <result column="sum" property="sum"/>
        <association property="goods" javaType="Goods">
            <id column="goods_id" property="goodsId"/>
            <result column="goods_name" property="goodsName"/>
            <result column="goods_price" property="goodsPrice"/>
        </association>
    </collection>
</resultMap>
<select id="comprehensiveQuery" resultMap="comprehensiveQueryMap">
    <!-- 查询时也要加上 order_detail_id 字段，不然也会产生上面出现的问题 -->
    SELECT 
        od.order_detail_id, 
        o.order_id, 
        c.customer_name, 
        e.employee_name,                              
        g.goods_name, 
        g.goods_price, 
        od.amount, 
        od.sum, 
        o.order_time
    FROM 
        orders o, 
        order_detail od, 
        goods g, 
        customer c, 
        employee e
    WHERE 
        o.order_id = od.order_id
        AND o.customer_id = c.customer_id
        AND o.employee_id = e.employee_id
        AND od.goods_id = g.goods_id
    ORDER BY 
        od.order_detail_id
</select>

这样就能查询出正常结果啦

这是今天做作业的时候遇到的一个小 Bug ，记录一下