mybatis|mybatis 实现多层级collection嵌套 mybatis实现多层级collection嵌套

mybatis多层级collection嵌套

json结构
第一步查询
第二步查询
第三步查询（第三层查询）
最后附实体截图，其实看json就可以了
方式二(推荐)

mybatis多层(三层)嵌套查询

java实体描述
mybatisxml
调用方法
注意总结

mybatis多层级collection嵌套
json结构

文章图片

第一步查询
【mybatis|mybatis 实现多层级collection嵌套】第一层查询，将第一层的id传递到第二层当条件查询column="id"

select * from t_shortcut_key_class where id=#{classID} ;

第二步查询
第二层查询，并将第二层的id传递到第三层当条件查询column="funID"是别名

select fun.id as funID,fun.classID,fun.describe,fun.sort from t_shortcut_key_functionas fun where fun.classID=#{classID} order by sort DESC ;

第三步查询（第三层查询）

select * from t_shortcut_key where funID=#{id};

其实蛮好理解的，就是查询一个，然后把条件传递下步继续查询。。。使用时调第一层，也就是从高到底。

最后附实体截图，其实看json就可以了
第一层实体：

文章图片

第二层实体：

文章图片

第三层实体：

文章图片

方式二(推荐)

SELECT c.id,c.name,cc.id as cId,cc.name as cName from t_chapter c,t_chapter_child cc WHERE c.id=cc.chapterID and c.subjectID=#{subjectID}

mybatis多层(三层)嵌套查询
java 实体描述

/** * * 家庭表 *
* * @author lohas */@Data@EqualsAndHashCode(callSuper = true)@TableName("t_family_info")@ApiModel(value = "https://www.it610.com/article/FamilyInfoEntity对象", description = "家庭表")public class FamilyInfoEntity extends Model { private static final long serialVersionUID = 1L; @ApiModelProperty(value = "https://www.it610.com/article/主键")@TableId(value = "https://www.it610.com/article/id", type = IdType.INPUT)private String id; @ApiModelProperty(value = "https://www.it610.com/article/家庭名称")private String familyName; @ApiModelProperty(value = "https://www.it610.com/article/家庭头像url")private String headUrl; @ApiModelProperty(value = "https://www.it610.com/article/国家")private String country; @ApiModelProperty(value = "https://www.it610.com/article/城市")private String city; @ApiModelProperty(value = "https://www.it610.com/article/地址")private String address; @ApiModelProperty(value = "https://www.it610.com/article/经度")private BigDecimal longitude; @ApiModelProperty(value = "https://www.it610.com/article/纬度")private BigDecimal latitude; @ApiModelProperty(value = "https://www.it610.com/article/租户ID")private String tenantId; @ApiModelProperty(value = "https://www.it610.com/article/是否删除（0：正常；低于0的数字：已删除）")private String deleteFlag; @ApiModelProperty(value = "https://www.it610.com/article/创建人ID")private String creatorId; @ApiModelProperty(value = "https://www.it610.com/article/创建时间")@JsonDeserialize(using = LocalDateTimeSerializerConfig.LocalDateTimeDeserializer.class)@JsonSerialize(using = LocalDateTimeSerializerConfig.LocalDateTimeSerializer.class)private LocalDateTime createTime; @ApiModelProperty(value = "https://www.it610.com/article/更新人ID")private String updateId; @ApiModelProperty(value = "https://www.it610.com/article/更新时间")@JsonDeserialize(using = LocalDateTimeSerializerConfig.LocalDateTimeDeserializer.class)@JsonSerialize(using = LocalDateTimeSerializerConfig.LocalDateTimeSerializer.class)private LocalDateTime updateTime; }

/** * @author lohas * @description */@Data@ApiModel(value = "https://www.it610.com/article/RoomDeviceInfoVo对象", description = "房间设备信息实体")public class RoomDeviceInfoVo implements Serializable { @ApiModelProperty(value = "https://www.it610.com/article/房间id")private String roomId; @ApiModelProperty(value = "https://www.it610.com/article/房间名称")private String roomName; @ApiModelProperty(value = "https://www.it610.com/article/设备数量")private Integer deviceNum; @ApiModelProperty(value = "https://www.it610.com/article/设备信息列表")private List deviceIds; }

/** * @author lohas * @description */@Data@ApiModel(value = "https://www.it610.com/article/FamilyRoomInfoVo对象", description = "家庭房间信息对象实例")public class FamilyRoomInfoVo extends FamilyInfoEntity { @ApiModelProperty(value = "https://www.it610.com/article/房间设备信息列表")private List roomDeviceInfoVoList; }

mybatis xml

SELECT *FROM (SELECT tfi.*,trf."id"AS r_room_id,--要加上r因为columnPrefix="r_"(如果字段唯一，可以不加)trf.room_name AS r_room_name, --要加上r因为columnPrefix="r_"trd.device_id AS r_d_device_id --要加上r因为columnPrefix="r_"，加上d因为columnPrefix="d_"FROM t_user_family tufLEFT JOIN t_family_info tfi ON tfi.ID = tuf.family_idLEFT JOIN t_family_room tfr ON tuf.family_id = tfr.family_idLEFT JOIN t_room_info trf ON tfr.room_id = trf.IDLEFT JOIN t_room_device trd ON tfr.room_id = trd.room_idWHERE tuf.user_id = #{userId}ORDER BY tfi.create_time DESC) AS fr,(SELECT trf."id"AS r_room_id,--要加上r因为columnPrefix="r_"COUNT(trd.device_id) AS r_device_num--要加上r因为columnPrefix="r_"FROM t_user_family tufLEFT JOIN t_family_info tfi ON tfi.ID = tuf.family_idLEFT JOIN t_family_room tfr ON tuf.family_id = tfr.family_idLEFT JOIN t_room_info trf ON tfr.room_id = trf.IDLEFT JOIN t_room_device trd ON tfr.room_id = trd.room_idWHERE tuf.user_id = #{userId}GROUP BY trf."id") AS rdWHERE fr.r_room_id = rd.r_room_id