0
0

MySQL树状结构表查询通解

文章目录

前言

​ 最近做了一个中医药方面的项目，该项目分为游戏端和服务端。笔者负责的是服务端的开发。在服务端的业务中包含两部分：系统信息管理模块、游戏端服务提供模块。由于中医药存在很多树状结构信息，因此在设计数据表时为了减少冗余度，就将很多数据表设计为了树状结构。树状结构的表能够更加有效的将数据进行管理，但在某些业务中存在查询某个节点所有子节点后父节点的需求，进而造成了查询效率低下。并且对于不同数据表而言，其字段均不相同，代码并不能复用。使得在开发过程中，存在大量重复性工作。笔者想，既然数据表都存在树状结构是否能写一个东西，能够对所有树状结构的数据表都能适用，并提高其查询效率。经过构思找到了一个实现思路。

​ 在很多项目中都存在树状结构的数据库，以表示数据间的关联和层次关系。这种数据库能够高效的描述数据间的关系，而且可以无限延申树状结构的深度。该树状结构的思想能够在数据库层面高效的解决数据存储问题，但在业务处理层面并不能高效的解决节点间父子节点的关系。因此，产生了数据库树状查询问题。对于不同的数据库有不同的解决方式：

  • Oracle数据库中存在树查询语句，可以直接查询出某个节点的所有父节点或子节点。
  • MySQL数据库中并没有Oracle数据库中的树查询语句，其解决思路大致可分为两种：- 方法一：将数据库中所有的数据一次性全部查询出来，而后在代码层面获取节点的父、子节点- 方法二：在代码层面以某个节点的唯一标识在代码层面进行递归，每次递归从数据库中查询树父、子节点> 在MySQL中方法二由于与数据库进行了多次交互，而与数据库的交互所花费的时间要远远大于代码层面所花费的时间，方法一与数据库只进行一次交互，因此该方法效率比方法二高。

​ 笔者基于方法一对该方法提高了该方法的复用性。方法一在解决树状查询时，通常是仅仅针对特定的一张表而言的，无法通用的解决MySQL树状查询问题，本方法在其基础上通用的解决了MySQL树状查询问题。即是凡是存在树状结构的数据表均可使用本方式进行树状查询。

一、数据准备

CREATEtable arborescence (
id intPRIMARYKEY,
parent_id int,
content VARCHAR(200));INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(1,NULL,'节点1');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(2,1,'节点2');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(3,1,'节点3');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(4,2,'节点4');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(5,2,'节点5');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(6,2,'节点6');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(7,3,'节点7');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(8,3,'节点8');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(9,3,'节点9');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(10,4,'节点10');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(11,6,'节点11');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(12,7,'节点12');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(13,8,'节点13');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(14,8,'节点14');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(15,9,'节点15');INSERTINTO`arborescence`(`id`,`parent_id`,`content`)VALUES(16,15,'节点16');

在该数据表中

id

为每个数据记录的唯一标识，

parent_id

为每个数据记录的父级记录。

以上数据的树状结构关系如图：

在这里插入图片描述

二、代码实现

在本方法中主要由两个类实现。

publicclassTree<T>{T node;//当前节点List<Tree<T>> nextNode;//子节点publicTree(T node,List<Tree<T>> nextNode){//有参构造this.node = node;this.nextNode = nextNode;}publicTree(){//无参构造}publicTree(T node){this.node = node;this.nextNode=newArrayList<>();}publicTgetNode(){return node;}publicvoidsetNode(T node){this.node = node;}publicList<Tree<T>>getNextNode(){return nextNode;}publicvoidsetNextNode(List<Tree<T>> nextNode){this.nextNode = nextNode;}}

​ Tree<T>类与常用的树状节点功能相同，记录当前节点及其直接子节点。主要关键点是使用到了泛型，从而保证了本方法的通用性。

publicclassTreeUtil<T>{publicList<T> sonList,fatherList;//所有子节点、所有父节点publicTree<T> tree;//树publicList<T>getSonList(){return sonList;}publicvoidsetSonList(List<T> sonList){this.sonList = sonList;}publicList<T>getFatherList(){return fatherList;}publicvoidsetFatherList(List<T> fatherList){this.fatherList = fatherList;}publicTree<T>getTree(){return tree;}publicvoidsetTree(Tree<T> tree){this.tree = tree;}publicTreeUtil(){this.sonList =newArrayList<>();this.fatherList=newArrayList<>();this.tree=newTree<>();}/**
     * 根据制定字段建立树和对应后代节点集合
     * @param list 所有节点
     * @param head 对象类型
     * @param fatherFiled 父节点字段
     * @param sonFiled 当前节点字段
     * @param start 起始字段的值
     */publicvoidbuildListAndTree(List<T> list,Class head,String fatherFiled,String sonFiled,String start){//根据某个节点建树和集合Map<String,T> sonMap=newHashMap<>();//以自身唯一标识建mapMap<String,List<T>> fatherMap=newHashMap<>();//以父节点唯一标识建mapfor(T temp:list){try{Field field1 = head.getDeclaredField(sonFiled);//自身唯一标识
                field1.setAccessible(true);Object o1 = field1.get(temp);
                sonMap.put(o1.toString(),temp);Field field2 = head.getDeclaredField(fatherFiled);//父节点唯一标识
                field2.setAccessible(true);Object o2 = field2.get(temp);if(o2==null)continue;//该节点为根节点不存在父节点if(fatherMap.containsKey(o2.toString())){//已经含有该父节点List<T> ts = fatherMap.get(o2.toString());
                    ts.add(temp);}else{//不含该父节点List<T> tempList=newArrayList<>();
                    tempList.add(temp);
                    fatherMap.put(o2.toString(),tempList);}}catch(Exception e){
                e.printStackTrace();}}if(sonMap.containsKey(start)){T startNode = sonMap.get(start);//起始节点this.sonList =buildSonList(fatherMap,startNode,sonFiled,head);//建子节点this.fatherList=buildFatherList(sonMap,startNode,head,fatherFiled);//建父节点this.tree=buildTree(fatherMap,startNode,head,sonFiled);//建树}}/**
     * 根据指定节点获得该节点的所有子节点
     * @param fatherMap 父节点唯一标识
     * @param startNode 起始节点
     * @param sonFiled 当前节点唯一标识属性
     * @param head 类
     * @return
     */privateList<T>buildSonList(Map<String,List<T>> fatherMap,T startNode,String sonFiled,Class head){//建集合List<T> result=newArrayList<>();Queue<T> queue=newLinkedList<>();
        queue.add(startNode);//队列while(!queue.isEmpty()){T curNode = queue.poll();try{Field field = head.getDeclaredField(sonFiled);
                field.setAccessible(true);Object o = field.get(curNode);if(fatherMap.containsKey(o.toString())){List<T> sons = fatherMap.get(o.toString());//当前节点所有子节点
                    queue.addAll(sons);}
                result.add(curNode);}catch(Exception e){
                e.printStackTrace();}}return result;}/**
     * 根据起始节点获得所有父节点
     * @param sonMap 节点集
     * @param startNode 起始节点
     * @param head 类
     * @param fatherFiled 父节点唯一标识属性
     * @return
     */privateList<T>buildFatherList(Map<String,T> sonMap,T startNode,Class head,String fatherFiled){List<T> result=newArrayList<>();try{Field field = head.getDeclaredField(fatherFiled);
            field.setAccessible(true);while(field.get(startNode)!=null){
                result.add(startNode);
                startNode=sonMap.get(field.get(startNode));}
            result.add(startNode);}catch(Exception e){
            e.printStackTrace();}Collections.reverse(result);return result;}/**
     * 根据起始节点复现树状结构
     * @param fatherMap 父节点集
     * @param startNode 起始节点
     * @param head 类
     * @param sonFiled 当前节点唯一标识属性
     * @return
     */privateTree<T>buildTree(Map<String,List<T>> fatherMap,T startNode,Class head,String sonFiled){//建树try{Field field = head.getDeclaredField(sonFiled);//当前节点唯一标识
            field.setAccessible(true);Object o = field.get(startNode);if(fatherMap.containsKey(o.toString())){//存在子节点List<T> sons = fatherMap.get(o.toString());List<Tree<T>> treeSon=newArrayList<>();for(T son:sons){Tree<T> temp =buildTree(fatherMap, son, head, sonFiled);
                    treeSon.add(temp);}Tree<T> root=newTree<>(startNode,treeSon);return root;}else{//不存在子节点returnnewTree<T>(startNode,null);}}catch(Exception e){
            e.printStackTrace();}returnnull;}}

​ TreeUtil<T>类是本方法功能的主要实现类，在使用时只需调用buildListAndTree()方法，需传入5个参数。

  • 第一个参数：数据表中所有有效节点数据
  • 第二个参数：数据表实体类的Class类
  • 第三个参数：实体类中表示父节点字段的属性名（即是上面数据表中parent_id、后续实体类中parentId属性）
  • 第四个参数：实体类中表示当前节点唯一标识字段的属性名（即是上面数据表中id、后续实体类中id属性）
  • 第五个参数：起始节点的唯一标识（即是起始节点的id值）
publicvoidbuildListAndTree(List<T> list,Class head,String fatherFiled,String sonFiled,String start){//根据某个节点建树和集合Map<String,T> sonMap=newHashMap<>();//以自身唯一标识建mapMap<String,List<T>> fatherMap=newHashMap<>();//以父节点唯一标识建mapfor(T temp:list){try{Field field1 = head.getDeclaredField(sonFiled);//自身唯一标识
                field1.setAccessible(true);Object o1 = field1.get(temp);
                sonMap.put(o1.toString(),temp);Field field2 = head.getDeclaredField(fatherFiled);//父节点唯一标识
                field2.setAccessible(true);Object o2 = field2.get(temp);if(o2==null)continue;//该节点为根节点不存在父节点if(fatherMap.containsKey(o2.toString())){//已经含有该父节点List<T> ts = fatherMap.get(o2.toString());
                    ts.add(temp);}else{//不含该父节点List<T> tempList=newArrayList<>();
                    tempList.add(temp);
                    fatherMap.put(o2.toString(),tempList);}}catch(Exception e){
                e.printStackTrace();}}if(sonMap.containsKey(start)){T startNode = sonMap.get(start);//起始节点this.sonList =buildSonList(fatherMap,startNode,sonFiled,head);//建子节点this.fatherList=buildFatherList(sonMap,startNode,head,fatherFiled);//建父节点this.tree=buildTree(fatherMap,startNode,head,sonFiled);//建树}}

三、案例使用

以上面数据库中数据为例，所使用的时mybatis-plus框架：

1. 建立数据表实体类

@Data@NoArgsConstructor@AllArgsConstructorpublicclassMyNode{privateInteger id;privateInteger parentId;privateString content;}

2. mapper文件

@MapperpublicinterfaceMyNodeMapperextendsBaseMapper<MyNode>{}

3. 使用

@SpringBootTestclassDemoApplicationTests{@ResourcepublicMyNodeMapper myNodeMapper;@TestvoidcontextLoads(){List<MyNode> list = myNodeMapper.selectList(null);//查询出数据表中所有有效数据TreeUtil<MyNode> util1=newTreeUtil<>();
        util1.buildListAndTree(list,MyNode.class,"parentId","id","1");//以节点唯一标识为1的节点为起始节点System.out.println("------------------------以1为起始节点------------------------");System.out.println("************父节点************");for(MyNode myNode:util1.getFatherList())System.out.println(myNode.toString());System.out.println("************子节点************");for(MyNode myNode:util1.getSonList())System.out.println(myNode.toString());TreeUtil<MyNode> util2=newTreeUtil<>();
        util2.buildListAndTree(list,MyNode.class,"parentId","id","3");//以节点唯一标识为3的节点为起始节点System.out.println("------------------------以3为起始节点------------------------");System.out.println("************父节点************");for(MyNode myNode:util2.getFatherList())System.out.println(myNode.toString());System.out.println("************子节点************");for(MyNode myNode:util2.getSonList())System.out.println(myNode.toString());TreeUtil<MyNode> util3=newTreeUtil<>();
        util3.buildListAndTree(list,MyNode.class,"parentId","id","16");//以节点唯一标识为16的节点为起始节点System.out.println("------------------------以16为起始节点------------------------");System.out.println("************父节点************");for(MyNode myNode:util3.getFatherList())System.out.println(myNode.toString());System.out.println("************子节点************");for(MyNode myNode:util3.getSonList())System.out.println(myNode.toString());}}

结果：

------------------------以1为起始节点------------------------
************父节点************
MyNode(id=1, parentId=null, content=节点1)
************子节点************
MyNode(id=1, parentId=null, content=节点1)
MyNode(id=2, parentId=1, content=节点2)
MyNode(id=3, parentId=1, content=节点3)
MyNode(id=4, parentId=2, content=节点4)
MyNode(id=5, parentId=2, content=节点5)
MyNode(id=6, parentId=2, content=节点6)
MyNode(id=7, parentId=3, content=节点7)
MyNode(id=8, parentId=3, content=节点8)
MyNode(id=9, parentId=3, content=节点9)
MyNode(id=10, parentId=4, content=节点10)
MyNode(id=11, parentId=6, content=节点11)
MyNode(id=12, parentId=7, content=节点12)
MyNode(id=13, parentId=8, content=节点13)
MyNode(id=14, parentId=8, content=节点14)
MyNode(id=15, parentId=9, content=节点15)
MyNode(id=16, parentId=15, content=节点16)
------------------------以3为起始节点------------------------
************父节点************
MyNode(id=1, parentId=null, content=节点1)
MyNode(id=3, parentId=1, content=节点3)
************子节点************
MyNode(id=3, parentId=1, content=节点3)
MyNode(id=7, parentId=3, content=节点7)
MyNode(id=8, parentId=3, content=节点8)
MyNode(id=9, parentId=3, content=节点9)
MyNode(id=12, parentId=7, content=节点12)
MyNode(id=13, parentId=8, content=节点13)
MyNode(id=14, parentId=8, content=节点14)
MyNode(id=15, parentId=9, content=节点15)
MyNode(id=16, parentId=15, content=节点16)
------------------------以16为起始节点------------------------
************父节点************
MyNode(id=1, parentId=null, content=节点1)
MyNode(id=3, parentId=1, content=节点3)
MyNode(id=9, parentId=3, content=节点9)
MyNode(id=15, parentId=9, content=节点15)
MyNode(id=16, parentId=15, content=节点16)
************子节点************
MyNode(id=16, parentId=15, content=节点16)

使用本方法后，对于MySQL中所有含有树状结构的表均可直接使用，只需建立对应的实体类，以及明确实体类中表示节点间父子关系的属性名

四、总结

  • 本方法所用到的技术点并不是那么高深，所包含的只是Java的基本内容预计基础的数据结构。如：Java中的泛型、反射，数据结构中的队列、多叉树等基础知识。 - 泛型的使用使得本方法对任何数据表都具有复用性- 反射的使用能够根据动态获取实体类对象的指定属性值- 多叉树是本方法中构建树状结构的关键数据结构- 在构建树状结构时队列的使用使得构建效率得到了提升
  • 本方法虽然可以解决树状查询的问题，但还存在一些问题，如：在处理大量数据时，存在内存溢出问题
  • 除技术上的总结外，其它非编码能力的提升也很大 - 在实际开发中要善于发现重复性的工作，并对该工作进行抽象，进而得到一个通用性的解- 代码能力需要不断的实践，这个实践并不是说不断做大量重复性的工作，要将所学到的东西付诸实际开发中
标签： mysql 数据库 java
本文转载自: https://blog.csdn.net/qq_51763048/article/details/129268313
版权归原作者 *灞波尔奔* 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL树状结构表查询通解”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航