一直想写个评论系统,看了下多说,网易, 简书的评论,想了下自己该实现怎样的评论系统。
评论系统关键是嵌套层数以及数据库表设计。嵌套层数多,表结构复杂,呈现也麻烦,最后决定实现一个二级评论。系统由 maven 构建,springboot 快速搭建 spring 环境。前台采用 angularjs+bootstrap, 后台使用 springmvc+mybatis, 数据库采用 MySQL. 前台请求后台 API 操作评论。
目录结构
数据库表设计
- ##说说表或者文章表
- create table saying (
- saying_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- sayingContent VARCHAR(500) NOT NULL,
- author VARCHAR(50) NOT NULL,
- sayingAvatar VARCHAR(50) NOT NULL,
- likes VARCHAR(500) NOT NULL,
- createTime datetime NOT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- ##一级评论表
- create table firstLevelComment (
- flc_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- sayingId INT NOT NULL,
- commenter VARCHAR(50) NOT NULL,
- commenterAvatar VARCHAR(50) NOT NULL,
- commentContent VARCHAR(500) NOT NULL,
- commentTime datetime NOT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- ##二级评论表
- create table secondLevelComment (
- slc_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- sayingId INT NOT NULL,
- flcId INT NOT NULL,
- replier VARCHAR(50) NOT NULL,
- toCommenter VARCHAR(50) NOT NULL,
- replyContent VARCHAR(50) NOT NULL,
- replyTime datetime NOT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
获取评论的 mapper(关键)
- <?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="personal.timeless.cms.mapper.SayingMapper" >
- <resultMap id="SayingMap" type="saying" >
- <id column="saying_id" property="id" jdbcType="INTEGER" />
- <result column="sayingContent" property="sayingContent" jdbcType="INTEGER" />
- <result column="author" property="author" jdbcType="VARCHAR" />
- <result column="sayingAvatar" property="avatar" jdbcType="VARCHAR" />
- <result column="likes" property="likes" jdbcType="VARCHAR" />
- <result column="createTime" property="createTime" jdbcType="TIMESTAMP" />
- <collection property="flcs" ofType="firstLevelComment" column="sayingId">
- <id column="flc_id" property="id" jdbcType="INTEGER" />
- <result column="sayingId" property="sayingId" jdbcType="INTEGER" />
- <result column="commenter" property="commenter"/>
- <result column="commenterAvatar" property="avatar"/>
- <result column="commentContent" property="commentContent"/>
- <result column="commentTime" property="commentTime" jdbcType="TIMESTAMP" />
- <collection property="slcs" ofType="secondLevelComment" column="flcId">
- <id column="slc_id" property="id" jdbcType="INTEGER" />
- <result column="flcId" property="flcId" jdbcType="INTEGER" />
- <result column="replier" property="replier"/>
- <result column="toCommenter" property="toCommenter"/>
- <result column="replyContent" property="replyContent"/>
- <result column="replyTime" property="replyTime" jdbcType="TIMESTAMP" />
- </collection>
- </collection>
- </resultMap>
- <select id="selectOneById" resultMap="SayingMap" parameterType="int" >
- select * from
- (select * from saying s left join firstLevelComment fc on s.saying_id=fc.sayingId where s.saying_id=#{id}) tmp left join secondLevelComment sc
- on tmp.flc_id = sc.flcId
- </select>
- <select id="updateLikesById">
- update saying set likes = #{likes} where saying_id = #{id}
- </select>
- </mapper>
页面展示
来源: http://www.jb51.net/article/105391.htm