mybatis三表联查

视频课程详情包含三张表：

video： 视频对象
 
 `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分',

chapter： 章对象
 
    `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 '创建时间',

episode 集对象
 
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(524) DEFAULT NULL COMMENT '集标题',
    `num` int(10) DEFAULT NULL COMMENT '第几集,全局顺序',
    `ordered` int(11) DEFAULT NULL COMMENT '顺序，章里面的顺序',
    `play_url` varchar(256) DEFAULT NULL COMMENT '播放地址',
    `chapter_id` int(11) DEFAULT NULL COMMENT '章节主键id',
    `free` tinyint(2) DEFAULT '0' COMMENT '0表示免费，1表示首付',
    `video_id` int(10) DEFAULT NULL COMMENT '视频id',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',

• 三张表的关系是：视频对象需要包含章对象，一个视频对象对应多个章对象；章对象包含集对象，一个章对象可以对应多个集对象；
• 遵循一对多原则：在多端创建外键； chapter内设置外键video_id ‘视频主键’, episode
  内设置外键chapter_id ‘章节主键id’
• 然后根据外键使用LEFT JOIN进行多表查询：

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

from video v

LEFT JOIN chapter c on v.id=c.video_id

LEFT JOIN episode e on c.id=e.chapter_id

• Mybats中实现：
• 创建实体类：
  Video ：包含List<Chapter>章对象集合

public class Video {


    private Integer id;

    private String title;


    private String summary;


    @JsonProperty("cover_img")
    private String coverImg;

    private Integer  price;


    @JsonProperty("create_time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;


    private Double point;


    @JsonProperty("chapter_list")
    private List<Chapter> chapterList;

Chapter ：包含List<Episode>集对象集合

public class Chapter {


    private Integer id;

    @JsonProperty("video_id")
    private Integer videoId;

    private String title;

    private Integer ordered;

    @JsonProperty("create_time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;


    @JsonProperty("episode_list")
    private List<Episode> episodeList;

Episode ：集对象

public class Episode {

    private Integer id;

    private String title;

    private Integer num;

    private Integer ordered;


    @JsonProperty("play_url")
    private String playUrl;


    @JsonProperty("chapter_id")
    private Integer chapterId;


    private Integer free;

    @JsonProperty("video_id")
    private Integer videoId;

    @JsonProperty("create_time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

mapper.xml:需要进行字段映射（驼峰命名映射为数据库字段的下划线命名）

 <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>