在实际业务中,我们经常会遇到树形的机构数据,如行政区划、栏目分类等。
数据库表的设计机构大致如下:
CREATE TABLE `ri_sys_cat` (
`cat_id` varchar(64) NOT NULL COMMENT 'ID',
`cat_kind` varchar(4) DEFAULT NULL COMMENT '行业分类类型',
`cat_code` varchar(10) DEFAULT NULL COMMENT '行业分类代码',
`cat_name` varchar(100) NOT NULL COMMENT '分类名称',
`parent_id` varchar(64) NOT NULL COMMENT '父类ID',
PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='公共行业分类表';
实际读取表数据的时候,要么在java端进行处理,要么写相应的mysql函数或者存储过程,进行处理。
java处理举例如下:
//定义相应的树形机构类
public class SysCatTree implements java.io.Serializable{
private static final long serialVersionUID = 3118551510633166045L;
/** 分类ID*/
private String catId;
/** 分类ID -catId别名*/
private String id;
/** 01-国民经济行业分类*/
private String catKind;
/** 分类名称*/
private String catName;
/** 分类名称 -catName别名*/
private String label;
/** 父类ID*/
private String parentId;
/** 行业编码*/
private String catCode;
/** 子类*/
private List<SysCatTree> children;
public void setCatId(String catId) {
this.catId = catId;
this.id = catId;
}
public String getCatName() {
return catName;
}
public void setCatName(String catName) {
this.catName = catName;
this.label = catName;
}
//...省略get/set方法
}
获取相应的数据库数据,并将list转为树形结构,代码如下
private static List<SysCatTree> createTree(List<SysCatTree> menuList, String parentId) {
List<SysCatTree> childMenu = new ArrayList<>();
for (SysCatTree catTree : menuList) {
if (parentId.equals(catTree.getParentId())) {
List<SysCatTree> c_node = createTree(menuList, catTree.getId());
catTree.setChildren(c_node);
childMenu.add(catTree);
}
}
return childMenu;
}
在我们的业务中,数据库表中存储的是树形编码code,而不是树形id,这时就需要根据编码code,查询其下面的所有子类编码,进行相应的数据查询等。为了满足这一需求,需要在数据库中,自定义函数。
自定义的mysql函数如下
DROP FUNCTION IF EXISTS queryChildSysCatByCode;
CREATE FUNCTION `queryChildSysCatByCode`(catKind VARCHAR(4),catcode VARCHAR(10)) RETURNS varchar(2000) CHARSET utf8
COMMENT '根据分类编码查询该编码下的子类编码树'
BEGIN
-- 定义局部变量
DECLARE sTempId VARCHAR(2000);
DECLARE sTempCode VARCHAR(500);
DECLARE sTempChildCode VARCHAR(2000);
-- 给sTempId赋初始值
select cat_id into sTempId from ri_sys_cat where cat_code = catcode;
-- 循环获取
WHILE sTempId IS NOT NULL DO
select GROUP_CONCAT(cat_id),GROUP_CONCAT(cat_code) into sTempId,sTempCode from ri_sys_cat where cat_kind = catKind and FIND_IN_SET(parent_id,sTempId)>0;
IF sTempChildCode IS NULL AND sTempCode IS NOT NULL
THEN set sTempChildCode = sTempCode;
ELSEIF sTempChildCode IS NOT NULL AND sTempCode IS NOT NULL
THEN set sTempChildCode = CONCAT(sTempChildCode,',',sTempCode);
END IF;
END WHILE;
return sTempChildCode;
调用如下
select queryChildSysCatByCode('04','RS01');
返回结果如下:
RS0101,RS0102,RS0103,RS0104,RS0101001,RS0101002,RS0101003,RS0101004,RS0101005,RS0101006,RS0101007,RS0102001,RS0102002,RS0102003,RS0102004,RS010301,RS010302,RS010303