视频推荐列表、视频分页列表

CREATE TABLE `video_info` (
  `video_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频ID',
  `video_cover` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频封面',
  `video_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频名称',
  `user_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `last_update_time` datetime NOT NULL COMMENT '最后更新时间',
  `p_category_id` int NOT NULL COMMENT '父级分类ID',
  `category_id` int DEFAULT NULL COMMENT '分类ID',
  `post_type` tinyint NOT NULL COMMENT '0:自制作  1:转载',
  `origin_info` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '原资源说明',
  `tags` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '标签',
  `introduction` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '简介',
  `interaction` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '互动设置',
  `duration` int DEFAULT '0' COMMENT '持续时间（秒）',
  `play_count` int DEFAULT '0' COMMENT '播放数量',
  `like_count` int DEFAULT '0' COMMENT '点赞数量',
  `danmu_count` int DEFAULT '0' COMMENT '弹幕数量',
  `comment_count` int DEFAULT '0' COMMENT '评论数量',
  `coin_count` int DEFAULT '0' COMMENT '投币数量',
  `collect_count` int DEFAULT '0' COMMENT '收藏数量',
  `recommend_type` tinyint(1) DEFAULT '0' COMMENT '是否推荐0:未推荐  1:已推荐',
  `last_play_time` datetime DEFAULT NULL COMMENT '最后播放时间',
  PRIMARY KEY (`video_id`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_category_id` (`category_id`) USING BTREE,
  KEY `idx_pcategory_id` (`p_category_id`) USING BTREE,
  KEY `idx_recommend_type` (`recommend_type`) USING BTREE,
  KEY `idx_last_update_time` (`last_play_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='视频信息';

CREATE TABLE `user_action` (
  `action_id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `video_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频ID',
  `video_user_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频用户ID',
  `comment_id` int NOT NULL DEFAULT '0' COMMENT '评论ID',
  `action_type` tinyint(1) NOT NULL COMMENT '0:评论喜欢点赞 1:讨厌评论 2:视频点赞 3:视频收藏 4:视频投币 ',
  `action_count` int NOT NULL COMMENT '数量',
  `user_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
  `action_time` datetime NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`action_id`) USING BTREE,
  UNIQUE KEY `idx_key_video_comment_type_user` (`video_id`,`comment_id`,`action_type`,`user_id`) USING BTREE,
  KEY `idx_video_id` (`video_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_type` (`action_type`) USING BTREE,
  KEY `idx_action_time` (`action_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户行为 点赞、评论';

Controller

//视频推荐列表
    @RequestMapping("/loadRecommendVideo")
    public ResponseVO loadRecommendVideo() {
        VideoInfoQuery videoInfoQuery = new VideoInfoQuery();
        //需要查询用户信息
        videoInfoQuery.setQueryUserInfo(true);
        //设置排序规则
        videoInfoQuery.setOrderBy("create_time desc");
        //设置视频推荐为true
        videoInfoQuery.setRecommendType(VideoRecommendTypeEnum.RECOMMEND.getType());
        List<VideoInfo> recommendVideoList = videoInfoService.findListByParam(videoInfoQuery);
        return getSuccessResponseVO(recommendVideoList);
    }

//视频分页列表
    @RequestMapping("/loadVideo")
    public ResponseVO postVideo(Integer pCategoryId, Integer categoryId, Integer pageNo) {
        VideoInfoQuery videoInfoQuery = new VideoInfoQuery();
        videoInfoQuery.setCategoryId(categoryId);
        videoInfoQuery.setpCategoryId(pCategoryId);
        videoInfoQuery.setPageNo(pageNo);
        videoInfoQuery.setQueryUserInfo(true);
        videoInfoQuery.setOrderBy("create_time desc");
        videoInfoQuery.setRecommendType(VideoRecommendTypeEnum.NO_RECOMMEND.getType());
        PaginationResultVO resultVO = videoInfoService.findListByPage(videoInfoQuery);
        return getSuccessResponseVO(resultVO);
    }

Service videoInfoService.findListByPage(videoInfoQuery);

     	@Override
        public PaginationResultVO<VideoInfo> findListByPage(VideoInfoQuery param) {
            int count = this.findCountByParam(param);
            int pageSize = param.getPageSize() == null ? PageSize.SIZE15.getSize() : param.getPageSize();
    
            SimplePage page = new SimplePage(param.getPageNo(), count, pageSize);
            param.setSimplePage(page);
            List<VideoInfo> list = this.findListByParam(param);
            PaginationResultVO<VideoInfo> result = new PaginationResultVO(count, page.getPageSize(), page.getPageNo(), page.getPageTotal(), list);
            return result;
        }

Service findListByParam(param);

@Override
    public List<VideoInfo> findListByParam(VideoInfoQuery param) {
        return this.videoInfoMapper.selectList(param);
    }

Mapper

<!-- 查询集合-->
    <select id="selectList" resultMap="base_result_map">
        SELECT
        <include refid="base_column_list"/>
        <if test="query.queryUserInfo">
            ,u.nick_name,u.avatar
        </if>
        FROM video_info v
        <if test="query.queryUserInfo">
            left join user_info u on u.user_id = v.user_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>

 <!--实体映射-->
    <resultMap id="base_result_map" type="com.easylive.entity.po.VideoInfo">
        <!--视频ID-->
        <result column="video_id" property="videoId"/>
        <!--视频封面-->
        <result column="video_cover" property="videoCover"/>
        <!--视频名称-->
        <result column="video_name" property="videoName"/>
        <!--用户ID-->
        <result column="user_id" property="userId"/>
        <!--创建时间-->
        <result column="create_time" property="createTime"/>
        <!--最后更新时间-->
        <result column="last_update_time" property="lastUpdateTime"/>
        <!--父级分类ID-->
        <result column="p_category_id" property="pCategoryId"/>
        <!--分类ID-->
        <result column="category_id" property="categoryId"/>
        <!--0:自制作  1:转载-->
        <result column="post_type" property="postType"/>
        <!--原资源说明-->
        <result column="origin_info" property="originInfo"/>
        <!--标签-->
        <result column="tags" property="tags"/>
        <!--简介-->
        <result column="introduction" property="introduction"/>
        <!--互动设置-->
        <result column="interaction" property="interaction"/>
        <!--持续时间（秒）-->
        <result column="duration" property="duration"/>
        <!--播放数量-->
        <result column="play_count" property="playCount"/>
        <!--点赞数量-->
        <result column="like_count" property="likeCount"/>
        <!--弹幕数量-->
        <result column="danmu_count" property="danmuCount"/>
        <!--评论数量-->
        <result column="comment_count" property="commentCount"/>
        <!--投币数量-->
        <result column="coin_count" property="coinCount"/>
        <!--收藏数量-->
        <result column="collect_count" property="collectCount"/>
        <!--是否推荐0:未推荐  1:已推荐-->
        <result column="recommend_type" property="recommendType"/>
        <!--最后播放时间-->
        <result column="last_play_time" property="lastPlayTime"/>
    </resultMap>

<!-- 通用查询结果列-->
    <sql id="base_column_list">
        v.video_id,v.video_cover,v.video_name,v.user_id,v.create_time,
        v.last_update_time,v.p_category_id,v.category_id,v.post_type,v.origin_info,
        v.tags,v.introduction,v.interaction,v.duration,v.play_count,
        v.like_count,v.danmu_count,v.comment_count,v.coin_count,v.collect_count,
        v.recommend_type,v.last_play_time
    </sql>

<!-- 通用查询条件列-->
    <sql id="query_condition">
        <where>
            <include refid="base_condition_filed"/>
            <if test="query.videoIdFuzzy!= null  and query.videoIdFuzzy!=''">
                and v.video_id like concat('%', #{query.videoIdFuzzy}, '%')
            </if>
            <if test="query.videoCoverFuzzy!= null  and query.videoCoverFuzzy!=''">
                and v.video_cover like concat('%', #{query.videoCoverFuzzy}, '%')
            </if>
            <if test="query.videoNameFuzzy!= null  and query.videoNameFuzzy!=''">
                and v.video_name like concat('%', #{query.videoNameFuzzy}, '%')
            </if>
            <if test="query.userIdFuzzy!= null  and query.userIdFuzzy!=''">
                and v.user_id like concat('%', #{query.userIdFuzzy}, '%')
            </if>
            <if test="query.createTimeStart!= null and query.createTimeStart!=''">
                <![CDATA[ and  v.create_time>=str_to_date(#{query.createTimeStart}, '%Y-%m-%d') ]]>
            </if>
            <if test="query.createTimeEnd!= null and query.createTimeEnd!=''">
                <![CDATA[ and  v.create_time< date_sub(str_to_date(#{query.createTimeEnd},'%Y-%m-%d'),interval -1 day) ]]>
            </if>
            <if test="query.lastUpdateTimeStart!= null and query.lastUpdateTimeStart!=''">
                <![CDATA[ and  v.last_update_time>=str_to_date(#{query.lastUpdateTimeStart}, '%Y-%m-%d') ]]>
            </if>
            <if test="query.lastUpdateTimeEnd!= null and query.lastUpdateTimeEnd!=''">
                <![CDATA[ and  v.last_update_time< date_sub(str_to_date(#{query.lastUpdateTimeEnd},'%Y-%m-%d'),interval -1 day) ]]>
            </if>
            <if test="query.originInfoFuzzy!= null  and query.originInfoFuzzy!=''">
                and v.origin_info like concat('%', #{query.originInfoFuzzy}, '%')
            </if>
            <if test="query.tagsFuzzy!= null  and query.tagsFuzzy!=''">
                and v.tags like concat('%', #{query.tagsFuzzy}, '%')
            </if>
            <if test="query.introductionFuzzy!= null  and query.introductionFuzzy!=''">
                and v.introduction like concat('%', #{query.introductionFuzzy}, '%')
            </if>
            <if test="query.interactionFuzzy!= null  and query.interactionFuzzy!=''">
                and v.interaction like concat('%', #{query.interactionFuzzy}, '%')
            </if>
            <if test="query.lastPlayTimeStart!= null and query.lastPlayTimeStart!=''">
                <![CDATA[ and  v.last_play_time>=str_to_date(#{query.lastPlayTimeStart}, '%Y-%m-%d') ]]>
            </if>
            <if test="query.lastPlayTimeEnd!= null and query.lastPlayTimeEnd!=''">
                <![CDATA[ and  v.last_play_time< date_sub(str_to_date(#{query.lastPlayTimeEnd},'%Y-%m-%d'),interval -1 day) ]]>
            </if>
            <!--补充的条件-->
            <if test="query.categoryIdOrPCategoryId!=null">
                and (category_id = #{query.categoryIdOrPCategoryId} or p_category_id = #{query.categoryIdOrPCategoryId})
            </if>
            <if test="query.videoIdArray!=null and query.videoIdArray.length>0">
                and video_id in(<foreach collection="query.videoIdArray" separator="," item="item">#{item}</foreach>)
            </if>
            <if test="query.excludeVideoIdArray!=null and query.excludeVideoIdArray.length>0">
                and video_id not in(<foreach collection="query.excludeVideoIdArray" separator="," item="item">#{item}</foreach>)
            </if>
            <if test="query.lastPlayHour!=null">
                <![CDATA[ and  v.last_play_time>=date_sub(now(), interval #{query.lastPlayHour} hour) ]]>
            </if>
        </where>
    </sql>

视频详情

Controller

	//获取视频详细信息
    @RequestMapping("/getVideoInfo")
    public ResponseVO getVideoInfo(@NotEmpty String videoId) {
        VideoInfo videoInfo = videoInfoService.getVideoInfoByVideoId(videoId);
        if(videoId==null){
            throw new BusinessException(ResponseCodeEnum.CODE_404);
        }

        //获取当前用户对应的点赞和投币信息
        TokenUserInfoDto userInfoDto = getTokenUserInfoDto();
        List<UserAction> userActionList = new ArrayList<>();
        if(userInfoDto!=null){
        	//用户行为查询
            UserActionQuery actionQuery = new UserActionQuery();
            actionQuery.setVideoId(videoId);
            actionQuery.setUserId(userInfoDto.getUserId());
            //查询视频对应用户的点赞投币收藏信息
            actionQuery.setActionTypeArray(new Integer[]{UserActionTypeEnum.VIDEO_LIKE.getType(),
                    UserActionTypeEnum.VIDEO_COLLECT.getType(),
                    UserActionTypeEnum.VIDEO_COIN.getType(),});
            userActionList = userActionService.findListByParam(actionQuery);
        }

        VideoInfoResultVo resultVo = new VideoInfoResultVo();
        //设置用户的点赞投币收藏信息
        resultVo.setUserActionList(userActionList);
        resultVo.setVideoInfo(CopyTools.copy(videoInfo, VideoInfoVo.class));
        return getSuccessResponseVO(resultVo);
    }