简单记录一下在java的Mybatis-plus中用一个SQL语句查询一个嵌套的实体类(实体类中有List,List中还有List)

简单记录一下在java的Mybatis-plus中用一个SQL语句查询一个嵌套的实体类(实体类中有List,List中还有List)

也就是KpCourseInfoClientDetailVO课程详情类中含有List courseChapterList章节列表,然后章节列表中含有List lessonList课时列表,通过一个SQL查询得到结果

KpCourseInfoClientDetailVO类

@Data
public class KpCourseInfoClientDetailVO extends BaseEntity {

    /**
     * 主键ID
     */
    @ApiModelProperty(value = "主键ID",example = "1" , position = 1)
    private Long id;

    /**
     * 图片
     */
    @ApiModelProperty(value = "图片",example = "https://yq-0.obs.cn-east-3.myhuaweicloud.com/12351.bjzgh.org/image/20220824/1661320343049.png" , position = 5)
    private String pic;

    /**
     * 名称
     */
    @ApiModelProperty(value = "名称",example = "Java修仙手册" , position = 11)
    private String title;

    /**
     * 课时数量
     */
    @ApiModelProperty(value = "课时数量",example = "5" , position = 21)
    private Integer lessons;


    /**
     * 最近在学人数
     */
    @ApiModelProperty(value = "最近在学人数",example = "5" , position = 31)
    private Integer clicks;

    /**
     * 总学时
     */
    @ApiModelProperty(value = "总学时(秒)",example = "120" , position = 35)
    private Integer duration;

    /**
     * 已学时长
     */
    @ApiModelProperty(value = "已学时长(秒)",example = "60" , position = 37)
    private Integer studyDuration;

    /**
     * 章节列表
     */
    @ApiModelProperty(value = "章节列表", position = 71)
    private List<KpCourseChapterClientVO> courseChapterList;

}

KpCourseChapterClientVO类

@Data
public class KpCourseChapterClientVO extends BaseEntity {

    /**
     * 主键ID
     */
    @ApiModelProperty(value = "主键ID",example = "1" , position = 1)
    private Long id;

    /**
     * 名称
     */
    @ApiModelProperty(value = "名称",example = "优秀奖《老年人便秘护理健康宣教》" , position = 51)
    private String title;

    /**
     * 课时列表
     */
    @ApiModelProperty(value = "课时列表", position = 71)
    private List<KpLessonClientListVO> lessonList;


}

KpLessonClientListVO类

@Data
public class KpLessonClientListVO extends BaseEntity {

    /**
     * 主键ID
     */
    @ApiModelProperty(value = "主键ID",example = "1" , position = 1)
    private Long id;

    /**
     * 名称
     */
    @ApiModelProperty(value = "名称",example = "走进故宫——下雪天的故宫是什么样的" , position = 31)
    private String title;

    /**
     * 视频时长，单位：秒
     */
    @ApiModelProperty(value = "视频时长，单位：秒",example = "120" , position = 111)
    private Double duration;

}

首先在Navicat中验证SQL的准确性

#查看前端课程详情
SELECT
	kci.id,
	kci.pic,
	kci.title,
	kci.lessons,
	kci.clicks,
	kci.duration,
	kcc.id chapterId,
	kcc.title chapterName,
	kl.id lessonId,
	kl.title lessonName,
	kl.duration lessonDuration 
FROM
	kp_course_info kci
	LEFT JOIN kp_course_chapter kcc ON kcc.course_id = kci.id
	LEFT JOIN kp_lesson kl ON kl.chapter_id = kcc.id 
WHERE
	kci.id = 11567 
	AND kci.data_status = 0 
	AND kcc.data_status = 0 
	AND kl.data_status = 0 
	AND kl.available_flag = 1 
	AND kl.review_status = 5
ORDER BY kcc.id DESC

得到下列结果,说明SQL没有问题

然后在KpCourseInfoMapper.xml中实现

<resultMap id="getCourseInfoDetail" type="com.chengyun.beijing.forms.exam.domain.vo.KpCourseInfoClientDetailVO">
    <id property="id" column="id"></id>
    <result property="pic" column="pic"></result>
    <result property="title" column="title"></result>
    <result property="lessons" column="lessons"></result>
    <result property="clicks" column="clicks"></result>
    <result property="duration" column="duration"></result>
    <collection property="courseChapterList" ofType="com.chengyun.beijing.forms.exam.domain.vo.KpCourseChapterClientVO" column="chapterId">
        <result property="id" column="chapterId"></result>
        <result property="title" column="chapterName"></result>
        <association property="lessonList" javaType="java.util.List" resultMap="lessonMap"/>
    </collection>
</resultMap>

<resultMap id="lessonMap" type="com.chengyun.beijing.forms.exam.domain.vo.KpLessonClientListVO">
    <result property="id" column="lessonId"></result>
    <result property="title" column="lessonName"></result>
    <result property="duration" column="lessonDuration"></result>
</resultMap>

<select id="getCourseInfoDetail" resultMap="getCourseInfoDetail">
    SELECT
        kci.id,
        kci.pic,
        kci.title,
        kci.lessons,
        kci.clicks,
        kci.duration,
        kcc.id chapterId,
        kcc.title chapterName,
        kl.id lessonId,
        kl.title lessonName,
        kl.duration lessonDuration
    FROM
        kp_course_info kci
            LEFT JOIN kp_course_chapter kcc ON kcc.course_id = kci.id
            LEFT JOIN kp_lesson kl ON kl.chapter_id = kcc.id
    ${ew.customSqlSegment}
</select>

因为是在实体类中List嵌套List,所以外部的List直接用collection 标签来写,然后再给其内部嵌套的List标签写个resultMap 来对应参数,最后通过接口调用就能拿到想要的参数结构了