最近在写多维计算的东西,需要支持mysql的数据库,mysql跟oracle和PG不同,简直特立独行,不支持窗口函数,也没有封装好的树函数,没办法只好百度加自己造。总算造出来一个小树,哈哈,然后紧接着碰到了JDBC创建mysql函数问题,试了几回发现是mysql的函数提的问题,我之后拆开运行。以下是代码总结,希望能帮到以下有同样困难的孩子,,不足之处请指出!
首先是根绝子节点进行寻根的树结构,可以在navicat直接运行的(需要的话替换称自己的表信息和字段),, 可以在这个基础上进行改造,具体的我就不讲解了。
//根据子节点获取根节点
delimiter /
DROP FUNCTION IF EXISTS `avatar`.`getParentList`/
CREATE FUNCTION `getParentList`(nodeName varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
DECLARE ptemp VARCHAR(1000);
DECLARE ctemp VARCHAR(1000);
SET ptemp = '#';
SET ctemp = (select type_id from MD_TYPE where type_name = nodeName);
WHILE ctemp IS NOT NULL DO
SET ptemp = concat(ptemp, ',', ctemp);
SELECT group_concat(parent_id)
INTO ctemp
FROM MD_TYPE
WHERE FIND_IN_SET(type_id, ctemp) > 0;
END WHILE;
RETURN ptemp;
END;
select type_id,type_name,type_level,parent_id from MD_TYPE where find_in_set(type_id,getParentList('日常检修1'));
这是将上面的函数移植到JDBC进行使用,不要直接嵌套,JAVA会报错的
String deleteFunctionSql="DROP FUNCTION IF EXISTS `"+resource_attr_map.get(DBNAME)+"`.`getChildList`";
stmt_query = resConn.prepareStatement(deleteFunctionSql);
stmt_query.executeUpdate();
String createFunctionSql="CREATE FUNCTION `getChildList`(nodeName varchar(1000))\n" +
" RETURNS VARCHAR(1000)\n" +
" BEGIN\n" +
" DECLARE ptemp VARCHAR(1000);\n" +
" DECLARE ctemp VARCHAR(1000);\n" +
" SET ptemp = '#';\n" +
" SET ctemp = (select type_id from MD_TYPE where type_name=nodeName);\n" +
" WHILE ctemp IS NOT NULL DO\n" +
" SET ptemp = concat(ptemp, ',', ctemp);\n" +
" SELECT group_concat(type_id)\n" +
" INTO ctemp\n" +
" FROM MD_TYPE\n" +
" WHERE FIND_IN_SET(parent_id, ctemp) > 0;\n" +
" END WHILE;\n" +
" RETURN ptemp;\n" +
" END;";
stmt_query = resConn.prepareStatement(createFunctionSql);
stmt_query.executeUpdate();
sql ="select type_id,type_name,type_level,parent_id from MD_TYPE where find_in_set(type_id,getParentList('日常检修1'));";
stmt_query = resConn.prepareStatement(sql);
rs=stmt_query.executeQuery();
while (rs.next()){
leavesList.add(rs.getString(1));
}