mysql自定义函数获取树形结构数据

2018-01-13 by 米糠杰

在实际业务中,我们经常会遇到树形的机构数据,如行政区划、栏目分类等。

数据库表的设计机构大致如下:

    

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 DOselect 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 NULLTHEN set sTempChildCode = sTempCode;ELSEIF sTempChildCodeIS NOT NULL AND sTempCode IS NOT NULLTHEN 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

 

   

 

 

最新更新:

第七城市

栏目导航(关闭)