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、其他表就不展示了，下面有源码案例
后端开发
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：父评论，一对一映射，这里你也可以直接用parentCommentIdreplayComments：子评论，一对多映射
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": []
}
]
}
]
源码
先到这。。。
本文来自博客园，作者：sw-code，转载请注明原文链接：https://www.cnblogs.com/sw-code/p/15358093.html
