评论

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 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": []
}
]
}
]

源码#

码云地址

先到这。。。-

跳转到 Cubox 查看