学成在线,查询课程计划

本文主要是介绍学成在线,查询课程计划，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

查询课程计划

`界面原型`

当我们新增/修改完课程基本信息后,将自动进入课程计划编辑界面,课程计划分为大章节和小章节

在这里插入图片描述

`数据模型`

teach_plan表: 一个课程可以关联多个课程计划,课程计划分为章节(grade为1)和小节(grade为2)两种,节的parentid为章的Id,章的parentid为0

在这里插入图片描述

teachplan_media表: 一个课程计划小节只能绑定一个媒资文件,但不同的课程计划小节可以绑定同一个媒资文件

在这里插入图片描述

`响应数据模型类`

根据课程Id查询课程关联的所有课程计划

GET /teachplan/22/tree-nodes

定义查询课程计划接口的响应模型类,该类主要有章基本信息,章下的所有小节信息,小节关联的媒资信息三部分组成

@Data
public class TeachplanDto extends Teachplan {// 课程计划关联的媒资信息,章节没有关联的媒资信息private TeachplanMedia teachplanMedia;// 章节下的小节信息private List<TeachplanDto> teachPlanTreeNodes;
}

// 响应结果为一个数组,数组中的元素就是要查询的课程关联的章节
[{   // 章节的基本信息"changeDate": null,"courseId": 74,"cousePubId": null,"createDate": null,"endTime": null,"grade": "2","isPreview": "0","mediaType": null,"orderby": 1,"parentid": 0,"pname": "第1章基础知识","startTime": null,"status": null,"id": 113,// 章节下的所有小节信息"teachPlanTreeNodes": [{	// 小节的基本信息"changeDate": null,"courseId": 74,"cousePubId": null,"createDate": null,"endTime": null,"grade": "3","isPreview": "1","mediaType": "001002","orderby": 1,"parentid": 113,"pname": "第1节项目概述","startTime": null,"status": null,"id": 115,"teachPlanTreeNodes": null,// 小节会关联媒资信息,如视频文档等"teachplanMedia": {"courseId": 74,"coursePubId": null,"mediaFilename": "2.avi","mediaId": 41,"teachplanId": 115,"id": null}}],// 章节不关联媒资信息"teachplanMedia": null}, // 其他章节信息
]

`业务开发`

第一步: 编写接口根据课程Id查询课程关联的所有课程计划

@Slf4j
@RestController
@Api(value = "课程计划编辑接口", tags = "课程计划编辑接口")
public class TeachplanController {@Autowiredprivate TeachplanService teachplanService;@ApiOperation("查询课程计划树形结构")@GetMapping("/teachplan/{courseId}/tree-nodes")public List<TeachplanDto> getTreeNodes(@PathVariable Long courseId) {return teachplanService.findTeachplanTree(courseId);}
}

第二步: 编写Mapper接口及SQL映射文件,自定义SQL语句按照响应结果手动配置查询结果的映射,最后组成一个树形结构返回

如果章节下没有小节,此时也需要显示章节,所以通过teachplan表进行左连接,左边是一级分类,右边是二级分类
当课程计划还没有关联媒资文件时,teachplan_media表中对应的记录为空,所以需要teachplan和teachplan_media左连接

public interface TeachplanMapper extends BaseMapper<Teachplan> {// 根据课程Id去数据库中查询课程关联的课程计划List<TeachplanDto> selectTreeNodes(Long courseId);
}

<!-- 课程分类树形结构的结果映射,最终查询的结果要返回的是TeachplanDto类型 -->
<resultMap id="treeNodeResultMap" type="com.xuecheng.content.model.dto.TeachplanDto"><!-- 一级数据映射 --><id column="p_id" property="id"></id><result column="p_pname" property="pname"></result><result column="p_parentid" property="parentid"></result><result column="p_grade" property="grade"></result><result column="p_mediaType" property="mediaType"></result><result column="p_startTime" property="startTime"/><result column="p_endTime" property="endTime"></result><result column="p_orderby" property="orderby"></result><result column="p_courseId" property="courseId"></result><result column="p_coursePubId" property="coursePubId"></result><!-- 一级数据下可能包含多个二级数据 --><collection property="teachPlanTreeNodes" ofType="com.xuecheng.content.model.dto.TeachplanDto"><!--二级数据映射--><id column="c_id" property="id"></id><result column="c_pname" property="pname"></result><result column="c_parentid" property="parentid"></result><result column="c_grade" property="grade"></result><result column="c_mediaType" property="mediaType"></result><result column="c_startTime" property="startTime"/><result column="c_endTime" property="endTime"></result><result column="c_orderby" property="orderby"></result><result column="c_courseId" property="courseId"></result><result column="c_coursePubId" property="coursePubId"></result><!-- 媒资信息映射 --><association property="teachplanMedia" javaType="com.xuecheng.content.model.po.TeachplanMedia"><result column="teachplanMeidaId" property="id"></result><result column="mediaFilename" property="mediaFilename"></result><result column="mediaId" property="mediaId"></result><result column="c_id" property="teachplanId" /><result column="c_courseId" property="courseId" /><result column="c_coursePubId" property="coursePubId" /></association></collection>
</resultMap>
<select id="selectTreeNodes" resultMap="treeNodeResultMap">SELECT p.id              p_id,p.pname           p_pname,p.parentid        p_parentid,p.grade           p_grade,p.media_type      p_mediaType,p.start_time      p_stratTime,p.end_time        p_endTime,p.orderby         p_orderby,p.course_id       p_courseId,p.course_pub_id   p_coursePubId,c.id              c_id,c.pname           c_pname,c.parentid        c_parentid,c.grade           c_grade,c.media_type      c_mediaType,c.start_time      c_stratTime,c.end_time        c_endTime,c.orderby         c_orderby,c.course_id       c_courseId,c.course_pub_id   c_coursePubId,tm.media_fileName mediaFilename,tm.id             teachplanMeidaId,tm.media_id       mediaIdFROM teachplan pLEFT JOIN teachplan c ON c.parentid = p.idLEFT JOIN teachplan_media tm ON tm.teachplan_id = c.idWHERE p.parentid = '0' AND p.course_id = #{value}ORDER BY p.orderby, c.orderby
</select>

第三步: 编写Service接口及其实现类

public interface TeachplanService {List<TeachplanDto> findTeachplanTree(Long courseId);
}

@Slf4j
@Service
public class TeachplanServiceImpl implements TeachplanService {@Autowiredprivate TeachplanMapper teachplanMapper;@Overridepublic List<TeachplanDto> findTeachplanTree(Long courseId) {return teachplanMapper.selectTreeNodes(courseId);}
}