評論系統(tǒng)數(shù)據(jù)庫設(shè)計及實現(xiàn)
需求分析
一般我們?yōu)g覽網(wǎng)站的時候經(jīng)常能看到如下圖的這種效果（圖片來自CSDN）
image
這種評論層層嵌套李剖，每個評論下面還掛著若干個對評論的回復(fù)。
這種結(jié)構(gòu)類似于樹狀結(jié)構(gòu)形真，用戶看起來一目了然祟滴，也是一種非常主流的評論系統(tǒng)設(shè)計。
數(shù)據(jù)庫設(shè)計
在以評論為主的樹形結(jié)構(gòu)中松申，數(shù)據(jù)庫的設(shè)計非常靈活灿里，可以是單表設(shè)計关炼，每個評論都有一個parent_id指向父評論。還可以分開為兩個表匣吊，評論一張表儒拂，對評論的回復(fù)是另一張表。
這里我使用的是單表設(shè)計色鸳。
數(shù)據(jù)表設(shè)計如下社痛。由于我開發(fā)的是一個新聞系統(tǒng)，所以我就直接以項目舉例命雀。
| 表字段 | 字段說明 |
|---|---|
| commentId | 評論的id蒜哀，自增值，每個評論都對應(yīng)一個唯一的commentId |
| newsId | 評論所對應(yīng)的新聞的id |
| content | 評論的內(nèi)容 |
| userId | 發(fā)出該評論用戶的id |
| parentId | 指向父評論的id,如果不是對評論的回復(fù),那么該值為null |
| date | 評論產(chǎn)生日期 |
SQL語句：
評論表：
create table if not exists comments
(
commentId bigint auto_increment primary key,
newsId bigint not null,
parentId bigint,
content text not null,
userId bigint not null,
date timestamp default current_timestamp(),
foreign key (parentID) references comments (commentId),
foreign key (userID) references users (userId),
foreign key (newsID) references news (newsId)
) charset = utf8mb4;
實現(xiàn)
- 查詢語句：
SELECT a.commentId,a.newsId,a.parentId,a.newsId,b.nickname,b.avatar,a.content,a.date
FROM comments AS a,users AS b WHERE a.newsId=#{newsId} AND a.userId=b.userId
為了減少數(shù)據(jù)庫查詢次數(shù)吏砂，直接一次將一個新聞下的所有評論都查詢了出來撵儿，然后通過程序來編排評論的顯示結(jié)構(gòu)乘客。通過適當(dāng)?shù)娜哂鄟硖岣咝阅芤彩浅Ｓ玫膬?yōu)化手段之一
- 評論的實體類
import lombok.Data;
import java.util.Date;
import java.util.List;
@Data
public class Comment {
Long commentId;
Long newsId;
Long parentId;
Long userId;
String nickname;
String avatar;
String content;
Date date;
List<Comment> child;
}
這里給出一段通過程序來組織所有評論的代碼（為了文章的精簡，只寫邏輯相關(guān)的代碼）
public List<Comment> getComments(Long newsId) {
List<Comment> allComments = commentMapper.getComments(newsId);
if (allComments == null || allComments.size() == 0) {
return new ArrayList<>();
}
List<Comment> comments = new ArrayList<>();
List<Comment> parents = new ArrayList<>();
for (Comment comment : allComments) {
if (comment.getParentId()==null) {
comments.add(comment);
parents.add(comment);
} else {
boolean foundParent=false;
for (Comment parent : parents) {
if (comment.getParentId() == parent.getCommentId()) {
if (parent.getChild() == null) {
parent.setChild(new ArrayList<>());
}
parent.getChild().add(comment);
parents.add(comment);
foundParent=true;
//如果對list迭代過程中同時修改list淀歇，會報java.util.ConcurrentModificationException 的異常寨典，所以我們需要break,當(dāng)然break也可以提高算法效率
break;
}
}
if (!foundParent) {
throw new RuntimeException("can not find the parent comment");
}
}
}
return comments;
最終形成的效果圖。
image
接口返回的數(shù)據(jù)如下：
{
"code": "success",
"message": "獲取評論成功",
"status": "200",
"data": [
{
"id": "236051",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "6小時前",
"content": "tt",
"userid": "24",
"child": []
},
{
"id": "236028",
"author_name": "起航",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/7Aq39lKL2jxoWSMgbiaYkQzOR0mOMTm2TLjVhRicYaFXAzg20I8gpcqySYYYQMWG60p8r5kibG3ibiav3CC8Bzibjblw/132",
"date": "2019-04-11",
"content": "很樸實的文字房匆，又讓人感動唏噓",
"formId": null,
"userid": "9676",
"child": [
{
"id": "236032",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "2天前",
"content": ":-)",
"userid": "24",
"child": [
{
"id": "236040",
"author_name": "God loves me",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/QTU6iasloiaun5OX6ZcZB964vhHLAc5RuIf8kMR3nwIXvy0HibYOe9RJ9o8escDOIj7MB1vica5ibZ2XSDXIibfQMsJA/132",
"date": "1天前",
"content": "為什么有人會選擇安樂死呢，活著難道比不上痛苦嗎",
"userid": "9663",
"child": [
{
"id": "236042",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "1天前",
"content": "如果無法有尊嚴(yán)的活著报亩，就難受",
"child": []
}
]
}
]
}
]
},
{
"id": "236024",
"author_name": "倡萌",
"author_url": "../../images/gravatar.png",
"date": "2019-04-11",
"content": "每個人都有自己難以忘懷的過往浴鸿，昨天今天明天，努力過好每一天弦追！",
"userid": "0",
"child": [
{
"id": "236041",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "1天前",
"content": "過好今天岳链，很重要"，
"userid": "24",
"child": []
}
]
},
{
"id": "236018",
"author_name": "Jielinfan",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTJBXIvvpMo5nXdlk6Mxwia9chS9E8VHGEQbDmyEAx8opRibztDzmpGHpbC3lR5vh8l4fsScZWoyEWyQ/132",
"date": "2019-04-08",
"content": "祝福老哥劲件。",
"userid": "280",
"child": [
{
"id": "236019",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "2019-04-09",
"content": ":-)",
"userid": "24",
"child": []
}
]
},
{
"id": "236017",
"author_name": "增大網(wǎng)",
"author_url": "../../images/gravatar.png",
"date": "2019-04-08",
"content": "送你一片大海掸哑，讓你一帆風(fēng)順；送你一個太陽零远，讓你熱情奔放苗分；送你一份真誠，祝你開心快樂牵辣；送你一份祝福摔癣，讓你快樂天天！",
"formId": null,
"userid": "0",
"child": []
},
{
"id": "236011",
"author_name": "今日新聞",
"author_url": "../../images/gravatar.png",
"date": "2019-04-07",
"content": "文章不錯非常喜歡",
"userid": "0",
"child": [
{
"id": "236052",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "6小時前",
"content": "謝謝",
"userid": "24",
"child": []
}
]
}
]
}
