Java的新项目学成在线笔记-day6（十）

3.2.3 课程管理服务 3.2.3.1 Sql 课程计划是树型结构，采用表的自连接方式进行查询，sql语句如下：

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  FROM 
  teachplan a  
  LEFT JOIN teachplan b   
   ON a.id = b.parentid  
  LEFT JOIN teachplan c 
     ON b.id = c.parentid  WHERE a.parentid = '0'  
  AND a.courseid = '402885816243d2dd016243f24c030002'  ORDER BY a.orderby, 
 b.orderby,  
c.orderby

3.2.3.2 Dao 
1) mapper接口

@Mapper public interface TeachplanMapper {   
 public TeachplanNode selectList(String courseId); }

2）mapper映射文件

<resultMap type="com.xuecheng.framework.domain.course.ext.TeachplanNode" id="teachplanMap" >      <id property="id" column="one_id"/>   
  <result property="pname" column="one_name"/> 
     <collection property="children"

ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">     
    <id property="id" column="two_id"/>    
     <result property="pname" column="two_name"/>  
       <collection property="children"  ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">     
        <id property="id" column="three_id"/>    
         <result property="pname" column="three_name"/>   
      </collection>  
   </collection>
  </resultMap> 
  <select id="selectList" resultMap="teachplanMap" parameterType="java.lang.String" >   
  SELECT   
     a.id one_id,  
     a.pname one_name,  
     b.id two_id,     
  b.pname two_name,   
    c.id three_id,   
    c.pname three_name  
      FROM      
  teachplan a LEFT JOIN teachplan b    
      ON a.id = b.parentid  
      LEFT JOIN teachplan c       
   ON b.id = c.parentid     
 WHERE  a.parentid = '0'    
  <if test="_parameter!=null and _parameter!=''">   
      and a.courseid=#{courseId}  
    </if>   
    ORDER BY a.orderby,  
     b.orderby,    
   c.orderby </select>

说明：针对输入参数为简单类型#{}中可以是任意类型，判断参数是否为空要用 _parameter（它属于mybatis的内 置参数）

3.4.3.3 Service 
创建CourseService类，定义查询课程计划方法。

@Service public class CourseService {
    @Autowired  
   TeachplanMapper teachplanMapper;  
   //查询课程计划  
   public TeachplanNode findTeachplanList(String courseId){ 
       TeachplanNode teachplanNode = teachplanMapper.selectList(courseId);       
  return teachplanNode;    
 } }

3.4.3.4 Controller 

@RestController @RequestMapping("/course") public class CourseController implements CourseControllerApi { 
   @Autowired   
  CourseService courseService;  
     //查询课程计划  
   @Override   
  @GetMapping("/teachplan/list/{courseId}") 
   public TeachplanNode findTeachplanList(String courseId) {    
    return courseService.findTeachplanList(courseId); 
    } }

3.4.3.5 测试 使用postman或swagger-ui测试查询接口。 Get 请求：http://localhost:31200/course/teachplan/list/402885816243d2dd016243f24c030002