根据某一节点，递归获取其所有子，父节点

1、数据表结构如：

2、需求：根据某一节点，递归获取其所有子，父节点，页面显示树状。如：
3、接口：
Controller

@RequestMapping(value = "", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
  @PreAuthorize("hasAnyAuthority('QueryZoneManager')")
  @Log(name = "获取区域管理节点数据", type = "查询")
  @ApiOperation(value = "获取区域管理节点数据")
  @MultiTenant
  public Set<ZoneForData> queryZone(Principal principal) {
    JwtTokenUser user = (JwtTokenUser) ((OAuth2Authentication) principal).getUserAuthentication()
        .getPrincipal();
    if (StringUtils.isNotEmpty(user.getZoneId())) {
      return zoneService.findAllNodeById(user.getZoneId());
    } else {
      return zoneService.findAllNode();
    }
  }

Service

public Set<ZoneForData> findAllNodeById(String zoneId) {
    Set<ZoneForData> rootMenu = new HashSet<>();
    Optional<Zone> zones = zoneRepository.findById(zoneId);
    List<Zone> zoneList = zoneRepository.findAll();
    zoneList.forEach(r -> {
      ZoneForData zoneForData = ZoneForData.builder().id(r.getId()).nodename(r.getNodename())
          .pid(r.getPid()).level(r.getLevel()).createTime(r.getCreateTime())
          .createUser(r.getCreateUser())
          .build();
      rootMenu.add(zoneForData);
    });
    Set<ZoneForData> menuList = new HashSet<>();
    if(zones.isPresent()){
      ZoneForData zoneForData = ZoneForData.builder().id(zones.get().getId()).nodename(zones.get().getNodename())
          .pid(zones.get().getPid()).level(zones.get().getLevel()).createTime(zones.get().getCreateTime())
          .createUser(zones.get().getCreateUser())
          .build();
      menuList.add(zoneForData);
    }
    for (ZoneForData menu : menuList) {
      menu.setChildren(getChild(menu.getId(), rootMenu));
    }
    return menuList;
  }

private Set<ZoneForData> getChild(String id, Set<ZoneForData> managements) {
    // 子菜单
    Set<ZoneForData> childList = new HashSet<>();
    for (ZoneForData management : managements) {
      // 遍历所有节点，将父菜单id与传过来的id比较
      if (StringUtils.isNotBlank(management.getPid())) {
        if (management.getPid().equals(id)) {
          childList.add(management);
        }
      }
    }
    // 把子菜单的子菜单再循环一遍
    for (ZoneForData management : childList) {
      // 递归
      management.setChildren(getChild(management.getId(), managements));
    } // 递归退出条件
    if (childList.size() == 0) {
      return null;
    }
    return childList;
  }

返回的实体类

package com.atue.smarthome.manageplatform.domain.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.HashSet;
import java.util.Set;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ZoneForData {
  private String id;
  private String nodename;
  private String pid;
  private Integer level;
  private String createUser;
  private Long createTime;
  private Set<ZoneForData> children =new HashSet<>();
}

5、根据节点id获取子、父所有节点。

@Query(value = "select id from `zone` where FIND_IN_SET(id,getZoneChildList(:zoneId));", nativeQuery = true)
    List<String> queryZoneChildListById(@Param("zoneId") String zoneId);

@Query(value = "select id from `zone` where FIND_IN_SET(id,getZoneParentList(:zoneId));", nativeQuery = true)
    List<String> queryZoneParentListById(@Param("zoneId") String zoneId);

存储过程

BEGIN DECLARE ptemp VARCHAR(10000); DECLARE ctemp VARCHAR(10000); SET ptemp = '#'; SET ctemp = rootId; WHILE ctemp IS NOT NULL DO SET ptemp = concat(ptemp, ',', ctemp); SELECT group_concat(id) INTO ctemp FROM zone WHERE FIND_IN_SET(pid, ctemp) > 0; END WHILE; RETURN ptemp; END


BEGIN DECLARE sTemp VARCHAR(10000); DECLARE sTempPar VARCHAR(10000); SET sTemp = ''; SET sTempPar =rootId; WHILE sTempPar is not null DO IF sTemp != '' THEN SET sTemp = concat(sTemp,',',sTempPar); ELSE SET sTemp = sTempPar; END IF; SET sTemp = concat(sTemp,',',sTempPar); SELECT group_concat(pid) INTO sTempPar FROM `zone` where pid<>id and FIND_IN_SET(id,sTempPar)>0; END WHILE; RETURN sTemp; END