In practical development, we found that many data structures are actually tree-like, with an indeterminate number of nodes under each node and an indeterminate depth. How should this data structure be handled?

In fact, our file directory is such a data structure:

So today we will design and query a simple directory as an example to explain.

1. Design database tables and classes

Let’s design the database and classes first.

For simplicity, our directory class here only has the basic information of id and name. Let’s first design the database table as follows:

In addition to the basic information, I have added the parent_id field to indicate the parent directory of a directory. With this field, can we look up all subdirectories under a directory?

Ok, we continue to design the class diagram and build the following directory class:

In the Java class, parent_id is not set, but a collection of subdirectories instead. In this way, when we use MyBatis to cascade query to a directory, we can directly put the sub-directory in this collection.

Table of contents SQL file and class code:

Java classes:

package com.example.treehandle.dataobject;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

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

/** * menu class */
@Getter
@Setter
@NoArgsConstructor
public class Menu implements Serializable {

   /** * primary key id */
   private int id;

   /** * Directory name */
   private String name;

   /** * Subdirectory */ under this directory
   private List<Menu> childMenus;

}
Copy the code

SQL file:

Initialize the table
drop table if exists `menu`;
create table `menu`
(
   `id`        int unsigned auto_increment,
   `name`      varchar(16) not null,
   `parent_id` int         not null.-- The parent directory ID of this directory. If this directory is the root directory, the value of this field is 0
   primary key (`id`)
) engine = InnoDB
  default charset = utf8mb4;

Initialize test data
insert into `menu` (`name`, `parent_id`)
values ('music'.0),
      ('video'.0),
      ('images'.0),
      ('Chinese Song'.1),   The "Chinese songs" directory is a subdirectory of "music"
      ('English Song'.1),   The English songs directory is a subdirectory of music
      ('animals'.3),    The animals directory is a subdirectory of Pictures
      ('quadratic'.3),   The "quadratic" directory is a subdirectory of "picture"
      ('anime'.7),    -- "animation" directory is a subdirectory of "quadratic"
      ('Quadratic meta-game'.7), The "quad-meta" directory is a subdirectory of "quad-meta"
      ('Princess Link'.9),  The "Princess Link" directory is a subdirectory of the "quadratic metagame"
      ('Blue Line'.9),  The "Blue Route" directory is a sub-directory of "quadratic metagame"
      ('the original god'.9); The "original god" directory is a subdirectory of "quadratic metagame"
Copy the code

So the big question is: how do you cascade queries with tree structures? Let’s go down.

2, MyBatis cascading query tree structure

First, we define the DAO layer. Here we write only two query methods:

package com.example.treehandle.dao;

import com.example.treehandle.dataobject.Menu;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface MenuDAO {

   /** * find directory by id */
   Menu getById(int id);

   /** * Find a directory by its parent directory ID (find all subdirectories under a directory) */
   List<Menu> getByParentId(int parentId);

}
Copy the code

Here, in addition to defining the getById method, we also define the getByParentId method, which is the method that helps us find subdirectories of a directory.

MyBatis Mapper XML file


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.treehandle.dao.MenuDAO">
   <resultMap id="menuResultMap" type="com.example.treehandle.dataobject.Menu">
      <id column="id" property="id"/>
      <result column="name" property="name"/>
      <! Select * from parent node where id = parent node
      <collection property="childMenus" select="com.example.treehandle.dao.MenuDAO.getByParentId" column="id" fetchType="eager"/>
   </resultMap>

   <select id="getById" resultMap="menuResultMap">
      select *
      from `menu`
      where id = #{id}
   </select>

   <select id="getByParentId" resultMap="menuResultMap">
      select *
      from `menu`
      where parent_id = #{parentId}
   </select>
</mapper>
Copy the code

In the resultMap node above, we defined a Collection node to indicate that the properties of our childMenus are a collection. The properties of this collection node are as follows:

  • propertyRepresents which property in the directory class this node data corresponds to
  • selectSpecifies that a DAO query method be executed and its results placed on the node
  • columnUsed to performselectProperty to query method parameters
  • fetchTypeSpecifies lazy fetching or all fetching

So it can be seen that when we run getById, when we encounter this collection node, we will run getByParentId with the value of our ID field as the parameter. That is, we will query the directory with its own ID as the parent directory ID (in other words, we will query all of our subdirectories). This allows you to recursively query down to a directory and all of its subdirectories.

Here, we have completed the design and query of the tree structure!

Write an interface and query it.

Full results:

{
    "id": 3."name": "Image"."childMenus": [{"id": 6."name": "Animal"."childMenus": []}, {"id": 7."name": "Quadratic element"."childMenus": [{"id": 8."name": "Anime"."childMenus": []}, {"id": 9."name": "Quadratic meta-game"."childMenus": [{"id": 10."name": "Princess Link"."childMenus": []}, {"id": 11."name": "The Blue Line"."childMenus": []}, {"id": 12."name": "The god"."childMenus": []}]}]}Copy the code

Thus, a clear and distinct tree structure is detected.

Sample repository address