Mybatise 一对多关联查询的四种写法

Mybatis 一对多关联查询有以下几种写法：

使用嵌套查询（Nested Select）：在主查询中使用子查询，将一对多关系转化为两个独立的查询，主查询中获取到一的数据后，再在子查询中获取多的数据，最后将两个结果合并在一起。这种写法的优点是语法简单，缺点是需要发起多次查询。

使用嵌套结果（Nested Results）：使用 Mybatis 的嵌套结果映射方式，将多个结果集映射为一个 Java 对象。这种写法的优点是可以将多个结果映射为一个对象，方便操作，缺点是需要手动设置结果映射关系。

1 使用嵌套查询（Nested Select）

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class SsyWaterTankDto implements Serializable {

    private static final long serialVersionUID = 1563650409808090068L;
    /**
     * 主键
     */
    private Long id;

    /**
     * 泵房ID
     */
    private Integer stationId;

    private String stationCode;

    /**
     * 水箱编码
     */
    private String tankCode;

    /**
     * 水箱信息列表
     */
    private List<SsyWaterTankDto> tankList;

    /**
     * 投入使用时间
     */
    private String usageTime;

    /**
     * 上次清洗时间
     */
    private String lastCleanTime;

    /**
     * 下次清洗时间
     */
    private String nextCleanTime;

    /**
     * 清洗周期
     */
    private Integer cleanCycle;

    /**
     * 创建时间
     */
    private String createTime;

    /**
     * 最后修改时间
     */
    private String updateTime;

    /**
     * 地址
     */
    private String address;

    /**
     * 站点名称
     */
    private String stationName;

    /**
     * 清洗次数
     */
    private Integer cleanNum;

    /**
     * 剩余时长
     */
    private String remainingTime;

    /**
     * 水箱序号 1-1#水箱；2-2#水箱
     */
    private Integer orderBy;

    /**
     * 水箱液位 带单位
     */
    private String waterLevel;

    /**
     * 时间状态
     * 1-红色 2-黄色 3-蓝色 0-正常
     */
    private String timeStatus;

    /**
     * 液位高度比例
     */
    private BigDecimal rate;
    /**
     * 单位
     */
    private String unit;

    /**
     * 是否报警
     */
    private Boolean alarm;

    /**
     * 水箱容量
     */
    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 使用嵌套结果（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.neighbourhood,
    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.neighbourhood as neighbourhood_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="neighbourhood" property="neighbourhood"/>
    <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="neighbourhood_son" property="neighbourhood"/>
    </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，主键自增
     */
    private Long id;

    /**
     * 父级id
     */
    private Long parentId;

    /**
     * 片区名称
     */
    private String areaName;

    /**
     * 区域范围
     */
    private String neighbourhood;

    /**
     * 创建时间
     */
    private Date createTime;

    /**
     * 最后修改时间
     */
    private Date updateTime;

    /**
     * 租户ID
     */
    private String ownership;

    /**
     * 状态[0-正常 1-删除]
     */
    private Integer status;

    /**
     * 区域范围
     */
    private String region;

    /**
     * 中心坐标
     */
    private String coordinate;

    private double sum;

    private List<AreaDto> list;
}