数据库表
DROP TABLE IF EXISTS MasterDataModel;
CREATE TABLE MasterDataModel(
MASTERID VARCHAR(255) NOT NULL COMMENT '主数据模型ID' ,
PARENTID VARCHAR(255) COMMENT '父数据模型ID' ,
ANCESTORS VARCHAR(255) COMMENT '祖籍列表' ,
TOPIC VARCHAR(255) COMMENT '数据模型主题' ,
DELFLAG VARCHAR(255) DEFAULT 0 COMMENT '删除标志位' ,
STATUS VARCHAR(255) DEFAULT 0 COMMENT '状态(0正常 1移除)' ,
ORDERNUM VARCHAR(255) COMMENT '显示顺序' ,
CREATED_BY VARCHAR(32) COMMENT '创建人' ,
CREATED_TIME DATETIME COMMENT '创建时间' ,
UPDATED_BY VARCHAR(32) COMMENT '更新人' ,
UPDATED_TIME DATETIME COMMENT '更新时间' ,
PRIMARY KEY (MASTERID)
) COMMENT = '主数据模型';
数据
INSERT INTO LOGBDPDATA.MASTER_DATA_MODEL (MASTER_ID, PARENT_ID, ANCESTORS, TOPIC, DEL_FLAG, STATUS, ORDER_NUM, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) VALUES ('7', '3', '0,1,3', '主题002_2', '0', 0, null, null, null, null, null);
INSERT INTO LOGBDPDATA.MASTER_DATA_MODEL (MASTER_ID, PARENT_ID, ANCESTORS, TOPIC, DEL_FLAG, STATUS, ORDER_NUM, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) VALUES ('1', '0', '0', '主题顶节点', '0', 0, null, null, null, null, null);
INSERT INTO LOGBDPDATA.MASTER_DATA_MODEL (MASTER_ID, PARENT_ID, ANCESTORS, TOPIC, DEL_FLAG, STATUS, ORDER_NUM, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) VALUES ('2', '1', '0,1', '主题001', '0', 0, null, null, null, null, null);
INSERT INTO LOGBDPDATA.MASTER_DATA_MODEL (MASTER_ID, PARENT_ID, ANCESTORS, TOPIC, DEL_FLAG, STATUS, ORDER_NUM, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) VALUES ('3', '1', '0,1', '主题002', '0', 0, null, null, null, null, null);
INSERT INTO LOGBDPDATA.MASTER_DATA_MODEL (MASTER_ID, PARENT_ID, ANCESTORS, TOPIC, DEL_FLAG, STATUS, ORDER_NUM, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) VALUES ('4', '2', '0,1,2', '主题001_1', '0', 0, null, null, null, null, null);
INSERT INTO LOGBDPDATA.MASTER_DATA_MODEL (MASTER_ID, PARENT_ID, ANCESTORS, TOPIC, DEL_FLAG, STATUS, ORDER_NUM, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) VALUES ('5', '2', '0,1,2', '主题001_2', '0', 0, null, null, null, null, null);
INSERT INTO LOGBDPDATA.MASTER_DATA_MODEL (MASTER_ID, PARENT_ID, ANCESTORS, TOPIC, DEL_FLAG, STATUS, ORDER_NUM, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) VALUES ('6', '3', '0,1,3', '主题002_1', '0', 0, null, null, null, null, null);
插入的效果:
实体类
@TableField(exist = false)
@ApiModelProperty(value = "子主题")
//@JsonInclude(JsonInclude.Include.NON_EMPTY)//如果数据没有在传给前端的时候,就不显示
private List<MasterDataModel> children;
第一种方案
实现的效果,一点开页面自动渲染目录树,后端需要查询全部的数据,组装成Tree型结构。
/**
* 查询全部主数据模型,用tree来组装
*
* @author wanglufei
* @date 2022/5/16 2:44 PM
*/
@Override
public List<MasterDataModel> listWithTree() {
/**
* 1.先将所有的主数据模型查出来
*/
List<MasterDataModel> masterDataModels = baseMapper.selectList(null);
/**
* 2.在查询出来的数据的基础上,找出它的父亲
*/
List<MasterDataModel> level1 = masterDataModels.stream()
.filter(masterDataModel -> {
//过滤的找出父id为0
return masterDataModel.getParentId().equals("0");
})
.map(item -> {//映射
//递归查询 子数据模型
item.setChildren(getChildrens(item, masterDataModels));
return item;
})
.sorted((item1, item2) -> {//排序
return (item1.getOrderNum() == null ? 0 : item1.getOrderNum())
- (item2.getOrderNum() == null ? 0 : item2.getOrderNum());
})
.collect(Collectors.toList());//收集起来成list
return level1;
}
/**
* 根据查询所有,将查询所有的结果按照父id,递归查询
*
* @param root
* @param allData
* @author wanglufei
* @date 2022/5/16 3:10 PM
*/
private List<MasterDataModel> getChildrens(MasterDataModel root,
List<MasterDataModel> allData) {
List<MasterDataModel> children = allData.stream()
.filter((masterDataModel) -> {
//找到他的子分类
return masterDataModel.getParentId().equals(root.getMasterId());
})
.map((masterDataModel) -> {
//对一级数据模型和二级数据模型进行映射
//递归出口:是否还有子节点,如果有的话继续往下遍历,如果没有则直接返回
masterDataModel.setChildren(getChildrens(masterDataModel, allData));
return masterDataModel;
})
.sorted((item1, item2) -> {//排序
return (item1.getOrderNum() == null ? 0 : item1.getOrderNum())
- (item2.getOrderNum() == null ? 0 : item2.getOrderNum());
})
.collect(Collectors.toList());
return children;
}
查询结果
"data": [
{
"masterId": "1",
"parentId": "0",
"ancestors": "0",
"topic": "主题顶节点",
"delFlag": "0",
"status": 0,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "2",
"parentId": "1",
"ancestors": "0,1",
"topic": "主题001",
"delFlag": "0",
"status": 0,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "4",
"parentId": "2",
"ancestors": "0,1,2",
"topic": "主题001_1",
"delFlag": "0",
"status": 0,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
},
{
"masterId": "5",
"parentId": "2",
"ancestors": "0,1,2",
"topic": "主题001_2",
"delFlag": "0",
"status": 0,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
}
]
},
{
"masterId": "3",
"parentId": "1",
"ancestors": "0,1",
"topic": "主题002",
"delFlag": "0",
"status": 0,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "7",
"parentId": "3",
"ancestors": "0,1,3",
"topic": "主题002_2",
"delFlag": "0",
"status": 0,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
},
{
"masterId": "6",
"parentId": "3",
"ancestors": "0,1,3",
"topic": "主题002_1",
"delFlag": "0",
"status": 0,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
}
]
}
]
}
]
第二种方案
使用sql来做递归查询。扩展点就是可以根据parentId来查询下面的子节点。当然也可以不传,在后台写死我们的根节点。
控制层
@PostMapping("/tree")
@ApiOperation("查询主数据模型用tree来组装起来")
public AjaxResult listBySQL(@RequestParam(required = false) String parentId) {
//@RequestParam(required = false) 参数也可以不要
List<MasterDataModel> data = masterDataModelService.listBySQL(parentId);
return new AjaxResult(200, "查询成功", data);
}
业务层
/**
* 查询主数据模型用tree来组装起来
*
* @param parentId
* @author wanglufei
* @date 2022/5/27 4:02 PM
*/
@Override
public List<MasterDataModel> listBySQL(String parentId) {
if (StringUtils.isEmpty(parentId)) {
parentId = "0";
}
List<MasterDataModel> data = masterDataModelMapper.listBySQL(parentId);
return data;
}
Mapper层
<resultMap id="Base_Tree"
type="com.ruoyi.xxx.xxx.xxx.MasterDataModel">
<id column="MASTER_ID" property="masterId"/>
<result column="PARENT_ID" property="parentId"/>
<result column="TOPIC" property="topic"/>
<collection property="children"
column="MASTER_ID"
ofType="com.ruoyi.xxx.xxx.xxx.MasterDataModel"
select="listBySQL"/>
</resultMap>
<select id="listBySQL" resultMap="Base_Tree">
SELECT
MASTER_ID,
PARENT_ID,
TOPIC
FROM
MASTER_DATA_MODEL
WHERE
PARENT_ID =#{parentId}
</select>
查询效果
//不传parentId节点的id
"data": [
{
"masterId": "1",
"parentId": "0",
"ancestors": null,
"topic": "主题顶节点",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "2",
"parentId": "1",
"ancestors": null,
"topic": "主题001",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "4",
"parentId": "2",
"ancestors": null,
"topic": "主题001_1",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
},
{
"masterId": "5",
"parentId": "2",
"ancestors": null,
"topic": "主题001_2",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
}
]
},
{
"masterId": "3",
"parentId": "1",
"ancestors": null,
"topic": "主题002",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "7",
"parentId": "3",
"ancestors": null,
"topic": "主题002_2",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
},
{
"masterId": "6",
"parentId": "3",
"ancestors": null,
"topic": "主题002_1",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
}
]
}
]
}
]
//给parentId
"data": [
{
"masterId": "2",
"parentId": "1",
"ancestors": null,
"topic": "主题001",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "4",
"parentId": "2",
"ancestors": null,
"topic": "主题001_1",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
},
{
"masterId": "5",
"parentId": "2",
"ancestors": null,
"topic": "主题001_2",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
}
]
},
{
"masterId": "3",
"parentId": "1",
"ancestors": null,
"topic": "主题002",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": [
{
"masterId": "7",
"parentId": "3",
"ancestors": null,
"topic": "主题002_2",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
},
{
"masterId": "6",
"parentId": "3",
"ancestors": null,
"topic": "主题002_1",
"delFlag": null,
"status": null,
"orderNum": null,
"createdBy": null,
"createdTime": null,
"updatedBy": null,
"updatedTime": null,
"children": []
}
]
}
]