1.组织树形数据库设计

SpringBoot+MybatisPlus架构

树形结构可以想象成三级分类 二级分类，理解成一个层级结构惯性就行

CREATE TABLE `tab_test_org` (
  `S_ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `S_ORG_ID` varchar(36) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '组织(区域)ID',
  `S_ORG_NAME` varchar(100) COLLATE utf8mb4_czech_ci NOT NULL COMMENT '组织名称',
  `S_PARENT_ID` varchar(100) COLLATE utf8mb4_czech_ci NOT NULL COMMENT '父组织id',
  `S_CODE` varchar(100) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '编号',
  `I_TYPE` int(11) DEFAULT NULL COMMENT '组织类别id',
  `I_ORDER` int(11) NOT NULL COMMENT '顺序号',
  `S_PATH` varchar(100) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '组织路径',
  `S_ICON` varchar(100) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '图标',
  `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建日期',
  `UPDATE_TIME` datetime DEFAULT NULL COMMENT '修改日期',
  `CREATE_BY` varchar(36) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '创建者',
  `UPDATE_BY` varchar(36) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '更新者',
  `OBJECT_VERSION` int(10) DEFAULT NULL COMMENT '版本号',
  `I_DELETE_FLAG` tinyint(2) DEFAULT '0' COMMENT '删除标识(0:正常 1:删除)',
  `I_LEVEL` int(11) DEFAULT NULL COMMENT '组织层级',
  `S_DESCRIPTION` varchar(200) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '描述',
  `S_EXTEND_INFO` longtext COLLATE utf8mb4_czech_ci COMMENT '扩展信息',
  `S_DOMAIN_TYPE` varchar(100) COLLATE utf8mb4_czech_ci DEFAULT NULL COMMENT '域类型,0或者空本级,下级域',
  PRIMARY KEY (`S_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_czech_ci COMMENT='组织(区域表)';

2.java代码编写

controller层

  /**
     * @description: 查询组织树数据
     * @param sParentId //父组织ID
     * @return R
     * @date 2021/7/26 14:18
     */
 @RequestMapping("/queryOrgThreeList")
    public R queryOrgThreeList(String sParentId){
        List<OrgThreeInfoVo> orgThreeInfoVos = tabOrgService.queryOrgThreeList(sParentId);
        return R.ok().put("list", orgThreeInfoVos);
    }

ServiceImpl层

  /**
     * @description: 加载组织树信息
     * @param sParentId
     * @return List<OrgThreeInfoVo>
     * @date 2021/7/26 16:03
     */
    @Override
    public List<OrgThreeInfoVo> queryOrgThreeList(String sParentId) {
         //查询组织树信息
        List<TabMvOrgEntity> tabMvOrgEntities = tabMvOrgDao.queryOrgThreeList(sParentId);
        //对数据源的转化
        List<OrgThreeInfoVo> orgThreeInfoVos=new ArrayList<>();
        for (TabMvOrgEntity tabMvOrgEntity : tabMvOrgEntities) {
            OrgThreeInfoVo orgThreeInfoVo=new OrgThreeInfoVo();
            orgThreeInfoVo.setSId(tabMvOrgEntity.getSId());
            //组织Id
            orgThreeInfoVo.setOrgId(tabMvOrgEntity.getSOrgId());
            //组织名称
            orgThreeInfoVo.setOrgName(tabMvOrgEntity.getSOrgName());
            //父组织Id
            orgThreeInfoVo.setSParentId(tabMvOrgEntity.getSParentId());
            orgThreeInfoVos.add(orgThreeInfoVo);
        }
        for (OrgThreeInfoVo orgThreeInfoVo : orgThreeInfoVos) {
            String orgId = orgThreeInfoVo.getOrgId();
            //递归操作
            List<OrgThreeInfoVo> child = queryOrgThreeList(orgId);
            //数据组装判断
            if(!child.isEmpty()){
                orgThreeInfoVo.setChildrenList(child);
            }
        }
        return orgThreeInfoVos;
    }

dao层

/**
 * @description: 加载组织树信息
 * @param sParentId
 * @return List<TabMvOrgEntity>
 * @date 2021/7/26 16:03
 */
List<TabMvOrgEntity> queryOrgThreeList(String sParentId);

mapper层

<sql id="selectOrgALL">
        org.S_ID,org.S_ORG_ID,org.S_ORG_NAME,org.S_PARENT_ID,org.S_CODE,org.I_TYPE,org.I_ORDER,org.S_PATH,org.S_ICON,org.CREATE_TIME,org.UPDATE_TIME
    </sql>
    <select id="queryOrgThreeList" resultMap="tabMvOrgMap">
         select <include refid="selectOrgALL"/> from tab_test_org org where org.I_DELETE_FLAG=0 AND org.S_PARENT_ID = #{sParentId}
    </select>

vo层

/**
 *  区域树信息
 * @author panlupeng
 * @date 2021/7/26
 */
@Data
public class OrgThreeInfoVo implements Serializable {

    private Integer sId;

    private String orgId;

    private String orgName;

    private String sParentId;

    private List<OrgThreeInfoVo> childrenList;
}

po层

/**
 * 组织(区域表)
 * 
 * @author panlupeng
 * @date 2021-07-26 13:56:40
 */
@Data
@TableName("tab_test_org")
public class TabMvOrgEntity implements Serializable {
	private static final long serialVersionUID = 1L;

	/**
	 * 主键ID
	 */
	@TableId
	private Integer sId;
	/**
	 * 组织(区域)ID
	 */
	private String sOrgId;
	/**
	 * 组织名称
	 */
	private String sOrgName;
	/**
	 * 父组织id
	 */
	private String sParentId;
	/**
	 * 编号
	 */
	private String sCode;
	/**
	 * 组织类别id
	 */
	private Integer iType;
	/**
	 * 顺序号
	 */
	private Integer iOrder;
	/**
	 * 组织路径
	 */
	private String sPath;
	/**
	 * 图标
	 */
	private String sIcon;
	/**
	 * 创建日期
	 */
	private Date createTime;
	/**
	 * 修改日期
	 */
	private Date updateTime;
	/**
	 * 创建者
	 */
	private String createBy;
	/**
	 * 更新者
	 */
	private String updateBy;
	/**
	 * 版本号
	 */
	private Integer objectVersion;
	/**
	 * 删除标识(0:正常 1:删除)
	 */
	private Integer iDeleteFlag;
	/**
	 * 组织层级
	 */
	private Integer iLevel;
	/**
	 * 描述
	 */
	private String sDescription;
	/**
	 * 扩展信息
	 */
	private String sExtendInfo;
	/**
	 * 域类型,0或者空本级,下级域
	 */
	private String sDomainType;

}

3.输出结果

{
    "msg": "success",
    "code": 0,
    "list": [
        {
            "orgId": "001",
            "orgName": "组织管理",
            "childrenList": [
                {
                    "orgId": "001001",
                    "orgName": "天津市",
                    "childrenList": [
                        {
                            "orgId": "001001001",
                            "orgName": "西青区",
                            "childrenList": null,
                            "sid": 4,
                            "sparentId": "001001"
                        }
                    ],
                    "sid": 2,
                    "sparentId": "001"
                },
                {
                    "orgId": "001002",
                    "orgName": "北京市",
                    "childrenList": null,
                    "sid": 3,
                    "sparentId": "001"
                }
            ],
            "sid": 1,
            "sparentId": "0"
        }
    ]
}