三表关联查询
视频详情
修改domain
Video 中添加 chapterlist
package net.xdclass.online_xdclass.domain;
import java.util.Date;
import java.util.List;
/**
* 小滴课堂 视频对象
*
* `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
* `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
* `summary` varchar(1026) DEFAULT NULL COMMENT '概述',
* `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
* `price` int(11) DEFAULT NULL COMMENT '价格,分',
* `create_time` datetime DEFAULT NULL COMMENT '创建时间',
* `point` double(11,2) DEFAULT '8.70' COMMENT '默认8.7，最高10分',
*/
public class Video {
private Integer id;
private String title;
private String summary;
private String coverImg;
private Integer price;
private Date createTime;
private Double point;
private List<Chapter> chapterList;
public List<Chapter> getChapterList() {
return chapterList;
}
public void setChapterList(List<Chapter> chapterList) {
this.chapterList = chapterList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSummary() {
return summary;
}
public void setSummary(String summary) {
this.summary = summary;
}
public String getCoverImg() {
return coverImg;
}
public void setCoverImg(String coverImg) {
this.coverImg = coverImg;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Double getPoint() {
return point;
}
public void setPoint(Double point) {
this.point = point;
}
@Override
public String toString() {
return "Video{" +
"id=" + id +
", title='" + title + '\'' +
", summary='" + summary + '\'' +
", coverImg='" + coverImg + '\'' +
", price=" + price +
", createTime=" + createTime +
", point=" + point +
'}';
}
}
Chapter 中添加 episodelist
package net.xdclass.online_xdclass.domain;
import java.util.Date;
import java.util.List;
/**
* 小滴课堂 章对象
*
* `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
* `video_id` int(11) DEFAULT NULL COMMENT '视频主键',
* `title` varchar(128) DEFAULT NULL COMMENT '章节名称',
* `ordered` int(11) DEFAULT NULL COMMENT '章节顺序',
* `create_time` datetime DEFAULT NULL COMMENT '创建时间',
*/
public class Chapter {
private Integer id;
private Integer videoId;
private String title;
private Integer ordered;
private Date createTime;
private List<Episode> episodeList;
public List<Episode> getEpisodeList() {
return episodeList;
}
public void setEpisodeList(List<Episode> episodeList) {
this.episodeList = episodeList;
}
@Override
public String toString() {
return "Chapter{" +
"id=" + id +
", videoId=" + videoId +
", title='" + title + '\'' +
", ordered=" + ordered +
", createTime=" + createTime +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getVideoId() {
return videoId;
}
public void setVideoId(Integer videoId) {
this.videoId = videoId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
VideoMapper
package net.xdclass.online_xdclass.mapper;
import net.xdclass.online_xdclass.domain.Video;
import net.xdclass.online_xdclass.domain.VideoBanner;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface VideoMapper{
/**
* 查询视频列表
* @return
*/
List<Video> listVideo();
/**
* 首页轮播图列表
* @return
*/
List<VideoBanner> listVideoBanner();
/**
* 查询视频详情
* @param videoId
* @return
*/
Video findDetailById(@Param("video_id") int videoId);
}
VideoMapper.xml
<?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="net.xdclass.online_xdclass.mapper.VideoMapper">
<select id="listVideo" resultType="Video">
select * from video
</select>
<select id="listVideoBanner" resultType="VideoBanner">
select * from video_banner order by weight asc
</select>
<resultMap id="VideoDetailResultMap" type="Video">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="title" jdbcType="VARCHAR" property="title"/>
<result column="summary" jdbcType="VARCHAR" property="summary"/>
<result column="cover_img" jdbcType="VARCHAR" property="coverImg"/>
<result column="price" jdbcType="INTEGER" property="price"/>
<result column="point" jdbcType="DOUBLE" property="point"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<collection property="chapterList" ofType="Chapter">
<id column="chapter_id" jdbcType="INTEGER" property="id"/>
<result column="chapter_title" jdbcType="VARCHAR" property="title"/>
<result column="ordered" jdbcType="INTEGER" property="ordered"/>
<result column="chapter_create_time" jdbcType="TIMESTAMP" property="createTime"/>
<collection property="episodeList" ofType="Episode">
<id column="episode_id" jdbcType="INTEGER" property="id"/>
<result column="num" jdbcType="INTEGER" property="num"/>
<result column="episode_title" jdbcType="VARCHAR" property="title"/>
<result column="episode_ordered" jdbcType="INTEGER" property="ordered"/>
<result column="play_url" jdbcType="VARCHAR" property="playUrl"/>
<result column="free" jdbcType="INTEGER" property="free"/>
<result column="episode_create_time" jdbcType="TIMESTAMP" property="createTime"/>
</collection>
</collection>
</resultMap>
<select id="findDetailById" resultMap="VideoDetailResultMap">
select
v.id, v.title,v.summary,v.cover_img,v.price,v.point,v.create_time,
c.id as chapter_id, c.title as chapter_title, c.ordered,c.create_time as chapter_create_time,
e.id as episode_id,e.num, e.title as episode_title,e.ordered as episode_ordered,e.play_url,e.free,e.create_time as episode_create_time
from video v
left join chapter c on v.id=c.video_id
left join episode e on c.id= e.chapter_id
where v.id = #{video_id}
order by c.ordered,e.num asc
</select>
</mapper>
Service
package net.xdclass.online_xdclass.service;
import net.xdclass.online_xdclass.domain.Video;
import net.xdclass.online_xdclass.domain.VideoBanner;
import java.util.List;
public interface VideoService {
List<Video> listVideo();
List<VideoBanner> listBanner();
Video findDetailById(int videoId);
}
impl
package net.xdclass.online_xdclass.service.impl;
import net.xdclass.online_xdclass.domain.Video;
import net.xdclass.online_xdclass.domain.VideoBanner;
import net.xdclass.online_xdclass.mapper.VideoMapper;
import net.xdclass.online_xdclass.service.VideoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class VideoServiceImpl implements VideoService {
@Autowired
private VideoMapper videoMapper;
@Override
public List<Video> listVideo() {
return videoMapper.listVideo();
}
@Override
public List<VideoBanner> listBanner() {
return videoMapper.listVideoBanner();
}
@Override
public Video findDetailById(int videoId) {
// 需要使用mybaits关联复杂查询
Video video = videoMapper.findDetailById(videoId);
return video;
}
}
Controller
package net.xdclass.online_xdclass.controller;
import net.xdclass.online_xdclass.domain.Video;
import net.xdclass.online_xdclass.domain.VideoBanner;
import net.xdclass.online_xdclass.service.VideoService;
import net.xdclass.online_xdclass.utils.JsonData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("api/v1/pub/video")
public class VideoController {
@Autowired
private VideoService videoService;
/**
* 轮播图列表
* @return
*/
@GetMapping("list_banner")
public JsonData indexBanner(){
List<VideoBanner> bannerList = videoService.listBanner();
return JsonData.buildSuccess(bannerList);
}
/**
* 视频列表
* @return
*/
@RequestMapping("list")
public JsonData listVideo(){
List<Video> videoList = videoService.listVideo();
return JsonData.buildSuccess(videoList);
}
/**
* 查询视频详情，包含章，集信息
* @param videoId
* @return
*/
@GetMapping("find_detail_by_id")
public JsonData findDetailById(@RequestParam(value = "video_id",required = true)int videoId){
Video video = videoService.findDetailById(videoId);
return JsonData.buildSuccess(video);
}
}