libilibi项目总结（15）消息管理和播放历史-优快云博客

本文链接：https://blog.youkuaiyun.com/2301_80190170/article/details/144481422

user_message

CREATE TABLE `user_message` (
  `message_id` int NOT NULL AUTO_INCREMENT COMMENT '消息ID自增',
  `user_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
  `video_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '主体ID',
  `message_type` tinyint(1) DEFAULT NULL COMMENT '消息类型',
  `send_user_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人ID',
  `read_type` tinyint(1) DEFAULT NULL COMMENT '0:未读 1:已读',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `extend_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '扩展信息',
  PRIMARY KEY (`message_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_read_type` (`read_type`) USING BTREE,
  KEY `idx_message_type` (`message_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户消息表';

获取未读信息数量

controller

 	//获取未读信息数量
    @RequestMapping("/getNoReadCount")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO getNoReadCount() {
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();
        //未登录
        if (tokenUserInfoDto == null) {
            return getSuccessResponseVO(0);
        }

        UserMessageQuery messageQuery = new UserMessageQuery();
        messageQuery.setUserId(tokenUserInfoDto.getUserId());
        messageQuery.setReadType(MessageReadTypeEnum.NO_READ.getType());
        Integer count = userMessageService.findCountByParam(messageQuery);

        return getSuccessResponseVO(count);
    }

分组获取未读信息数量

controller

//分组获取未读信息数量
    @RequestMapping("/getNoReadCountGroup")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO getNoReadCountGroup() {
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();
        List<UserMessageCountDto> dataList = userMessageService.getMessageTypeNoReadCount(tokenUserInfoDto.getUserId());
        return getSuccessResponseVO(dataList);
    }

XML

<select id="getMessageTypeNoReadCount" resultType="com.easylive.entity.dto.UserMessageCountDto">
        select message_type messageType,count(1) messageCount
        from user_message
        where user_id = #{userId} and read_type = 0
        group by message_type
</select>

已读该类型的所有信息

controller

//已读该类型的所有信息
    @RequestMapping("/readAll")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO readAll(Integer messageType) {
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();

        UserMessageQuery userMessageQuery = new UserMessageQuery();
        userMessageQuery.setUserId(tokenUserInfoDto.getUserId());
        userMessageQuery.setMessageType(messageType);

        UserMessage userMessage = new UserMessage();
        userMessage.setReadType(MessageReadTypeEnum.READ.getType());

        userMessageService.updateByParam(userMessage, userMessageQuery);
        return getSuccessResponseVO(null);
    }

获取对应类型的所有信息

controller

//获取对应类型的所有信息
    @RequestMapping("/loadMessage")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO loadMessage(@NotNull Integer messageType, Integer pageNo) {
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();

        UserMessageQuery messageQuery = new UserMessageQuery();
        messageQuery.setMessageType(messageType);
        messageQuery.setPageNo(pageNo);
        messageQuery.setUserId(tokenUserInfoDto.getUserId());
        messageQuery.setOrderBy("message_id desc");

        PaginationResultVO resultVO = userMessageService.findListByPage(messageQuery);
        return getSuccessResponseVO(resultVO);
    }

删除消息

contorller

//删除消息
    @RequestMapping("/delMessage")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO delMessage(@NotNull Integer messageId) {
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();

        UserMessageQuery messageQuery = new UserMessageQuery();
        messageQuery.setUserId(tokenUserInfoDto.getUserId());
        messageQuery.setMessageId(messageId);

        userMessageService.deleteByParam(messageQuery);
        return getSuccessResponseVO(null);
    }

video_play_history

CREATE TABLE `video_play_history` (
  `user_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
  `video_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频ID',
  `file_index` int NOT NULL COMMENT '文件索引',
  `last_update_time` datetime NOT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`user_id`(4),`video_id`) USING BTREE,
  KEY `idx_video_id` (`video_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='视频播放历史';

获取所有播放记录

controller

questMapping("/loadHistory")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO loadHistory(Integer pageNo) {
       
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();
        
        VideoPlayHistoryQuery historyQuery = new VideoPlayHistoryQuery();
        historyQuery.setUserId(tokenUserInfoDto.getUserId());
        historyQuery.setOrderBy("last_update_time desc");
        historyQuery.setPageNo(pageNo);
        historyQuery.setQueryVideoDetail(true);
        
        return getSuccessResponseVO(videoPlayHistoryService.findListByPage(historyQuery));
    }

XML

	<!-- 查询集合-->
    <select id="selectList" resultMap="base_result_map">
        SELECT
        <include refid="base_column_list"/>
        <if test="query.queryVideoDetail">
            ,d.video_cover videoCover,d.video_name videoName
        </if>
        FROM video_play_history v
        <if test="query.queryVideoDetail">
            left join video_info d on d.video_id = v.video_id
        </if>
        <include refid="query_condition"/>
        <if test="query.orderBy!=null">
            order by ${query.orderBy}
        </if>
        <if test="query.simplePage!=null">
            limit #{query.simplePage.start},#{query.simplePage.end}
        </if>
    </select>

清除所有播放纪录

controller

	//清除所有播放纪录
    @RequestMapping("/cleanHistory")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO cleanHistory() {
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();
        VideoPlayHistoryQuery historyQuery = new VideoPlayHistoryQuery();
        historyQuery.setUserId(tokenUserInfoDto.getUserId());
        videoPlayHistoryService.deleteByParam(historyQuery);
        return getSuccessResponseVO(null);
    }

删除单条播放纪律

contorller

	//删除单条播放纪律
    @RequestMapping("/delHistory")
    @GlobalInterceptor(checkLogin = true)
    public ResponseVO delHistory(@NotEmpty String videoId) {
        TokenUserInfoDto tokenUserInfoDto = getTokenUserInfoDto();
        videoPlayHistoryService.deleteVideoPlayHistoryByUserIdAndVideoId(tokenUserInfoDto.getUserId(), videoId);
        return getSuccessResponseVO(null);
    }

记录播放历史

controller

在播放视频时，要先获取改视频文件的对应的M3U8文件，此时将播放记录信息加入消息队列，进行异步写入数据库。

//获取M3U8文件
    @RequestMapping("/videoResource/{fileId}")
    public void getVideoResource(HttpServletResponse response, @PathVariable @NotEmpty String fileId) {
        VideoInfoFile videoInfoFile = videoInfoFileService.getVideoInfoFileByFileId(fileId);
        String filePath = videoInfoFile.getFilePath();
        readFile(response, filePath + "/" + Constants.M3U8_NAME);

        VideoPlayInfoDto videoPlayInfoDto = new VideoPlayInfoDto();
        //视频id
        videoPlayInfoDto.setVideoId(videoInfoFile.getVideoId());
        //分p
        videoPlayInfoDto.setFileIndex(videoInfoFile.getFileIndex());

        TokenUserInfoDto tokenUserInfoDto = getTokenInfoFromCookie();
        if (tokenUserInfoDto != null) {
            videoPlayInfoDto.setUserId(tokenUserInfoDto.getUserId());
        }
        //redisComponent.addVideoPlay(videoPlayInfoDto);
        amqpTemplate.convertAndSend("directExchange", Constants.VIDEO_PLAY_ROUTING_KEY, videoPlayInfoDto);
    }

VideoPlayQueueConsumer(视频播放记录处理消费者）

@RabbitListener(queues = "videoPlayRouting")
    public void consumeVideoPlayQueue(@Payload VideoPlayInfoDto videoPlayInfoDto) {
        try {
            // 更新播放数、记录历史、更新ES等操作
            videoInfoService.addReadCount(videoPlayInfoDto.getVideoId());
            videoPlayHistoryService.saveHistory(videoPlayInfoDto.getUserId(), videoPlayInfoDto.getVideoId(), videoPlayInfoDto.getFileIndex());
            redisComponent.recordVideoPlayCount(videoPlayInfoDto.getVideoId());
            esSearchComponent.updateDocCount(videoPlayInfoDto.getVideoId(), SearchOrderTypeEnum.VIDEO_PLAY.getField(), 1);
        } catch (Exception e) {
            log.error("处理视频播放队列消息失败", e);
        }
    }