Mybatis的递归查询实现二级评论

最近闲来无事，在做毕业设计。前台页面需要一个评论功能，感觉三方的评论太复杂，功能也太多。就想着自己写一个简单点的，本人比较菜，方法比较笨，可能效率不高。

环境介绍

后端：SpringBoot + Mybatis

前端：Vue + Element Plus

效果展示

数据库设计

这里展示的是需要登录才能使用评论（依赖用户表），如果不需要登录，可删除 comment_user_id，添加邮箱和昵称字段： email， nickname

1、评论表字段说明

字段名 类型 说明 id bigint 主键 article_id bigint 评论所在文章ID parent_comment_id bigint 父评论id，null表示一级评论 comment_user_id bigint 评论发布用户ID content longtext 评论内容 create_time datetime 创建时间

只展示最基本的字段，需要字段自行添加，例如： version、 deleted

2、评论表建表语句

CREATE TABLE comment (
  id bigint(20) NOT NULL COMMENT '评论ID',
  article_id bigint(20) DEFAULT NULL COMMENT '文章ID',
  comment_user_id bigint(20) DEFAULT NULL COMMENT '评论者ID',
  parent_comment_id bigint(20) DEFAULT NULL COMMENT '父评论ID',
  content longtext COMMENT '评论内容',
  create_time datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (id) USING BTREE,
  KEY comment_user (comment_user_id),
  KEY comment_article (article_id),
  KEY child_parent (parent_comment_id),
  CONSTRAINT child_parent FOREIGN KEY (parent_comment_id) REFERENCES comment (id),
  CONSTRAINT comment_article FOREIGN KEY (article_id) REFERENCES article (id),
  CONSTRAINT comment_user FOREIGN KEY (comment_user_id) REFERENCES user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、其他表就不展示了，下面有源码案例

后端开发

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class Comment {
    private Long id;
    private Long articleId;
    private Long parentCommentId;
    private String content;
    private Date createTime;
    private Date updateTime;
    private User commentUser;
    private Comment parentComment;
    private List replayComments = new ArrayList<>();
}

• commentUser：评论用户，一对一映射
• parentComment：父评论，一对一映射，这里你也可以直接用parentCommentId
• replayComments：子评论，一对多映射

Mapper

mapper文件

我们来看看， SQL语句怎么写，还是比较复杂的，因为考虑到映射，关联的表比较多

        select c.*,cu.avatar c_u_avatar, cu.nickname c_u_nickname, pu.id p_u_id, pu.avatar p_u_avatar, pu.nickname p_u_nickname
        from ((comment c LEFT JOIN comment p on p.id = c.parent_comment_id) LEFT JOIN user cu on cu.id = c.comment_user_id) LEFT JOIN user pu on pu.id = p.comment_user_id
        where c.parent_comment_id = #{id} order by create_time

        select c.*, u.avatar u_avatar, u.nickname u_nickname
        from comment c left join user u on u.id = c.comment_user_id
        where c.article_id=#{articleId} and parent_comment_id is null

这里使用了很多 LEFT JOIN，可以替换成同意思的 WHERE

mapper类

@Repository
public interface CommentMapper {

    // 列出所有一评论
    List listTopComment(@Param("articleId") Long articleId);

    //列出一级评论下子评论一级子评论的子评论
    List listTreeComment(@Param("id") Long id);

}

Service

CommentService

public interface CommentService {
    List listTopComment(Long articleId);
}

CommentServiceImpl

@Service
ublic class CommentServiceImpl implements CommentService {

    final CommentMapper commentMapper;
    public CommentServiceImpl(CommentMapper commentMapper) {
        this.commentMapper = commentMapper;
    }

    @Override
    public List listTopComment(Long articleId) {
        // 获取所有一级评论
        List topComment = commentMapper.listTopComment(articleId);
        // 获取所有子评论
        for (Comment comment : topComment) {
            comment.setReplayComments(commentMapper.listTreeComment(comment.getId()));
        }
        return topComment;
    }
}

Controller

@RestController
@RequestMapping("/comment")
public class CommentController {

    final CommentService commentService;
    public CommentController(CommentService commentService) {
        this.commentService = commentService;
    }

    @GetMapping("/list/{articleId}")
    List listComment(@PathVariable("articleId") Long articleId) {
        return commentService.listTopComment(articleId);
    }
}

测试

弄几条数据

文章数据
insert into article(id, title, content) value(1, '测试', '测试测试测试测试测试测试测试')

用户数据
insert into user(id, nickname, avatar) value(1, '测试员1', '/images/avatar01.jpg')
insert into user(id, nickname, avatar) value(2, '测试员2', '/images/avatar02.jpg')

#评论数据
insert into comment(id, article_id, parent_comment_id, comment_user_id, content,create_time)
value(1, 1, null, 1, '她好美', NOW())
insert into comment(id, article_id, parent_comment_id, comment_user_id, content,create_time)
value(2, 1, 1, 2, '你那是馋她身子', NOW())
insert into comment(id, article_id, parent_comment_id, comment_user_id, content,create_time)
value(3, 1, 2, 2, '哈哈', NOW())

分析结果

[
    {
        "id": 1,
        "articleId": 1,
        "parentCommentId": null,
        "content": "她好美",
        "createTime": "2021-09-30T11:06:19.000+00:00",
        "updateTime": null,
        "commentUser": {
            "id": 1,
            "nickname": "测试员1",
            "avatar": null
        },
        "parentComment": null,
        "replayComments": [
            {
                "id": 2,
                "articleId": 1,
                "parentCommentId": null,
                "content": "你那是馋她身子",
                "createTime": "2021-09-30T11:07:25.000+00:00",
                "updateTime": null,
                "commentUser": {
                    "id": 2,
                    "nickname": "测试员2",
                    "avatar": "/images/avatar02.jpg"
                },
                "parentComment": {
                    "id": 1,
                    "articleId": null,
                    "parentCommentId": null,
                    "content": null,
                    "createTime": null,
                    "updateTime": null,
                    "commentUser": {
                        "id": 1,
                        "nickname": "测试员1",
                        "avatar": "/images/avatar01.jpg"
                    },
                    "parentComment": null,
                    "replayComments": []
                },
                "replayComments": [
                    {
                        "id": 3,
                        "articleId": 1,
                        "parentCommentId": null,
                        "content": "哈哈",
                        "createTime": "2021-09-30T11:36:24.000+00:00",
                        "updateTime": null,
                        "commentUser": {
                            "id": 2,
                            "nickname": "测试员2",
                            "avatar": "/images/avatar02.jpg"
                        },
                        "parentComment": {
                            "id": 2,
                            "articleId": null,
                            "parentCommentId": null,
                            "content": null,
                            "createTime": null,
                            "updateTime": null,
                            "commentUser": {
                                "id": 2,
                                "nickname": "测试员2",
                                "avatar": "/images/avatar02.jpg"
                            },
                            "parentComment": null,
                            "replayComments": []
                        },
                        "replayComments": []
                    }
                ]
            }
        ]
    }
]

我们可以看到，数据是嵌套的，这种套娃格式也是一种方案

[
    {
        "id": 1,
        "content": "她好美",
        "...": ...,
        "replayComments": [
            {
                "id": 2,
                "...": ...,
                "replayComments": [
                    {
                        "id": 3,
                "...": ...,
                "replayComments": null,
                    }
                ]
            }
        ]
    }
]

改套娃为二级

将套娃评论改为二级，只需要在逻辑部分将二级评论的子评论（三级）等等（四级，五级…），全部改为二级评论

Service

@Service
public class CommentServiceImpl implements CommentService {

    int commentNum = 0;
    final CommentMapper commentMapper;
    public CommentServiceImpl(CommentMapper commentMapper) {
        this.commentMapper = commentMapper;
    }

    @Override
    public List listTopComment(Long articleId) {
        // 获取所有一级评论
        List topComment = commentMapper.listTopComment(articleId);
        // 获取所有子评论
        for (Comment comment : topComment) {
            comment.setReplayComments(commentMapper.listTreeComment(comment.getId()));
        }
        return eachComment(topComment);
    }

    @Override
    public List listComment(Long articleId) {
        commentNum = 0;
        // 获取所有一级评论
        List topComment = commentMapper.listTopComment(articleId);
        // 获取所有子评论
        for (Comment comment : topComment) {
            comment.setReplayComments(commentMapper.listTreeComment(comment.getId()));
        }
        return eachComment(topComment);
    }

    // 循环遍历顶级评论，并放入commentView中
    private List eachComment(List comments) {
        List commentView = new ArrayList<>();
        for (Comment comment : comments) {
            Comment c = new Comment();
            BeanUtils.copyProperties(comment, c);
            commentView.add(c);
        }
        //合并评论的各层子代到一级子代中
        combineChildren(commentView);
        return commentView;
    }

    // 进行所有子评论的遍历，并添加到顶级评论的子评论中
    private void combineChildren(List comments) {
        for (Comment comment : comments) {
            commentNum++;
            List replys1 = comment.getReplayComments();
            for (Comment reply1 : replys1) {
                //循环迭代找出子代，存放在tampReplys中
                recursively(reply1);
            }
            //修改顶级节点的reply集合为迭代后的集合
            comment.setReplayComments(tempReplys);
            //清除临时存放区
            tempReplys = new ArrayList<>();
        }
    }

    //存放迭代找出的所有子代集合
    private List tempReplys = new ArrayList<>();

    // 递归，将顶级评论的子评论以及子评论的子评论全部放到 tempReplys
    private void recursively(Comment comment) {
        tempReplys.add(comment);//顶节点添加到临时存放区
        commentNum++;
        if (comment.getReplayComments().size() > 0) {
            List replys = comment.getReplayComments();
            for (Comment reply : replys) {
                if (reply.getReplayComments().size() > 0) {
                    recursively(reply);
                } else {
                    commentNum++;
                    tempReplys.add(reply);
                }
            }
        }
    }
}

commentNum 记录了评论数量，有需要的可以返回给前端

测试

Controller调用 listComment方法

[
    {
        "id": 1,
        "articleId": 1,
        "parentCommentId": null,
        "content": "她好美",
        "createTime": "2021-09-30T11:06:19.000+00:00",
        "updateTime": null,
        "commentUser": {
            "id": 1,
            "nickname": "测试员1",
            "avatar": null
        },
        "parentComment": null,
        "replayComments": [
            {
                "id": 2,
                "articleId": 1,
                "parentCommentId": null,
                "content": "你那是馋她身子",
                "createTime": "2021-09-30T11:07:25.000+00:00",
                "updateTime": null,
                "commentUser": {
                    "id": 2,
                    "nickname": "测试员2",
                    "avatar": "/images/avatar02.jpg"
                },
                "parentComment": {
                    "id": 1,
                    "articleId": null,
                    "parentCommentId": null,
                    "content": null,
                    "createTime": null,
                    "updateTime": null,
                    "commentUser": {
                        "id": 1,
                        "nickname": "测试员1",
                        "avatar": "/images/avatar01.jpg"
                    },
                    "parentComment": null,
                    "replayComments": []
                },
                "replayComments": [
                    {
                        "id": 3,
                        "articleId": 1,
                        "parentCommentId": null,
                        "content": "哈哈",
                        "createTime": "2021-09-30T11:36:24.000+00:00",
                        "updateTime": null,
                        "commentUser": {
                            "id": 2,
                            "nickname": "测试员2",
                            "avatar": "/images/avatar02.jpg"
                        },
                        "parentComment": {
                            "id": 2,
                            "articleId": null,
                            "parentCommentId": null,
                            "content": null,
                            "createTime": null,
                            "updateTime": null,
                            "commentUser": {
                                "id": 2,
                                "nickname": "测试员2",
                                "avatar": "/images/avatar02.jpg"
                            },
                            "parentComment": null,
                            "replayComments": []
                        },
                        "replayComments": []
                    }
                ]
            },
            {
                "id": 3,
                "articleId": 1,
                "parentCommentId": null,
                "content": "哈哈",
                "createTime": "2021-09-30T11:36:24.000+00:00",
                "updateTime": null,
                "commentUser": {
                    "id": 2,
                    "nickname": "测试员2",
                    "avatar": "/images/avatar02.jpg"
                },
                "parentComment": {
                    "id": 2,
                    "articleId": null,
                    "parentCommentId": null,
                    "content": null,
                    "createTime": null,
                    "updateTime": null,
                    "commentUser": {
                        "id": 2,
                        "nickname": "测试员2",
                        "avatar": "/images/avatar02.jpg"
                    },
                    "parentComment": null,
                    "replayComments": []
                },
                "replayComments": []
            }
        ]
    }
]

分析一波

可以看到 id为的 3 的评论已经成为二级评论了

[
    {
        "id": 1,
        "content": "她好美",
        "...": ...,
        "replayComments": [
            {
                "id": 2,
                "...": ...,
                "replayComments": [
                    {
                        "id": 3,
                "...": ...,
                "replayComments": [],
                    }
                ]
            },
        {
                "id": 3,
                "...": ...,
                "replayComments": []
            }
        ]
    }
]

源码

码云地址

先到这。。。

Original: https://www.cnblogs.com/sw-code/p/15358093.html
Author: sw-code
Title: Mybatis的递归查询实现二级评论