机构层级列表递归展示查询，并优化SQL查询

爱被打了一巴掌 2023-08-17 16:01 84阅读 0赞

### 文章目录 ###

*   *   *  问题背景
         *  原始方案
         *   *  ①按照层级进行递归查询
             *  ② 定义递归体进行递归查询
             *  ③ 查询结果（部分省略截图）
             *  ④原始方案总结
         *  优化需求
         *  优化方案
         *   *  ①查询出所有机构信息作为数据源，后面使用代码手动处理
             *  ② 递归体的实现
             *  ③ 查询结果（部分省略截图）
             *  ④ 新方案总结
         *  总结

### 问题背景 ###

接口需求：查询所有有效机构信息，并按照机构层级进行展示，用于前端的机构树展示  
接口定义如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70]  
很明显，这样的机构列表需要进行递归查询。

### 原始方案 ###

#### ①按照层级进行递归查询 ####

@RequestMapping(value = "/getOfficeInfo",method = RequestMethod.POST)
    	public Map<String,Object> getOfficeInfo(){ 
    		Map<String,Object> map = new HashMap<>();
    		TbBdsSysOffice office = new TbBdsSysOffice();
    		office.setGrade("1");
    		//查询出所有的一级机构
    		List<TbBdsSysOffice> officeList = service.getOfficeInfoById(office);
    
    			List<Map<String,Object>> list = new ArrayList<>();
    			//遍历一级机构
    			officeList.forEach(tempOffice->{ 
    			   Map<String,Object> mapTemp = new HashMap<>();
    				mapTemp.put("companyId",tempOffice.getId());
    				mapTemp.put("companyName",tempOffice.getName());
    				//调用递归体
    				mapTemp.put("childrenList",getOfficeByRecursion(tempOffice));
    				list.add(mapTemp);
    			});
    		map.put("list",list);
    		return map;
    	}

#### ② 定义递归体进行递归查询 ####

/** * 递归体 * @param office * @return */
    	public List<Map<String,Object>> getOfficeByRecursion(TbBdsSysOffice office){ 
    		Map<String,Object> map = new HashMap<>();
    		List<List<Map<String,Object>>> resultList = new ArrayList();
    		TbBdsSysOffice newOffice = new TbBdsSysOffice();
    		newOffice.setParentId(office.getId());
    		//查询下一级机构信息（访问数据库，产生SQL）
    		List<TbBdsSysOffice> officeList = service.getOfficeInfoById(newOffice);
    		List<Map<String,Object>> list = new ArrayList<>();
    		if(null!=officeList&&officeList.size()>0){ 
    		//遍历下一级机构信息，并封装返回参数
    			officeList.forEach(tempOffice->{ 
    				Map<String,Object> mapTemp = new HashMap<>();
    				mapTemp.put("companyId",tempOffice.getId());
    				mapTemp.put("companyName",tempOffice.getName());
    				//进行递归调用
    				mapTemp.put("childrenList",getOfficeByRecursion(tempOffice));
    				list.add(mapTemp);
    			});
    			resultList.add(list);
    		}
    		return list;
    	}

#### ③ 查询结果（部分省略截图） ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70 1]

#### ④原始方案总结 ####

满足接口需求，数据也很完美的返回了，但是存在不足  
**查看日志会发现，在请求这一方法时，会调用大量的SQL查询语句，主要是递归体中包含了service查询接口，这样每一条递归都会去查询数据库，所以频繁的与数据库进行交互是不可取的，而且目前这个数量级还很小，如果后面后面数据量大的话，这儿会很耗时，所以需要进行优化**

### 优化需求 ###

由于上一个方案在实现方式上存在缺陷，所以新需求是优化SQL语句，避免与数据库的频繁交互。（这个需求是卫老师在离职前给我留的最后一个工单，在这里缅怀一下敬爱的卫老师，刚进公司是他一直带着我的。之前一直在忙着新需求的开发，就把这个优化需求延后了，今天才着手处理了一下）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70 2]

### 优化方案 ###

#### ①查询出所有机构信息作为数据源，后面使用代码手动处理 ####

@RequestMapping(value = "/getOfficeInfo",method = RequestMethod.POST)
    	public Map<String,Object> getOfficeInfo(){ 
    		Map<String, Object> map = new HashMap<>();
    		TbBdsSysOffice office = new TbBdsSysOffice();
    		//第一步：获取所有机构列表
    		List<TbBdsSysOffice> officeListAll = service.getOfficeInfoById(office);
    		//第二步：获取一级机构列表
    		List<TbBdsSysOffice> officeList = officeListAll.stream().filter(one -> one.getGrade().equals("1")).collect(Collectors.toList());
    		List<Map<String, Object>> list = new ArrayList<>();
    		//遍历一级机构
    			officeList.forEach(tempOffice->{ 
    				Map<String, Object> mapTemp = new HashMap<>();
    				mapTemp.put("companyId",tempOffice.getId());
    				mapTemp.put("companyName",tempOffice.getName());
    				//调用递归体
    				mapTemp.put("childrenList", getOfficeByRecursion(tempOffice, officeListAll));
    				list.add(mapTemp);
    			});
    		map.put("list", list);
    		return map;
    	}

这里其实就使用一条查询语句就够了，查询出了所有数据，然后进行过滤和遍历，下面的递归体中也是使用了这个所有数据作为比较，筛选出符合要求的数据。这里值得提下，java8处理集合的特性，真是特别好用，今天还强烈的安利给了我的两位同事。

#### ② 递归体的实现 ####

/** * 递归体 * @param office * @param officeListAll 机构所有信息 * @return */
    	public List<Map<String, Object>> getOfficeByRecursion(TbBdsSysOffice office, List<TbBdsSysOffice> officeListAll) { 
    		Map<String,Object> map = new HashMap<>();
    		List<List<Map<String,Object>>> resultList = new ArrayList();
    		TbBdsSysOffice newOffice = new TbBdsSysOffice();
    		newOffice.setParentId(office.getId());
    		//筛选出下一级的机构信息
    		List<TbBdsSysOffice> officeList = officeListAll.stream().filter(one -> one.getParentId().equals(office.getId())).collect(Collectors.toList());
    		List<Map<String,Object>> list = new ArrayList<>();
    		if(null!=officeList&&officeList.size()>0){ 
    		//遍历下一级的机构信息，并封装返回参数
    			officeList.forEach(tempOffice->{ 
    				Map<String,Object> mapTemp = new HashMap<>();
    				mapTemp.put("companyId",tempOffice.getId());
    				mapTemp.put("companyName",tempOffice.getName());
    				//调用递归体
    				mapTemp.put("childrenList", getOfficeByRecursion(tempOffice, officeListAll));
    				list.add(mapTemp);
    			});
    			resultList.add(list);
    		}
    		return list;
    	}

这里的递归体跟原始方案相比，就是在筛选下一级机构哪儿不同，原始是通过数据库查询得到下一级信息（这就是症结点所在），而新方案是使用现有信息进行过滤得到下一级信息，避免了对数据的访问

#### ③ 查询结果（部分省略截图） ####

同样的数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70 3]  
再看一下，日志输出：  
![在这里插入图片描述][20190927191921400.png]  
只产生了一条SQL语句。完美！

#### ④ 新方案总结 ####

主要是换了一种思维，不用每次都去数据库里面查，处理现有的数据就好，即使是使用主键查询，尽量不用，还是不用的好，避免和数据库的多次交互。

### 总结 ###

之前的优化方案是，使用mysql直接完成递归查询返回，这也是卫老师提供给我的思路，但是早上在进行实践的时候发现，虽然可以使用mysql递归进行查询，但是没法保留层级关系（接口返回需要），那返回回来的数据还是得手动进行处理，就没考虑那种方案，JAVA8的新特性也比较给力，代码简洁了不少，推荐一个java8的教程，简单易懂，强烈安利一下[java8教程][java8]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70]: /images/20230810/3a9d200b31aa4ab6ab7bf439bf07efa2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70 1]: /images/20230810/16241c2efcf6433ab5f4c85d806f2055.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70 2]: /images/20230810/1eaefc1578e94b8b9ff9c37c62f7e83e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoZW56aG91X3lo_size_16_color_FFFFFF_t_70 3]: /images/20230810/fc4e41498bc84e29b178ae60d5e0a17b.png
[20190927191921400.png]: /images/20230810/6fe6ec1da0c245c1ae7453081dccc0bd.png
[java8]: https://www.mkyong.com/tutorials/java-8-tutorials/