collection、ofType、select的联合用法（Mybatis实现树状结构查询）

需求

得到树结构数据也可以用lambda表达式也行，也可以直接循环递归也行，本文采用的是直接在Mybatis层得到结果，各有各的优势。

代码

1、实体类

@Data
public class CourseChapterVO implements Serializable {private static final long serialVersionUID = -67718827554034368L;/*** 主键ID*/private Integer id;/*** 课程ID*/private Integer courseId;/*** 课程章节名称*/private String chapterName;/*** 父ID*/private Integer chapterParent;/*** 层级*/private Integer chapterLevel;@JsonInclude(JsonInclude.Include.NON_EMPTY)  //当children为空时不返回    private List<CourseChapterVO> children;}

2、mapper.xml

<resultMap type="com.ruoyi.manager.vo.CourseChapterVO" id="CourseChapterMap2"><result property="id" column="id" jdbcType="INTEGER"/><result property="courseId" column="course_id" jdbcType="INTEGER"/><result property="chapterName" column="chapter_name" jdbcType="VARCHAR"/><result property="chapterParent" column="chapter_parent" jdbcType="INTEGER"/><result property="chapterLevel" column="chapter_level" jdbcType="INTEGER"/><collection property="children" ofType="com.ruoyi.manager.vo.CourseChapterVO" column="id" select="selectListTree2"/></resultMap><select id="selectListTree1" resultMap="CourseChapterMap2">select id , course_id, chapter_name, chapter_parent, chapter_levelfrom course_chapter where chapter_parent =0 </select><select id="selectListTree2" resultMap="CourseChapterMap2">select id , course_id, chapter_name, chapter_parent, chapter_level from course_chapter where chapter_parent =#{id}</select>

3、mapper.java

List<CourseChapterVO> selectListTree1(CourseChapter courseChapter);

4、serviceImpl.java

 @Overridepublic List<CourseChapterVO> queryAll(CourseChapter courseChapter) {return this.courseChapterMapper.selectListTree1(courseChapter);}

5、service.java

List<CourseChapterVO> queryAll(CourseChapter courseChapter);

6、controller.java

 @GetMapping("list")public R queryAll(CourseChapter courseChapter) {return R.data(this.courseChapterService.queryAll(courseChapter));}

7、实现效果

{"msg": "","code": 0,"data": [{"id": 1,"courseId": 5,"chapterName": "第一章节学习","chapterParent": 0,"chapterLevel": 1,"children": [{"id": 2,"courseId": 5,"chapterName": "第一章节学习子1","chapterParent": 1,"chapterLevel": 2},{"id": 3,"courseId": 5,"chapterName": "第一章节学习子2","chapterParent": 1,"chapterLevel": 2},{"id": 4,"courseId": 5,"chapterName": "第一章节学习子3","chapterParent": 1,"chapterLevel": 2}]},{"id": 5,"courseId": 5,"chapterName": "第二章节学习","chapterParent": 0,"chapterLevel": 1,"children": [{"id": 6,"courseId": 5,"chapterName": "第二章节学习子","chapterParent": 5,"chapterLevel": 2,"children": [{"id": 9,"courseId": 5,"chapterName": "第二章节学习子1子1","chapterParent": 6,"chapterLevel": 3}]},{"id": 7,"courseId": 5,"chapterName": "第二章节学习子1","chapterParent": 5,"chapterLevel": 2},{"id": 8,"courseId": 5,"chapterName": "第二章节学习子2","chapterParent": 5,"chapterLevel": 2}]},{"id": 10,"courseId": 5,"chapterName": "第三章节学习gfdgdf","chapterParent": 0,"chapterLevel": 1}]
}