java企业-部门-职位如何转成树层级结构?
有这样的一张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实体类
@Datapublic 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);
}
}
回答：
可以借助两个 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;
}
回答：
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));
以上是 java企业-部门-职位如何转成树层级结构? 的全部内容， 来源链接： utcz.com/p/944751.html
