网盘-树形结构-文件夹/文件表设计实现与mybatis处理
目录：
- 1.需求背景
- 2.设计方案
- 3.实现细节
1.需求背景
项目中要做基于网盘的业务，需要实现类似百度网盘的功能。文件夹表(user_folder)、文件表(user_file)是基本表。文件表可以作为文件夹表的附属信息表，在使用的时候( left join)关联出来。
主要是文件夹表涉及到树形结构，需要和父级目录关联(parent_id)，树形结构有很多种设计方式，可以参考以下文章。
常规设计
非常规设计
结合本项目特点选择了 路径枚举方式
2.设计方案
CREATE TABLE `user_folder` (
`id` bigint unsigned NOT NULL COMMENT '主键',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件夹名称 taskid',
`user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '文件所属者ID',
`parent_id` bigint NOT NULL DEFAULT '0' COMMENT '父级文件夹ID',
`parent_ids` varchar(900) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '父级目录ID字符数组',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '乐观锁',
`create_dt` datetime NOT NULL COMMENT '创建时间',
`creator_id` bigint unsigned NOT NULL COMMENT '创建人',
`update_dt` datetime NOT NULL COMMENT '更新时间',
`update_id` bigint unsigned NOT NULL COMMENT '更新人',
`deleted_dt` int NOT NULL DEFAULT '0' COMMENT '删除时间戳',
PRIMARY KEY (`id`) USING BTREE,
KEY `parent_id` (`parent_id`) USING BTREE,
KEY `name` (`name`) USING BTREE,
KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文件夹信息表';
CREATE TABLE `user_file` (
`id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '用户文件ID，主键',
`user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '文件所属者ID',
`parent_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '父级文件夹ID',
`origin_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '源文件ID',
`file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件名称',
`file_size` bigint unsigned NOT NULL COMMENT '文件大小(字节)',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '乐观锁',
`create_dt` datetime NOT NULL COMMENT '创建时间',
`creator_id` bigint unsigned NOT NULL COMMENT '创建人',
`update_dt` datetime NOT NULL COMMENT '更新时间',
`update_id` bigint unsigned NOT NULL COMMENT '更新人',
`deleted_dt` int NOT NULL DEFAULT '0' COMMENT '删除时间戳',
PRIMARY KEY (`id`) USING BTREE,
KEY `user_id_parent_id_origin_id` (`user_id`,`parent_id`,`origin_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户文件信息表';
文件夹模拟数据
文件模拟数据
查询父级目录：
查询出本身的parent_ids，再使用FIND_IN_SET（id,parent_ids） 查询父级目录
select
*
from user_folder ul
left join user_file ue on ul.id = ue.parent_id
where ul.user_id = #{userId}
and FIND_IN_SET(ul.id,#{parentIdsStr})
查询子目录：
根据本身id，再使用 find_in_set(id,parent_ids)查询子目录，参数和上面的相反
select
*
from user_folder ul
left join user_file ue on ul.id = ue.parent_id
where
find_in_set(#{folderId},ul.parent_ids)
and ul.user_id = #{userId}
路径枚举(parent_ids)在mybatis中的转换：
转成 List<Long> parentIds就不用再重复编码转换父级目录id集合
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(List.class)
public class ListTypeHandler extends BaseTypeHandler<List<Long>> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<Long> list, JdbcType jdbcType) throws SQLException {
String idsStr = list.stream().map(String::valueOf).collect(Collectors.joining(","));
ps.setString(i, idsStr);
}
@Override
public List<Long> getNullableResult(ResultSet rs, String columnName) throws SQLException {
String str = rs.getString(columnName);
return strToList(str);
}
@Override
public List<Long> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String str = rs.getString(columnIndex);
return strToList(str);
}
@Override
public List<Long> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String str = cs.getString(columnIndex);
return strToList(str);
}
private List<Long> strToList(String str) {
String[] split = str.split(",");
return Arrays.stream(split).mapToLong(Long::valueOf).collect(ArrayList::new, ArrayList::add, ArrayList::addAll);
}
}
3.实现细节
1.查询树形结构数据并组织成树形结构，并关联文件
查询树结构接口满足以下要求：
- 根据文件夹id查询所有父级目录
- 根据文件夹id查询所有子级目录
- 根据文件夹id查询所有父级目录和所有子级目录
//常量 根节点的parent_id = -1
ROOT_PARENT_ID = -1
//查询枚举类型
public enum TreeNodeEnum {
//向上节点
UP,
//向下节点
DOWN,
//全部节点
WHOLE;
}
//文件夹实体
@Builder(toBuilder = true)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TreeFolderBO {
@ApiModelProperty(value = "文件id", example = "1001")
private Long id;
@ApiModelProperty(value = "文件名称", example = "测试")
private String name;
@ApiModelProperty(value = "文件所属者ID", example = "1001")
private Long userId;
@ApiModelProperty("父级文件夹ID")
private Long parentId;
@ApiModelProperty("父级文件夹ID集合")
private List<Long> parentIds;
@ApiModelProperty("当前节点高度")
private Integer depth;
@ApiModelProperty(value = "绝对路径", example = "/我的文件/test")
private String path;
@ApiModelProperty("文件夹创建时间")
private LocalDateTime createTime;
@ApiModelProperty(value = "子文件集合", example = "")
private List<TreeFolderBO> folderList = new ArrayList<>();
@ApiModelProperty(value = "子文件集合", example = "")
private List<UserFileBO> fileList = new ArrayList<>();
}
//文件实体
@Builder(toBuilder = true)
@NoArgsConstructor
@AllArgsConstructor
@Data
public class UserFileBO {
@ApiModelProperty(value = "文件id", example = "1001")
private Long id;
@ApiModelProperty(value = "文件所属者ID", example = "1001")
private Long userId;
@ApiModelProperty("父级文件夹ID")
private Long parentId;
@ApiModelProperty("源文件ID")
private Long originId;
@ApiModelProperty(value = "文件名称", example = "测试")
private String fileName;
@ApiModelProperty(value = "文件类型", example = "jpg")
private String fileType;
}
//查询并组织结构
public TreeFolderBO getTreeFolder(Long folderId, TreeNodeEnum nodeEnum) {
//查询本身信息
HyDiskUserFolder folder = folderMapper.selectById(folderId);
List<Long> parentIds = folder.getParentIds();
String parentIdsStr = parentIds.stream().map(String::valueOf).collect(Collectors.joining(","));
List<TreeFolderBO> list;
//查询文件夹并关联文件
TreeFolderBO folderBO = folderMapper.selectInfoById(userId, folderId, false);
if (nodeEnum.equals(TreeNodeEnum.WHOLE)) {
查询用户所有文件夹和文件集合
} else if (nodeEnum.equals(TreeNodeEnum.UP)) {
查询用户所有父文件夹和文件，并将自己添加到数据中
list.add(folderBO);
} else {
查询用户所有子文件夹和文件，并将自己添加到数据中
list.add(folderBO);
}
转化parent_ids数组长度为树节点深度
list.forEach(item -> {
int depth = item.getParentIds().size();
if (item.getParentId().equals(ROOT_PARENT_ID)) {
depth = 0;
}
item.setDepth(depth);
});
return TreeFolderUtil.buildTreeFolder(null, list);
}
import cn.hutool.core.collection.CollectionUtil;
import com.aicloud.hongying.model.bo.ConflictFileBO;
import com.aicloud.hongying.model.bo.TreeFolderBO;
import com.aicloud.hongying.model.bo.UserFileBO;
import javax.annotation.Nullable;
import java.util.*;
import java.util.stream.Collectors;
public class TreeFolderUtil {
/**
* 根据节点集合构建树形结构
*
* @param path 第一次调用为{@code null}
* @param list 节点集合
* @return 树形结构
*/
public static TreeFolderBO buildTreeFolder(@Nullable String path, List<TreeFolderBO> list) {
path = path == null ? "" : path;
if (list.size() == 1) {
TreeFolderBO treeFolderBO = list.get(0);
treeFolderBO.setPath(path + "/" + treeFolderBO.getName());
return treeFolderBO;
}
//找出相对根节点
@SuppressWarnings("all")
TreeFolderBO root = list.stream()
.min(Comparator.comparing(TreeFolderBO::getDepth))
.get();
String name = root.getName();
path = path + "/" + name;
root.setPath(path);
//按照节点高度分组
Map<Integer, List<TreeFolderBO>> depthGroupMap = list.stream()
.filter(item -> !item.getId().equals(root.getId()))
.collect(Collectors.groupingBy(TreeFolderBO::getDepth));
//获取下一级 高度节点数据分组集合
List<TreeFolderBO> nextList = depthGroupMap.getOrDefault(root.getDepth() + 1, new ArrayList<>());
if (CollectionUtil.isNotEmpty(nextList)) {
List<TreeFolderBO> subList = new ArrayList<>();
for (TreeFolderBO item : nextList) {
//查找root所有下级节点
List<TreeFolderBO> collect = list.stream()
.filter(folderBO ->
item.getId().equals(folderBO.getId())
|| (item.getDepth() < folderBO.getDepth() && folderBO.getParentIds().contains(item.getId()))
).collect(Collectors.toList());
//遍历递归子节点
TreeFolderBO treeFolderBO = buildTreeFolder(path, collect);
subList.add(treeFolderBO);
}
root.setFolderList(subList);
}
return root;
}
/**
* 在树形结构上查找节点
*
* @param folderId 文件夹id
* @param treeFolder 树形结构
* @return 对应节点
*/
public static Optional<TreeFolderBO> findFolderById(Long folderId, TreeFolderBO treeFolder) {
if (folderId.equals(treeFolder.getId())) {
return Optional.of(treeFolder);
}
List<TreeFolderBO> folderList = treeFolder.getFolderList();
Optional<TreeFolderBO> optional = folderList.stream()
.filter(f -> f.getId().equals(folderId))
.findFirst();
if (optional.isPresent()) {
return optional;
} else {
for (TreeFolderBO folderBO : folderList) {
Optional<TreeFolderBO> folderOptional = findFolderById(folderId, folderBO);
if (folderOptional.isPresent()) {
return folderOptional;
}
}
}
return Optional.empty();
}
/**
* 将树结构的文件夹下的文件展开并存入list中
*
* @param treeFolder 树结构文件夹
* @param list 存入集合
*/
public static void flatMapToFile(TreeFolderBO treeFolder, List<UserFileBO> list) {
List<UserFileBO> fileList = treeFolder.getFileList();
if (CollectionUtil.isNotEmpty(fileList)) {
list.addAll(fileList);
}
for (TreeFolderBO folderBO : treeFolder.getFolderList()) {
flatMapToFile(folderBO, list);
}
}
/**
* 将树结构的文件夹下的文件夹展开并存入list中
*
* @param treeFolder 树结构文件夹
* @param list 存入集合
*/
public static void flatMapToFolder(TreeFolderBO treeFolder, List<TreeFolderBO> list) {
list.add(treeFolder);
List<TreeFolderBO> folderList = treeFolder.getFolderList();
for (TreeFolderBO folderBO : folderList) {
flatMapToFolder(folderBO, list);
}
}
}
touch fish
浙公网安备 33010602011771号