现有实体类定义 #

@Entity
public class Product {
    @Id
    @Column(unique = true)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String title;
    @OneToOne
    private Brand brand;
    @OneToOne(fetch = FetchType.EAGER)
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Category category;
}

@Entity
public class Category {
    @Id
    @Column(unique = true)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @NotBlank(message = "Please enter category name!")
    @Length(max = 50, message = "Maximum size exceeded!")
    private String name;
    @OneToOne
    private Category parent;
}

问题描述 #

是否可以创建递归查询，遍历商品所属分类的所有父级分类？例如，当目标分类为“For Woman”时，遍历所有分类的父级链，若其中存在匹配该分类的项，则返回对应分类下的商品。当前的Java实现逻辑如下，希望用递归查询替代：

List<ProductOption> productOptions = new ArrayList<>();
Category mainCategory = categoryRepository.findById(id);
for(Category category : categoryRepository.findAll()){
    Category cat = category;
    while(cat!=null){
        if(cat==mainCategory){
            productOptions.addAll(productOptionRepository.findAllByProduct_CategoryId(category.getId()));
        }
        cat=cat.getParent();
    }
}

解决方案 #

当然可以用递归查询替代客户端循环的方式，把逻辑转移到数据库层面能显著提升性能，尤其是数据量较大的时候。下面提供几种可行的实现方案：

1. 基于CTE的JPQL递归查询（适用于PostgreSQL、MySQL 8+等支持CTE的数据库） #

通过CTE（公共表表达式）可以直接在数据库层面递归找出所有属于目标分类父级链的分类，再关联查询对应的商品选项。

首先在CategoryRepository中添加方法获取所有关联分类：

@Query(value = "WITH RECURSIVE category_hierarchy AS (" +
               "    SELECT id, parent_id FROM category WHERE id = :targetId " +
               "    UNION ALL " +
               "    SELECT c.id, c.parent_id FROM category c " +
               "    JOIN category_hierarchy ch ON c.parent_id = ch.id" +
               ") " +
               "SELECT * FROM category_hierarchy", nativeQuery = true)
List<Category> findAllSubCategoriesInHierarchy(@Param("targetId") Integer targetId);

然后用这个结果查询商品选项：

Category mainCategory = categoryRepository.findById(id)
        .orElseThrow(() -> new RuntimeException("Target category not found"));
List<Category> relevantCategories = categoryRepository.findAllSubCategoriesInHierarchy(mainCategory.getId());
List<Integer> categoryIds = relevantCategories.stream()
        .map(Category::getId)
        .collect(Collectors.toList());
List<ProductOption> productOptions = productOptionRepository.findAllByProduct_CategoryIdIn(categoryIds);

2. Spring Data JPA派生查询+自定义递归（兼容更多数据库） #

如果你的数据库不支持CTE（比如MySQL 5.x），可以在Repository层实现递归逻辑，性能虽不如数据库端递归，但比客户端循环更高效：

在CategoryRepository中添加递归方法：

default List<Category> findAllSubCategoriesRecursively(Category targetCategory) {
    List<Category> result = new ArrayList<>();
    result.add(targetCategory);
    // 查询所有以当前分类为父级的子分类
    List<Category> children = findByParent(targetCategory);
    for (Category child : children) {
        result.addAll(findAllSubCategoriesRecursively(child));
    }
    return result;
}

// 派生查询方法，自动生成SQL
List<Category> findByParent(Category parent);

同样收集分类ID后查询商品选项：

Category mainCategory = categoryRepository.findById(id)
        .orElseThrow(() -> new RuntimeException("Target category not found"));
List<Category> relevantCategories = categoryRepository.findAllSubCategoriesRecursively(mainCategory);
List<Integer> categoryIds = relevantCategories.stream()
        .map(Category::getId)
        .collect(Collectors.toList());
List<ProductOption> productOptions = productOptionRepository.findAllByProduct_CategoryIdIn(categoryIds);

3. 一步到位的关联查询 #

你也可以直接写一个JPQL查询，一次性获取所有符合条件的商品选项，省去中间查询分类的步骤：

@Query(value = "WITH RECURSIVE category_hierarchy AS (" +
               "    SELECT id FROM category WHERE id = :targetId " +
               "    UNION ALL " +
               "    SELECT c.id FROM category c " +
               "    JOIN category_hierarchy ch ON c.parent_id = ch.id" +
               ") " +
               "SELECT po FROM ProductOption po " +
               "JOIN po.product p " +
               "WHERE p.category.id IN (SELECT id FROM category_hierarchy)", nativeQuery = false)
List<ProductOption> findAllByCategoryHierarchy(@Param("targetId") Integer targetId);

调用时直接传入目标分类ID即可：

List<ProductOption> productOptions = productOptionRepository.findAllByCategoryHierarchy(id);

注意事项 #

• 建议给category.parent_id字段建立索引，提升递归查询的性能。
• 务必处理categoryRepository.findById(id)的空值情况，避免空指针异常。
• 如果使用MySQL 5.x，优先选择第二种方案，因为它不支持CTE语法。

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