Java开发项目设计树形表结构和数据处理
目录
1、添加依赖
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
<exclusions>
<exclusion>
<groupId>io.swagger</groupId>
<artifactId>swagger-annotations</artifactId>
</exclusion>
<exclusion>
<groupId>io.swagger</groupId>
<artifactId>swagger-models</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-annotations</artifactId>
<version>1.5.21</version>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-models</artifactId>
<version>1.5.21</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>2、创建表和插入数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for data_folder
-- ----------------------------
DROP TABLE IF EXISTS `data_folder`;
CREATE TABLE `data_folder` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父类ID',
`type` int(3) NULL DEFAULT NULL COMMENT '文件夹类型(0-数据模型,1-数据连接)',
`data_id` bigint(20) NULL DEFAULT NULL COMMENT '关联的数据ID',
`folder_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '文件夹名称',
`data_type` int(3) NULL DEFAULT NULL COMMENT '文件类型(0-文件夹,1-数据模型,2-数据连接_EXCEL,3-数据连接_CSV,4-数据连接_ORACLE,5-数据连接_MYSQL)',
`user_id` bigint(20) NULL DEFAULT NULL COMMENT '创建用户id',
`update_user_id` bigint(20) NULL DEFAULT NULL COMMENT '修改用户id',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '数据准备' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of data_folder
-- ----------------------------
INSERT INTO `data_folder` VALUES (1, 0, 0, NULL, '数据模型-查询平台', 0, 1, NULL, '2022-05-05 15:26:51', '2022-05-10 16:48:52');
INSERT INTO `data_folder` VALUES (2, 0, 0, NULL, '数据模型-共享平台', 0, 1, NULL, '2022-05-05 15:27:02', NULL);
INSERT INTO `data_folder` VALUES (3, 0, 1, NULL, '数据连接-交换平台', 0, 1, NULL, '2022-05-05 15:27:19', NULL);
INSERT INTO `data_folder` VALUES (4, 0, 1, NULL, '数据连接-查询平台', 0, 1, NULL, '2022-05-05 15:26:51', NULL);
INSERT INTO `data_folder` VALUES (5, 0, 1, NULL, '数据连接-共享平台', 0, 1, NULL, '2022-05-05 15:27:02', NULL);
INSERT INTO `data_folder` VALUES (6, 0, 0, NULL, '数据模型-交换平台', 0, 1, NULL, '2022-05-05 15:27:19', NULL);
INSERT INTO `data_folder` VALUES (9, 3, 1, 1, '登录日志表', 2, 1, NULL, '2022-05-06 10:46:12', NULL);
INSERT INTO `data_folder` VALUES (10, 3, 1, 2, '22服务器mysql测试连接', 5, 1, NULL, '2022-05-06 10:46:12', NULL);
INSERT INTO `data_folder` VALUES (11, 3, 1, 3, 'CSV上传文件', 3, 1, 2, '2022-05-06 10:46:12', '2022-05-09 15:41:16');
INSERT INTO `data_folder` VALUES (12, 3, 1, 4, '22服务器oracle数据库测试连接', 4, 1, NULL, '2022-05-06 10:46:12', NULL);
INSERT INTO `data_folder` VALUES (13, 3, 1, NULL, '文件夹1', 0, 1, NULL, '2022-05-06 11:51:37', NULL);
INSERT INTO `data_folder` VALUES (14, 13, 1, 10, '用户信息表', 2, 1, NULL, '2022-05-06 11:52:29', NULL);
INSERT INTO `data_folder` VALUES (15, 0, 0, 1, '数据模型1', 1, 1, NULL, '2022-05-09 15:57:54', NULL);
INSERT INTO `data_folder` VALUES (16, 0, 0, NULL, '新建文件夹-1', 0, NULL, NULL, '2022-05-10 16:33:55', '2022-05-10 16:34:09');
INSERT INTO `data_folder` VALUES (17, 16, 0, NULL, '子文件夹', 0, NULL, NULL, '2022-05-10 16:34:27', '2022-05-10 16:34:27');
INSERT INTO `data_folder` VALUES (18, 17, 0, NULL, '1', 0, NULL, NULL, '2022-05-10 16:35:15', '2022-05-10 16:35:15');
INSERT INTO `data_folder` VALUES (19, 1, 0, NULL, '子文件', 0, NULL, NULL, '2022-05-10 16:43:40', '2022-05-10 16:43:40');
INSERT INTO `data_folder` VALUES (21, 1, 0, NULL, '112', 0, NULL, NULL, '2022-05-10 16:53:39', '2022-05-10 16:53:56');
SET FOREIGN_KEY_CHECKS = 1;3、返回的树形结构实体
package com.shucha.smartreport.biz.dto;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-05-06 11:00
*/
@Data
@ApiModel(value = "模型、数据连接树形结构数据")
public class DataFolderDTO {
@ApiModelProperty(value = "主键id")
private Long id;
@ApiModelProperty(value = "父id")
private Long parentId;
@ApiModelProperty(value = "文件夹类型(0-数据模型,1-数据连接)")
private Integer type;
@ApiModelProperty(value = "文件类型(0-文件夹,1-数据模型,2-数据连接_EXCEL,3-数据连接_CSV,4-数据连接_ORACLE,5-数据连接_MYSQL)")
private Integer dataType;
@ApiModelProperty(value = "名称")
private String folderName;
@ApiModelProperty(value = "关联的数据ID")
private Long dataId;
@ApiModelProperty(value = "子类数据")
private List<DataFolderDTO> children;
}
4、treeUtil工具类
package com.shucha.smartreport.util;
import org.springframework.util.StringUtils;
import javax.validation.constraints.NotNull;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* list -> tree
* 将普通list数据转换为树形数据结构
*/
public class TreeUtil {
/**
* 集合转树结构
*
* @param list 目标集合
* @param clazz 集合元素类型
* @return 转换后的树形结构
*/
public static <T> List<T> toTree(@NotNull List<T> list, @NotNull Class<T> clazz) {
return toTree(list, null, null, null, clazz);
}
/**
* 集合转树结构
*
* @param list 目标集合列表
* @param id 节点编号字段名称
* @param parent 父节点编号字段名称
* @param children 子节点集合属性名称
* @param clazz 集合元素类型
* @return 转换后的树形结构
*/
public static <T> List<T> toTree(@NotNull List<T> list, String id, String parent, String children, @NotNull Class<T> clazz) {
try {
// 如果目标集合为空,直接返回一个空树
if (list == null || list.isEmpty()) return null;
// 如果被依赖字段名称为空则默认为id
if (StringUtils.isEmpty(id)) id = "id";
// 如果依赖字段为空则默认为parent
if (StringUtils.isEmpty(parent)) parent = "pid";
// 如果子节点集合属性名称为空则默认为children
if (StringUtils.isEmpty(children)) children = "children";
// 初始化根节点集合, 支持 Set 和 List
List<T> roots = new ArrayList<>();
// 获取 id 字段, 从当前对象或其父类
Field idField;
try {
idField = clazz.getDeclaredField(id);
} catch (NoSuchFieldException e1) {
idField = clazz.getSuperclass().getDeclaredField(id);
}
// 获取 parentId 字段, 从当前对象或其父类
Field parentField;
try {
parentField = clazz.getDeclaredField(parent);
} catch (NoSuchFieldException e1) {
parentField = clazz.getSuperclass().getDeclaredField(parent);
}
// 获取 children 字段, 从当前对象或其父类
Field childrenField;
try {
childrenField = clazz.getDeclaredField(children);
} catch (NoSuchFieldException e1) {
childrenField = clazz.getSuperclass().getDeclaredField(children);
}
// 设置为可访问
idField.setAccessible(true);
parentField.setAccessible(true);
childrenField.setAccessible(true);
// 找出所有的根节点
for (T c : list) {
Object parentId = parentField.get(c);
if (isRootNode(parentId)) {
roots.add(c);
}
}
// 从目标集合移除所有根节点
list.removeAll(roots);
// 遍历根节点, 依次添加子节点
for (T root : roots) {
addChild(root, list, idField, parentField, childrenField);
}
// 关闭可访问
idField.setAccessible(false);
parentField.setAccessible(false);
childrenField.setAccessible(false);
return roots;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 为目标节点添加孩子节点
*
* @param node 目标节点
* @param list 目标集合
* @param idField ID 字段
* @param parentField 父节点字段
* @param childrenField 字节点字段
*/
private static <T> void addChild(@NotNull T node, @NotNull List<T> list, @NotNull Field idField, @NotNull Field parentField, @NotNull Field childrenField) throws IllegalAccessException {
Object id = idField.get(node);
List<T> children = (List<T>) childrenField.get(node);
// 如果子节点的集合为 null, 初始化孩子集合
if (children == null) {
children = new ArrayList<>();
}
for (T t : list) {
Object o = parentField.get(t);
if (id.equals(o)) {
// 将当前节点添加到目标节点的孩子节点
children.add(t);
// 重设目标节点的孩子节点集合,这里必须重设,因为如果目标节点的孩子节点是null的话,这样是没有地址的,就会造成数据丢失,所以必须重设,如果目标节点所在类的孩子节点初始化为一个空集合,而不是null,则可以不需要这一步,因为java一切皆指针
childrenField.set(node, children);
// 递归添加孩子节点
addChild(t, list, idField, parentField, childrenField);
}
}
}
/**
* 判断是否是根节点, 判断方式为: 父节点编号为空或为 0, 则认为是根节点. 此处的判断应根据自己的业务数据而定.
*
* @param parentId 父节点编号
* @return 是否是根节点
*/
private static boolean isRootNode(Object parentId) {
boolean flag = false;
if (parentId == null) {
flag = true;
} else if (parentId instanceof String && (StringUtils.isEmpty(parentId) || parentId.equals("0"))) {
flag = true;
} else if (parentId instanceof Integer && Integer.valueOf(0).equals(parentId)) {
flag = true;
} else if (parentId instanceof Long && Long.valueOf("0").equals(parentId)) {
flag = true;
}
return flag;
}
}
5、查询数据
5.1 一次性查出所有
SELECT * FROM `data_folder` WHERE type = 1
5.2 根据父类ID查询
-- 开发使用
SELECT
id.LEVEL,
data.*
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM data_folder WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
data_folder,
( SELECT @ids := 3, @l := 0 ) b
WHERE
@ids IS NOT NULL
) id,
data_folder data
WHERE
FIND_IN_SET( data.id, id._ids )
ORDER BY
LEVEL,
id;
-- 原始的sql
SELECT
ID.LEVEL,
DATA.*
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父级 id字段, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
表名,
( SELECT @ids := '条件id', @l := 0 ) b
WHERE
@ids IS NOT NULL
) ID,
表名 DATA
WHERE
FIND_IN_SET( DATA.id, ID._ids )
ORDER BY
LEVEL,
id
5.3 调用方法返回树形结构数据
通过5.1和5.2查询得到的数据列表之后，然后调用下面的方法得到树形结构数
// 1、folderName不为空模糊查询时候，需要查询数据的父ID
List<Long> ids = new ArrayList<>();
if (StringUtil.isNotBlank(folderName)) {
ids = getParentIdList(folderName, type);
}
// 5.1的方法
List<DataFolder> list = dataFolderService.list(Wrappers.<DataFolder>lambdaQuery()
.in(ids.size() > 0, DataFolder::getId, ids)
.eq(DataFolder::getType,1));
ArrayList<DataFolderDTO> folderDTOS = new ArrayList<>();
if (list.size() > 0) {
for (DataFolder folder : list) {
DataFolderDTO dto = new DataFolderDTO();
dto.setId(folder.getId());
dto.setParentId(folder.getParentId());
dto.setDataId(folder.getDataId());
dto.setFolderName(folder.getFolderName());
dto.setDataType(folder.getDataType());
dto.setType(folder.getType());
folderDTOS.add(dto);
}
}
List<DataFolderDTO> powerListDTOSs = TreeUtil.toTree(folderDTOS, "id", "parentId", "children", DataFolderDTO.class);
/**
* 数据准备 树形结构模糊查询获取父ID
*
* @param folderName
* @param type
* @return
*/
List<Long> getParentIdList(String folderName, Integer type) {
List<Long> ids = new ArrayList<>();
List<DataFolder> likeList = dataFolderService.list(Wrappers.<DataFolder>lambdaQuery()
.select(DataFolder::getParentId)
.eq(DataFolder::getType, type)
.like(DataFolder::getFolderName, folderName)
.groupBy(DataFolder::getParentId));
if (likeList.size() > 0) {
ids = likeList.stream().map(DataFolder::getParentId).collect(Collectors.toList());
}
return ids;
}得到的数据格式如下：
{
"code": 0,
"success": true,
"message": "ok",
"data": [
{
"id": 3,
"parentId": 0,
"type": 1,
"dataType": 0,
"folderName": "数据连接-交换平台",
"dataId": null,
"children": [
{
"id": 13,
"parentId": 3,
"type": 1,
"dataType": 0,
"folderName": "文件夹1",
"dataId": null,
"children": [
{
"id": 14,
"parentId": 13,
"type": 1,
"dataType": 2,
"folderName": "用户信息表",
"dataId": 10,
"children": null
}
]
},
{
"id": 9,
"parentId": 3,
"type": 1,
"dataType": 2,
"folderName": "登录日志表",
"dataId": 1,
"children": null
},
{
"id": 10,
"parentId": 3,
"type": 1,
"dataType": 5,
"folderName": "22服务器mysql测试连接",
"dataId": 2,
"children": null
},
{
"id": 11,
"parentId": 3,
"type": 1,
"dataType": 3,
"folderName": "CSV上传文件",
"dataId": 3,
"children": null
},
{
"id": 12,
"parentId": 3,
"type": 1,
"dataType": 4,
"folderName": "22服务器oracle数据库测试连接",
"dataId": 4,
"children": null
}
]
},
{
"id": 5,
"parentId": 0,
"type": 1,
"dataType": 0,
"folderName": "数据连接-共享平台",
"dataId": null,
"children": null
},
{
"id": 4,
"parentId": 0,
"type": 1,
"dataType": 0,
"folderName": "数据连接-查询平台",
"dataId": null,
"children": null
}
]
}5.4 根据ID查询所有父节点
SELECT t2.id, t2.`folder_name`
FROM
(
SELECT
@r AS _id,
(SELECT @r := parent_id FROM data_folder WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 13, @l := 0) vars, data_folder AS h
WHERE @r <> 0
) t1
JOIN data_folder t2
ON t1._id = t2.Id
ORDER BY id asc;
SELECT GROUP_CONCAT(folder_name ORDER BY t2.id asc SEPARATOR ' / ')
FROM
(
SELECT
@r AS _id,
(SELECT @r := parent_id FROM data_folder WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 13, @l := 0) vars, data_folder AS h
WHERE @r <> 0
) t1
JOIN data_folder t2
ON t1._id = t2.Id
ORDER BY id asc;
SELECT IFNULL(GROUP_CONCAT(folder_name ORDER BY t2.id asc SEPARATOR ' / '),'主目录') as file_path
FROM
(
SELECT
@r AS _id,
(SELECT @r := parent_id FROM data_folder WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 13, @l := 0) vars, data_folder AS h
WHERE @r <> 0
) t1
JOIN data_folder t2 ON t1._id = t2.Id
ORDER BY id asc;
