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 <= #{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;
}