递归实现树结构查询

2023-07-15 18:43| 来源: 网络整理| 查看: 265

前言，我的需求是要按前端固定的格式返回树结构固定格式如下：

{ // checkArr 固定写死，如果没有这条，树就不会显示勾选框 "checkArr": [ { "type": "0", "checked": "0" } ], // 自定义数据，设备、报警等自定义字段放到这里 "basicData": { // 报警 // 设备 }, "id": "11111", "title": "荣华山中水回用-智慧水务", "children": [] }

有两种实现方式，都是采用的递归思想，可以把这些节点当作一个个文件夹，和点开一层层文件夹一个道理。递归适用于数据不是很大，如果层级关系很复杂，建议一层层查询。我这里是groovy语言，和java类似。

1.递归查询 //将报警的对象id存入map，后续根据对象id是否存在这个map判断是否报警 String sql2 = "SELECT tag_id as tagId\n" + "FROM falarm_alarm_real\n" + "WHERE id NOT IN (SELECT alarm_id FROM falarm_alarm_deal)"; List list2 = dynamicDataSource.excuteTenantSqlQuery(sql2, "t01"); for (Map stringObjectMap : list2) { String tagId = stringObjectMap.get("tagId").toString(); String sql3 = "select structure_uuid as structureUuid from bs_monitor_attr where id = '" + tagId + "'"; List list3 = dynamicDataSource.excuteTenantSqlQuery(sql3, "t01"); alarmMap.put(list3.get(0).get("structureUuid").toString(), list3.get(0).get("structureUuid").toString()); } //查询根节点 String sql = "select id,structure_name as title,parent_uuid as parentId from bs_structure where parent_uuid = 'root' or structure_name = '1111'"; List treeDataList = getGoodsTreeData(sql); findNode(treeDataList); data.put("data", ResultBody.success(treeDataList.get(0))); //递归查询节点 void findNode(List treeDataList) { for (Map map : treeDataList) { String childId = map.get("id").toString(); String childSql = "select id,structure_name as title,parent_uuid as parentId from bs_structure where parent_uuid = '" + childId + "' and object_type in ('设备','非设备')"; List childList = getGoodsTreeData(childSql); findNode(childList); map.put("children", childList); } } //处理数据格式 List getGoodsTreeData(String sql){ List treeDataList = dynamicDataSource.excuteTenantSqlQuery(sql,"t01"); for (Map map : treeDataList) { //固定结构 HashMap map2 = new HashMap(); map2.put("type", "0"); map2.put("checked", "0"); map.put("checkArr", map2); //类型和报警状态 HashMap map3 = new HashMap(); String id = map.get("id").toString(); //1.类型 String sql1 = "select object_type as objectType from bs_structure where id = '" + id + "'"; List objectTypeList = dynamicDataSource.excuteTenantSqlQuery(sql1, "t01"); map3.put("objectType", objectTypeList.get(0).get("objectType")); //2.报警状态 if (ObjectUtil.isEmpty(alarmMap.get(id))) { map3.put("isAlarm", "false"); } else { map3.put("isAlarm", "true"); } map.put("basicData", map3); } return treeDataList; } return data; 解释说明：

1.dynamicDataSource是我们封装好的一个执行sql的类。 2.data可以当作一个返回的map对象。 3.树结构查询只需要看查询根节点和递归查询节点，其它是业务相关代码。

返回示例： { "code": 200, "msg": "执行成功!", "data": { "data": { "result": { "checkArr": { "checked": "0", "type": "0" }, "children": [ { "checkArr": { "checked": "0", "type": "0" }, "children": [ { "checkArr": { "checked": "0", "type": "0" }, "children": [ { "checkArr": { "checked": "0", "type": "0" }, "children": [], "basicData": { "isAlarm": "false", "objectType": "设备" }, "id": "1687745795559", "title": "2号压力设备", "parentId": "1687745035786" } ], "basicData": { "isAlarm": "false", "objectType": "非设备" }, "id": "1687745035786", "title": "管网压力", "parentId": "1688107502733" }, { "checkArr": { "checked": "0", "type": "0" }, "children": [ { "checkArr": { "checked": "0", "type": "0" }, "children": [], "basicData": { "isAlarm": "false", "objectType": "设备" }, "id": "1687764305898", "title": "吉兴", "parentId": "1687745070130" } ], "basicData": { "isAlarm": "false", "objectType": "非设备" }, "id": "1687745070130", "title": "用户数据", "parentId": "1688107502733" }, { "checkArr": { "checked": "0", "type": "0" }, "children": [ { "checkArr": { "checked": "0", "type": "0" }, "children": [], "basicData": { "isAlarm": "false", "objectType": "设备" }, "id": "1688117840953", "title": "中水池液位", "parentId": "1687855969627" } ], "basicData": { "isAlarm": "false", "objectType": "非设备" }, "id": "1687855969627", "title": "中水池", "parentId": "1688107502733" } ], "basicData": { "isAlarm": "false", "objectType": "非设备" }, "id": "1688107502733", "title": "荣华山", "parentId": "11111" } ], "basicData": { "isAlarm": "false", "objectType": "非设备" }, "id": "11111", "title": "智慧水务", "parentId": "root" }, "status": 200, "message": "执行成功", "success": true } }, "success": true } 2.递归sql查询

直接附上sql，这里sql会根据根节点查询需要的字段并按父子关系排序，level字段就是层级，适合只需要后端返回数据，前端根据id和父级id构建树的情况，需要注意mysql-server的版本，我的是5.7，其它版本sql可能有细微变化。sql如下：

WITH RECURSIVE tree AS ( SELECT id, structure_name, object_type, parent_uuid, 0 AS level FROM bs_structure WHERE parent_uuid = 'root' UNION ALL SELECT t.id, t.structure_name, t.object_type, t.parent_uuid, level + 1 FROM bs_structure t INNER JOIN tree ON t.parent_uuid = tree.id ) SELECT id, structure_name, object_type, parent_uuid, level FROM tree ORDER BY level, id; 解释说明

1.需要指明parent_uuid = ‘root’，也就是根节点是什么。 2.第二个select查询的字段，第一个select查询的字段必须有，否则报错。 3.id就是与父节点字段关联的主键，父节点字段就是parent_uuid。

返回示例

注意看id与parent_uuid的关系，还有level字段，代表了层级。在这里插入图片描述

【本文地址】

公司简介

联系我们