java + mysql 查询树形数据

太过爱你忘了你带给我的痛 2022-12-29 14:26 337阅读 0赞

1.支持根据子级 查询到 其所有父级  
2.支持根据父级查询到所有子级  
2.支持根据父级分页

# 1.创建存储函数 #

# 根据父级查所有子级
    CREATE  FUNCTION `queryChildrensId`(orgid char(255)) RETURNS varchar(4000) CHARSET utf8
    BEGIN
    DECLARE oTemp VARCHAR(4000);
    DECLARE oTempChild VARCHAR(4000);
     
    SET oTemp = '';
    SET oTempChild = orgid;
     
    WHILE oTempChild IS NOT NULL
    DO
    SET oTemp = CONCAT(oTemp,',',oTempChild);
    SELECT GROUP_CONCAT(pro_id) INTO oTempChild FROM pro_project_info WHERE FIND_IN_SET(parent_id,oTempChild) > 0 and status != -1;
    END WHILE;
    RETURN oTemp;
    END

# 根据子级查询所有父级
    CREATE DEFINER=`company-manage`@`%` FUNCTION `queryParentsId`(rootId varchar(100)) RETURNS varchar(1000) CHARSET latin1
    BEGIN   
    DECLARE fid varchar(100) default '';   
    DECLARE str varchar(1000) default rootId;   
      
    WHILE rootId is not null  do   
        SET fid =(SELECT parent_id FROM pro_project_info WHERE pro_id = rootId);   
        IF fid is not null and fid != -1 THEN   
            SET str = concat(str, ',', fid);   
            SET rootId = fid;   
        ELSE   
            SET rootId = fid;   
        END IF;   
    END WHILE;   
    return str;  
    END

# 2.sql #

SELECT
    	a.*
    FROM
    	pro_project_info a
    	JOIN (
    SELECT
    	a.pid,
    	GROUP_CONCAT( a.pids ) pids 
    FROM
    	(
    SELECT
    	a.*,
    	concat( queryParentsId ( a.pro_id ), queryChildrensId ( a.pro_id ) ) pids,
    	SUBSTRING_INDEX( queryParentsId ( a.pro_id ), ',', - 1 ) pid 
    FROM
    	pro_project_info a 
    WHERE
    	a.STATUS != - 1 
    	) a 
    WHERE
    	a.pid IN ( SELECT pro_id FROM pro_project_info WHERE STATUS != - 1 AND parent_id = - 1 ORDER BY update_time DESC ) 
    GROUP BY
    	a.pid 
    ORDER BY
    	a.update_time DESC 
    	LIMIT 0,
    	10 
    	) d ON FIND_IN_SET( a.pro_id, d.pids ) 
    GROUP BY
    	a.pro_id 
    ORDER BY
    	a.update_time DESC;

# 3.将结果封装成树形数据 #

/** * - listToTree * - <p>方法说明<p> * - 将JSONArray数组转为树状结构 * - @param arr 需要转化的数据 * - @param id 数据唯一的标识键值 * - @param pid 父id唯一标识键值 * - @param child 子节点键值 * - @return JSONArray */
        public static JSONArray listToTree(JSONArray arr, String id, String pid, String child) { 
            JSONArray r = new JSONArray();
            JSONObject hash = new JSONObject();
            //将数组转为Object的形式，key为数组中的id
            for (int i = 0; i < arr.size(); i++) { 
                JSONObject json = (JSONObject) arr.get(i);
                hash.put(json.getString(id), json);
            }
            //遍历结果集
            for (int j = 0; j < arr.size(); j++) { 
                //单条记录
                JSONObject aVal = (JSONObject) arr.get(j);
                //在hash中取出key为单条记录中pid的值
                JSONObject hashVP = (JSONObject) hash.get(aVal.get(pid).toString());
                //如果记录的pid存在，则说明它有父节点，将她添加到孩子节点的集合中
                if (hashVP != null) { 
                    //检查是否有child属性
                    if (hashVP.get(child) != null) { 
                        JSONArray ch = (JSONArray) hashVP.get(child);
                        ch.add(aVal);
                        hashVP.put(child, ch);
                    } else { 
                        JSONArray ch = new JSONArray();
                        ch.add(aVal);
                        hashVP.put(child, ch);
                    }
                } else { 
                    r.add(aVal);
                }
            }
            return r;
        }