mybatis实现一对多查询

MyBatis可以通过嵌套查询和resultMap来实现一对多查询。

假设有两个表，一个是订单表(order)，另一个是订单项表(order_item)，一个订单可以有多个订单项。那么我们就可以通过以下方式实现一对多查询：

1.编写SQL语句

SELECT o.*, oi.* FROM order o LEFT JOIN order_item oi ON o.order_id = oi.order_id WHERE o.order_id = #{orderId}

2.在mapper.xml文件中定义select语句

<select id="selectOrderAndItemsById" resultMap="orderResultMap">
    SELECT o.*, oi.* FROM order o LEFT JOIN order_item oi ON o.order_id = oi.order_id WHERE o.order_id = #{orderId}
</select>

3.在resultMap中定义一对多关系

<resultMap id="orderResultMap" type="Order">
    <id column="order_id" property="orderId" />
    <result column="order_no" property="orderNo" />
    <collection property="orderItems" ofType="OrderItem">
        <id column="order_item_id" property="orderItemId" />
        <result column="item_name" property="itemName" />
        <result column="item_price" property="itemPrice" />
    </collection>
</resultMap>

4.在实体类中定义一对多关系

public class Order {
    private Integer orderId;
    private String orderNo;
    private List<OrderItem> orderItems;
    // getter and setter
}

public class OrderItem {
    private Integer orderItemId;
    private String itemName;
    private BigDecimal itemPrice;
    // getter and setter
}

5.调用查询方法

Order order = orderMapper.selectOrderAndItemsById(1);