一,适用场景
当业务中要查询带层级的数据时,并且不知道层级数或者层级大于三级时,使用递归操作把数据整合成要展示的数据时。当然编程语言同样可以做到,本次只是阐述用mybatis时的用法。
二、数据库数据结构说明
id 当前数据主键,也是标识
parentId 标识当前数据的父id -1则代表是一级
depPath 存放用于查询自己上级,或者下属层级的数据时比较方便 (业务方面)
适用场景:比如某银行某业务需要审批才能让客户看到,该业务需要操作人员的上级才有审批权限,并且该操作人员可以审批他下级人员所操作的业务,那这个时候要么我们就要去关联查询他的上级和下级人员,由于不知道当前人员所属层级结构,也会用到递归,且相当繁琐,但是如果在更新层级人员时就储存好层级关系,使用时肯定非常方便。
三、持久层调用书写
package com.dong.boot.service;
import com.dong.boot.mapper.DepartmentMapper;
import com.dong.boot.model.Department;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class DepartmentService {
@Autowired
DepartmentMapper departmentMapper;
public List<Department> getAllDepartment() {
return departmentMapper.getAllDepartmentsByParentId(-1);
}
}
getAllDepartmentsByParentId(-1) 意思是根据父id查询出所属下级的信息
四、mybatis xml的书写
<resultMap id="BaseResultMap" type="com.dong.boot.model.Department">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="parentId" property="parentId" jdbcType="INTEGER"/>
<result column="depPath" property="depPath" jdbcType="VARCHAR"/>
<result column="enabled" property="enabled" jdbcType="BIT"/>
<result column="isParent" property="isParent" jdbcType="BIT"/>
</resultMap>
<resultMap id="DepartmentWithChildren" type="com.dong.boot.model.Department" extends="BaseResultMap">
<collection property="children" ofType="com.dong.boot.model.Department" select="com.dong.boot.mapper.DepartmentMapper.getAllDepartmentsByParentId" column="id">
</collection>
</resultMap>
<sql id="Base_Column_List">
id, name, parentId, depPath, enabled, isParent
</sql>
<select id="getAllDepartmentsByParentId" resultMap="DepartmentWithChildren">
select <include refid="Base_Column_List"/> from department where parentId =#{pid}
</select>
getAllDepartmentsByParentId每次都会查询出一个存放bean的list,然后再根据每个bean的父id去查询下一层级,最终就会查询出下面层级的数据
五、查询结果
[
{
"id": 1,
"name": "股东会",
"parentId": -1,
"depPath": ".1",
"enabled": true,
"children": [
{
"id": 4,
"name": "董事会",
"parentId": 1,
"depPath": ".1.4",
"enabled": true,
"children": [
{
"id": 5,
"name": "总办",
"parentId": 4,
"depPath": ".1.4.5",
"enabled": true,
"children": [
{
"id": 8,
"name": "财务部",
"parentId": 5,
"depPath": ".1.4.5.8",
"enabled": true,
"children": [],
"result": null,
"parent": false
},
{
"id": 78,
"name": "市场部",
"parentId": 5,
"depPath": ".1.4.5.78",
"enabled": true,
"children": [
{
"id": 81,
"name": "华北市场部",
"parentId": 78,
"depPath": ".1.4.5.78.81",
"enabled": true,
"children": [
{
"id": 85,
"name": "石家庄市场部",
"parentId": 81,
"depPath": ".1.4.5.78.81.85",
"enabled": true,
"children": [],
"result": null,
"parent": false
}
],
"result": null,
"parent": true
},
{
"id": 82,
"name": "华南市场部",
"parentId": 78,
"depPath": ".1.4.5.78.82",
"enabled": true,
"children": [],
"result": null,
"parent": false
},
{
"id": 86,
"name": "西北市场部",
"parentId": 78,
"depPath": ".1.4.5.78.86",
"enabled": true,
"children": [
{
"id": 87,
"name": "西安市场",
"parentId": 86,
"depPath": ".1.4.5.78.86.87",
"enabled": true,
"children": [
{
"id": 89,
"name": "莲湖区市场",
"parentId": 87,
"depPath": ".1.4.5.78.86.87.89",
"enabled": true,
"children": [],
"result": null,
"parent": false
}
],
"result": null,
"parent": true
}
],
"result": null,
"parent": true
}
],
"result": null,
"parent": true
},
{
"id": 91,
"name": "技术部",
"parentId": 5,
"depPath": ".1.4.5.91",
"enabled": true,
"children": [],
"result": null,
"parent": false
},
{
"id": 92,
"name": "运维部",
"parentId": 5,
"depPath": ".1.4.5.92",
"enabled": true,
"children": [
{
"id": 93,
"name": "运维1部",
"parentId": 92,
"depPath": ".1.4.5.92.93",
"enabled": true,
"children": [],
"result": null,
"parent": false
},
{
"id": 94,
"name": "运维2部",
"parentId": 92,
"depPath": ".1.4.5.92.94",
"enabled": true,
"children": [],
"result": null,
"parent": false
}
],
"result": null,
"parent": true
}
],
"result": null,
"parent": true
}
],
"result": null,
"parent": true
},
{
"id": 96,
"name": "bbb",
"parentId": 1,
"depPath": ".1.96",
"enabled": true,
"children": [
{
"id": 104,
"name": "111",
"parentId": 96,
"depPath": ".1.96.104",
"enabled": true,
"children": [],
"result": null,
"parent": false
}
],
"result": null,
"parent": true
}
],
"result": null,
"parent": true
}
]