三表关联查询
视频详情
修改 domain
Video 中添加 chapterlist
1package net.xdclass.online_xdclass.domain;
2
3import java.util.Date;
4import java.util.List;
5
6/**
7 * 小滴课堂 视频对象
8 *
9 * `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
10 * `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
11 * `summary` varchar(1026) DEFAULT NULL COMMENT '概述',
12 * `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
13 * `price` int(11) DEFAULT NULL COMMENT '价格,分',
14 * `create_time` datetime DEFAULT NULL COMMENT '创建时间',
15 * `point` double(11,2) DEFAULT '8.70' COMMENT '默认8.7，最高10分',
16 */
17public class Video {
18
19
20 private Integer id;
21
22 private String title;
23
24
25 private String summary;
26
27
28 private String coverImg;
29
30 private Integer price;
31
32
33 private Date createTime;
34
35
36 private Double point;
37
38
39 private List<Chapter> chapterList;
40
41
42 public List<Chapter> getChapterList() {
43 return chapterList;
44 }
45
46 public void setChapterList(List<Chapter> chapterList) {
47 this.chapterList = chapterList;
48 }
49
50 public Integer getId() {
51 return id;
52 }
53
54 public void setId(Integer id) {
55 this.id = id;
56 }
57
58 public String getTitle() {
59 return title;
60 }
61
62 public void setTitle(String title) {
63 this.title = title;
64 }
65
66 public String getSummary() {
67 return summary;
68 }
69
70 public void setSummary(String summary) {
71 this.summary = summary;
72 }
73
74 public String getCoverImg() {
75 return coverImg;
76 }
77
78 public void setCoverImg(String coverImg) {
79 this.coverImg = coverImg;
80 }
81
82 public Integer getPrice() {
83 return price;
84 }
85
86 public void setPrice(Integer price) {
87 this.price = price;
88 }
89
90 public Date getCreateTime() {
91 return createTime;
92 }
93
94 public void setCreateTime(Date createTime) {
95 this.createTime = createTime;
96 }
97
98 public Double getPoint() {
99 return point;
100 }
101
102 public void setPoint(Double point) {
103 this.point = point;
104 }
105
106 @Override
107 public String toString() {
108 return "Video{" +
109 "id=" + id +
110 ", title='" + title + '\'' +
111 ", summary='" + summary + '\'' +
112 ", coverImg='" + coverImg + '\'' +
113 ", price=" + price +
114 ", createTime=" + createTime +
115 ", point=" + point +
116 '}';
117 }
118}
119
Chapter 中添加 episodelist
1package net.xdclass.online_xdclass.domain;
2
3import java.util.Date;
4import java.util.List;
5
6/**
7 * 小滴课堂 章对象
8 *
9 * `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
10 * `video_id` int(11) DEFAULT NULL COMMENT '视频主键',
11 * `title` varchar(128) DEFAULT NULL COMMENT '章节名称',
12 * `ordered` int(11) DEFAULT NULL COMMENT '章节顺序',
13 * `create_time` datetime DEFAULT NULL COMMENT '创建时间',
14 */
15public class Chapter {
16
17
18 private Integer id;
19
20 private Integer videoId;
21
22 private String title;
23
24 private Integer ordered;
25
26 private Date createTime;
27
28
29 private List<Episode> episodeList;
30
31
32 public List<Episode> getEpisodeList() {
33 return episodeList;
34 }
35
36 public void setEpisodeList(List<Episode> episodeList) {
37 this.episodeList = episodeList;
38 }
39
40 @Override
41 public String toString() {
42 return "Chapter{" +
43 "id=" + id +
44 ", videoId=" + videoId +
45 ", title='" + title + '\'' +
46 ", ordered=" + ordered +
47 ", createTime=" + createTime +
48 '}';
49 }
50
51 public Integer getId() {
52 return id;
53 }
54
55 public void setId(Integer id) {
56 this.id = id;
57 }
58
59 public Integer getVideoId() {
60 return videoId;
61 }
62
63 public void setVideoId(Integer videoId) {
64 this.videoId = videoId;
65 }
66
67 public String getTitle() {
68 return title;
69 }
70
71 public void setTitle(String title) {
72 this.title = title;
73 }
74
75 public Integer getOrdered() {
76 return ordered;
77 }
78
79 public void setOrdered(Integer ordered) {
80 this.ordered = ordered;
81 }
82
83 public Date getCreateTime() {
84 return createTime;
85 }
86
87 public void setCreateTime(Date createTime) {
88 this.createTime = createTime;
89 }
90}
91
VideoMapper
1package net.xdclass.online_xdclass.mapper;
2
3import net.xdclass.online_xdclass.domain.Video;
4import net.xdclass.online_xdclass.domain.VideoBanner;
5import org.apache.ibatis.annotations.Param;
6
7import java.util.List;
8
9public interface VideoMapper{
10
11 /**
12 * 查询视频列表
13 * @return
14 */
15 List<Video> listVideo();
16
17 /**
18 * 首页轮播图列表
19 * @return
20 */
21 List<VideoBanner> listVideoBanner();
22
23 /**
24 * 查询视频详情
25 * @param videoId
26 * @return
27 */
28 Video findDetailById(@Param("video_id") int videoId);
29}
30
VideoMapper.xml
1<?xml version="1.0" encoding="UTF-8" ?>
2<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
3<mapper namespace="net.xdclass.online_xdclass.mapper.VideoMapper">
4
5
6
7 <select id="listVideo" resultType="Video">
8
9 select * from video
10
11 </select>
12
13
14 <select id="listVideoBanner" resultType="VideoBanner">
15
16 select * from video_banner order by weight asc
17
18 </select>
19
20
21
22 <resultMap id="VideoDetailResultMap" type="Video">
23
24 <id column="id" jdbcType="INTEGER" property="id"/>
25
26 <result column="title" jdbcType="VARCHAR" property="title"/>
27 <result column="summary" jdbcType="VARCHAR" property="summary"/>
28 <result column="cover_img" jdbcType="VARCHAR" property="coverImg"/>
29 <result column="price" jdbcType="INTEGER" property="price"/>
30 <result column="point" jdbcType="DOUBLE" property="point"/>
31 <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
32
33
34 <collection property="chapterList" ofType="Chapter">
35 <id column="chapter_id" jdbcType="INTEGER" property="id"/>
36 <result column="chapter_title" jdbcType="VARCHAR" property="title"/>
37 <result column="ordered" jdbcType="INTEGER" property="ordered"/>
38 <result column="chapter_create_time" jdbcType="TIMESTAMP" property="createTime"/>
39
40 <collection property="episodeList" ofType="Episode">
41 <id column="episode_id" jdbcType="INTEGER" property="id"/>
42 <result column="num" jdbcType="INTEGER" property="num"/>
43 <result column="episode_title" jdbcType="VARCHAR" property="title"/>
44 <result column="episode_ordered" jdbcType="INTEGER" property="ordered"/>
45 <result column="play_url" jdbcType="VARCHAR" property="playUrl"/>
46 <result column="free" jdbcType="INTEGER" property="free"/>
47 <result column="episode_create_time" jdbcType="TIMESTAMP" property="createTime"/>
48 </collection>
49
50 </collection>
51
52 </resultMap>
53
54
55 <select id="findDetailById" resultMap="VideoDetailResultMap">
56
57 select
58 v.id, v.title,v.summary,v.cover_img,v.price,v.point,v.create_time,
59 c.id as chapter_id, c.title as chapter_title, c.ordered,c.create_time as chapter_create_time,
60 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
61
62 from video v
63
64 left join chapter c on v.id=c.video_id
65
66 left join episode e on c.id= e.chapter_id
67
68 where v.id = #{video_id}
69 order by c.ordered,e.num asc
70
71
72 </select>
73
74
75</mapper>
Service
1package net.xdclass.online_xdclass.service;
2
3import net.xdclass.online_xdclass.domain.Video;
4import net.xdclass.online_xdclass.domain.VideoBanner;
5
6import java.util.List;
7
8public interface VideoService {
9
10 List<Video> listVideo();
11
12 List<VideoBanner> listBanner();
13
14 Video findDetailById(int videoId);
15}
16
impl
1package net.xdclass.online_xdclass.service.impl;
2
3import net.xdclass.online_xdclass.domain.Video;
4import net.xdclass.online_xdclass.domain.VideoBanner;
5import net.xdclass.online_xdclass.mapper.VideoMapper;
6import net.xdclass.online_xdclass.service.VideoService;
7import org.springframework.beans.factory.annotation.Autowired;
8import org.springframework.stereotype.Service;
9
10import java.util.List;
11
12
13@Service
14public class VideoServiceImpl implements VideoService {
15
16 @Autowired
17 private VideoMapper videoMapper;
18
19
20
21 @Override
22 public List<Video> listVideo() {
23
24 return videoMapper.listVideo();
25 }
26
27
28
29 @Override
30 public List<VideoBanner> listBanner() {
31 return videoMapper.listVideoBanner();
32 }
33
34
35
36
37 @Override
38 public Video findDetailById(int videoId) {
39
40 // 需要使用mybaits关联复杂查询
41 Video video = videoMapper.findDetailById(videoId);
42
43 return video;
44 }
45}
46
Controller
1package net.xdclass.online_xdclass.controller;
2
3import net.xdclass.online_xdclass.domain.Video;
4import net.xdclass.online_xdclass.domain.VideoBanner;
5import net.xdclass.online_xdclass.service.VideoService;
6import net.xdclass.online_xdclass.utils.JsonData;
7import org.springframework.beans.factory.annotation.Autowired;
8import org.springframework.web.bind.annotation.GetMapping;
9import org.springframework.web.bind.annotation.RequestMapping;
10import org.springframework.web.bind.annotation.RequestParam;
11import org.springframework.web.bind.annotation.RestController;
12
13import java.util.List;
14
15@RestController
16@RequestMapping("api/v1/pub/video")
17public class VideoController {
18
19
20 @Autowired
21 private VideoService videoService;
22
23
24 /**
25 * 轮播图列表
26 * @return
27 */
28 @GetMapping("list_banner")
29 public JsonData indexBanner(){
30
31
32 List<VideoBanner> bannerList = videoService.listBanner();
33
34 return JsonData.buildSuccess(bannerList);
35
36 }
37
38
39 /**
40 * 视频列表
41 * @return
42 */
43 @RequestMapping("list")
44 public JsonData listVideo(){
45
46 List<Video> videoList = videoService.listVideo();
47 return JsonData.buildSuccess(videoList);
48 }
49
50
51 /**
52 * 查询视频详情，包含章，集信息
53 * @param videoId
54 * @return
55 */
56 @GetMapping("find_detail_by_id")
57 public JsonData findDetailById(@RequestParam(value = "video_id",required = true)int videoId){
58
59
60 Video video = videoService.findDetailById(videoId);
61
62 return JsonData.buildSuccess(video);
63
64 }
65
66
67
68
69
70
71}
72