SpringBoot + JOOQ实现递归分类查询

1.表关系

2.实体表

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ContentColumnVO implements Serializable  {
    private Long      id;
    private String    name;
    private String    icon;
    private Double    weight;
    private Long      parentId;
    private String    parentPath;
    private Timestamp createdAt;
    private Timestamp updatedAt;
    private List<ContentColumnVO> contentColumnList;
}

3.Controller层

    @ApiOperation("获取所有栏目信息接口")
    @GetMapping("columns")
    public ApiResponse findColumnsAll() {
        return ApiResponse.from(contentColumnService.findColumnsAll());
    }

4.interface层

    /**
     * 获取所有文章分类
     * @return 文章分类列表
     */
    List<ContentColumnVO> findColumnsAll();

5.业务层

    public List<ContentColumnVO> findColumnsAll() {
        try {
            DSLContext create = DSL.using(configuration);

            List<ContentColumn> allColumns  = create.selectFrom(Tables.CONTENT_COLUMN).fetchInto(ContentColumn.class);
            // 生成 parentId 到子列列表的映射
            Map<Long, List<ContentColumn>> childMap = new HashMap<>();
            for (ContentColumn column : allColumns) {
                Long parentId = column.getParentId();
                childMap.putIfAbsent(parentId, new ArrayList<>());
                childMap.get(parentId).add(column);
            }
            // 递归对列进行分类
            return categorizeColumns(childMap, 0L);
        } catch (Exception ex) {
            log.warn("findColumnsAll", ex);
            return null;
        }
    }


    // 求文章分类
    private List<ContentColumnVO> categorizeColumns(Map<Long, List<ContentColumn>> childMap, Long parentId) {
        List<ContentColumn> columns = childMap.get(parentId);
        if (columns == null) {
            return new ArrayList<>();
        }

        List<ContentColumnVO> categorizedColumns = new ArrayList<>();
        for (ContentColumn column : columns) {
            List<ContentColumnVO> childColumns = categorizeColumns(childMap, column.getId());
            ContentColumnVO columnVO = ContentColumnVO.builder()
                    .id(column.getId())
                    .name(column.getName())
                    .icon(column.getIcon())
                    .weight(column.getWeight())
                    .parentId(column.getParentId())
                    .parentPath(column.getParentPath())
                    .createdAt(column.getCreatedAt())
                    .updatedAt(column.getUpdatedAt())
                    .contentColumnList(childColumns)
                    .build();
            categorizedColumns.add(columnVO);
        }

        return categorizedColumns;
    }

6.测试接口