致访客
感谢各位一年多的陪伴,因内容调整,本站将于近日迁移到新域名并不再更新主要内容。

MENU

springBoot + Mybatis implements query tree structure

• February 10, 2022 • 公告

致访客
感谢各位一年多的陪伴,因内容调整,本站将于近日迁移到新域名并不再更新主要内容。
特此通知。

query tree node web case

Create database table

DROP TABLE IF EXISTS sys_dept;
CREATE TABLE sys_dept (
dept_id bigint(20) NOT NULL AUTO_INCREMENT,
parent_id bigint(20) NULL DEFAULT NULL COMMENT '上级部门ID,一级部门为0',
name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
order_num int(11) NULL DEFAULT NULL COMMENT '排序',
del_flag tinyint(4) NULL DEFAULT 0 COMMENT '是否删除 -1:已删除 0:正常',
PRIMARY KEY (dept_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '部门管理' ROW_FORMAT = Dynamic;
INSERT INTO sys_dept VALUES (1, 0, '总集团', 0, 0);
INSERT INTO sys_dept VALUES (2, 1, '北京分公司', 1, 0);
INSERT INTO sys_dept VALUES (3, 1, '上海分公司', 2, 0);
INSERT INTO sys_dept VALUES (4, 3, '技术部', 0, 0);
INSERT INTO sys_dept VALUES (5, 3, '销售部', 1, 0);
INSERT INTO sys_dept VALUES (6, 2, '其他部', 0, 0);
INSERT INTO sys_dept VALUES (7, 4, '技术三级菜单', 0, 0);
INSERT INTO sys_dept VALUES (8, 5, '销售三级菜单', 0, 0);
Create entity class

/**

  • 部门Tree结构
    */

@TableName("sys_dept")
public class DeptEmtity implements Serializable {

/**
 * 部门id
 */
@TableId(value="dept_id",type= IdType.AUTO)
private Integer deptId;
/**
 * 父Id
 */
private Integer parentId;
/**
 * 部门名称
 */
private String name;
/**
 * 排序编号
 */
private String orderNum;
/**
 * 删除标识
 */
private String delFlag;
/**
 * 子节点
 */
private List<DeptEmtity> treeNode;

public Integer getDeptId() {
    return deptId;
}

public void setDeptId(Integer deptId) {
    this.deptId = deptId;
}

public Integer getParentId() {
    return parentId;
}

public void setParentId(Integer parentId) {
    this.parentId = parentId;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getOrderNum() {
    return orderNum;
}

public void setOrderNum(String orderNum) {
    this.orderNum = orderNum;
}

public String getDelFlag() {
    return delFlag;
}

public void setDelFlag(String delFlag) {
    this.delFlag = delFlag;
}

public List<DeptEmtity> getTreeNode() {
    return treeNode;
}

public void setTreeNode(List<DeptEmtity> treeNode) {
    this.treeNode = treeNode;
}

}
The important thing is to look at the mapping implementation of collection in xml. The code of NodeMapper.xml is as follows:

<mapper namespace="com.example.demo.dao.UserDao">

<resultMap id="BaseTreeResultMap" type="com.example.demo.entity.DeptEmtity">
    <result column="dept_id" property="deptId"/>
    <result column="parent_id" property="parentId"/>
    <result column="name" property="name"/>
    <result column="order_num" property="orderNum"/>
    <collection column="dept_id" property="treeNode" javaType="java.util.ArrayList"
                ofType="com.example.demo.entity.DeptEmtity" select="getNextNodeTree"/>
</resultMap>

<resultMap id="NextTreeResultMap" type="com.example.demo.entity.DeptEmtity">
    <result column="dept_id" property="deptId"/>
    <result column="parent_id" property="parentId"/>
    <result column="name" property="name"/>
    <result column="order_num" property="orderNum"/>
    <collection column="dept_id" property="treeNode" javaType="java.util.ArrayList"
                ofType="com.example.demo.entity.DeptEmtity" select="getNextNodeTree"/>
</resultMap>

<select id="getNextNodeTree" resultMap="NextTreeResultMap">
    SELECT
    *
    FROM sys_dept
    WHERE parent_id = #{deptId}
</select>

<select id="getNodeTree" resultMap="BaseTreeResultMap">
    SELECT
    *
    FROM sys_dept
    WHERE parent_id = 0
</select>

</mapper>

parent_id = 0 represents the top-level node. Then continue to call the getNextNodeTree method through the collection node to make a loop call.

At the dao layer, we can achieve this by just calling the getNodeTree method:

@Mapper
public interface UserDao {

List<DeptEmtity> getNodeTree();

}

Here are the key knowledge points:

Column means it will take the parent node dept_id and get the next object as a parameter
The treeNode object is a list, in fact, it can be omitted or not written
ofType is used to distinguish the JavaBean property type from the type contained in the collection
select is used to execute the loop which SQL

The result after the project demonstration is shown in the figure:

2019063021273410.png