查询数据显示成树结构形式

꯭ 瞎꯭扯꯭蛋꯭

已于 2024-07-01 17:32:46 修改

阅读量211

点赞数 1

文章标签： java

于 2024-06-28 16:19:04 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_43887285/article/details/140045930

版权

标题查询数据显示成树结构形式

数据表

DROP TABLE IF EXISTS `t_manual_chapters`;
CREATE TABLE `t_manual_chapters`  (
  `chapter_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '章节ID',
  `chapter_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '章节标题',
  `parent_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '父级id',
  `manual_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手册ID',
  `seq` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '章节序号',
  `path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '路径',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `create_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `update_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (`chapter_id`) USING BTREE,
  INDEX `manual_id`(`manual_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '手册章节表' ROW_FORMAT = Dynamic;

实体类


package com.yh.pojo.manual;

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("t_manual_chapters")
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class TManualChapters implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId
    @JsonSerialize(using = ToStringSerializer.class)
    private String chapterId;

    private String chapterTitle;

    private String manualId;

    private String seq;

    private String parentId;

    private String path;

    private String createTime;
    
    private String createId;

    private String updateTime;

    private String updateId;

    @TableField(exist = false)
    private boolean isLeaf;
}

package com.yh.pojo.vo;

public class ChapterTreeBean implements Serializable {

    private static final long serialVersionUID = 1L;

    private String chapterId;

    private String chapterTitle;

    private String manualId;

    private String seq;

    private String parentId;

    private String path;

    private boolean isLeaf;

    private String createTime;
    
    private String createId;

    private String updateTime;

    private String updateId;

    public ChapterTreeBean() {}

    public ChapterTreeBean(TManualChapters chapters) {
        this.chapterId = chapters.getChapterId();
        this.chapterTitle = chapters.getChapterTitle();
        this.manualId = chapters.getManualId();
        this.seq = chapters.getSeq();
        this.isLeaf = chapters.isLeaf();
        this.parentId = chapters.getParentId();
        this.path = chapters.getPath();
        this.createTime = chapters.getCreateTime();
        this.createId = chapters.getCreateId();
        this.updateTime = chapters.getUpdateTime();
        this.updateId = chapters.getUpdateId();
        if (!chapters.isLeaf()) {
            this.children = new ArrayList<ChapterTreeBean>();
        }
    }

    private List<ChapterTreeBean> children;

    public String getChapterId() {
        return chapterId;
    }

    public void setChapterId(String chapterId) {
        this.chapterId = chapterId;
    }

    public String getChapterTitle() {
        return chapterTitle;
    }

    public void setChapterTitle(String chapterTitle) {
        this.chapterTitle = chapterTitle;
    }

    public String getManualId() {
        return manualId;
    }

    public void setManualId(String manualId) {
        this.manualId = manualId;
    }

    public String getSeq() {
        return seq;
    }

    public void setSeq(String seq) {
        this.seq = seq;
    }

    public String getParentId() {
        return parentId;
    }

    public void setParentId(String parentId) {
        this.parentId = parentId;
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    public boolean isLeaf() {
        return isLeaf;
    }

    public void setLeaf(boolean leaf) {
        isLeaf = leaf;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }

    public String getCreateId() {
        return createId;
    }

    public void setCreateId(String createId) {
        this.createId = createId;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }

    public String getUpdateId() {
        return updateId;
    }

    public void setUpdateId(String updateId) {
        this.updateId = updateId;
    }

    public List<ChapterTreeBean> getChildren() {
        return children;
    }

    public void setChildren(List<ChapterTreeBean> children) {
        this.children = children;
    }
}

Controller层

@GetMapping("/chapterTrees")
@ApiOperation(value = "获取章节标题详情树结构", httpMethod = "GET", response = Result.class, notes = "获取章节标题详情树结构")
public Result chapterTrees(@RequestParam("manualId") String manualId,
                   @ApiParam(name = "chapterId", value = "标题ID") @RequestParam(required = false) String chapterId,
                   @ApiParam(name = "chapterTitle", value = "标题名称") @RequestParam(required = false) String chapterTitle) {
    return manualsService.chapterTrees(chapterTitle, chapterId, manualId);
}

Service层

@Override
public Result<List<ChapterTreeBean>> chapterTrees(String chapterTitle, String chapterId, String manualId) {
    Result<List<ChapterTreeBean>> result = new Result<>();
    try {
        List<TManualChapters> list =  manualChaptersMapper.getChapterTreeChildrenByState(chapterTitle, manualId);
        List<ChapterTreeBean> treeList = new ArrayList<>();
        getTreeList(treeList, list, null);
        result.setResult(treeList);
        result.setSuccess(true);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        result.setMessage(e.getMessage());
        result.setSuccess(false);
    }
    return result;
}

private void getTreeList(List<ChapterTreeBean> treeList, List<TManualChapters> chapterList, ChapterTreeBean chapterTree) {
    for (TManualChapters chapter : chapterList) {
        String parentId = chapter.getParentId();
        ChapterTreeBean tree = new ChapterTreeBean(chapter);
        if (chapterTree == null && StringUtils.equals(parentId, "0")) {
            treeList.add(tree);
            if (!tree.isLeaf()) {
                getTreeList(treeList, chapterList, tree);
            }
        } else if (chapterTree != null && parentId != null && parentId.equals(chapterTree.getChapterId())) {
            chapterTree.getChildren().add(tree);
            if (!tree.isLeaf()) {
                getTreeList(treeList, chapterList, tree);
            }
        }
    }
}

Mapper层

<select id="getChapterTreeChildrenByState" resultType="com.yh.pojo.manual.TManualChapters">
    SELECT DISTINCT a.chapter_id as chapterId,
    a.chapter_title as chapterTitle,
    a.parent_id as parentId,
    a.path as path,
    a.seq as seq,
    a.manual_id as manualId,
    a.create_time as createTime,
    a.update_time as updateTime,
    a.create_id as createId,
    a.update_id as updateId,
    IF(ISNULL(b.parent_id), true, FALSE) isLeaf
    FROM t_manual_chapters a
    left join t_manual_chapters b on a.chapter_id = b.parent_id
    left join t_manuals c on a.manual_id = c.manual_id
    WHERE a.manual_id = #{manualId} and c.state = "2"
    <if test="chapterTitle != null and chapterTitle != ''">
        and a.chapter_title like concat('%',#{chapterTitle},'%')
    </if>
    order by a.seq
</select>