How to handle the issue of Mybatis one-to-many queries

1 year ago
William Carter
2 minutes

Mybatis offers great support for handling one-to-many query issues. Here are some methods for dealing with one-to-many query problems.

  1. group of items or objects gathered together
<resultMap id="userMap" type="User">
  <id column="id" property="id" />
  <result column="name" property="name" />
  <collection property="orders" ofType="Order">
    <id column="order_id" property="id" />
    <result column="order_name" property="name" />
  </collection>
</resultMap>

<select id="getUserWithOrders" resultMap="userMap">
  SELECT u.id, u.name, o.id as order_id, o.name as order_name
  FROM user u
  LEFT JOIN order o ON u.id = o.user_id
  WHERE u.id = #{id}
</select>
  1. union
  2. Assortment
<resultMap id="userMap" type="User">
  <id column="id" property="id" />
  <result column="name" property="name" />
  <association property="department" javaType="Department">
    <id column="department_id" property="id" />
    <result column="department_name" property="name" />
    <collection property="employees" ofType="Employee">
      <id column="employee_id" property="id" />
      <result column="employee_name" property="name" />
    </collection>
  </association>
</resultMap>

<select id="getUserWithDepartmentAndEmployees" resultMap="userMap">
  SELECT u.id, u.name, d.id as department_id, d.name as department_name, e.id as employee_id, e.name as employee_name
  FROM user u
  LEFT JOIN department d ON u.department_id = d.id
  LEFT JOIN employee e ON d.id = e.department_id
  WHERE u.id = #{id}
</select>
  1. Using nested queries is a way to handle retrieving multiple related child objects while querying the main object. This approach is suitable for cases where the one-to-many relationship is relatively straightforward.
<select id="getUserWithOrders" resultType="User">
  SELECT u.id, u.name, (SELECT o.id, o.name FROM order o WHERE o.user_id = u.id) as orders
  FROM user u
  WHERE u.id = #{id}
</select>

These methods can be chosen based on specific business needs, taking into account the complexity of relationships and query performance.

bannerAds