4 课程分类查询

本文介绍: mysql为了避免无限递归默认递归次数为1000，可以通过设置cte_max_recursion_depth参数增加递归深度，还可以通过max_execution_time限制执行时间，超过此时间也会终止递归操作。如果没有此po类则需要生成的此表的po类拷贝到内容管理模块的model工程中，将mapper拷贝到内容管理模块的service工程中。//下边开始往ChildrenTreeNodes属性中放子节点。如果树的层级固定可以使用表的自链接去查询，比如：我们只查询两级课程分类，可以用下边的SQL。

4 课程分类查询

4.1 需求分析

下边根据内容管理模块的业务流程，下一步要实现新增课程，在新增课程界面，有三处信息需要选择，如下图：

课程等级、课程类型来源于数据字典表，此部分的信息前端已从系统管理服务读取。

课程分类信息没有在数据字典表中存储，而是由单独一张课程分类表，存储在内容管理数据库中。

下边看下course_category课程分类表的结构

这张表是一个树型结构，通过父结点id将各元素组成一个树。

我们可以看下该表的数据，下图是一部分数据：

现在的需求是需要在内容管理服务中编写一个接口读取该课程分类表的数据，组成一个树型结构返回给前端。

课程分类的PO类如下：

如果没有此po类则需要生成的此表的po类拷贝到内容管理模块的model工程中，将mapper拷贝到内容管理模块的service工程中。

4.2 接口定义

我们可以点击新增课程，观察前端的请求记录：

http://localhost:8601/api/content/course-category/tree-nodes 该地址正是前端获取课程分类的接口地址。

通过上图界面的内容可以看出该接口的协议为：HTTP GET

请求参数为空。

通过查阅接口文档，此接口要返回全部课程分类，以树型结构返回，如下所示。

JSON
[
         {
            “childrenTreeNodes” : [
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-1”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “HTML/CSS”,
                  “name” : “HTML/CSS”,
                  “orderby” : 1,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-2”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “JavaScript”,
                  “name” : “JavaScript”,
                  “orderby” : 2,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-3”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “jQuery”,
                  “name” : “jQuery”,
                  “orderby” : 3,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-4”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “ExtJS”,
                  “name” : “ExtJS”,
                  “orderby” : 4,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-5”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “AngularJS”,
                  “name” : “AngularJS”,
                  “orderby” : 5,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-6”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “ReactJS”,
                  “name” : “ReactJS”,
                  “orderby” : 6,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-7”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “Bootstrap”,
                  “name” : “Bootstrap”,
                  “orderby” : 7,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-8”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “Node.js”,
                  “name” : “Node.js”,
                  “orderby” : 8,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-9”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “Vue”,
                  “name” : “Vue”,
                  “orderby” : 9,
                  “parentid” : “1-1”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-1-10”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “其它”,
                  “name” : “其它”,
                  “orderby” : 10,
                  “parentid” : “1-1”
               }
            ],
            “id” : “1-1”,
            “isLeaf” : null,
            “isShow” : null,
            “label” : “前端开发”,
            “name” : “前端开发”,
            “orderby” : 1,
            “parentid” : “1”
         },
         {
            “childrenTreeNodes” : [
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-1”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “微信开发”,
                  “name” : “微信开发”,
                  “orderby” : 1,
                  “parentid” : “1-2”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-2”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “iOS”,
                  “name” : “iOS”,
                  “orderby” : 2,
                  “parentid” : “1-2”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-3”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “手游开发”,
                  “name” : “手游开发”,
                  “orderby” : 3,
                  “parentid” : “1-2”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-4”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “Swift”,
                  “name” : “Swift”,
                  “orderby” : 4,
                  “parentid” : “1-2”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-5”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “Android”,
                  “name” : “Android”,
                  “orderby” : 5,
                  “parentid” : “1-2”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-6”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “ReactNative”,
                  “name” : “ReactNative”,
                  “orderby” : 6,
                  “parentid” : “1-2”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-7”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “Cordova”,
                  “name” : “Cordova”,
                  “orderby” : 7,
                  “parentid” : “1-2”
               },
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-8”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “其它”,
                  “name” : “其它”,
                  “orderby” : 8,
                  “parentid” : “1-2”
               }
            ],
            “id” : “1-2”,
            “isLeaf” : null,
            “isShow” : null,
            “label” : “移动开发”,
            “name” : “移动开发”,
            “orderby” : 2,
            “parentid” : “1”
         }
   ]

上边的数据格式是一个数组结构，数组的元素即为分类信息，分类信息设计两级分类，第一级的分类信息示例如下：

JSON
“id” : “1-2”,
“isLeaf” : null,
“isShow” : null,
“label” : “移动开发”,
“name” : “移动开发”,
“orderby” : 2,
“parentid” : “1”

第二级的分类是第一级分类中childrenTreeNodes属性，它是一个数组结构：

JSON
{
“id” : “1-2”,
“isLeaf” : null,
“isShow” : null,
“label” : “移动开发”,
“name” : “移动开发”,
“orderby” : 2,
“parentid” : “1”,
“childrenTreeNodes” : [
               {
                  “childrenTreeNodes” : null,
                  “id” : “1-2-1”,
                  “isLeaf” : null,
                  “isShow” : null,
                  “label” : “微信开发”,
                  “name” : “微信开发”,
                  “orderby” : 1,
                  “parentid” : “1-2”
               }
}

所以，定义一个DTO类表示分类信息的模型类，如下：

Java
package com.xuecheng.content.model.dto;

import com.xuecheng.content.model.po.CourseCategory;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
* @description 课程分类树型结点dto
* @author Mr.M
* @date 2022/9/7 15:16
* @version 1.0
*/
@Data
public class CourseCategoryTreeDto extends CourseCategory implements Serializable {

List<CourseCategoryTreeDto> childrenTreeNodes;
}

接口定义如下：

Java
package com.xuecheng.content.api;

import com.xuecheng.content.model.dto.CourseCategoryTreeDto;
import com.xuecheng.content.service.CourseCategoryService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
* <p>
* 数据字典前端控制器
* </p>
*
* @author itcast
*/
@Slf4j
@RestController
public class CourseCategoryController {

    @GetMapping(“/course-category/tree-nodes”)
    public List<CourseCategoryTreeDto> queryTreeNodes() {
       return null;
    }
}

4.3 接口开发

4.3.1 树型表查询

课程分类表是一个树型结构，其中parentid字段为父结点ID，它是树型结构的标志字段。

如果树的层级固定可以使用表的自链接去查询，比如：我们只查询两级课程分类，可以用下边的SQL

Java
select
       one.id            one_id,
       one.name          one_name,
       one.parentid      one_parentid,
       one.orderby       one_orderby,
       one.label         one_label,
       two.id            two_id,
       two.name          two_name,
       two.parentid      two_parentid,
       two.orderby       two_orderby,
       two.label         two_label
   from course_category one
            inner join course_category two on one.id = two.parentid
   where one.parentid = 1
     and one.is_show = 1
     and two.is_show = 1
   order by one.orderby,
            two.orderby

如果树的层级不确定，此时可以使用MySQL递归实现，使用with语法，如下：

Java
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] …)] AS (subquery)
        [, cte_name [(col_name [, col_name] …)] AS (subquery)] …

cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询

col_name :公共表达式包含的列名,可以写也可以不写

下边是一个递归的简单例子：

Java
with RECURSIVE t1 AS
(
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM t1 WHERE n < 5
)
SELECT * FROM t1;

输出：

说明：

t1 相当于一个表名

select 1 相当于这个表的初始值，这里使用UNION ALL 将初始值加入到表中。

n<5为递归执行的条件，当n>=5时结束递归调用。

下边我们使用递归实现课程分类的查询

Java
with recursive t1 as (
select * from course_category p where id= ‘1’
union all
select t.* from course_category t inner join t1 on t1.id = t.parentid
)
select * from t1 order by t1.id, t1.orderby

查询结果如下：

t1表中初始的数据是id等于1的记录，即根结点。

通过inner join t1 t2 on t2.id = t.parentid 找到id=’1’的下级节点。

通过这种方法就找到了id=’1’的所有下级节点，下级节点包括了所有层级的节点。

上边这种方法是向下递归，即找到初始节点的所有下级节点。

如何向上递归？

下边的sql实现了向上递归：

Java
with recursive t1 as (
select * from course_category p where id= ‘1-1-1’
union all
select t.* from course_category t inner join t1 on t1.parentid = t.id
)
select * from t1 order by t1.id, t1.orderby

初始节点为1-1-1，通过递归找到它的父级节点，父级节点包括所有级别的节点。

以上是我们研究了树型表的查询方法，通过递归的方式查询课程分类比较灵活，因为它可以不限制层级。

mysql为了避免无限递归默认递归次数为1000，可以通过设置cte_max_recursion_depth参数增加递归深度，还可以通过max_execution_time限制执行时间，超过此时间也会终止递归操作。

mysql递归相当于在存储过程中执行若干次sql语句，java程序仅与数据库建立一次链接执行递归操作，所以只要控制好递归深度，控制好数据量性能就没有问题。

思考：如果java程序在递归操作中连接数据库去查询数据组装数据，这个性能高吗？

4.3.2 开发Mapper

下边我们可自定义mapper方法查询课程分类，最终将查询结果映射到List<CourseCategoryTreeDto>中。

生成课程分类表的mapper文件并拷贝至内容管理模块的service工程中。

1、下边定义一个mapper方法，并定义sql语句。

Java
public interface CourseCategoryMapper extends BaseMapper<CourseCategory> {
public List<CourseCategoryTreeDto> selectTreeNodes(String id);
}

2、找到对应的mapper.xml文件，编写sql语句。

Java
<select id=”selectTreeNodes” resultType=”com.xuecheng.content.model.dto.CourseCategoryTreeDto” parameterType=”string”>
    with recursive t1 as (
        select * from course_category p where id= #{id}
        union all
        select t.* from course_category t inner join t1 on t1.id = t.parentid
    )
    select * from t1 order by t1.id, t1.orderby

</select>

4.3.3 开发service

定义service接口，调用mapper查询课程分类，遍历数据按照接口要求对数据进行封装

Java
public interface CourseCategoryService {
    /**
     * 课程分类树形结构查询
     *
     * @return
     */
    public List<CourseCategoryTreeDto> queryTreeNodes(String id);
}

编写service接口实现

Java
@Slf4j
@Service
public class CourseCategoryServiceImpl implements CourseCategoryService {

@Autowired
CourseCategoryMapper courseCategoryMapper;

    public List<CourseCategoryTreeDto> queryTreeNodes(String id) {
       List<CourseCategoryTreeDto> courseCategoryTreeDtos = courseCategoryMapper.selectTreeNodes(id);
    //将list转map,以备使用,排除根节点
    Map<String, CourseCategoryTreeDto> mapTemp = courseCategoryTreeDtos.stream().filter(item->!id.equals(item.getId())).collect(Collectors.toMap(key -> key.getId(), value -> value, (key1, key2) -> key2));
    //最终返回的list
    List<CourseCategoryTreeDto> categoryTreeDtos = new ArrayList<>();
    //依次遍历每个元素,排除根节点
    courseCategoryTreeDtos.stream().filter(item->!id.equals(item.getId())).forEach(item->{
        if(item.getParentid().equals(id)){
            categoryTreeDtos.add(item);
        }
        //找到当前节点的父节点
        CourseCategoryTreeDto courseCategoryTreeDto = mapTemp.get(item.getParentid());
        if(courseCategoryTreeDto!=null){
            if(courseCategoryTreeDto.getChildrenTreeNodes() ==null){
                courseCategoryTreeDto.setChildrenTreeNodes(new ArrayList<CourseCategoryTreeDto>());
            }
            //下边开始往ChildrenTreeNodes属性中放子节点
            courseCategoryTreeDto.getChildrenTreeNodes().add(item);
        }
    });
    return categoryTreeDtos;
    }

}

4.3.4 单元测试

定义单元测试类对service接口进行测试

Java
@SpringBootTest
class CourseCategoryServiceTests {

@Autowired
CourseCategoryService courseCategoryService;

    @Test
    void testqueryTreeNodes() {
        List<CourseCategoryTreeDto> categoryTreeDtos = courseCategoryService.queryTreeNodes(“1”);
        System.out.println(categoryTreeDtos);
    }

}

4.4 接口测试

4.4.1 接口层代码完善

完善controller方法，注入service调用业务层方法查询课程分类。

Java
package com.xuecheng.content.controller;

import com.xuecheng.content.model.dto.CourseCategoryTreeDto;
import com.xuecheng.content.model.po.Dictionary;
import com.xuecheng.content.service.CourseCategoryService;
import com.xuecheng.content.service.DictionaryService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
* <p>
* 数据字典前端控制器
* </p>
*
* @author itcast
*/
@Slf4j
@RestController
public class CourseCategoryController {

@Autowired
CourseCategoryService courseCategoryService;

    @GetMapping(“/course-category/tree-nodes”)
    public List<CourseCategoryTreeDto> queryTreeNodes() {
       return courseCategoryService.queryTreeNodes(“1”);
    }
}