一、数据库表设计
这里我们采用自关联的设计,通过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的功能就实现了