-
多表之间存在父子级关系：根据某一级，查询所有子级 & 构建树结构数据
表park_project、park_project_sub 、building 、building_floor 、building_floor_room 存在父子级关系，并且确定是4层。
多表之间存在父子级关系，根据某一级，查询所有子级
现需要
根据某一级，查询这一级下的所有子级，因为所有的子级都分布在不同的表中，如果一张一张表去依次查的话，显然不是很方便，所以需要把它们连接起来作查询。<resultMap id="positionIdsResultMap" type="PositionIdsDTO"> <id column="project_id" property="projectId" /> <id column="project_sub_id" property="projectSubId" /> <id column="building_id" property="buildingId" /> <id column="building_floor_id" property="buildingFloorId" /> <id column="room_id" property="roomId" /> </resultMap> <!-- 根据传入的 projectId 和 positionIdAndType 查询所有关联的位置类型和位置id --> <select id="selectPositionIds" resultMap="positionIdsResultMap"> SELECT pp.id project_id, pps.id project_sub_id, b.id building_id, bf.id building_floor_id, bfr.id room_id, pp.delete_flag, pps.delete_flag, b.delete_flag, bf.delete_flag, bfr.delete_flag FROM park_project pp LEFT JOIN park_project_sub pps ON pps.project_id = pp.id LEFT JOIN building b ON b.sub_project_id = pps.id LEFT JOIN building_floor bf ON bf.building_id = b.id LEFT JOIN building_floor_room bfr ON bfr.floor_id = bf.id WHERE 1 = 1 AND pp.id = #{projectId} <if test="positionType == 1"> AND pp.id = #{positionId} AND pp.delete_flag = 0 </if> <if test="positionType == 2"> AND pps.id = #{positionId} AND pp.delete_flag = 0 AND pps.delete_flag = 0 </if> <if test="positionType == 3"> AND b.id = #{positionId,jdbcType=VARCHAR} AND pp.delete_flag = 0 AND pps.delete_flag = 0 AND b.delete_flag = 0 </if> <if test="positionType == 4"> AND bf.id = #{positionId,jdbcType=VARCHAR} AND pp.delete_flag = 0 AND pps.delete_flag = 0 AND b.delete_flag = 0 AND bf.delete_flag = 0 </if> <if test="positionType == 5"> AND bfr.id = #{positionId,jdbcType=VARCHAR} AND pp.delete_flag = 0 AND pps.delete_flag = 0 AND b.delete_flag = 0 AND bf.delete_flag = 0 AND bfr.delete_flag = 0 </if> </select>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
如上查询完成后，可在代码里针对查询出来的每条数据，根据当前查询的位置类型，作对应的处理
@Override public List<String> selectPositionIds(String projectId, String positionIdAndType) { Set positionIdAndTypeSet= new TreeSet<String>(); String[] split = positionIdAndType.split("-"); // 位置id String positionId = split[0]; // 位置类型 String positionType = split[1]; List<PositionIdsDTO> positionIdsDTOS = buildingFloorRoomRepository.selectPositionIds(projectId, positionId, positionType); // 如果没查询到对应的数据 直接返回null if (positionIdsDTOS == null || positionIdsDTOS.size() == 0) { return new ArrayList<String>(); } // 根据positionType返回对应数据（下面的应该还有问题，应该要再加1个是否为null的判断） if (positionType.equals(BuildingFloorRoomEnum.PROJECT.getCode())){ positionIdsDTOS.forEach(dto -> { positionIdAndTypeSet.add(dto.getProjectId() + "-" + BuildingFloorRoomEnum.PROJECT.getCode()); positionIdAndTypeSet.add(dto.getProjectSubId() + "-" + BuildingFloorRoomEnum.PROJECT_SUB.getCode()); positionIdAndTypeSet.add(dto.getBuildingId() + "-" + BuildingFloorRoomEnum.BUILDING.getCode()); positionIdAndTypeSet.add(dto.getBuildingFloorId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR.getCode()); positionIdAndTypeSet.add(dto.getRoomId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR_ROOM.getCode()); }); } else if (positionType.equals(BuildingFloorRoomEnum.PROJECT_SUB.getCode())) { positionIdsDTOS.forEach(dto -> { positionIdAndTypeSet.add(dto.getProjectSubId() + "-" + BuildingFloorRoomEnum.PROJECT_SUB.getCode()); positionIdAndTypeSet.add(dto.getBuildingId() + "-" + BuildingFloorRoomEnum.BUILDING.getCode()); positionIdAndTypeSet.add(dto.getBuildingFloorId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR.getCode()); positionIdAndTypeSet.add(dto.getRoomId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR_ROOM.getCode()); }); } else if (positionType.equals(BuildingFloorRoomEnum.BUILDING.getCode())) { positionIdsDTOS.forEach(dto -> { positionIdAndTypeSet.add(dto.getBuildingId() + "-" + BuildingFloorRoomEnum.BUILDING.getCode()); positionIdAndTypeSet.add(dto.getBuildingFloorId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR.getCode()); positionIdAndTypeSet.add(dto.getRoomId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR_ROOM.getCode()); }); } else if (positionType.equals(BuildingFloorRoomEnum.BUILDING_FLOOR.getCode())) { positionIdsDTOS.forEach(dto -> { positionIdAndTypeSet.add(dto.getBuildingFloorId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR.getCode()); positionIdAndTypeSet.add(dto.getRoomId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR_ROOM.getCode()); }); } else if (positionType.equals(BuildingFloorRoomEnum.BUILDING_FLOOR_ROOM.getCode())) { positionIdsDTOS.forEach(dto -> { positionIdAndTypeSet.add(dto.getRoomId() + "-" + BuildingFloorRoomEnum.BUILDING_FLOOR_ROOM.getCode()); }); } return new ArrayList<>(positionIdAndTypeSet); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
在mybatisplus中拼接上and (条件1 or 条件2 or 条件3 …)
public List<FacilityLedger> listFacilityLedger(FacilityLedgerListParam param) { List<String> positionIds = param.getPositionIds(); List<String[]> positions = null; if (CollUtil.isNotEmpty(positionIds)) { positions = positionIds.stream() .map(positionId -> positionId.split("-")) .filter(position -> position.length == 2) .collect(Collectors.toList()); } // 只允许查询某一个项目下的数据 if (param != null && param.getProjectId() != null) { List<String[]> finalPositions = positions; LambdaQueryWrapper<FacilityLedger> wrapper = Wrappers.lambdaQuery(FacilityLedger.class) // 项目id .eq(FacilityLedger::getProjectId, param.getProjectId()) // 未删除 .eq(FacilityLedger::getDeleteFlag, StatusType.INVALID.getValue()) // 设备id .in(CollUtil.isNotEmpty(param.getIds()), FacilityLedger::getId, param.getIds()) // 分类id .in(CollUtil.isNotEmpty(param.getClassificationIds()), FacilityLedger::getClassificationId, param.getClassificationIds()) // 重要等级 .in(CollUtil.isNotEmpty(param.getImportanceLevels()), FacilityLedger::getImportanceLevel, param.getImportanceLevels()) // 使用状态 .in(CollUtil.isNotEmpty(param.getUseStatuss()), FacilityLedger::getUseStatus, param.getUseStatuss()) // 责任部门id .in(CollUtil.isNotEmpty(param.getResponsibleDeptIds()), FacilityLedger::getResponsibleDeptId, param.getResponsibleDeptIds()) // 大于等于首次启用时间 -开始 .ge(param.getFirstEnabledTimeStart() != null, FacilityLedger::getFirstEnabledTime, param.getFirstEnabledTimeStart()) // 小于等于首次启用时间 - 结束 .le(param.getFirstEnabledTimeEnd() != null, FacilityLedger::getFirstEnabledTime, param.getFirstEnabledTimeEnd()) .and(StringUtils.isNotEmpty(param.getNameKey()), (queryWrapper) -> queryWrapper.like( // 根据设备名字进行模糊查询 StrUtil.isNotEmpty(param.getNameKey()), FacilityLedger::getFacilityName, param.getNameKey()) // 根据分类编号进行模糊查询 .or().like(StrUtil.isNotEmpty(param.getNameKey()), FacilityLedger::getClassificationCode, param.getNameKey()) // 根据设备位置进行模糊查询 .or().like(StrUtil.isNotEmpty(param.getNameKey()), FacilityLedger::getPositionDetails, param.getNameKey())) // 根据位置类型和位置id进行查询 .and( CollUtil.isNotEmpty(finalPositions), wrapper1 -> { for (String[] position : finalPositions) { wrapper1.or() .eq(FacilityLedger::getPositionId, position[0]) .eq(FacilityLedger::getPositionType, position[1]); } }) .last(" order by ISNULL(importance_level),importance_level asc, create_time desc "); List<FacilityLedger> list = list(wrapper); return list; } return Collections.emptyList(); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
多表之间存在父子级关系，构建树结构数据
- 注意下面这个left join连接的时候的这个delete_flag条件是写在on这里的，而不是写在where后面，这是有区别的，写在on这里是连接条件，如果没有匹配的左边表的数据仍然会保留，而如果写在where后面，则即使匹配了，左表的数据仍然可能被过滤了
- collection标签的使用
<resultMap id="ResourceTreeMap" type="ResourceNode"> <id column="project_id" property="id"/> <result column="project_name" property="name"/> <result column="project_type" property="type"/> <collection property="children" ofType="ResourceNode"> <id column="sub_project_id" property="id"/> <result column="sub_project_type" property="type"/> <result column="sub_project_stage" property="name"/> <collection property="children" ofType="ResourceNode"> <id column="building_id" property="id"/> <result column="building_type" property="type"/> <result column="building_name" property="name"/> <collection property="children" ofType="ResourceNode"> <id column="floor_id" property="id"/> <result column="floor_type" property="type"/> <result column="floor_name" property="name"/> <collection property="children" ofType="ResourceNode"> <id column="room_id" property="id"/> <result column="room_type" property="type"/> <result column="room_name" property="name"/> </collection> </collection> </collection> </collection> </resultMap> <select id="selectRoomTree" resultMap="ResourceTreeMap"> SELECT t.id AS project_id, ( CASE WHEN f.id IS NOT NULL THEN 1 ELSE NULL END ) AS project_type, t.project_name, sub.id AS sub_project_id, ( CASE WHEN f.id IS NOT NULL THEN 2 ELSE NULL END ) AS sub_project_type, CONCAT( sub.project_stage ) AS sub_project_stage, b.id AS building_id, ( CASE WHEN f.id IS NOT NULL THEN 3 ELSE NULL END ) AS building_type, b.building_name AS building_name, f.id AS floor_id, ( CASE WHEN f.id IS NOT NULL THEN 4 ELSE NULL END ) AS floor_type, f.floor_name AS floor_name, r.id AS room_id, ( CASE WHEN r.id IS NOT NULL THEN 5 ELSE NULL END ) AS room_type, r.room_code AS room_name FROM park_project t LEFT JOIN park_project_sub sub on t.id = sub.project_id and sub.delete_flag = 0 LEFT JOIN building b on sub.id = b.sub_project_id and b.delete_flag = 0 LEFT JOIN building_floor f on b.id = f.building_id and f.delete_flag = 0 LEFT JOIN building_floor_room r on f.id = r.floor_id and r.delete_flag = 0 and r.valid_state = 1 where t.id = #{projectId} <if test="supportType != null"> and r.support_business_type like concat('%', #{surpportType}, '%') </if> <if test="resourceName != null"> and ( t.project_name like concat('%', #{resourceName}, '%') or sub.project_stage like concat('%', #{resourceName}, '%') or b.building_name like concat('%', #{resourceName}, '%') or f.floor_name like concat('%', #{resourceName}, '%') or r.room_code like concat('%', #{resourceName}, '%') ) </if> ORDER BY sub.project_stage asc,b.id desc,f.sort_num asc,r.id desc </select>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
public class ResourceNode implements Serializable { /** * 资源ID */ private Long id; /** * 父级id */ private Long parentId; /** * 资源名称 */ private String name; /** * 类型 1-园区，2-分期，3-楼宇，4楼层，5-房源（默认） */ private int type; /** * 子级 */ private List<ResourceNode> children; /** * 备注 */ private String remarks; /** * 父级名称 */ private String parentName; public ResourceNode() { } public ResourceNode(Long id, Long parentId, String name, int type, String parentName) { this.id = id; this.parentId = parentId; this.name = name; this.type = type; this.parentName = parentName; } }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
-
相关阅读:
C# Redis 分布式锁 续集 （新增Mysql分布式锁[还有更多种姿势]）
【Web前端】HTML详解(下篇)
语音特征：spectrogram、Fbank(fiterbank)、MFCC
直线导轨的滑块损坏时怎么办？
4.1 数据库安全性概述
Java多线程：从基本概念到避坑指南
面试必考精华版Leetcode1466. 重新规划路线
第四章redis配置文件的介绍
R语言鸢尾花iris数据集的层次聚类分析
Flutter 开发问题记录
- 原文地址：https://blog.csdn.net/qq_16992475/article/details/134439162
