java企业-部门-职位如何转成树层级结构?

Question

有这样的一张Mysql表

company	company_name	dept	dept_name	job	job_name
c1	企业A	d1	财务部	j1	财务主管
c1	企业A	d1	财务部	j2	会计
c1	企业A	d2	技术部	j21	Java
c1	企业A	d2	技术部	j22	JS
c2	企业B	d20	销售部	j20	销售员

DTO实体类

@Data
public static class Company {
    private String code;
    private String name;
    private List<Department> departments;
}

@Data
public static class Department {
    private String code;
    private String name;
    private List<Job> jobs;
}

@Data
public static class Job {
    private String code;
    private String name;
}

查出表中全部数据，如何返回以下的格式?

[
    {
        "code": "C1",
        "name": "企业A",
        "departments": [
            {
                "code": "d1",
                "name": "财务部",
                "jobs": [
                    {
                        "code": "j1",
                        "name": "财务主管"
                    },
                    {
                        "code": "j2",
                        "name": "会计"
                    }
                ]
            },
            {
                "code": "d2",
                "name": "技术部",
                "jobs": [
                    {
                        "code": "j21",
                        "name": "Java"
                    },
                    {
                        "code": "j22",
                        "name": "JS"
                    }
                ]
            }
        ]
    },{
        "code": "C2",
        "name": "企业B",
        "departments": [
            {
                "code": "d20",
                "name": "销售部",
                "jobs": [
                    {
                        "code": "j20",
                        "name": "销售员"
                    }
                ]
            }
        ]
    }
]

我的思路

1.查出整张表的数据
List<Po> records = "select * from table";
2.声明数组data保存结果 List<Company> data = new ArrayList<>();
3.遍历records
for(Po po : records) {
    Job job = new Job();
    job.setName(po.getJobName);
    job.setCode(po.getCode);
    boolean addCompany = true;

    /**
    *判断po是否存在于data数组，
            1.存在
                获取所有部门，遍历，继续判断部门是否存在，
                i.存在就添加job,
                ii.不存在就添加department
            2.不存在，则新增company
    */
    // 遍历data
    for(Company c : data) {
        if (po.getCompanyCode().equals(c.getCode())) {
            List<Department> depts = c.getDepartments();
            boolean addDept = true;
            for (Department dept : depts) {
                if (po.getDeptCode().equals(dept.getCode())) {
                    dept.getJobs().add(job);
                    addDept = false;
                }
            }

            if (addDept) {
                List<Job> jobs = new ArrayList<>();
                jobs.add(job);
                Department deptRow = new Department();
                deptRow.setCode(po.getDeptCode());
                deptRow.setName(po.getDeptName());
                deptRow.setJobs(jobs);
                c.getDepartments().add(deptRow);
            }
            addCompany = false;
        }
    }

    if(addCompany) {
        Company company = new Company();
        data.add(company);
    }

}

Answer 1

可以借助两个 HashMap 来实现一次遍历完成树状结构，如下。

1.查出整张表的数据
List<Po> records = "select * from table";
2.声明数组data保存结果 List<Company> data = new ArrayList<>();
3.遍历records

List<Company> data = new ArrayList<>();
Company curCompany;
Department curDepartment;
Map<String, Company> companyMap = new HashMap();
Map<String, Department> departmentMap = new HashMap();
for(Po po : records) {
    // 处理当前记录所属 company
    String companyKey = po.getCompanyCode()
    if (companyMap.containsKey(companyKey)) {
        curCompany = companyMap.get(companyKey);
    } else {
        curCompany = new Company();
        curCompany.setCode(po.getCompanyCode())
        curCompany.setName(po.getCompanyName());
        companyMap.put(companyKey, curCompany);
        data.add(curCompany);
    }

    // 处理当前记录所属 department
    String departmentKey = po.getCompanyCode() + po.getDeptCode()
    if (departmentMap.containsKey(departmentKey)) {
        curDepartment = departmentMap.get(departmentKey)
    } else {
        curDepartment = new Department();
        curDepartment.setCode(po.getDeptCode())
        curDepartment.setName(po.getDeptName());
        departmentMap.put(departmentKey, curDepartment)
        curCompany.getDepartments().add(curDepartment);
    }

    // 处理当前记录 job
    Job job = new Job();
    job.setCode(po.getCode);
    job.setName(po.getJobName);
    curDepartment.getJobs().add(job);
}

Answer 2

前端处理就可以了，你看下 list 是不是你返回的数据，但是你这个表建的有点奇怪，正常应该是三个表，然后关联。

（我这个是新手写法，让高手用递归写应该就几行代码搞定）

const list = [
    {
        "company": "c1",
        "company_name": "企业A",
        "dept": "d1",
        "dept_name": "财务部",
        "job": "j1",
        "job_name": "财务主管"
    },     {
        "company": "c1",
        "company_name": "企业A",
        "dept": "d1",
        "dept_name": "财务部",
        "job": "j2",
        "job_name": "会计"
    },     {
        "company": "c1",
        "company_name": "企业A",
        "dept": "d2",
        "dept_name": "技术部",
        "job": "j21",
        "job_name": "Java"
    },     {
        "company": "c1",
        "company_name": "企业A",
        "dept": "d2",
        "dept_name": "技术部",
        "job": "j22",
        "job_name": "JS"
    },     {
        "company": "c2",
        "company_name": "企业B",
        "dept": "d20",
        "dept_name": "销售部",
        "job": "j20",
        "job_name": "销售员"
    }
];

const list_tree = () => {
    const arr = [];
    list.forEach(function(val) {
        if (!arr.find(item => item.code == val.company)) {
            const qiye = {
                "code": val.company,
                "name": val.company_name
            }

            const departments = [];
            list.forEach(function(val2) {
                if (val2.company == val.company && !departments.find(item2 => item2.code == val2.dept)) {
                    const bumen = {
                        "code": val2.dept,
                        "name": val2.dept_name
                    }

                    const jobs = [];
                    list.forEach(function(val3) {
                        if (val3.company == val.company && val3.dept == val2.dept && !jobs.find(item3 => item3.code == val3.job)) {
                            const zhiwei = {
                                "code": val3.job,
                                "name": val3.job_name
                            }
                            jobs.push(zhiwei);
                        }
                    });
                    bumen.jobs = jobs;

                    departments.push(bumen);
                }
            });

            qiye.departments = departments;
            arr.push(qiye);
        }
    });
    return arr;
}

Answer 3

SQL 和 js 新手，用它俩写写，练习练习：（都直接返回要求的 json）

SQLite 实现

（用 SQLite 意为：连 1~2 MB 的 SQLite 都支持的功能，其他数据库肯定都支持，容易移植）

WITH
  -- 原始数据表
  data(company, company_name, dept, dept_name, job, job_name) AS (
    VALUES
      ('c1', '企业A', 'd1', '财务部', 'j1', '财务主管'),
      ('c1', '企业A', 'd1', '财务部', 'j2', '会计'),
      ('c1', '企业A', 'd2', '技术部', 'j21', 'Java'),
      ('c1', '企业A', 'd2', '技术部', 'j22', 'JS'),
      ('c2', '企业B', 'd20', '销售部', 'j20', '销售员')
  ),

  -- 公司表
  companies(company, company_name) AS (
    SELECT DISTINCT company, company_name
      FROM data
  )

SELECT json_group_array(
         json_object(
           'code', company,
           'name', company_name,
           'departments', (
             SELECT json_group_array(json(t2.c1))
               FROM (SELECT json_object(
                              'code', dept,
                              'name', dept_name,
                              'jobs', json_group_array(
                                json_object(
                                  'code', job,
                                  'name', job_name
                                )
                              )
                            ) c1
                       FROM data
                      WHERE company = t1.company
                      GROUP BY dept) t2
           )
         )
       )
  FROM companies t1;

JavaScript 实现

（总觉得解构赋值能起更多作用……知道了再改吧）

const data = [
  {company: 'c1', company_name: '企业A', dept: 'd1', dept_name: '财务部', job: 'j1', job_name: '财务主管'},
  {company: 'c1', company_name: '企业A', dept: 'd1', dept_name: '财务部', job: 'j2', job_name: '会计'},
  {company: 'c1', company_name: '企业A', dept: 'd2', dept_name: '技术部', job: 'j21', job_name: 'Java'},
  {company: 'c1', company_name: '企业A', dept: 'd2', dept_name: '技术部', job: 'j22', job_name: 'JS'},
  {company: 'c2', company_name: '企业B', dept: 'd20', dept_name: '销售部', job: 'j20', job_name: '销售员'},
];

let result = {};

for (const {company, company_name, dept, dept_name, job, job_name} of data) {
  ((result[company] ??= {code: company, name: company_name, departments: {}})
    .departments[dept] ??= {code: dept, name: dept_name, jobs: []})
    .jobs.push({code: job, name: job_name});
}

(result = Object.values(result)).forEach(i => i.departments = Object.values(i.departments));

console.log(JSON.stringify(result));