1 maven结构

2 树查询自连接

2-1 表结构

-- ----------------------------
-- Table structure for teachplan
-- ----------------------------
DROP TABLE IF EXISTS `teachplan`;
CREATE TABLE `teachplan` (
  `id` varchar(32) NOT NULL,
  `pname` varchar(64) NOT NULL,
  `parentid` varchar(32) NOT NULL,
  `grade` char(1) NOT NULL COMMENT '层级，分为1、2、3级',
  `ptype` char(1) DEFAULT NULL COMMENT '课程类型:1视频、2文档',
  `description` varchar(500) DEFAULT NULL COMMENT '章节及课程时介绍',
  `timelength` double(5,2) DEFAULT NULL COMMENT '时长，单位分钟',
  `courseid` varchar(32) DEFAULT NULL COMMENT '课程id',
  `orderby` varchar(32) DEFAULT NULL COMMENT '排序字段',
  `status` char(1) NOT NULL COMMENT '状态：未发布、已发布',
  `trylearn` char(1) DEFAULT NULL COMMENT '是否试学',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2-1 sql查询

<select id="selectList" parameterType="java.lang.String"
        resultMap="teachplanMap">
    SELECT
      a.id one_id,
      a.pname one_pname,
      b.id two_id,
      b.pname two_pname,
      c.id three_id,
      c.pname three_pname,
      teachplan_media.media_id,
      teachplan_media.media_fileoriginalname 
    FROM
      teachplan a
      LEFT JOIN teachplan b
        ON b.parentid = a.id
      LEFT JOIN teachplan c
        ON c.parentid = b.id
      LEFT JOIN teachplan_media 
      ON c.id = teachplan_media.teachplan_id
    WHERE a.parentid = '0'
     <if test="_parameter!=null and _parameter != ''">
         AND a.courseid = #{courseId}
     </if>

    ORDER BY a.orderby,
      b.orderby,
      c.orderby

</select>

2-3 查询结果

2-4 resultMap封装

<resultMap id="teachplanMap" type="com.xuecheng.framework.domain.course.ext.TeachplanNode">
     <id column="one_id" property="id"></id>
     <result column="one_pname" property="pname"></result>
     <collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">
           <id column="two_id" property="id"></id>
           <result column="two_pname" property="pname"></result>
           <collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">
               <id column="three_id" property="id"></id>
               <result column="three_pname" property="pname"></result>
               <result column="media_id" property="mediaId"></result>
               <result column="media_fileoriginalname" property="mediaFileoriginalname"></result>
           </collection>
     </collection>
</resultMap>