requirement analysis
Generally, when we browse the website, we can often see the effect as shown in the figure below (the picture comes from CSDN)
This kind of comment is nested layer by layer, and there are several replies to the comment under each comment.
This kind of structure is similar to the tree structure. Users can see it at a glance, and it is also a very mainstream design of comment system.
Database design
In the tree structure dominated by comments, the design of the database is very flexible, which can be a single table design. Each comment has a parent [ID] pointing to the parent comment. It can also be divided into two tables, one for comments and the other for comments.
Here I use a single table design.
The data table is designed as follows. Because I developed a news system, I took the project as an example.
| Table fields | Field description |
|---|---|
| commentId | The id of the comment, self increment. Each comment corresponds to a unique commentId |
| newsId | The id of the news corresponding to the comment |
| content | Content of comments |
| userId | The id of the user who sent the comment |
| parentId | The id of the parent comment. If it is not a reply to the comment, the value is null |
| date | Comment date |
SQL statement:
Comments:
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;
Realization
- Query statement:
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
In order to reduce the number of database queries, all comments under a news are queried directly, and then the display structure of the comments is programmed. It is also one of the common optimization methods to improve performance through appropriate redundancy
- Comment entity class
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; }
Here is a piece of code to organize all comments through the program (for the sake of the simplification of the article, only write the logic related code)
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; //If the list is modified at the same time during the list iteration, an exception of java.util.ConcurrentModificationException will be reported, so we need to break. Of course, break can also improve the algorithm efficiency break; } } if (!foundParent) { throw new RuntimeException("can not find the parent comment"); } } } return comments;
The above algorithm has two disadvantages:
- High time complexity: O (n^2)
- Depends on the premise that all comments in the allComments array are in ascending time order.
In fact, this is a typical flat data tree problem, and we can further optimize it to the best O (n) complexity
public List<Comment> getComments(Long newsId) { List<Comment> all = new ArrayList<>(); Map<Long, Comment> map = new HashMap<>(); List<Comment> result = new ArrayList<>(); for (Comment c : all) { if (c.parentId == null) { result.add(c); } map.put(c.commentId, c); } for (Comment c : all) { if (c.parentId != null) { Comment parent = map.get(c.parentId); if (parent.child == null) { parent.child = new ArrayList<>(); } parent.child.add(c); } } return result; }
The final rendering.
The data returned by the interface is as follows:
{ "code": "success", "message": "Get comment successful", "status": "200", "data": [ { "id": "236051", "author_name": "Jianbo", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132", "date": "6 Hours ago", "content": "tt", "userid": "24", "child": [] }, { "id": "236028", "author_name": "Set sail", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/7Aq39lKL2jxoWSMgbiaYkQzOR0mOMTm2TLjVhRicYaFXAzg20I8gpcqySYYYQMWG60p8r5kibG3ibiav3CC8Bzibjblw/132", "date": "2019-04-11", "content": "It's very simple and touching", "formId": null, "userid": "9676", "child": [ { "id": "236032", "author_name": "Jianbo", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132", "date": "2 Days ago", "content": ":-)", "userid": "24", "child": [ { "id": "236040", "author_name": "God loves me", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/QTU6iasloiaun5OX6ZcZB964vhHLAc5RuIf8kMR3nwIXvy0HibYOe9RJ9o8escDOIj7MB1vica5ibZ2XSDXIibfQMsJA/132", "date": "1 Days ago", "content": "Why do people choose euthanasia? Can't life be more painful than pain", "userid": "9663", "child": [ { "id": "236042", "author_name": "Jianbo", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132", "date": "1 Days ago", "content": "If you can't live with dignity, it's hard", "child": [] } ] } ] } ] }, { "id": "236024", "author_name": "Advocating adorable", "author_url": "../../images/gravatar.png", "date": "2019-04-11", "content": "Everyone has his unforgettable past. Yesterday, today and tomorrow, try to live every day!", "userid": "0", "child": [ { "id": "236041", "author_name": "Jianbo", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132", "date": "1 Days ago", "content": "It's important to have a good day"， "userid": "24", "child": [] } ] }, { "id": "236018", "author_name": "Jielinfan", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTJBXIvvpMo5nXdlk6Mxwia9chS9E8VHGEQbDmyEAx8opRibztDzmpGHpbC3lR5vh8l4fsScZWoyEWyQ/132", "date": "2019-04-08", "content": "Best wishes to brother.", "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": "Augmentation net", "author_url": "../../images/gravatar.png", "date": "2019-04-08", "content": "Send you a piece of sea, let you sail smoothly; send you a sun, let you warm and unrestrained; send you a sincere, wish you happy and happy; send you a blessing, let you happy every day!", "formId": null, "userid": "0", "child": [] }, { "id": "236011", "author_name": "Today's news", "author_url": "../../images/gravatar.png", "date": "2019-04-07", "content": "Good article, I like it very much", "userid": "0", "child": [ { "id": "236052", "author_name": "Jianbo", "author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132", "date": "6 Hours ago", "content": "Thank you", "userid": "24", "child": [] } ] } ] }