Four ways to write Mybatise one-to-many association query

Mybatis one-to-many association query has the following writing methods:

Use nested query (Nested Select): Use a subquery in the main query to convert the one-to-many relationship into two independent queries. After obtaining one data in the main query, obtain more data in the subquery. Finally the two results are merged together. The advantage of this writing method is that the syntax is simple, and the disadvantage is that multiple queries need to be initiated.

Use nested results (Nested Results): Use Mybatis’s nested result mapping method to map multiple result sets into a Java object. The advantage of this writing method is that multiple results can be mapped into one object, which is convenient for operation. The disadvantage is that the result mapping relationship needs to be manually set.

1 Use nested query (Nested Select)

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class SsyWaterTankDto implements Serializable {<!-- -->

    private static final long serialVersionUID = 1563650409808090068L;
    /**
     * primary key
     */
    private Long id;

    /**
     * Pump room ID
     */
    private Integer stationId;

    private String stationCode;

    /**
     * Tank code
     */
    private String tankCode;

    /**
     * Water tank information list
     */
    private List<SsyWaterTankDto> tankList;

    /**
     * put into use time
     */
    private String usageTime;

    /**
     * Last cleaning time
     */
    private String lastCleanTime;

    /**
     * Next cleaning time
     */
    private String nextCleanTime;

    /**
     * Cleaning cycle
     */
    private Integer cleanCycle;

    /**
     * Creation time
     */
    private String createTime;

    /**
     * Last Modified
     */
    private String updateTime;

    /**
     * address
     */
    private String address;

    /**
     * Site name
     */
    private String stationName;

    /**
     * Number of washes
     */
    private Integer cleanNum;

    /**
     * remaining time
     */
    private String remainingTime;

    /**
     * Water tank serial number 1-1# water tank; 2-2# water tank
     */
    private Integer orderBy;

    /**
     * Tank level with unit
     */
    private String waterLevel;

    /**
     * Time status
     * 1-Red 2-Yellow 3-Blue 0-Normal
     */
    private String timeStatus;

    /**
     * Liquid level height ratio
     */
    private BigDecimal rate;
    /**
     * unit
     */
    private String unit;

    /**
     * Whether to call the police
     */
    private Boolean alarm;

    /**
     * Water tank capacity
     */
    private BigDecimal capacity;


}

 <select id="queryWaterTankList" resultMap="getStationWaterTank">
    SELECT
    wt.station_id,
    wt. usage_time,
    wt.last_clean_time,
    wt. next_clean_time,
    wt. clean_cycle,
    wt. create_time,
    wt.update_time,
    ps.station_name,
    ps.station_code,
    ps. `address`,
    (SELECT COUNT(1) FROM ssy_water_tank_record wtr WHERE wtr. tank_id = wt. id) as cleanNum
    FROM
    ssy_water_tank wt LEFT JOIN ssy_pump_station ps on wt.station_id= ps.id
    LEFT JOIN ssy_area sa on ps.area_id = sa.id
    WHERE ps.ownership = #{<!-- -->ownership} and wt.del_status = '0' and ps.`status` = '0'
    <if test="areaId != null">
      and (sa.id = #{<!-- -->areaId} or sa.parent_id = #{<!-- -->areaId})
    </if>
    <if test="stationId != null">
      and wt.station_id = #{<!-- -->stationId}
    </if>
    <if test="stationName != null and stationName != ''">
      and ps.station_name like CONCAT('%',#{<!-- -->stationName}, '%')
    </if>
    <if test="nextCleanTimeEnd != null and nextCleanTimeEnd != ''">
      AND wt.next_clean_time &lt;= #{<!-- -->nextCleanTimeEnd}
    </if>
    <if test="nextCleanTimeBegin != null and nextCleanTimeBegin != ''">
      AND wt.next_clean_time > #{<!-- -->nextCleanTimeBegin}
    </if>
    group by wt.station_id
    order by wt. next_clean_time
  </select>

  <resultMap type="cc.eslink.secondsupply.domain.dto.SsyWaterTankDto" id="getStationWaterTank">
    <result column="station_id" property="stationId"/>
    <result column="usage_time" property="usageTime"/>
    <result column="last_clean_time" property="lastCleanTime"/>
    <result column="next_clean_time" property="nextCleanTime"/>
    <result column="clean_cycle" property="cleanCycle"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <result column="station_name" property="stationName"/>
    <result column="station_code" property="stationCode"/>
    <result column="address" property="address"/>
    <result column="cleanNum" property="cleanNum"/>
    <collection property="tankList" javaType="list" column="{stationId = station_id}" select="selectWaterTankByStation"/>
  </resultMap>

  <select id="selectWaterTankByStation" resultType="cc.eslink.secondsupply.domain.dto.SsyWaterTankDto">
    SELECT
    id,
    station_id as stationId,
    tank_code as tankCode,
    usage_time as usageTime,
    last_clean_time as lastCleanTime,
    next_clean_time as nextCleanTime,
    clean_cycle as cleanCycle,
    create_time as createTime,
    update_time as updateTime,
    order_by as orderBy,
    capacity as capacity
    FROM
    ssy_water_tank
    WHERE station_id = #{<!-- -->stationId}
    and del_status = '0'
    </select>

<select id="selectDepartmentsWithEmployees" resultType="Department">
    SELECT d.id, d.name, e.id as employee_id, e.name as employee_name
    FROM department d
    LEFT JOIN employee e ON d.id = e.department_id
    ORDER BY d.id, e.id
</select>

2 Use Nested Results

 <select id="selectAreaNode" resultMap="selectAreaNode">
    select
    sa.id, sa.parent_id, sa.area_name, sa.create_time, sa.update_time,
    sa.ownership, sa.`status`, sa.region, sa.coordinate, sa.neighborhood,
    b.id as id_son, b.parent_id as parent_id_son, b.area_name as area_name_son,
    b. create_time as create_time_son, b. update_time as update_time_son,
    b.ownership as ownership_son, b.`status` as status_son, b.region as region_son,
    b. coordinate as coordinate_son, b. neighborhood as neighborhood_son
    from
    (select * from ssy_area where `status`= 0 and parent_id = -1) sa
    left join ssy_area b on sa.id = b.parent_id
    where 1=1
    <if test='areaName != null and areaName != ""'>
      and (sa.area_name like CONCAT('%',#{<!-- -->areaName},'%') or b.area_name like CONCAT('%',#{<!- - -->areaName},'%') )
    </if>
    order by sa.update_time desc , b.update_time desc
  </select>

  <resultMap type="cc.eslink.secondsupply.domain.dto.AreaDto" id="selectAreaNode">
    <id property="id" column="id"/>
    <result column="parent_id" property="parentId"/>
    <result column="area_name" property="areaName"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <result column="ownership" property="ownership"/>
    <result column="status" property="status"/>
    <result column="status" property="status"/>
    <result column="region" property="region"/>
    <result column="coordinate" property="coordinate"/>
    <result column="neighborhood" property="neighborhood"/>
    <collection property="list" javaType="list" ofType="cc.eslink.secondsupply.domain.dto.AreaDto">
      <id column="id_son" property="id"/>
      <result column="parent_id_son" property="parentId"/>
      <result column="area_name_son" property="areaName"/>
      <result column="create_time_son" property="createTime"/>
      <result column="update_time_son" property="updateTime"/>
      <result column="ownership_son" property="ownership"/>
      <result column="status_son" property="status"/>
      <result column="status_son" property="status"/>
      <result column="region_son" property="region"/>
      <result column="coordinate_son" property="coordinate"/>
      <result column="neighborhood_son" property="neighborhood"/>
    </collection>
  </resultMap>

package cc.eslink.secondsupply.domain.dto;

import com.google.common.collect.ImmutableMap;
import lombok. AllArgsConstructor;
import lombok. Builder;
import lombok.Data;
import lombok. NoArgsConstructor;
import org.apache.dubbo.common.utils.CollectionUtils;

import java.io.Serializable;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @Author : wxj
 * @Date : 2022/2/18 15:20
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class AreaDto implements Serializable {<!-- -->


    private static final long serialVersionUID = 2673903588299859681L;
    /**
     * ID, primary key auto-increment
     */
    private Long id;

    /**
     * parent id
     */
    private Long parentId;

    /**
     * Area name
     */
    private String areaName;

    /**
     * geographic range
     */
    private String neighborhood;

    /**
     * Creation time
     */
    private Date createTime;

    /**
     * Last Modified
     */
    private Date updateTime;

    /**
     * Tenant ID
     */
    private String ownership;

    /**
     * Status [0-Normal 1-Deleted]
     */
    private Integer status;

    /**
     * geographic range
     */
    private String region;

    /**
     * Center coordinates
     */
    private String coordinate;

    private double sum;

    private List<AreaDto> list;
}