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

有这样的一张Mysql表

companycompany_namedeptdept_namejobjob_name
c1企业Ad1财务部j1财务主管
c1企业Ad1财务部j2会计
c1企业Ad2技术部j21Java
c1企业Ad2技术部j22JS
c2企业Bd20销售部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);
    }

}
回复
阅读 1.6k
3 个回答

可以借助两个 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);
}

前端处理就可以了,你看下 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;
}

SQLjs 新手,用它俩写写,练习练习:(都直接返回要求的 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));
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏