樹型表查詢方法 —

引言：

自鏈接查詢：?

?遞歸查詢：?

編寫service接口實現：

引言：

看下圖，這是 course_category 課程分類表的結構：

這張表是一個樹型結構，通過父結點id將各元素組成一個樹。

我們可以看下該表的數據，下圖是一部分數據：

現在的需求是需要在內容管理服務中編寫一個接口讀取該課程分類表的數據，組成一個樹型結構返回給前端。

樹形表的根本在于它有一個 parentid 字段，而這個字段是為了記錄當前結點的父節點的 id 值。所以每一個樹型表都會有一個這樣的字段。

通過查閱接口文檔，此接口要返回全部課程分類，以樹型結構返回，如下所示：

 [{"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"}]

而上邊的數據格式是一個數組結構，數組的元素即為分類信息，分類信息設計兩級分類，第一級的分類信息示例如下：

"id" : "1-2",
"isLeaf" : null,
"isShow" : null,
"label" : "移動開發",
"name" : "移動開發",
"orderby" : 2,
"parentid" : "1"

第二級的分類是第一級分類中childrenTreeNodes屬性，它是一個數組結構：

{
"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"}}

所以我們采用下面的model來定義這張表：

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* @version 1.0*/
// Serializable 網絡傳輸序列化
@Data
public class CourseCategoryTreeDto extends CourseCategory implements Serializable {List<CourseCategoryTreeDto> childrenTreeNodes;
}

接口定義如下：

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>*/
@Slf4j
@RestController
public class CourseCategoryController {@GetMapping("/course-category/tree-nodes")public List<CourseCategoryTreeDto> queryTreeNodes() {return null;}
}

自鏈接查詢：?

課程分類表是一個樹型結構，其中parentid字段為父結點ID，它是樹型結構的標志字段。

如果樹的層級固定可以使用表的自鏈接去查詢，比如：我們只查詢兩級課程分類，可以用下邊的SQL：

select * from course_category oneinner join course_category two on one.id = two.parentid

我們讓?course_category 表去 inner join?自己，之后起別名，one代表一級節點分類，two代表二級節點分類，所以通過查詢二級節點的父節點id = 一級節點id，這個就是查詢條件。

如果不想顯示根結點并且排序，參考下面代碼：?

selectone.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_labelfrom course_category oneinner join course_category two on one.id = two.parentidwhere one.parentid = 1and one.is_show = 1and two.is_show = 1order by one.orderby,two.orderby

而如果我們還想要通過指定節點來查詢另一張表的對應的數據，需要用到左右關聯。

如上兩張圖，左側圖是課程計劃（樹型表），每個課程計劃都有所屬課程。每個課程的課程計劃有兩個級別，第一級為大章節，grade 為1、第二級為小章節，grade 為2。第二級的 parentid 為第一級的 id。課程計劃的顯示順序根據排序字段去顯示。根據業務流程中的界面原型，課程計劃列表展示時還有課程計劃關聯的視頻信息。

課程計劃關聯的視頻信息在 teachplan_media 表，兩張表是一對一關系，每個課程計劃只能在teachplan_media表中存在一個視頻。

我們的需求是無論關聯不關聯都必須查出課程計劃（即使沒有課程視頻也要查出課程計劃），所以選擇使用左關聯（left join）。

selectone.id             one_id,one.pname          one_pname,one.parentid       one_parentid,one.grade          one_grade,one.media_type     one_mediaType,one.start_time     one_stratTime,one.end_time       one_endTime,one.orderby        one_orderby,one.course_id      one_courseId,one.course_pub_id  one_coursePubId,two.id             two_id,two.pname          two_pname,two.parentid       two_parentid,two.grade          two_grade,two.media_type     two_mediaType,two.start_time     two_stratTime,two.end_time       two_endTime,two.orderby        two_orderby,two.course_id      two_courseId,two.course_pub_id  two_coursePubId,m1.media_fileName mediaFilename,m1.id teachplanMeidaId,m1.media_id mediaIdfrom teachplan oneINNER JOIN teachplan two on one.id = two.parentidLEFT JOIN teachplan_media m1 on m1.teachplan_id = two.idwhere one.parentid = 0 and one.course_id=#{value}order by one.orderby,two.orderby

首先使用inner join自鏈接查詢樹型表，隨后使用左關聯根據課程視頻的teachplan_id = 課程計劃的id?來找到每個課程計劃（二級分類）對應的課程視頻。?

目前我們想要的數據是如上圖，而目前查詢到的數據如下圖：

因為字段名與屬性名不一致，所以需要定義 resultMap 手動映射。而且我們想要的數據格式內還有子節點，所以需要使用一對多映射（Collection），我們的數據模型如下圖：

@Data
@ToString
public class TeachplanDto extends Teachplan {//課程計劃關聯的媒資信息TeachplanMedia teachplanMedia;//子結點List<TeachplanDto> teachPlanTreeNodes;
}

所以?property 映射的屬性就是?teachPlanTreeNodes，而 ofType 的 List 中的對象類型就是 TeachplanDto。

<!-- 一級中包含多個二級數據 -->
<collection property="teachPlanTreeNodes" ofType="com.xuecheng.content.model.dto.TeachplanDto">

而在小章節內有一個視頻，所以用到一對一映射（Assoiation），其中 property映射的就是"teachplanMedia"屬性，也就是課程視頻；而 javaType的類型就是"com.xuecheng.content.model.po.TeachplanMedia"。

<association property="teachplanMedia" javaType="com.xuecheng.content.model.po.TeachplanMedia">

下面是對應的mapper.xml文件：（通過單次 SQL 關聯查詢 + 結果集映射，一次性獲取所有層級數據，減少數據庫交互。）

<!-- 課程分類樹型結構查詢映射結果 -->
<resultMap id="treeNodeResultMap" type="com.xuecheng.content.model.dto.TeachplanDto"><!-- 一級數據映射 --><id     column="one_id"        property="id" /><result column="one_pname"      property="pname" /><result column="one_parentid"     property="parentid" /><result column="one_grade"  property="grade" /><result column="one_mediaType"   property="mediaType" /><result column="one_stratTime"   property="stratTime" /><result column="one_endTime"   property="endTime" /><result column="one_orderby"   property="orderby" /><result column="one_courseId"   property="courseId" /><result column="one_coursePubId"   property="coursePubId" /><!-- 一級中包含多個二級數據 --><collection property="teachPlanTreeNodes" ofType="com.xuecheng.content.model.dto.TeachplanDto"><!-- 二級數據映射 --><id     column="two_id"        property="id" /><result column="two_pname"      property="pname" /><result column="two_parentid"     property="parentid" /><result column="two_grade"  property="grade" /><result column="two_mediaType"   property="mediaType" /><result column="two_stratTime"   property="stratTime" /><result column="two_endTime"   property="endTime" /><result column="two_orderby"   property="orderby" /><result column="two_courseId"   property="courseId" /><result column="two_coursePubId"   property="coursePubId" /><association property="teachplanMedia" javaType="com.xuecheng.content.model.po.TeachplanMedia"><result column="teachplanMeidaId"   property="id" /><result column="mediaFilename"   property="mediaFilename" /><result column="mediaId"   property="mediaId" /><result column="two_id"   property="teachplanId" /><result column="two_courseId"   property="courseId" /><result column="two_coursePubId"   property="coursePubId" /></association></collection>
</resultMap>
<!--課程計劃樹型結構查詢-->
<select id="selectTreeNodes" resultMap="treeNodeResultMap" parameterType="long" >selectone.id             one_id,one.pname          one_pname,one.parentid       one_parentid,one.grade          one_grade,one.media_type     one_mediaType,one.start_time     one_stratTime,one.end_time       one_endTime,one.orderby        one_orderby,one.course_id      one_courseId,one.course_pub_id  one_coursePubId,two.id             two_id,two.pname          two_pname,two.parentid       two_parentid,two.grade          two_grade,two.media_type     two_mediaType,two.start_time     two_stratTime,two.end_time       two_endTime,two.orderby        two_orderby,two.course_id      two_courseId,two.course_pub_id  two_coursePubId,m1.media_fileName mediaFilename,m1.id teachplanMeidaId,m1.media_id mediaIdfrom teachplan oneINNER JOIN teachplan two on one.id = two.parentidLEFT JOIN teachplan_media m1 on m1.teachplan_id = two.idwhere one.parentid = 0 and one.course_id=#{value}order by one.orderby,two.orderby
</select>

遞歸查詢：?

如果樹的層級不確定，此時可以使用MySQL遞歸實現，使用with語法，如下：
WITH [RECURSIVE]cte_name [(col_name [, col_name] ...)] AS (subquery)[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
cte_name :公共表達式的名稱,可以理解為表名,用來表示as后面跟著的子查詢

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

下邊是一個遞歸的簡單例子：

with RECURSIVE t1  AS
(SELECT 1 as nUNION ALLSELECT n + 1 FROM t1 WHERE n < 5
)
SELECT * FROM t1;

通過上述SQL，其中 t1 相當于一個表名，select 1 相當于這個表的初始值，這里使用UNION ALL 不斷將每次遞歸得到的數據加入到表中，而 n<5 為遞歸執行的條件，當 n>=5 時結束遞歸調用：

下邊我們使用遞歸實現課程分類的查詢：

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

最開始根結點id=1，然后通過 union all?不斷將每次遞歸得到的數據加入到 t1 表中（t1表存儲查完的數據），隨后向下遞歸，使用 inner join 去拿 course_category 表關聯 t1?表，來查詢當前 t1 表下的樹枝，所以查詢 t1.id =?course_category.parentid，因為 parentid 中記錄的是當前結點的父節點。

所以查詢完的數據最終會保存到 t1表并且使用 order by 按照順序輸出。

查詢結果如下：

t1表中初始的數據是id等于1的記錄，即根結點。

那如何向上遞歸？

下邊的sql實現了向上遞歸：

with recursive t1 as (
select * from  course_category p where  id= '1-1-1'
union allselect 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程序在遞歸操作中連接數據庫去查詢數據組裝數據，這個性能高嗎？

下邊我們可自定義mapper方法查詢課程分類，最終將查詢結果映射到List<CourseCategoryTreeDto>中。

生成課程分類表的mapper文件并拷貝至內容管理模塊的service工程中。

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

找到對應的mapper.xml文件，編寫sql語句：

<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 allselect t.* from course_category t inner join t1 on t1.id = t.parentid)select *  from t1 order by t1.id, t1.orderby</select>

編寫service接口實現：

由于現在我們查詢是直接將所有的數據查出，而沒有適應接口所返回的數據格式，所以我們要在Service層處理。

所以現在分為兩步進行：

使用剛剛定義的mapper接口查詢數據

將查詢到的數據封裝成 List<CourseCategoryTreeDto> 數據格式：?

@Data
public class CourseCategoryTreeDto extends CourseCategory implements Serializable {List<CourseCategoryTreeDto> childrenTreeNodes;
}

?下面我們講述如何將查詢到的數據封裝成 List<CourseCategoryTreeDto> 數據格式。

將list轉為map，key就是結點id，value就是CourseCategoryTreeDto對象，目的是為了方面從map獲取結點。
從頭遍歷List<CourseCategoryTreeDto>，將遍歷到的子節點放在父節點的childrenTreeNodes中。

首先List轉map可以使用stream流，并給key、value賦值，但是也會遇到兩個key重復，這個時候以第二個key為主，隨后在使用filter將根結點過濾（判斷查詢id與當前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<>();

隨后同樣使用stream流進行處理，如果當前節點的parentid=根結點的id，那么就將其放進?List<CourseCategoryTreeDto> categoryTreeDtos ，隨后在判斷當前結點是否有父節點（通過map的get(parentid)方法找），如果沒有父節點就new一個集合放入（因為要向該集合中放入其子節點），如果要是找到其父節點，那么就將該節點放入其父節點的集合中。

下面是完整代碼：

package com.xuecheng.content.service.impl;import com.xuecheng.content.mapper.CourseCategoryMapper;
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.stereotype.Service;import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;@Slf4j
@Service
public class CourseCategoryServiceImpl implements CourseCategoryService {@AutowiredCourseCategoryMapper 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));//最終返回的listList<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;}}