What is the method for performing nested queries in MyBatis for one-to-many relationships?

2 years ago
Olivia Parker
2 minutes

In MyBatis, the method to implement a nested query for one-to-many relationships is to use either nested result mapping or nested select.

  1. Elements are used to define one-to-many relationships. For example, there is an Order entity class and an OrderItem entity class, where one order can contain multiple order items. The relationship with OrderItem can be defined by using elements in the mapping file of Order, specifying the associated field and mapping result of OrderItem. In this way, when querying Order, its associated OrderItem will be automatically queried.

The sample code is as follows:

<!-- Order的映射文件 -->
<resultMap id="orderMap" type="Order">
  <id property="id" column="order_id"/>
  <!-- 其他属性映射 -->

  <collection property="orderItems" ofType="OrderItem" resultMap="orderItemMap"/>
</resultMap>

<resultMap id="orderItemMap" type="OrderItem">
  <id property="id" column="item_id"/>
  <!-- 其他属性映射 -->
</resultMap>

<select id="getOrder" resultMap="orderMap">
  SELECT *
  FROM orders
  WHERE order_id = #{orderId}
</select>
  1. Define the relationship with another query statement using elements, specify the associated fields, and map the results of the query statement.

The code sample is as follows:

<!-- Order的映射文件 -->
<resultMap id="orderMap" type="Order">
  <id property="id" column="order_id"/>
  <!-- 其他属性映射 -->

  <collection property="orderItems" ofType="OrderItem" select="getOrderItemsByOrderId"/>
</resultMap>

<select id="getOrder" resultMap="orderMap">
  SELECT *
  FROM orders
  WHERE order_id = #{orderId}
</select>

<select id="getOrderItemsByOrderId" resultType="OrderItem">
  SELECT *
  FROM order_items
  WHERE order_id = #{orderId}
</select>

The above are two methods to achieve nested one-to-many queries in MyBatis, you can choose the suitable method according to your specific needs.

bannerAds