SpringBoot+Mybatis三级查询

一、概述

　　现有一个需求：查询视频详情。对应三张表，视频表、章节列表、集列表。一个视频对应多个章节，一个章节对应多集视频。请根据视频id查询视频详情，并把视频的章节列表，章节中的集列表都带出来。

　　SpringBoot和MyBatis-plus说明：

 <!--根模块继承了SpringBoot，子模块也跟着继承了SpringBoot-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.14</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

   <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>

　　注意：我不晓得别人查询怎样，我这边用这个版本mapper.xml中collection嵌套会出现属性识别不出来的问题。我改用了多个resultMap嵌套查询

二、示例（红色地方标注红的是主要的地方）

<!--    根据视频的id获取视频的详情（章节+章节中的集）-->
    <sql id="getVideoDetailById_sql_id">
        v.id,v.title,v.summary,v.cover_img,v.price,v.point,
        c.id as cid,c.title as ctitle,c.video_id as c_video_id,
        e.id as eid,e.title as etitle,e.num,e.play_url,e.free,e.video_id as e_video_id
    </sql>
    <resultMap id="getVideoDetailByIdMap" type="com.tony.cursor.entity.Video">
        <id property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="summary" column="summary"/>
        <result property="coverImg" column="cover_img"/>
        <result property="price" column="price"/>
        <result property="point" column="point"/>
        <collection property="chapters" ofType="com.tony.cursor.entity.Chapter"
                    resultMap="getVideoDetailByIdMap_childMap"/>
    </resultMap>
    <resultMap id="getVideoDetailByIdMap_childMap" type="com.tony.cursor.entity.Chapter">
        <id property="id" column="cid"/>
        <result property="title" column="ctitle"/>
        <result property="videoId" column="c_video_id"/>
        <collection property="episodes" ofType="com.tony.cursor.entity.Episode">
            <id property="id" column="eid"/>
            <result property="title" column="etitle"/>
            <result property="num" column="num"/>
            <result property="playUrl" column="play_url"/>
            <result property="free" column="free"/>
            <result property="videoId" column="e_video_id"/>
        </collection>
    </resultMap>
    <select id="getVideoDetailById" resultMap="getVideoDetailByIdMap">
        select
        <include refid="getVideoDetailById_sql_id"/>
        from video v left join chapter c on v.id=c.video_id
        left join episode e on c.video_id=e.video_id where v.id=#{id}
    </select>