遍历数据库菜单/文件夹 封装成对象 的几种方式

背景

将数据库中存储的多级菜单/文件夹 遍历封装成对象给前端

数据库表

书签文件夹表

CREATE TABLE `bookmark_folder` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `name` varchar(50) DEFAULT NULL COMMENT '文件夹名称',
  `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '父文件夹id，顶层文件夹为0',
  `delflag` int(2) DEFAULT '0' COMMENT '0未删除  1已删除',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COMMENT='书签文件夹表';

书签文件夹DTO

/**
 * 书签文件夹dto
 *
 * @author lyk
 * @date 2020/10/12 13:43
 */
@Data
public class BookmarkFolderDTO {

    /**
     * 书签文件夹id
     */
    private Long id;

    /**
     * 名称
     */
    private String name;

    /**
     * 父id（0为顶层文件夹）
     */
    private Long parentId;

    private List<BookmarkFolderDto> folderDtoList;


}

mapper

<select id="listFolder" resultType="com.ivan.web.dto.BookmarkFolderDTO">
        select id,`name`,parent_id
        from bookmark_folder
        where
        user_id = #{userId}
        and delflag = 0
    </select>

<select id="listFolderByUserIdAndParentId" resultType="com.ivan.web.dto.BookmarkFolderDTO">
        select id,`name`,parent_id
        from bookmark_folder
        where
        user_id = #{userId}
        and parent_id=#{parentId}
        and delflag = 0
    </select>

实现

方式一（一次获取，直接遍历得到，推荐）

public List<BookmarkFolderDTO> listFolder() {
		// 使用了shiro 可换成其他方式获取用户Id
		OfficialToken user = (OfficialToken) SecurityUtils.getSubject().getPrincipal();
        List<BookmarkFolderDTO> folderDtoList = bookmarkDao.listFolder(user.getUserId());
        folderDtoList = getFolder(folderDtoList);
        return folderDtoList;
    }

    private List<BookmarkFolderDTO> getFolder(List<BookmarkFolderDTO> folderDtoList) {
        Map<Long, List<BookmarkFolderDto>> map = folderDtoList.stream().collect(Collectors.groupingBy(BookmarkFolderDto::getParentId));
        // 获取顶层文件夹
        List<BookmarkFolderDto> topFolderDto = folderDtoList.stream().filter(item -> item.getParentId() == 0).collect(Collectors.toList());
        for (BookmarkFolderDto bookmarkFolderDto : folderDtoList) {
            if (!map.containsKey(bookmarkFolderDto.getId())) {
                continue;
            }
            bookmarkFolderDto.setFolderDtoList(map.get(bookmarkFolderDto.getId()));
        }
        return topFolderDto;
    }

方式二（递归获取）

public List<BookmarkFolderDTO> listFolder() {
		// 使用了shiro 可换成其他方式获取用户Id
		OfficialToken user = (OfficialToken) SecurityUtils.getSubject().getPrincipal();
        List<BookmarkFolderDTO> folderDtoList = bookmarkDao.listFolderByUserIdAndParentId(user.getUserId(),0);
        getFolderRecursion(user.getUserId(),folderDtoList);
        return folderDtoList;
    }
private void getFolderRecursion(Long userId,List<BookmarkFolderDTO> folderDtoList){
	if(folderDtoList==null || folderDtoList.size()==0){
		return;
	}
	for(BookmarkFolderDTO folder : folderDtoList){
		List<BookmarkFolderDTO> childrenList bookmarkDao.listFolderByUserIdAndParentId(userId,folder.getId());
		folder.setFolderDtoList(childrenList);
		getFolderRecursion(userId,childrenList);
	}
}

结尾

第一种亲测可用，第二种是直接在网页上打得，没有运行过，逻辑应该没有错。若大佬有更好的方法望不吝赐教。