递归展示树状图/树状表格

一、数据库表设计

这里我们采用自关联的设计，通过id和pid的对应来确认数据的上下级关系

建表语句，我这里把一级菜单的pid设置成了0

/*
 Navicat Premium Data Transfer

 Source Server         : 神舟软件
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : 10.0.33.228:3306
 Source Schema         : sascoa

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 23/11/2022 09:52:24
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for zgsw_scsl_sxbg
-- ----------------------------
DROP TABLE IF EXISTS `zgsw_scsl_sxbg`;
CREATE TABLE `zgsw_scsl_sxbg`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '序号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称',
  `fullname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `sex` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `birthday` date NULL DEFAULT NULL COMMENT '生日',
  `birthdaytime` datetime NULL DEFAULT NULL COMMENT '时间',
  `pid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '父级id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '生成示例树形表格' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of zgsw_scsl_sxbg
-- ----------------------------
INSERT INTO `zgsw_scsl_sxbg` VALUES ('1', '第一层级', '第一层级', 12, '2', '2022-11-23', '2022-11-23 03:30:24', '0');
INSERT INTO `zgsw_scsl_sxbg` VALUES ('2', '第二层级', '第二层级', 12, '1', '2022-11-23', '2022-11-23 03:30:54', '1');
INSERT INTO `zgsw_scsl_sxbg` VALUES ('3', '第三层级', '第三层级', 12, '1', '2022-11-23', '2022-11-23 03:31:11', '2');
INSERT INTO `zgsw_scsl_sxbg` VALUES ('4', '测试1', '测试', 12, '1', '2022-11-23', '2022-11-23 03:31:40', '0');
INSERT INTO `zgsw_scsl_sxbg` VALUES ('5', '测试2', '测试', 12, '1', '2022-11-23', '2022-11-23 03:31:40', '4');
INSERT INTO `zgsw_scsl_sxbg` VALUES ('6', '测试3', '测试', 12, '1', '2022-11-23', '2022-11-23 03:31:40', '5');

SET FOREIGN_KEY_CHECKS = 1;

二、后端java递归代码

我这里在mapper文件里没有做映射，是通过java递归方法set的值

<resultMap type="ZgswScslSxbg" id="ZgswScslSxbgResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="fullname"    column="fullname"    />
        <result property="age"    column="age"    />
        <result property="sex"    column="sex"    />
        <result property="birthday"    column="birthday"    />
        <result property="birthdaytime"    column="birthdaytime"    />
        <result property="pid"    column="pid"    />
    </resultMap>

    <sql id="selectZgswScslSxbgVo">
        select id, name, fullname, age, sex, birthday, birthdaytime, pid from zgsw_scsl_sxbg
    </sql>

    <select id="selectZgswScslSxbgList" parameterType="ZgswScslSxbg" resultMap="ZgswScslSxbgResult">
        <include refid="selectZgswScslSxbgVo"/>
        <where>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="fullname != null  and fullname != ''"> and fullname like concat('%', #{fullname}, '%')</if>
            <if test="age != null "> and age = #{age}</if>
            <if test="sex != null  and sex != ''"> and sex = #{sex}</if>
            <if test="birthday != null "> and birthday = #{birthday}</if>
            <if test="birthdaytime != null "> and birthdaytime = #{birthdaytime}</if>
            <if test="pid != null  and pid != ''"> and pid = #{pid}</if>
        </where>
        <if test="orderByCode != null and orderByCode != ''">
            order by ${orderByCode}
        </if>
    </select>

首先我们需要在java实体类里，封装一个自己类型的列表
我们的工程中没有用Lombok注解，getter setter被我手动去掉了

package com.bjsasc.asp.dev.zgsw.scsl.sxbg.domain;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import io.swagger.annotations.ApiModelProperty;
import org.apache.commons.lang3.builder.ToStringBuilder;
import io.swagger.annotations.ApiModel;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.bjsasc.asp.dev.framework.aop.lang.annotation.Excel;
import com.bjsasc.asp.dev.framework.mvc.domain.BaseEntity;
import java.util.Date;
import java.util.List;

/**
 * 生成示例树形格对象 zgsw_scsl_sxbg
 * 
 * @author 陶得雨
 * @date 2022-11-23
 */
@ApiModel(description = "生成示例树形表格")
public class ZgswScslSxbg extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 序号 */
    private String id;

    /** 名称 */
	@ApiModelProperty(value="名称",name="name")
    @Excel(name = "名称")
    private String name;

    /** 姓名 */
	@ApiModelProperty(value="姓名",name="fullname")
    @Excel(name = "姓名")
    private String fullname;

    /** 年龄 */
	@ApiModelProperty(value="年龄",name="age")
    @Excel(name = "年龄")
    private Long age;

    /** 性别 */
	@ApiModelProperty(value="性别",name="sex")
    @Excel(name = "性别")
    private String sex;

    /** 生日 */
	@ApiModelProperty(value="生日",name="birthday")
    @JsonFormat(pattern = "yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @Excel(name = "生日", width = 30, dateFormat = "yyyy-MM-dd")
    private Date birthday;

    /** 时间 */
	@ApiModelProperty(value="时间",name="birthdaytime")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @Excel(name = "时间", width = 30, dateFormat = "yyyy-MM-dd")
    private Date birthdaytime;

    /** 父级id */
	@ApiModelProperty(value="父级id",name="pid")
    @Excel(name = "父级id")
    private String pid;

	private List<ZgswScslSxbg> children;

    /** 已办表查询参数start */
    private String ybid;

    private String orderByCode;

    private String taskId;

    private String nodeId;

    private String processInstanceId;

    private String businessKey;

    private String duration;

    private String subgwtype;

    private String type;

    private String moduleId;

    private String userid;

    private String username;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private String startTime;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private String endTime;
    
}

下面是递归代码

@Override
public List<ZgswScslSxbg> selectZgswScslSxbgList(ZgswScslSxbg zgswScslSxbg)
{
	return getZgswScslSxbgList("0");
}
private List<ZgswScslSxbg> getZgswScslSxbgList(String pid)
{
	ZgswScslSxbg zgswScslSxbg = new ZgswScslSxbg();
	zgswScslSxbg.setPid(pid);
	List<ZgswScslSxbg> zgswScslSxbgList = zgswScslSxbgMapper.selectZgswScslSxbgList(zgswScslSxbg);
	if(zgswScslSxbgList.size()>0){
		for (ZgswScslSxbg zgswScslSxbg1:zgswScslSxbgList) {
			if(zgswScslSxbg1.getId()!= null){
				zgswScslSxbg1.setChildren(getZgswScslSxbgList(zgswScslSxbg1.getId()));
			}
		}
	}
	return zgswScslSxbgList;
}

三、前端展示树状表格

我这里的需求是要展示出来树状table而不是树状图，其实它俩的数据结构是一样的，只是视图部分用的组件有所区别

ElementUI官网有树状table的用法。

只需要在代码里加上这两部分即可

row-key="id" :tree-props="{children: 'children', hasChildren: 'hasChildren'}"

前端代码

 <el-table v-loading="loading" class="new-style-table" :data="sxbgList" @selection-change="handleSelectionChange" row-key="id" :tree-props="{children: 'children', hasChildren: 'hasChildren'}">
      <el-table-column type="selection" width="55" align="center" />
      <el-table-column type="index" label="序号" width="65px" align="center" />
      <el-table-column label="名称" align="center" prop="name" />
      <el-table-column label="姓名" align="center" prop="fullname" />
      <el-table-column label="年龄" align="center" prop="age" />
      <el-table-column label="性别" align="center" prop="sex" width="140px">
        <template slot-scope="scope">
          <template v-if="scope.row.sex === '0'">未知</template>
          <template v-else-if="scope.row.sex === '1'">男</template>
          <template v-else-if="scope.row.sex === '2'">女</template>
        </template>
      </el-table-column>
      <el-table-column label="生日" align="center" prop="birthday" width="200" />
      <el-table-column label="时间" align="center" prop="birthdaytime" width="180">
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="scope">
          <el-button
            size="mini"
            type="text"
            @click="handleUpdate(scope.row)"
          >编辑</el-button>
          <el-button
            size="mini"
            type="text"
            style="color:#ff5d5d;"
            @click="handleDelete(scope.row)"
          >删除</el-button>
        </template>
      </el-table-column>
    </el-table>

四、效果展示

这样一个树状table的功能就实现了