MySQL+Java实现组织递归

一、java代码实现递归

组织表sql

CREATE TABLE `my_org` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_name` varchar(50) DEFAULT NULL COMMENT '组织名称',
  `org_code` varchar(50) DEFAULT NULL COMMENT '组织code',
  `parent_name` varchar(50) DEFAULT NULL COMMENT '父类名称',
  `parent_code` varchar(50) DEFAULT NULL COMMENT '父类code',
  `route` varchar(100) DEFAULT NULL COMMENT '层级路径',
  `org_desc` varchar(500) DEFAULT NULL COMMENT '组织描述',
  `status` int(2) DEFAULT NULL COMMENT '是否上线（1：是，0：否）',
  `create_user` varchar(50) DEFAULT NULL COMMENT '创建人',
  `update_user` varchar(50) DEFAULT NULL COMMENT '更新人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_code` (`org_code`) USING BTREE,
  KEY `idx_parent_code` (`parent_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COMMENT='组织表';

表数据

id    org_name    org_code    parent_name    parent_code    route    org_desc    status    create_time
25    财政部    111        0    0,111    财政部    1    2022/7/26 15:15
26    财政部A    222    财政部    111    0,111,222    财政部A    1    2022/7/26 15:17
27    财政部B    333    财政部    111    0,111,333    财政部B    1    2022/7/26 15:18
28    文艺部    444        0    0,444    文艺部    1    2022/7/26 15:19
29    文艺部X    555    文艺部    444    0,444,555    文艺部X    1    2022/7/26 15:19
30    文艺部U    666    文艺部    444    0,444,666    文艺部U    1    2022/7/26 15:19
35    文艺部U    777    文艺部    444    0,444,777    文艺部U    1    2022/7/26 18:07
36    文艺部K    888    文艺部    444    0,444,888    文艺部K    1    2022/7/27 9:48

组织实体类

/**
 * <p>
 * 组织表
 * </p>
 *
 * @author 夏威夷8080
 * @since 2022-07-26
 */
@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="MyOrg对象", description="组织表")
public class MyOrg implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "组织名称")
    private String orgName;

    @ApiModelProperty(value = "组织code")
    private String orgCode;

    @ApiModelProperty(value = "父类名称")
    private String parentName;

    @ApiModelProperty(value = "父类code")
    private String parentCode;

    @ApiModelProperty(value = "层级路径")
    private String route;

    @ApiModelProperty(value = "组织描述")
    private String orgDesc;

    @ApiModelProperty(value = "是否上线（1：是，0：否）")
    private Integer status;

    @ApiModelProperty(value = "创建人")
    private String createUser;

    @ApiModelProperty(value = "更新人")
    private String updateUser;

    @ApiModelProperty(value = "创建时间")
    private Date createTime;

    @ApiModelProperty(value = "更新时间")
    private Date updateTime;


}

组织树dto

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

@Data
public class MyOrgTreeDTO extends MyOrg implements Serializable {


    private static final long serialVersionUID = -2220459684448678777L;

    @ApiModelProperty(value = "子组织集合")
    private List<MyOrgTreeDTO> childList;

}

递归查询组织树，入参是一个父节点code

@Override
public List<MyOrgTreeDTO> treeList(QueryOrgTreeVO vo) {
    String rootCode = vo.getRootCode();
    // get all org
    List<MyOrg> allOrgList = myOrgManager.list(new QueryWrapper<MyOrg>().lambda()
            .eq(MyOrg::getStatus, 1)
            .orderByAsc(MyOrg::getId));
    return this.recursionOrg(rootCode, allOrgList);
}

/**
 * 递归查询所有知识库组织
 *
 * @param parentOrgCode
 * @param allOrgList
 * @return
 */
public List<MyOrgTreeDTO> recursionOrg(String parentOrgCode, List<MyOrg> allOrgList) {
    List<MyOrg> orgList = allOrgList.parallelStream()
            .filter(org -> org.getParentCode().equals(parentOrgCode))
//                .sorted(Comparator.comparing(MyOrg::getId, Comparator.nullsLast(Comparator.naturalOrder()))
//                        .thenComparing(MyOrg::getId))
            .collect(Collectors.toList());
    List<MyOrgTreeDTO> treeDTOList = Convert.toList(MyOrgTreeDTO.class, orgList);

    for (int i = 0; i < treeDTOList.size(); i++) {
        MyOrgTreeDTO orgTreeCurrent = treeDTOList.get(i);
        // set child
        List<MyOrgTreeDTO> childList = this.recursionOrg(orgList.get(i).getOrgCode(), allOrgList);
        orgTreeCurrent.setChildList(childList);
    }
    return treeDTOList;
}

返回数据结构

{
  "code": "200",
  "msg": "success",
  "data": [
    {
      "id": 25,
      "orgName": "财政部",
      "orgCode": "111",
      "parentName": "",
      "parentCode": "0",
      "route": "0,111",
      "orgDesc": "财政部",
      "status": 1,
      "createUser": "11",
      "updateUser": "11",
      "createTime": "2022-07-26 15:15:49",
      "updateTime": "2022-07-26 15:15:51",
      "childList": [
        {
          "id": 26,
          "orgName": "财政部A",
          "orgCode": "222",
          "parentName": "财政部",
          "parentCode": "111",
          "route": "0,111,222",
          "orgDesc": "财政部A",
          "status": 1,
          "createUser": "admin",
          "updateUser": "",
          "createTime": "2022-07-26 15:17:38",
          "childList": []
        },
        {
          "id": 27,
          "orgName": "财政部B",
          "orgCode": "333",
          "parentName": "财政部",
          "parentCode": "111",
          "route": "0,111,333",
          "orgDesc": "财政部B",
          "status": 1,
          "createUser": "admin",
          "updateUser": "",
          "createTime": "2022-07-26 15:18:25",
          "childList": []
        }
      ]
    },
    {
      "id": 28,
      "orgName": "文艺部",
      "orgCode": "444",
      "parentName": "",
      "parentCode": "0",
      "route": "0,444",
      "orgDesc": "文艺部",
      "status": 1,
      "createUser": "admin",
      "updateUser": "",
      "createTime": "2022-07-26 15:19:08",
      "childList": [
        {
          "id": 29,
          "orgName": "文艺部X",
          "orgCode": "555",
          "parentName": "文艺部",
          "parentCode": "444",
          "route": "0,444,555",
          "orgDesc": "文艺部X",
          "status": 1,
          "createUser": "admin",
          "updateUser": "",
          "createTime": "2022-07-26 15:19:42",
          "childList": []
        },
        {
          "id": 30,
          "orgName": "文艺部U",
          "orgCode": "666",
          "parentName": "文艺部",
          "parentCode": "444",
          "route": "0,444,666",
          "orgDesc": "文艺部U",
          "status": 1,
          "createUser": "admin",
          "updateUser": "",
          "createTime": "2022-07-26 15:19:54",
          "childList": []
        },
        {
          "id": 35,
          "orgName": "文艺部U",
          "orgCode": "777",
          "parentName": "文艺部",
          "parentCode": "444",
          "route": "0,444,777",
          "orgDesc": "文艺部U",
          "status": 1,
          "createUser": "admin",
          "createTime": "2022-07-26 18:07:26",
          "childList": []
        }
      ]
    }
  ]
}

递归查询某一组织的所有层级路径

就是表数据里的route列

@Override
public List<String> getRoute(String currentCode) {
    // get all org
    List<MyOrg> allOrgList = myOrgManager.list(new QueryWrapper<MyOrg>().lambda()
            .eq(MyOrg::getStatus, 1)
            .orderByAsc(MyOrg::getId));
    List<String> orgCodeSet = new ArrayList<>();
    recursionRoute(currentCode, allOrgList, orgCodeSet);
    Collections.reverse(orgCodeSet);
    return orgCodeSet;
}

public void recursionRoute(String currentCode, List<MyOrg> allOrgList, List<String> orgCodeSet) {
    // 拿到当前组织的code和父code
    // 将当前code置入集合
    orgCodeSet.add(currentCode);
    List<MyOrg> orgList = allOrgList.parallelStream()
            .filter(org -> org.getOrgCode().equals(currentCode))
            .collect(Collectors.toList());
    if (!CollectionUtils.isEmpty(orgList)) {
        String parentCode = orgList.get(0).getParentCode();
        // 把父code作为新的currentCode调递归方法
        recursionRoute(parentCode, allOrgList, orgCodeSet);
    }
}

二、sql语句实现递归

如果你的mysql版本是5.8及以上的，可以借助with recursive... as...语句轻松实现递归查询。

2.1 查询出当前组织下的所有所有子组织，并且名称带上上级组织名称和code

with recursive type_cte as (
    select * from my_org where org_code = '111'
    union all
    select t.* from my_org t
                        inner join type_cte type_cte2 on t.parent_code = type_cte2.org_code
)
select
    org_name, org_code, org_desc, parent_code, parent_name
from type_cte

2.2 查询组织的所有父级组织

with recursive type_cte as (
    select org_code,org_name, parent_code, parent_name from my_org where org_code = '333'
    union all
    select concat(type_cte2.org_code, '>', t.org_code),concat(type_cte2.org_name, '>', t.org_name), t.parent_code, t.parent_name from my_org t
                        inner join type_cte type_cte2 on t.org_code = type_cte2.parent_code
)
select
    org_name, org_code, parent_code, parent_name
from type_cte

本文由《MySql教程网》原创，转载请注明出处！http://mysql360.com