原版在此:
http://www.mytju.com/classcode/news_readNews.asp?newsID=422
觉得不够爽,改了下。
删除节点(根节点除外)时,可以删除所有子孙节点。
支持任何一个节点(根节点除外)的移动,会连同子孙节点一起移动。
---------------------------------------------------
(1)表结构
CREATE TABLE `tree_node` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`code` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`pid` int(10) NOT NULL,
`treeId` int(10) NOT NULL,
`leftId` int(10) NOT NULL,
`rightId` int(10) NOT NULL,
`level` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
(2)添加根节点
DROP Procedure IF EXISTS `addTreeRootNode` ;
DELIMITER |
/*
* pid,添加到的目标节点ID
* code,节点代码
* name,节点名称
* resultCode,结果代码,
* resultMsg,结果消息
*/
CREATE PROCEDURE `addTreeRootNode`(vTreeId int,code varchar(50),name varchar(50),OUT resultCode int,OUT resultMsg varchar(50))
ExitLabel:BEGIN
DECLARE vLeftId INT; /* 左节点ID */
DECLARE aff INT; /* SQL 影响记录条数 */
/* 目标节点的右值、treeId和Level值 */
SELECT `leftId` INTO vLeftId
FROM `tree_node`
WHERE `treeId`= vTreeId AND `pid` = 0;
IF vLeftId IS NOT NULL THEN
SET resultCode = 1002;
SET resultMsg = "根节点已存在";
LEAVE ExitLabel;
END IF;
/*----------开始更新--------------*/
START TRANSACTION;
/* 增加节点自己,pid=0,leftId=1,rightId=2,level=0 */
INSERT INTO `tree_node`(`code`,`name`,`pid`,`leftId`,`rightId`,`level`,`treeId`)
VALUES (code,name,0,1,2,0,vTreeId);
/* 影响行数 */
SELECT ROW_COUNT() INTO aff;
IF aff = 1 THEN
COMMIT;
SET resultCode = 1000;
SET resultMsg = "成功";
ELSE
ROLLBACK;
SET resultCode = 1001;
SET resultMsg = "失败";
END IF;
END |
(3)添加节点
DROP Procedure IF EXISTS `addTreeNode` ;
DELIMITER |
/*
* pid,添加到的目标节点ID
* code,节点代码
* name,节点名称
* resultCode,结果代码,
* resultMsg,结果消息
*/
CREATE PROCEDURE `addTreeNode`(pid int,code varchar(50),name varchar(50),OUT resultCode int,OUT resultMsg varchar(50))
ExitLabel:BEGIN
DECLARE vRightId INT; /* 右节点ID */
DECLARE vLevel INT; /*层级*/
DECLARE vTreeId INT; /*树ID*/
DECLARE aff INT; /* SQL 影响记录条数 */
DECLARE af INT DEFAULT 0; /* 总影响记录条数*/
/* 目标节点的右值、treeId和Level值 */
SELECT `rightId`,`treeId`,`level`
INTO vRightId,vTreeId,vLevel
FROM `tree_node`
WHERE `id`= pid;
IF vRightId IS NULL THEN
SET resultCode = 1002;
SET resultMsg = "指定的节点不存在";
LEAVE ExitLabel;
END IF;
/*----------开始更新--------------*/
START TRANSACTION;
/* 更新右侧节点的left值 */
UPDATE `tree_node`
SET `leftId`=`leftId`+2
WHERE `treeId` = vTreeId AND `leftId` > vRightId;
/* 影响行数 */
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
/* 更新右侧节点的right值 */
UPDATE `tree_node`
SET `rightId`=`rightId`+2
WHERE `treeId` = vTreeId AND `rightId` >= vRightId;
/* 影响行数 */
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
/* 增加节点自己 */
INSERT INTO `tree_node`(`code`,`name`,`pid`,`leftId`,`rightId`,`level`,`treeId`)
VALUES (code,name,pid,vRightId,vRightId+1,vLevel+1,vTreeId);
/* 影响行数 */
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
/*至少会插入节点自己,更新根节点,所以最少是2*/
IF af >= 2 THEN
COMMIT;
SET resultCode = 1000;
SET resultMsg = "成功";
ELSE
ROLLBACK;
SET resultCode = 1001;
SET resultMsg = "失败";
END IF;
END |
(4)删除节点
DROP Procedure IF EXISTS `deleteTreeNode` ;
DELIMITER |
/*
* nodeId,要删除的节点ID
* resultCode,结果代码,
* resultMsg,结果消息
*/
CREATE PROCEDURE `deleteTreeNode`(nodeId int,OUT resultCode int,OUT resultMsg varchar(50))
ExitLabel:BEGIN
DECLARE vLeftId INT; /* 左节点ID */
DECLARE vRightId INT; /* 右节点ID */
DECLARE vTreeId INT; /*树ID*/
DECLARE aff INT; /* SQL 影响记录条数 */
DECLARE af INT DEFAULT 0; /* 总影响记录条数*/
/* 查询要删除的节点,变量名不能与字段名一样的哦!! */
SELECT `leftId`,`rightId`,`treeId`
INTO vLeftId,vRightId,vTreeId
FROM `tree_node`
WHERE `id` = nodeId;
IF vLeftId IS NULL THEN
SET resultCode = 1002;
SET resultMsg = "要删除的节点不存在";
LEAVE ExitLabel;
END IF;
IF vLeftId=1 THEN
SET resultCode = 1003;
SET resultMsg = "根节点不能删除";
LEAVE ExitLabel;
END IF;
/*----------开始更新--------------*/
START TRANSACTION;
/* 删除节点及所有子孙节点 */
DELETE
FROM `tree_node`
WHERE `treeId` = vTreeId
AND `leftId` between vLeftId AND vRightId;
/* 影响行数 */
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
/* 更新右侧节点的left值 */
UPDATE `tree_node`
SET `leftId`=`leftId`-(vRightId-vLeftId+1)
WHERE `treeId` = vTreeId AND `leftId`>vLeftId;
/* 影响行数 */
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
/* 更新右侧节点的right值 */
UPDATE `tree_node`
SET `rightId`=`rightId`-(vRightId-vLeftId+1)
WHERE `treeId` = vTreeId AND `rightId`>vLeftId;
/* 影响行数 */
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
/* 删除其它关联数据 */
/*至少会删除自己,更新根节点,所以最少是2*/
IF af >= 2 THEN
COMMIT;
SET resultCode = 1000;
SET resultMsg = "成功";
ELSE
ROLLBACK;
SET resultCode = 1001;
SET resultMsg = "失败";
END IF;
END |
(5)移动节点
DROP Procedure IF EXISTS `moveTreeNode`;
DELIMITER |
/*
* nodeId,移动的节点ID
* targetId,目标节点ID(即放在哪个节点下面)
* resultCode,结果代码,
* resultMsg,结果消息
*/
CREATE PROCEDURE `moveTreeNode`(nodeId int,targetId int,OUT resultCode int,OUT resultMsg varchar(50))
ExitLabel:BEGIN
/* 强烈注意,变量名不要与字段名相同 */
DECLARE vLeftId INT; /* 左节点ID */
DECLARE vRightId INT; /* 右节点ID */
DECLARE vTreeId INT; /*树ID*/
DECLARE vPid INT; /*节点的父ID*/
DECLARE vLevel INT; /* 节点的Level */
DECLARE vTargetLeftId INT; /* 目标节点的左节点ID */
DECLARE vTargetRightId INT; /* 目标节点的右节点ID */
DECLARE vTargetLevel INT; /* 目标节点的Level */
DECLARE vDiff INT; /* 差值*/
DECLARE vLevelDiff INT; /* Level差值*/
DECLARE vGroupTreeId INT; /*没啥用*/
DECLARE vGroupIdStr varchar(1000); /*待移动小树杈的节点列表*/
/* 查询要移动的节点,变量名不能与字段名一样的哦!! */
SELECT `leftId`,`rightId`,`treeId`,`pid`,`level`
INTO vLeftId,vRightId,vTreeId,vPid,vLevel
FROM `tree_node`
WHERE `id`= nodeId;
/*leftID不为空*/
IF vLeftId IS NULL THEN
SET resultCode = 1002;
SET resultMsg = "要移动的节点不存在";
LEAVE ExitLabel;
END IF;
IF vLeftId=1 THEN
SET resultCode = 1003;
SET resultMsg = "根节点不能移动";
LEAVE ExitLabel;
END IF;
IF nodeId = targetId THEN
SET resultCode = 1004;
SET resultMsg = "不能移动到自己";
LEAVE ExitLabel;
END IF;
/* 如果目标ID是要移动节点的父节点,不需要移动(同级认为无顺序)*/
IF vPid = targetId THEN
SET resultCode = 1000; /*认为是成功操作*/
SET resultMsg = "目标节点是要移动节点的父节点,不需要移动";
LEAVE ExitLabel;
END IF;
/* 查询要移动的节点,变量名不能与字段名一样的哦!! */
SELECT `leftId`,`rightId`,`level`
INTO vTargetLeftId,vTargetRightId,vTargetLevel
FROM `tree_node`
WHERE `treeId` = vTreeId AND `id`= targetId;
IF vTargetLeftId IS NULL THEN
SET resultCode = 1006;
SET resultMsg = "目标节点不存在";
LEAVE ExitLabel;
END IF;
IF vTargetLeftId > vLeftId AND vTargetLeftId < vRightId THEN
SET resultCode = 1007;
SET resultMsg = "目标节点不能是要移动节点的子节点";
LEAVE ExitLabel;
END IF;
/*---------------开始更新-------------------------*/
START TRANSACTION;
/* 保存小树杈的ID值*/
SELECT `treeId`, group_concat(CAST(`id` as char)) as idStr
INTO vGroupTreeId,vGroupIdStr
FROM `tree_node`
WHERE `treeId` = vTreeId
AND `leftId` between vLeftId AND vRightId
GROUP BY `treeId`;
/* 目标节点在右边 */
/* 目标节点是当前节点所在路径上的节点的话,比较特殊,与移动到右侧节点的处理相同 */
IF vTargetLeftId>vLeftId OR (vTargetLeftId < vLeftId AND vTargetRightId > vRightId) THEN
/*目标节点的当前right值-1就是移动节点的新right值*/
SET vDiff = vTargetRightId - 1 - vRightId; /*左右值的差值*/
SET vLevelDiff = vTargetLevel + 1 - vLevel; /*Level的差值*/
/* 更新小树杈的left、right和level*/
UPDATE `tree_node`
SET `leftId`=`leftId` +vDiff,
`rightId`=`rightId` + vDiff,
`level` = `level` + vLevelDiff
WHERE `treeId` = vTreeId
AND `leftId` between vLeftId AND vRightId;
/* 更新移动节点的父ID */
UPDATE `tree_node`
SET `pid` = targetId
WHERE `id`= nodeId;
/*插入位置左侧的节点的left值和right值要减小*/
SET vDiff = vRightId-vLeftId+1;
/* left>移动节点原right值 and left<目标节点原right值,并且不是小树杈上的节点*/
UPDATE `tree_node`
SET `leftId`=`leftId`- vDiff
WHERE `treeId` = vTreeId
AND `leftId`>vRightId AND `leftId`< vTargetRightId
AND NOT FIND_IN_SET(CAST(`id` as char),vGroupIdStr);
/* right>移动节点原right值 and right<目标节点原right值,并且不是小树杈上的节点*/
UPDATE `tree_node`
SET `rightId`=`rightId`- vDiff
WHERE `treeId` = vTreeId
AND `rightId`>vRightId AND `rightId`< vTargetRightId
AND NOT FIND_IN_SET(CAST(`id` as char),vGroupIdStr);
ELSE
/* 目标节点在左边 */
/*目标节点的当前right值就是移动节点的新left值*/
SET vDiff = vLeftId - vTargetRightId; /*左右值的差值*/
SET vLevelDiff = vTargetLevel + 1 - vLevel; /*Level的差值*/
/* 更新小树杈的left、right和level*/
UPDATE `tree_node`
SET `leftId`=`leftId` -vDiff,
`rightId`=`rightId` - vDiff,
`level` = `level` + vLevelDiff
WHERE `treeId` = vTreeId
AND `leftId` between vLeftId AND vRightId;
/* 更新移动节点的父ID */
UPDATE `tree_node`
SET `pid` = targetId
WHERE `id`= nodeId;
/*插入位置右侧的节点的left值和right值要增大*/
SET vDiff = vRightId-vLeftId+1;
/* left>目标节点原right值 and left<移动节点原right值,并且不是小树杈上的节点*/
UPDATE `tree_node`
SET `leftId`=`leftId`+ vDiff
WHERE `treeId` = vTreeId
AND `leftId`>vTargetRightId AND `leftId`< vRightId
AND NOT FIND_IN_SET(CAST(`id` as char),vGroupIdStr);
/* right>=目标节点原right值 and right<移动节点原right值,并且不是小树杈上的节点*/
UPDATE `tree_node`
SET `rightId`=`rightId`+ vDiff
WHERE `treeId` = vTreeId
AND `rightId`>=vTargetRightId AND `rightId`< vRightId
AND NOT FIND_IN_SET(CAST(`id` as char),vGroupIdStr);
END IF;
/* 成功,这个费劲啊 */
COMMIT;
SET resultCode = 1000;
SET resultMsg = "成功";
END |
(6)编辑节点
DROP Procedure IF EXISTS `editTreeNode` ;
DELIMITER |
/*
* nodeId,编辑的节点ID
* pid,移动到的目标节点ID
* code,节点代码
* name,节点名称
* resultCode,结果代码,
* resultMsg,结果消息
*/
CREATE PROCEDURE `editTreeNode`(nodeId int,pid int,code varchar(50),name varchar(50),OUT resultCode int,OUT resultMsg varchar(50))
ExitLabel:BEGIN
call moveTreeNode(nodeId,pid,resultCode,resultMsg);
IF resultCode=1000 THEN
/* 节点信息 */
UPDATE `tree_node`
SET `code` = code,`name` = name
WHERE `id`= nodeId;
COMMIT;
END IF;
END |
(7)同级节点排序(这个是后加的,没放一起试。前台把父id,子id逗号拼接Str)
DROP PROCEDURE IF EXISTS `sortTreeNode`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sortTreeNode`(vPid int,vSubNodeIdStr varchar(2000),OUT resultCode int,OUT resultMsg varchar(50))
ExitLabel:BEGIN
/* 强烈注意,变量名不要与字段名相同 */
DECLARE vLeftId INT; /* 父节点的left */
DECLARE vRightId INT; /* 父节点的right */
DECLARE vTreeId INT; /*树ID*/
DECLARE vSubNodeId INT; /* 子节点ID */
DECLARE vSubNodeLeftId INT; /* 子节点left */
DECLARE vSubNodeRightId INT; /* 子节点right */
DECLARE vDiff INT; /* 差值*/
DECLARE vNextLeftId INT; /* 下一个left值 */
DECLARE vNodePid INT; /* 子节点的父ID*/
DECLARE vGroupIdStr varchar(2000); /*待更新小树杈的节点列表*/
/* 父节点 */
SELECT `leftId`,`rightId`,`treeId`
INTO vLeftId,vRightId,vTreeId
FROM `product_sort_tree_node`
WHERE `id`= vPid;
/*父节点不存在*/
IF vLeftId IS NULL THEN
SET resultCode = 1002;
SET resultMsg = "指定的父节点不存在";
LEAVE ExitLabel;
END IF;
/*子节点ID列表为空*/
IF vSubNodeIdStr IS NULL OR LENGTH(vSubNodeIdStr)=0 THEN
SET resultCode = 1003;
SET resultMsg = "未指定子节点ID列表";
LEAVE ExitLabel;
END IF;
/*临时表*/
SET group_concat_max_len=200000; /*group_concat默认最大是1024,太小*/
DROP TEMPORARY TABLE if exists tmp_table;
CREATE TEMPORARY TABLE tmp_table
SELECT A.`id`,A.`leftId`,A.`rightId`,A.`pid`,
(
SELECT group_concat(CAST(B.`id` as char)) as idStr
FROM `product_sort_tree_node` as B
WHERE B.`treeId` = A.`treeId`
AND B.`leftId` between A.`leftId` AND A.`rightId`
) as idStr
FROM `product_sort_tree_node` as A
WHERE FIND_IN_SET(CAST(`id` as char),vSubNodeIdStr);
/*---------------开始更新-------------------------*/
START TRANSACTION;
/* 下一个left值 */
SET vNextLeftId = (vLeftId + 1);
while LENGTH(vSubNodeIdStr)>0 do
/*取串里的第一个ID,无逗号了,则返回整个串*/
SET vSubNodeId = SUBSTRING_INDEX(vSubNodeIdStr, ',', 1);
SET vSubNodeIdStr = SUBSTRING(vSubNodeIdStr,LENGTH(vSubNodeId)+2);
/* 子节点 */
SELECT `leftId`,`rightId`,`pid`,`idStr`
INTO vSubNodeLeftId,vSubNodeRightId,vNodePid,vGroupIdStr
FROM `tmp_table`
WHERE `id`= vSubNodeId;
/*父节点不符--还有一点,子节点ID列表必须包含了所有的子节点,省事就不check了。*/
IF vNodePid<>vPid THEN
SET resultCode = 1004;
SET resultMsg = "子节点的父节点与参数中的父节点不符";
ROLLBACK;
LEAVE ExitLabel;
END IF;
/* 旧left与新left的差值 */
SET vDiff = vSubNodeLeftId - vNextLeftId;
/* 更新该子节点的小树杈的left、right*/
if vDiff<>0 then
UPDATE `product_sort_tree_node`
SET `leftId`=`leftId` - vDiff,
`rightId`=`rightId` - vDiff
WHERE FIND_IN_SET(CAST(`id` as char),vGroupIdStr);
end IF;
/* 下一个left值 */
SET vNextLeftId = vSubNodeRightId - vDiff + 1;
end while;
/* 成功 */
COMMIT;
SET resultCode = 1000;
SET resultMsg = "成功";
END
;;
------------------------用法--------------------------
由于tree_node存储了多颗树的节点,所以注意总是带上treeId这个条件。
(0)得到整个树形结构:
select id,concat(repeat("-",level),name),treeId,leftId,rightId
from tree_node
where treeId=1
order by leftId;
(1)获取节点的所有子孙节点:
---------------------------
如节点7的lft是1,rgt是4
select leftId,rightId from tree_node where id = 7;
select id,concat(repeat("-",level),name),treeId,leftId,rightId
from tree_node
where treeId=1 and leftId between 1 and 4
order by leftId;
----------------------------
(2)获取节点所在路径:
---------------------------
如节点8的lft是2,rgt是3
select leftId,rightId from tree_node where id = 8;
select id,concat(repeat("-",level),name),treeId,leftId,rightId
from tree_node
where treeId=1 and leftId<2 and rightId>3
order by leftId;
会得到从根节点到该节点的所有节点,拼接即可
---------------------------
(3)获取所有子孙节点的个数
个数= (right – left - 1) / 2
(4)存储过程的调用方法:
set @code=0;
set @msg= '';
call moveTreeNode(55,52,@code,@msg);
select @code,@msg;
(5)java中的调用方法:
CallableStatement callStmt = null;
try {
callStmt = conn.prepareCall("{call deleteTreeNode(?,?,?)}");
callStmt.setInt(1, nodeId);
callStmt.registerOutParameter(2, java.sql.Types.INTEGER);
callStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
callStmt.executeUpdate();
int code = callStmt.getInt(2);
String msg = callStmt.getString(3);
System.out.println(code+msg);
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != callStmt) {
callStmt.close();
}
}
用registerOutParameter方法注册输出参数即可。
应该根据返回的code值,决定是否报错,这里木有写~~
初步代码,简单测试了好像木有问题,有待进一步测试。
有问题敬请指出。