转载声明:文章来源:https://blog.csdn.net/qq_43396436/article/details/121656487
一、数据库设计
评论表(TFW_Comments)和回复内容表(TFW_UserResponse)以及评论回复关系表(TFW_MsgRelation)
二、数据库设计思路:
注:各位读者自动忽略评论表的服务机构ID字段,这个字段相当于这条评论是在哪个帖子(文章下面)
1、根据文章ID或者是帖子ID查询评论表获取评论(本文的服务机构ID)。第一层(评论)
2、根据评论ID并且回复类型等于1的去关系表获取第二层的回复(commentsId)。第二层(评论下的回复)
3、根据评论ID、回复类型等于2、回复ID去关系表获取第三层回复。第三层(评论下回复中的回复)注:回复ID是它的上级
三、实现类源码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | @Override public Map<String, Object> findComments(JSONObject jsonObject) { data.clear(); String userId = jsonObject.getString( "userId" ); String role = this .role(jsonObject); if (role.equals( "-1" )){ //没有权限 data.put( "error" , "-1" ); data.put( "msg" , "当前用户没有权限" ); return data; } List<Map<String, Object>> info = commentsDao.findComment(jsonObject.getString( "fWJLID" ), null ); //查询点赞次数 int countTag = 0; MsgRelationTag msgRelationTag = new MsgRelationTag(); for (Map item : info){ item.put( "inputShow" , false ); int commentsId = (int) item.get( "commentsId" ); //查询点赞次数 countTag = msgRelationDao.findCountTagByTagId(commentsId,1); item.put( "countTag" ,countTag); //设置点赞状态 msgRelationTag.setTagId(commentsId); msgRelationTag.setTagType(1); msgRelationTag.setTagUserId(Integer.parseInt(userId)); MsgRelationTag msgTag = msgRelationDao.findMsgTag(msgRelationTag); if (msgTag != null ) { item.put( "tagStatus" ,msgTag.getStatus()); } else { item.put( "tagStatus" , "" ); } //如果有@id if (item.get( "atId" ) != null ){ String content = item.get( "content" ).toString(); StringBuffer tmrAtId = findUserName(item.get( "atId" ).toString()); item.put( "content" ,content+ '@' +tmrAtId); } //二级回复数据 List<Map<String, Object>> twoReply = new ArrayList<>(); //所有数据 List<Map<String, Object>> userResponse = userResponseDao.findUserResponse(commentsId, null , "" , "" , null ); for (Map userResponseInfo :userResponse){ int userResponseIds = Integer.parseInt(userResponseInfo.get( "userResponseId" ).toString()); //查询点赞次数 countTag = msgRelationDao.findCountTagByTagId(userResponseIds,2); //设置点赞状态 msgRelationTag.setTagId(userResponseIds); msgRelationTag.setTagType(2); msgTag = msgRelationDao.findMsgTag(msgRelationTag); if (msgTag != null ) {userResponseInfo.put( "tagStatus" ,msgTag.getStatus());} else {userResponseInfo.put( "tagStatus" , "" );} userResponseInfo.put( "countTag" ,countTag); userResponseInfo.put( "inputShow" , false ); Integer responseType = (Integer) userResponseInfo.get( "responseType" ); for (Map stairReplyInfo : userResponse){ Integer userResponseId = (Integer) stairReplyInfo.get( "userResponseId" ); int msgRelationId = Integer.parseInt(stairReplyInfo.get( "msgRelationId" ).toString()); //接受者id*/ twoReply = userResponseDao.findUserResponse(msgRelationId, userResponseId, "1" , "" , null ); //二级回复数据 for (Map twoReplyItem : twoReply){ int twoReplyId = Integer.parseInt(twoReplyItem.get( "userResponseId" ).toString()); twoReplyItem.put( "inputShow" , false ); //查询点赞次数 countTag = msgRelationDao.findCountTagByTagId(twoReplyId,2); twoReplyItem.put( "countTag" ,countTag); //设置点赞状态 msgRelationTag.setTagId(twoReplyId); msgTag = msgRelationDao.findMsgTag(msgRelationTag); if (msgTag != null ) {twoReplyItem.put( "tagStatus" ,msgTag.getStatus());} else {twoReplyItem.put( "tagStatus" , "" );} String userRepContent = twoReplyItem.get( "userRepContent" ).toString(); if (twoReplyItem.get( "tmrAtId" ) != null ){ StringBuffer tmrAtId = findUserName(twoReplyItem.get( "tmrAtId" ).toString()); twoReplyItem.put( "userRepContent" ,userRepContent+ '@' +tmrAtId); } } stairReplyInfo.put( "twoReply" ,twoReply); } } item.put( "stairReply" ,userResponse); } data.put( "data" ,info); data.put( "error" ,0); data.put( "msg" , "查询成功" ); return data; } |
其它的代码可以忽略。主要语句有:
四、获取帖子下的评论
1 | List<Map<String, Object>> info = commentsDao.findComment(jsonObject.getString( "fWJLID" ), null ); |
上图根据FWJLID获取评论。(此处可以当成帖子的ID,获取帖子下的评论)一级展示
对应SQL语句(OPT是我的用户表)
1 2 3 | select tc.content ,tc.commentsId,convert(varchar(19),tc.startTime,120) as startTime,tc.recipientId ,tc.operatorId,zo.NAME as operatorName,tc.atId,zo.HeadImgUrl as operatorHeadImgUrl from TFW_Comments tc left join zd_opt zo on zo.AID = tc.operatorId where tc.FWJLID = 5101 |
查询结果:
五、获取评论下的回复
1 | List<Map<String, Object>> userResponse = userResponseDao.findUserResponse(commentsId, null , "" , "" , null ); |
上图根据commentsid获取评论下的回复。(根据评论ID获取回复)二级展示
对应sql语句
1 2 3 4 5 6 7 8 9 10 11 | select tur.userResponseId,tur.operatorId,tur.recipientId,convert(varchar(19),tur.startTime,120) as startTime,tur.userRepContent,tmr.atId as tmrAtId, tmr.msgRelationId ,tmr.responseType,tmr.replyId, zo.NAME as operatorName, zo1.NAME as recipientName, zo.HeadImgUrl as operatorHeadImgUrl, zo1.HeadImgUrl as recipientHeadImgUrl from TFW_MsgRelation tmr left join TFW_UserResponse tur on tur.userResponseId = tmr.userResponseId left join zd_opt zo on zo.AID = tur.operatorId left join zd_opt zo1 on zo1.AID = tur.recipientId where tmr.commentsId = 47 |
查询结果
六、获取二级回复
1 | twoReply = userResponseDao.findUserResponse(msgRelationId, userResponseId, "1" , "" , null ); //二级回复数据 |
上图是根据评论ID(msgRelationId)和回复ID(userResponseId)去获取二级回复。回复ID也就是父类。就是回复那一条回复的ID。 第三层展示
对应sql
1 2 3 4 5 6 7 8 9 10 11 | select tur.userResponseId,tur.operatorId,tur.recipientId,convert(varchar(19),tur.startTime,120) as startTime,tur.userRepContent,tmr.atId as tmrAtId, tmr.msgRelationId ,tmr.responseType,tmr.replyId, zo.NAME as operatorName, zo1.NAME as recipientName, zo.HeadImgUrl as operatorHeadImgUrl, zo1.HeadImgUrl as recipientHeadImgUrl from TFW_MsgRelation tmr left join TFW_UserResponse tur on tur.userResponseId = tmr.userResponseId left join zd_opt zo on zo.AID = tur.operatorId left join zd_opt zo1 on zo1.AID = tur.recipientId where tmr.commentsId = 136 and tmr.replyId = 155 |
查询结果
返回页面展示和返回体展示
帖子还没人回复快来抢沙发