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 ;

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 |

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 |

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 |

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 |

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 |

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
;;

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();
    }
}