树结构 Java8 mysql递归

组织树查询

import java.util.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonFormat;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

@ApiModel(value = "组织结构树")
public class BaseOrgVO {
	private Long id;

	@ApiModelProperty(value = "父类节点id，如节点是根节点则为空")
	private Long pid;

	@ApiModelProperty(value = "对应type字段所具体的名称，同一个父节点下唯一")
	private String name;

	@ApiModelProperty(value = "节点的排序序号")
	private Integer sequence;

	@ApiModelProperty(value = "是否删除，{0:未删除，1:删除}")
	private Integer isdel;

	@ApiModelProperty(value = "创建时间")
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date createtime;

	@ApiModelProperty(value = "组织级别  省，市，分局，学校")
	private String type;

	//BaseOrg 没有这个
	private List<BaseOrgVO> children;

	public BaseOrgVO() {
	}

	public BaseOrgVO(Long id, Long pid, String name, Integer sequence, Integer isdel, Date createtime, String type) {
		this.id = id;
		this.pid = pid;
		this.name = name;
		this.sequence = sequence;
		this.isdel = isdel;
		this.createtime = createtime;
		this.type = type;
	}
	public BaseOrgVO(Long id, Long pid, String name, Integer sequence, Integer isdel, Date createtime, String type,
			List<BaseOrgVO> children) {
		this.id = id;
		this.pid = pid;
		this.name = name;
		this.sequence = sequence;
		this.isdel = isdel;
		this.createtime = createtime;
		this.type = type;
		this.children = children;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Long getPid() {
		return pid;
	}

	public void setPid(Long pid) {
		this.pid = pid;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getSequence() {
		return sequence;
	}

	public void setSequence(Integer sequence) {
		this.sequence = sequence;
	}

	public Integer getIsdel() {
		return isdel;
	}

	public void setIsdel(Integer isdel) {
		this.isdel = isdel;
	}

	public Date getCreatetime() {
		return createtime;
	}

	public void setCreatetime(Date createtime) {
		this.createtime = createtime;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public List<BaseOrgVO> getChildren() {
		return children;
	}

	public void setChildren(List<BaseOrgVO> children) {
		this.children = children;
	}

	@Override
	public String toString() {
		return "BaseOrgVO [id=" + id + ", pid=" + pid + ", name=" + name + ", sequence=" + sequence + ", isdel=" + isdel
				+ ", createtime=" + createtime + ", type=" + type + ", children=" + children + "]";
	}
	
	
}

package com.ctsi.pubinfo.sercice.impl;

import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.ctsi.pubinfo.db.entity.BaseOrg;
import com.ctsi.pubinfo.db.repository.BaseOrgRepository;
import com.ctsi.pubinfo.pojo.BaseOrgVO;
import com.ctsi.pubinfo.sercice.BaseChannelGroupService;
import com.ctsi.pubinfo.sercice.BaseOrgService;

/**
 * @author zjq
 * @version 创建时间：2021-9-17 9:48:15
 */

@Service
public class BaseOrgServicempl implements BaseOrgService {
	
	private static final Long FID = Long.parseLong("0");

	@Autowired
	private BaseOrgRepository baseOrgRepository;

	@Override
	public List<BaseOrgVO> listWithTree() {
		// 获取省市区数据
		List<BaseOrg> entities = baseOrgRepository.findAllWithoutSchool();
		// 把BaseOrg的数组entities包装成BaseOrgVO数组
		List<BaseOrgVO> baseOrgVOS = entities.stream().map(BaseOrg -> {
			BaseOrgVO baseOrgVO = new BaseOrgVO();
			BeanUtils.copyProperties(BaseOrg, baseOrgVO);
			return baseOrgVO;
		}).collect(Collectors.toList());

		
		// 取得所有parentId为0的数据，也就是一级目录	
		//Long 类型 用.equals来作比较   ==的话超过128会为false
		List<BaseOrgVO> list = baseOrgVOS.stream().filter(baseOrgVO -> FID.equals(baseOrgVO.getPid())).map((menu) -> {
			menu.setChildren(getChildrenData(menu, baseOrgVOS));
			return menu;
		}).collect(Collectors.toList());		
		return list;
	}

	// 获取孩子（下级目录）的方法，递归实现
	private List<BaseOrgVO> getChildrenData(BaseOrgVO root, List<BaseOrgVO> all) {

		List<BaseOrgVO> children = all.stream().filter(m -> {
			return m.getPid().equals(root.getId());
		}).map(e -> {
			e.setChildren(getChildrenData(e, all));
			return e;
		}).collect(Collectors.toList());
		return children;
	}

}

省市县分级查询

选择省市县，列出下面所有学校

方法一：构造MySQL函数

CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenAreaInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM base_org WHERE FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END

/**
	 * 省市区查询  调用数据库自定义封装好的函数
	 * @param    show variables like '%func%'  查看MySQL函数能否创建
	 * @param    set global log_bin_trust_function_creators=1   开启函数创建功能
	 * @param    SELECT @@global.group_concat_max_len;     默认1024
	 * @param    SET GLOBAL group_concat_max_len=102400;      超出会报错，设置长度为最大
	 * @param    函数名称     queryChildrenAreaInfo()
	 * @return
	 */
	@Query(value = "SELECT * FROM base_org WHERE FIND_IN_SET(id,queryChildrenAreaInfo(?1)) and type = '学校'" ,nativeQuery = true)
	List<BaseOrg> findAllSchool(Long id); 


方法一：构造MySQL函数
//MySQL版本8.0及以上可使用  MySQL自带的递归
WITH RECURSIVE _children AS
(SELECT b.* FROM base_org b where b.pid='1'
 UNION ALL
SELECT b.* FROM _children,base_org b WHERE b.pid=_children.id
)
SELECT * FROM _children