Postgres+JPA实现自关联父子表按子节点ID向上递归查父级

实现思路 #

针对固定id/name/id_parent结构的自关联层级表，直接使用PostgreSQL原生递归CTE实现从叶子节点到根节点的路径遍历，避免全表扫描；封装为通用PL/pgSQL表函数，一次创建即可复用于所有同结构层级表，不需要为每张表单独写递归逻辑。

核心逻辑：

• 锚点层直接匹配传入的所有目标叶子节点
• 递归层通过id = 上一层的id_parent规则向上遍历，直到碰到根节点（id_parent为null）自动终止
• 最终结果去重，避免多叶子节点共享祖先节点时出现重复记录

通用函数创建 #

执行以下SQL创建全局可用的表函数：

CREATE OR REPLACE FUNCTION get_hierarchy_upwards(
    target_table TEXT,
    leaf_ids BIGINT[]
)
RETURNS TABLE (
    id BIGINT,
    name TEXT,
    id_parent BIGINT
) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'WITH RECURSIVE hierarchy_path AS (
            SELECT t.id, t.name, t.id_parent
            FROM %I t
            WHERE t.id = ANY($1)
            UNION
            SELECT t.id, t.name, t.id_parent
            FROM %I t
            INNER JOIN hierarchy_path hp ON t.id = hp.id_parent
        )
        SELECT DISTINCT id, name, id_parent FROM hierarchy_path',
        target_table, target_table
    ) USING leaf_ids;
END;
$$ LANGUAGE plpgsql STABLE;

函数参数说明：

• target_table：要查询的自关联层级表表名，比如示例中的company
• leaf_ids：目标叶子节点的ID数组，比如示例场景传入ARRAY[4,7]

返回值固定为id/name/id_parent三字段，和所有同构层级表结构完全匹配，标记为STABLE类型可以让PostgreSQL对执行计划做优化，查询性能更高。 #

调用示例 #

以给出的Company表场景为例，执行以下SQL即可拿到所有路径节点：

SELECT * FROM get_hierarchy_upwards('company', ARRAY[4,7]);

返回结果集如下，完全覆盖需要的路径节点，没有多余数据：

拿到结果集后，Java层只需要做一次id_parent的分组映射，就能快速组装成目标层级树，不需要额外的数据库查询。 #

JPA适配方式 #

在JPA环境下可以直接通过原生SQL调用该函数，不需要额外的依赖配置：

// 在对应Repository接口中定义方法
@Query(value = "SELECT * FROM get_hierarchy_upwards(:tableName, :leafIds)", nativeQuery = true)
List<HierarchyNodeDTO> queryHierarchyUpwards(
    @Param("tableName") String tableName, 
    @Param("leafIds") Long[] leafIds
);

如果部分JPA版本对表名作为入参支持有限，可以直接把递归CTE作为SQL模板，替换表名后执行，逻辑和函数完全一致。 #

性能注意事项 #

• 所有自关联表必须给id_parent字段创建B-tree索引，递归关联的性能会有数量级提升
• 该方案从叶子节点出发向上遍历，不会扫描任何无关节点，百万级数据量下只要层级深度不超过1000层，查询耗时基本在毫秒级，远优于全量加载后内存过滤的方案
• crosstab是行转列函数，本身不适合递归层级遍历场景，不需要在这类需求里使用

内容的提问来源于stack exchange，提问作者varijkapil13