如何获取树形结构中的叶子节点？SQL及JPA实现方案求助

SQL 解决方案（基于CTE） #

核心逻辑是先递归获取指定节点的所有后代，再筛选出无任何子节点的节点（即叶子节点）。假设表名为node，示例如下：

查询节点0的叶子节点 #

WITH RECURSIVE descendants AS (
    -- 起始节点：目标根节点
    SELECT id, parentId
    FROM node
    WHERE id = 0
    UNION ALL
    -- 递归遍历所有后代
    SELECT n.id, n.parentId
    FROM node n
    INNER JOIN descendants d ON n.parentId = d.id
)
-- 过滤出没有子节点的后代
SELECT d.id
FROM descendants d
WHERE NOT EXISTS (
    SELECT 1
    FROM node n
    WHERE n.parentId = d.id
);

替换WHERE id = 0为WHERE id = 1即可查询节点1的叶子节点。

JPA 解决方案 #

方案1：原生SQL递归查询（推荐大数据量场景） #

如果使用Hibernate等支持递归CTE的JPA提供者，可通过原生SQL实现：

@Repository
public interface NodeRepository extends JpaRepository<Node, Long> {

    @Query(value = "WITH RECURSIVE descendants AS (" +
            "SELECT id, parent_id FROM node WHERE id = :rootId " +
            "UNION ALL " +
            "SELECT n.id, n.parent_id FROM node n " +
            "INNER JOIN descendants d ON n.parent_id = d.id) " +
            "SELECT d.id FROM descendants d " +
            "WHERE NOT EXISTS (SELECT 1 FROM node n WHERE n.parent_id = d.id)",
            nativeQuery = true)
    List<Long> findLeafNodeIdsByRootId(@Param("rootId") Long rootId);
}

方案2：实体关联递归遍历（适合小数据量） #

若实体类定义了子节点关联：

@Entity
@Table(name = "node")
public class Node {
    @Id
    private Long id;
    private Long parentId;
    
    @OneToMany(mappedBy = "parentId", fetch = FetchType.LAZY)
    private List<Node> children;
    
    // getter、setter 省略
}

编写递归方法收集叶子节点：

@Service
public class NodeService {
    @Autowired
    private NodeRepository nodeRepository;

    public List<Node> findLeafNodesByRootId(Long rootId) {
        Node root = nodeRepository.findById(rootId)
                .orElseThrow(() -> new IllegalArgumentException("指定节点不存在"));
        List<Node> leafNodes = new ArrayList<>();
        collectLeafNodes(root, leafNodes);
        return leafNodes;
    }

    private void collectLeafNodes(Node node, List<Node> leafNodes) {
        if (node.getChildren() == null || node.getChildren().isEmpty()) {
            leafNodes.add(node);
            return;
        }
        node.getChildren().forEach(child -> collectLeafNodes(child, leafNodes));
    }
}

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