有时候我们有查询分类的查询需求,并且我们也不确定会有多少层,需要从顶层查到最底层,可以使用以下两种方法:
一,递归查询法
@GetMapping(value = "/ttt")
public HttpResult hui(){
LambdaQueryWrapper<Company> queryWrapper=new LambdaQueryWrapper<>();
queryWrapper.eq(Company::getParentId,-1);
List<Company> list=companyService.list(queryWrapper);
List<CompanyRes> companyRes = ConvertUtils.convertList(list, CompanyRes.class);
getAll(companyRes);
return HttpResult.ok(companyRes);
}
/**
*多次回调自己实现查询到底的业务
*/
public void getAll(List<CompanyRes> list){
list.stream().map(a->{
LambdaQueryWrapper<Company> queryWrapper=new LambdaQueryWrapper<>();
queryWrapper.eq(Company::getParentId,a.getId());
List<Company> list1=companyService.list(queryWrapper);
List<CompanyRes> companyRes = ConvertUtils.convertList(list1, CompanyRes.class);
if (companyRes!=null){
getAll(companyRes);
a.setList(companyRes);
}
return a;
}).collect(Collectors.toList());
}
/**
*反参类
*/
@Data
public class CompanyRes {
@TableId(type = IdType.ID_WORKER)
@JsonSerialize(using = ToStringSerializer.class)
private Long id;
/** 公司名称 */
private String companyName;
/**
* 父级公司id
*/
@JsonSerialize(using = ToStringSerializer.class)
private Long parentId;
/**
* 公司法人
*/
private String legalPerson;
/**
* 公司地址
*/
private String companyAddress;
/**
* 公司联系方式
*/
private String companyPhone;
private List<CompanyRes> list;
}
二,一起查询,代码中构建,以mysql为例
1.先根据顶级父类id,查询出所有的子级数据
SELECT
ID.LEVEL,
DATA.*
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
company,
( SELECT @ids := 顶级父类 id, @l := 0 ) b
WHERE
@ids IS NOT NULL
) ID,
company DATA
WHERE
FIND_IN_SET( DATA.id, ID._ids )
ORDER BY
LEVEL,
id
2.代码中构建树形
class TreeVo {
private int id;
private int pid;
private String name;
private List<TreeVo> list;
}
public List<TreeVo> getTree(List<TreeVo> list){
Map<Intager,TreeVo> map=new HashMap<>();
//将拿到的集合按照id分类为一个map
for(TreeVo treeVo: list){
map.put(treeVo.getId(),treeVo);
}
List<TreeVo> list3=new ArrayList<>();
for(TreeVo treeVo: list){
TreeVo treeVo1=map.get(treeVo.getPid());
if(treeVo1==null){//说明为顶级树形
list3.add(treeVo);
}else{//说明含有父级
List<TreeVo> list5= treeVo1.getList();
if(ObjectUtils.isNotEmpty(list5)){
list5.add(treeVo);
}else{
List<TreeVo> list0=new ArrayList<>();
list0.add(treeVo);
treeVo1.setList(list0);
}
}
}
return list3;
}