提示:以下内容仅供参开
前言
提示:
提示:以下是本篇文章正文内容,下面案例可供参考
一、需求场景
实际开发中,后端需要返回树形结构数据,比如:部门树
二、数据库表
表结构展示:
三.实现方式
1.方式1:
方式1是通过mybatis的递归查询实现
代码如下(示例):
实体类对象:
/**
*
* @TableName t_department
*/
@TableName(value ="t_department")
@Data
public class Department implements Serializable {
/**
* id
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 部门名称
*/
@TableField(value = "name")
private String name;
/**
* 父id
*/
@TableField(value = "parentId")
private Integer parentId;
/**
* 路径
*/
@TableField(value = "depPath")
private String depPath;
/**
* 是否启用
*/
@TableField(value = "enabled")
private Integer enabled;
/**
* 是否上级
*/
@TableField(value = "isParent")
private Integer isParent;
@TableField(exist = false)
private List<Department> children;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
mapper层代码:
public interface DepartmentMapper extends BaseMapper<Department> {
List<Department> getAllById(Integer id);
}
service层
@Override
public List<Department> getAll() {
return departmentMapper.getAllById(-1);
}
xml展示:
<resultMap id="BaseResultMap" type="org.example.domain.Department">
<id property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="parentId" column="parentId" jdbcType="INTEGER"/>
<result property="depPath" column="depPath" jdbcType="VARCHAR"/>
<result property="enabled" column="enabled" jdbcType="TINYINT"/>
<result property="isParent" column="isParent" jdbcType="TINYINT"/>
</resultMap>
<resultMap id="DepartmentMap" type="org.example.domain.Department" extends="BaseResultMap">
<collection property="children" ofType="org.example.domain.Department" column="id"
select="org.example.mapper.DepartmentMapper.getAllById">
</collection>
</resultMap>
<sql id="Base_Column_List">
id,name,parentId,
depPath,enabled,isParent
</sql>
<select id="getAllById" resultMap="DepartmentMap">
select <include refid="Base_Column_List" />
from t_department
where parentId = #{id}
</select>
主要是通过collection标签实现递归查询,首先查询根节点,然后返回的id依次去查询子节点
测试:
@Test
public void testGetDepartments1(){
List<Department> list = departmentService.getAll();
System.out.println(JSON.toJSONString(list));
}
输出结果:
==> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
==> Parameters: -1(Integer)
<== Columns: id, name, parentId, depPath, enabled, isParent
<== Row: 1, 股东会, -1, .1, 1, 1
====> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
====> Parameters: 1(Integer)
<==== Columns: id, name, parentId, depPath, enabled, isParent
<==== Row: 2, 董事会, 1, .1.2, 1, 1
======> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
======> Parameters: 2(Integer)
<====== Columns: id, name, parentId, depPath, enabled, isParent
<====== Row: 3, 总办, 2, .1.2.3, 1, 1
========> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
========> Parameters: 3(Integer)
<======== Columns: id, name, parentId, depPath, enabled, isParent
<======== Row: 4, 财务部, 3, .1.2.3.4, 1, 0
==========> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
==========> Parameters: 4(Integer)
<========== Total: 0
<======== Row: 5, 市场部, 3, .1.2.3.5, 1, 1
==========> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
==========> Parameters: 5(Integer)
<========== Columns: id, name, parentId, depPath, enabled, isParent
<========== Row: 6, 华东市场部, 5, 1.2.3.5.6, 1, 1
============> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
============> Parameters: 6(Integer)
<============ Columns: id, name, parentId, depPath, enabled, isParent
<============ Row: 8, 上海市场部, 6, 1.2.3.5.6.8, 1, 0
==============> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
==============> Parameters: 8(Integer)
<============== Total: 0
<============ Total: 1
<========== Row: 7, 华南市场部, 5, 1.2.3.5.7, 1, 0
============> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
============> Parameters: 7(Integer)
<============ Total: 0
<========== Row: 9, 西北市场部, 5, .1.2.3.5.9, 1, 1
============> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
============> Parameters: 9(Integer)
<============ Columns: id, name, parentId, depPath, enabled, isParent
<============ Row: 10, 贵阳市场, 9, .1.2.3.5.9.10, 1, 1
==============> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
==============> Parameters: 10(Integer)
<============== Columns: id, name, parentId, depPath, enabled, isParent
<============== Row: 11, 乌当区市场, 10, .1.2.3.5.9.10.11, 1, 0
================> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
================> Parameters: 11(Integer)
<================ Total: 0
<============== Total: 1
<============ Total: 1
<========== Total: 3
<======== Row: 12, 技术部, 3, .1.2.3.12, 1, 0
==========> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
==========> Parameters: 12(Integer)
<========== Total: 0
<======== Row: 13, 运维部, 3, .1.2.3.13, 1, 0
==========> Preparing: select id,name,parentId, depPath,enabled,isParent from t_department where parentId = ?
==========> Parameters: 13(Integer)
<========== Total: 0
<======== Total: 4
<====== Total: 1
<==== Total: 1
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@437281c5]
[{"children":[{"children":[{"children":[{"children":[],"depPath":".1.2.3.4","enabled":1,"id":4,"isParent":0,"name":"财务部","parentId":3},{"children":[{"children":[{"children":[],"depPath":"1.2.3.5.6.8","enabled":1,"id":8,"isParent":0,"name":"上海市场部","parentId":6}],"depPath":"1.2.3.5.6","enabled":1,"id":6,"isParent":1,"name":"华东市场部","parentId":5},{"children":[],"depPath":"1.2.3.5.7","enabled":1,"id":7,"isParent":0,"name":"华南市场部","parentId":5},{"children":[{"children":[{"children":[],"depPath":".1.2.3.5.9.10.11","enabled":1,"id":11,"isParent":0,"name":"乌当区市场","parentId":10}],"depPath":".1.2.3.5.9.10","enabled":1,"id":10,"isParent":1,"name":"贵阳市场","parentId":9}],"depPath":".1.2.3.5.9","enabled":1,"id":9,"isParent":1,"name":"西北市场部","parentId":5}],"depPath":".1.2.3.5","enabled":1,"id":5,"isParent":1,"name":"市场部","parentId":3},{"children":[],"depPath":".1.2.3.12","enabled":1,"id":12,"isParent":0,"name":"技术部","parentId":3},{"children":[],"depPath":".1.2.3.13","enabled":1,"id":13,"isParent":0,"name":"运维部","parentId":3}],"depPath":".1.2.3","enabled":1,"id":3,"isParent":1,"name":"总办","parentId":2}],"depPath":".1.2","enabled":1,"id":2,"isParent":1,"name":"董事会","parentId":1}],"depPath":".1","enabled":1,"id":1,"isParent":1,"name":"股东会","parentId":-1}]
部门树结构:
[
{
"children":[
{
"children":[
{
"children":[
{
"children":[
],
"depPath":".1.2.3.4",
"enabled":1,
"id":4,
"isParent":0,
"name":"财务部",
"parentId":3
},
{
"children":[
{
"children":[
{
"children":[
],
"depPath":"1.2.3.5.6.8",
"enabled":1,
"id":8,
"isParent":0,
"name":"上海市场部",
"parentId":6
}
],
"depPath":"1.2.3.5.6",
"enabled":1,
"id":6,
"isParent":1,
"name":"华东市场部",
"parentId":5
},
{
"children":[
],
"depPath":"1.2.3.5.7",
"enabled":1,
"id":7,
"isParent":0,
"name":"华南市场部",
"parentId":5
},
{
"children":[
{
"children":[
{
"children":[
],
"depPath":".1.2.3.5.9.10.11",
"enabled":1,
"id":11,
"isParent":0,
"name":"乌当区市场",
"parentId":10
}
],
"depPath":".1.2.3.5.9.10",
"enabled":1,
"id":10,
"isParent":1,
"name":"贵阳市场",
"parentId":9
}
],
"depPath":".1.2.3.5.9",
"enabled":1,
"id":9,
"isParent":1,
"name":"西北市场部",
"parentId":5
}
],
"depPath":".1.2.3.5",
"enabled":1,
"id":5,
"isParent":1,
"name":"市场部",
"parentId":3
},
{
"children":[
],
"depPath":".1.2.3.12",
"enabled":1,
"id":12,
"isParent":0,
"name":"技术部",
"parentId":3
},
{
"children":[
],
"depPath":".1.2.3.13",
"enabled":1,
"id":13,
"isParent":0,
"name":"运维部",
"parentId":3
}
],
"depPath":".1.2.3",
"enabled":1,
"id":3,
"isParent":1,
"name":"总办",
"parentId":2
}
],
"depPath":".1.2",
"enabled":1,
"id":2,
"isParent":1,
"name":"董事会",
"parentId":1
}
],
"depPath":".1",
"enabled":1,
"id":1,
"isParent":1,
"name":"股东会",
"parentId":-1
}
]
2.方式2:
方式2是通过java的递归查询实现
代码如下(示例):
/**
* 获取部门树
* @return
*/
@Override
public List<Department> getAll2() {
// 查询全部部门
List<Department> list = departmentMapper.selectList(null);
// 获取根节点
List<Department> Departments = list.stream()
.filter(e -> e.getParentId()==-1)
.map(e -> {
e.setChildren(getChildrens(e, list));
return e;
}).collect(Collectors.toList());
return Departments;
}
/**
* 递归查询子部门,并设置子部门
* @param e
* @param list
* @return
*/
private List<Department> getChildrens(Department e, List<Department> list) {
List<Department> children = list.stream()
.filter(m -> Objects.equals(e.getId(), m.getParentId()))
.map(m -> {
m.setChildren(getChildrens(m, list));
return m;
}).collect(Collectors.toList());
return children;
}
测试结果同上:
@Test
public void testGetDepartments2(){
List<Department> list = departmentService.getAll2();
System.out.println(JSON.toJSONString(list));
}
笔者推荐第二种方式,数据库只查询一次。