树形结构数据获取方式
前言
树形结构是一个比较常用的数据类型,一般多用于查询包含父子类关系的数据。这次以一个简单的地域信息,通过jdk1.8新特性lamda来进行处理,就比较方便处理的,主要还是采用了递归思想。
一.数据库一次性查出来处理,lamada处理成树形结构
1.对应数据库表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for area
-- ----------------------------
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
`area_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地区名称',
`parent_id` int(11) NOT NULL COMMENT '父Id',
`sort` int(11) NULL DEFAULT NULL COMMENT '排序',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '地区表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO `area` VALUES (1, '广东省', 0, 1);
INSERT INTO `area` VALUES (2, '深圳市', 1, 1);
INSERT INTO `area` VALUES (3, '广州市', 1, 2);
INSERT INTO `area` VALUES (4, '湖南省', 0, 2);
INSERT INTO `area` VALUES (5, '长沙市', 4, 1);
INSERT INTO `area` VALUES (6, '株洲市', 4, 2);
INSERT INTO `area` VALUES (7, '湖北省', 0, 3);
INSERT INTO `area` VALUES (8, '武汉市', 7, 1);
INSERT INTO `area` VALUES (9, '汉口区', 8, 1);
INSERT INTO `area` VALUES (10, '汉阳区', 8, 2);
INSERT INTO `area` VALUES (11, '武昌区', 8, 3);
INSERT INTO `area` VALUES (12, '鄂州市', 7, 2);
INSERT INTO `area` VALUES (13, '鄂城区', 12, 1);
INSERT INTO `area` VALUES (14, '华容区', 12, 2);
INSERT INTO `area` VALUES (15, '梁子湖区', 12, 3);
SET FOREIGN_KEY_CHECKS = 1;
2.表对应的实体类
/**
* 实体类
*/
@Data
public class Area {
private Integer id;
private String areaName;
private Integer parentId;
private Integer sort;
// 封装的子类集合
private List<Area> childrenList;
}
3.对应方法sql
// 查询所有地域信息
List<Area> getAllArea(Area area);
<!--根据父id获取子节点-->
<select id="getAllArea" resultMap="areaColumnMap" parameterType="Integer">
select id ,area_name ,parent_id,sort from area
</select>
4.单元测试用例
@Test
public void getAllArea(){
Area area = new Area();
List<Area> areaList = areaService.getAllArea(area);
//2、组装树形结构
List<Area> newList = areaList.stream()
.filter(t -> t.getParentId() == 0)
.map((menu) -> {
menu.setChildrenList(this.getChildren(menu,areaList));
return menu;
})
.sorted((menu1,menu2) -> {return (menu1.getSort() == null ? 0 : menu1.getSort()) - (menu2.getSort() == null ? 0 : menu2.getSort()) ;})
.collect(Collectors.toList());
System.out.println(JSON.toJSONString(newList));
}
/**
* 递归查找当前菜单的子菜单
* @param root 单个对象
* @param all 所有的集合
* @return 排序后的子类
*/
private List<Area> getChildren(Area root,List<Area> all){
List<Area> childrenList = all.stream()
.filter(t -> t.getParentId() == root.getId())
.map(g -> {
//找子菜单
g.setChildrenList(getChildren(g,all));
return g;
})
//菜单排序
.sorted(Comparator.comparingInt(temp-> (temp.getSort() == null ? 0 : temp.getSort())))
.collect(Collectors.toList());
return childrenList;
}
5.查询树形结果
[
{
"areaName": "广东省",
"childrenList": [
{
"areaName": "深圳市",
"childrenList": [],
"id": 2,
"parentId": 1,
"sort": 1
},
{
"areaName": "广州市",
"childrenList": [],
"id": 3,
"parentId": 1,
"sort": 2
}
],
"id": 1,
"parentId": 0,
"sort": 1
},
{
"areaName": "湖南省",
"childrenList": [
{
"areaName": "长沙市",
"childrenList": [],
"id": 5,
"parentId": 4,
"sort": 1
},
{
"areaName": "株洲市",
"childrenList": [],
"id": 6,
"parentId": 4,
"sort": 2
}
],
"id": 4,
"parentId": 0,
"sort": 2
},
{
"areaName": "湖北省",
"childrenList": [
{
"areaName": "武汉市",
"childrenList": [
{
"areaName": "汉口区",
"childrenList": [],
"id": 9,
"parentId": 8,
"sort": 1
},
{
"areaName": "汉阳区",
"childrenList": [],
"id": 10,
"parentId": 8,
"sort": 2
},
{
"areaName": "武昌区",
"childrenList": [],
"id": 11,
"parentId": 8,
"sort": 3
}
],
"id": 8,
"parentId": 7,
"sort": 1
},
{
"areaName": "鄂州市",
"childrenList": [
{
"areaName": "鄂城区",
"childrenList": [],
"id": 13,
"parentId": 12,
"sort": 1
},
{
"areaName": "华容区",
"childrenList": [],
"id": 14,
"parentId": 12,
"sort": 2
},
{
"areaName": "梁子湖区",
"childrenList": [],
"id": 15,
"parentId": 12,
"sort": 3
}
],
"id": 12,
"parentId": 7,
"sort": 2
}
],
"id": 7,
"parentId": 0,
"sort": 3
}
]
二.sql进行处理,形成树形结果,适用于jdk1.7以下版本
实体类和上面一样,mybatis对应的sql和映射关系要改一下
// 直接以树形结构返回结果
List<Area> queryAreaList(Area area);
<resultMap id="areaTreeMap" type="ycz.demo.com.entity.Area">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="area_name" property="areaName" jdbcType="VARCHAR" />
<result column="parent_id" property="parentId" jdbcType="INTEGER" />
<!--id作为参数递归调用getDeptTree,查询子节点-->
<collection property="childrenList" column="id" select="getParentById" ofType="ycz.demo.com.entity.Area">
</collection>
</resultMap>
<!--根据父id获取子节点-->
<select id="getParentById" resultMap="areaTreeMap" parameterType="Integer">
select id ,area_name ,parent_id from area where parent_id = #{parentId}
</select>
<select id="queryAreaList" resultMap="areaTreeMap" parameterType="ycz.demo.com.entity.Area">
select id ,area_name ,parent_id from area
</select>
这个方法也能达到相同效果,但会与数据库多次交互,引发效率问题
2021-12-12 20:13:29.823 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
2021-12-12 20:13:30.116 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
2021-12-12 20:13:30.122 [main] DEBUG ycz.demo.com.dao.AreaDao.queryAreaList - ==> Preparing: select id ,area_name ,parent_id from area
2021-12-12 20:13:30.147 [main] DEBUG ycz.demo.com.dao.AreaDao.queryAreaList - ==> Parameters:
2021-12-12 20:13:30.175 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ====> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.176 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ====> Parameters: 1(Integer)
2021-12-12 20:13:30.177 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.177 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Parameters: 2(Integer)
2021-12-12 20:13:30.178 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <====== Total: 0
2021-12-12 20:13:30.182 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.183 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Parameters: 3(Integer)
2021-12-12 20:13:30.185 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <====== Total: 0
2021-12-12 20:13:30.186 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <==== Total: 2
2021-12-12 20:13:30.188 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ====> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.188 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ====> Parameters: 4(Integer)
2021-12-12 20:13:30.189 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.189 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Parameters: 5(Integer)
2021-12-12 20:13:30.190 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <====== Total: 0
2021-12-12 20:13:30.190 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.190 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Parameters: 6(Integer)
2021-12-12 20:13:30.191 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <====== Total: 0
2021-12-12 20:13:30.191 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <==== Total: 2
2021-12-12 20:13:30.192 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ====> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.192 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ====> Parameters: 7(Integer)
2021-12-12 20:13:30.194 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.194 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Parameters: 8(Integer)
2021-12-12 20:13:30.195 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.195 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Parameters: 9(Integer)
2021-12-12 20:13:30.195 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <======== Total: 0
2021-12-12 20:13:30.196 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.196 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Parameters: 10(Integer)
2021-12-12 20:13:30.196 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <======== Total: 0
2021-12-12 20:13:30.197 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.197 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Parameters: 11(Integer)
2021-12-12 20:13:30.198 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <======== Total: 0
2021-12-12 20:13:30.198 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <====== Total: 3
2021-12-12 20:13:30.199 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.200 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ======> Parameters: 12(Integer)
2021-12-12 20:13:30.200 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.201 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Parameters: 13(Integer)
2021-12-12 20:13:30.202 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <======== Total: 0
2021-12-12 20:13:30.202 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.202 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Parameters: 14(Integer)
2021-12-12 20:13:30.203 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <======== Total: 0
2021-12-12 20:13:30.204 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Preparing: select id ,area_name ,parent_id from area where parent_id = ?
2021-12-12 20:13:30.205 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - ========> Parameters: 15(Integer)
2021-12-12 20:13:30.206 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <======== Total: 0
2021-12-12 20:13:30.207 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <====== Total: 3
2021-12-12 20:13:30.207 [main] DEBUG ycz.demo.com.dao.AreaDao.getParentById - <==== Total: 2
2021-12-12 20:13:30.208 [main] DEBUG ycz.demo.com.dao.AreaDao.queryAreaList - <== Total: 15
[{"areaName":"广东省","childrenList":[{"areaName":"深圳市","childrenList":[],"id":2,"parentId":1},{"areaName":"广州市","childrenList":[],"id":3,"parentId":1}],"id":1,"parentId":0},{"areaName":"深圳市","childrenList":[],"id":2,"parentId":1},{"areaName":"广州市","childrenList":[],"id":3,"parentId":1},{"areaName":"湖南省","childrenList":[{"areaName":"长沙市","childrenList":[],"id":5,"parentId":4},{"areaName":"株洲市","childrenList":[],"id":6,"parentId":4}],"id":4,"parentId":0},{"areaName":"长沙市","childrenList":[],"id":5,"parentId":4},{"areaName":"株洲市","childrenList":[],"id":6,"parentId":4},{"areaName":"湖北省","childrenList":[{"areaName":"武汉市","childrenList":[{"areaName":"汉口区","childrenList":[],"id":9,"parentId":8},{"areaName":"汉阳区","childrenList":[],"id":10,"parentId":8},{"areaName":"武昌区","childrenList":[],"id":11,"parentId":8}],"id":8,"parentId":7},{"areaName":"鄂州市","childrenList":[{"areaName":"鄂城区","childrenList":[],"id":13,"parentId":12},{"areaName":"华容区","childrenList":[],"id":14,"parentId":12},{"areaName":"梁子湖区","childrenList":[],"id":15,"parentId":12}],"id":12,"parentId":7}],"id":7,"parentId":0},{"areaName":"武汉市","childrenList":[{"$ref":"$[6].childrenList[0].childrenList[0]"},{"$ref":"$[6].childrenList[0].childrenList[1]"},{"$ref":"$[6].childrenList[0].childrenList[2]"}],"id":8,"parentId":7},{"areaName":"汉口区","childrenList":[],"id":9,"parentId":8},{"areaName":"汉阳区","childrenList":[],"id":10,"parentId":8},{"areaName":"武昌区","childrenList":[],"id":11,"parentId":8},{"areaName":"鄂州市","childrenList":[{"$ref":"$[6].childrenList[1].childrenList[0]"},{"$ref":"$[6].childrenList[1].childrenList[1]"},{"$ref":"$[6].childrenList[1].childrenList[2]"}],"id":12,"parentId":7},{"areaName":"鄂城区","childrenList":[],"id":13,"parentId":12},{"areaName":"华容区","childrenList":[],"id":14,"parentId":12},{"areaName":"梁子湖区","childrenList":[],"id":15,"parentId":12}]
总结
1.一般会把表所有数据一次性查出来,然后代码进行处理,避免多次与数据库交互;
2.考虑到效率问题,方法一会更优;
方法二也能达到效果,但是会与数据库多次交互,数据量不大时区别不大,数据量大是效率就感觉明显了
3.使用lamda时,jdk必须1.8以上;