526互联

评论

发布时间 2024-01-02 20:13:49作者: 小小的编程员

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

www.cnblogs.comsw-code 粉丝 - 9 关注 - 0

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

环境介绍#

后端：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、其他表就不展示了，下面有源码案例

后端开发#

Comment实体类#

@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<Comment> replayComments = new ArrayList<>();
}
  • commentUser：评论用户，一对一映射
  • parentComment：父评论，一对一映射，这里你也可以直接用parentCommentId
  • replayComments：子评论，一对多映射

Mapper#

mapper文件

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

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.swx.blog_test.mapper.CommentMapper">

    <resultMap id="tree" type="Comment">
        <id property="id" column="id"/>
        <result property="content" column="content"/>
        <result property="createTime" column="create_time"/>
        <result property="articleId" column="article_id" />
        <association property="commentUser" javaType="User">
            <id property="id" column="comment_user_id"/>
            <result property="avatar" column="c_u_avatar" />
            <result property="nickname" column="c_u_nickname" />
        </association>
        <association property="parentComment" javaType="Comment">
            <id property="id" column="parent_comment_id"/>
            <association property="commentUser" javaType="User">
                <id property="id" column="p_u_id"/>
                <result property="avatar" column="p_u_avatar" />
                <result property="nickname" column="p_u_nickname" />
            </association>
        </association>
        <collection property="replayComments" select="listTreeComment" column="id"/>
    </resultMap>

    <resultMap id="simple" type="Comment">
        <id property="id" column="id"/>
        <result property="content" column="content"/>
        <result property="createTime" column="create_time"/>
        <result property="articleId" column="article_id" />
        <association property="commentUser" javaType="User">
            <id property="id" column="comment_user_id"/>
            <result property="avatarUrl" column="u_avatar_url" />
            <result property="nickname" column="u_nickname" />
        </association>
    </resultMap>

    <select id="listTreeComment" resultMap="tree" parameterType="long">
        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>

    <select id="pageComment" resultMap="simple">
        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
    </select>
</mapper>

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

mapper类

@Repository
public interface CommentMapper {
    
    // 列出所有一评论
    List<Comment> listTopComment(@Param("articleId") Long articleId);
        
    //列出一级评论下子评论一级子评论的子评论
    List<Comment> listTreeComment(@Param("id") Long id);
    
}

Service#

CommentService

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

CommentServiceImpl

@Service
ublic class CommentServiceImpl implements CommentService {

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

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

    // 循环遍历顶级评论，并放入commentView中
    private List<Comment> eachComment(List<Comment> comments) {
        List<Comment> 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<Comment> comments) {
        for (Comment comment : comments) {
            commentNum++;
            List<Comment> replys1 = comment.getReplayComments();
            for (Comment reply1 : replys1) {
                //循环迭代找出子代，存放在tampReplys中
                recursively(reply1);
            }
            //修改顶级节点的reply集合为迭代后的集合
            comment.setReplayComments(tempReplys);
            //清除临时存放区
            tempReplys = new ArrayList<>();
        }
    }

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

    // 递归，将顶级评论的子评论以及子评论的子评论全部放到 tempReplys
    private void recursively(Comment comment) {
        tempReplys.add(comment);//顶节点添加到临时存放区
        commentNum++;
        if (comment.getReplayComments().size() > 0) {
            List<Comment> 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": []
            }
        ]
    }
]

源码#

码云地址

先到这。。。-

跳转到 Cubox 查看