HQL是否支持do...while循环？如何获取分类的所有父/子分类？

Hey there! Let's break this down for you step by step.

First off, to answer your quick question: HQL does NOT have do...while (or any loop structures). HQL is a declarative query language focused on querying object models, not executing imperative logic like loops. So we need to use either recursive database queries (if your DB supports it) or handle the looping logic in your Java code instead.

Now let's tackle your main goal: fetching all parent categories (up to the root where parent is null) and all child categories (down to the leaf where child is null) for your myCategory.

1. Fetching All Parent Categories #

You have two solid approaches here:

Option 1: Recursive CTE Query (Recommended if your database supports it)

Most modern databases (like MySQL 8+, PostgreSQL, SQL Server) support Common Table Expressions (CTEs) with recursion. Here's how to write the HQL for this:

WITH RECURSIVE parent_hierarchy AS (
    -- Start with the target category
    SELECT c FROM Category c WHERE c = :myCategory
    UNION ALL
    -- Recursively fetch each parent category
    SELECT p FROM parent_hierarchy ph JOIN ph.parentCategory p
)
-- Exclude the original category itself to get only parent levels
SELECT ph FROM parent_hierarchy ph WHERE ph != :myCategory

This query starts with myCategory, then keeps joining to its parent category until there's no parent left (the JOIN will return no rows when parentCategory is null).

Option 2: Loop in Java Code

If your database doesn't support recursive CTEs, you can handle the loop directly in your Java code. Just keep fetching the parent category until you hit null:

List<Category> allParentCategories = new ArrayList<>();
Category currentParent = myCategory.getParentCategory();

while (currentParent != null) {
    allParentCategories.add(currentParent);
    // If parentCategory is lazily loaded, use HQL to fetch the full object to avoid exceptions
    currentParent = session.createQuery(
            "SELECT c FROM Category c WHERE c.id = :categoryId", Category.class)
            .setParameter("categoryId", currentParent.getId())
            .uniqueResult();
}

Note: If parentCategory is marked as lazy-loaded, you'll need to explicitly fetch it via HQL (as shown) or initialize the proxy to avoid LazyInitializationException.

2. Fetching All Child Categories #

The logic is similar, just reversed to traverse down the child chain:

Option 1: Recursive CTE Query

WITH RECURSIVE child_hierarchy AS (
    -- Start with the target category
    SELECT c FROM Category c WHERE c = :myCategory
    UNION ALL
    -- Recursively fetch each child category
    SELECT ch FROM child_hierarchy ch JOIN ch.childCategory ch WHERE ch.childCategory IS NOT NULL
)
-- Exclude the original category itself to get only child levels
SELECT ch FROM child_hierarchy ch WHERE ch != :myCategory

This will keep fetching the child category until childCategory is null.

Option 2: Loop in Java Code

List<Category> allChildCategories = new ArrayList<>();
Category currentChild = myCategory.getChildCategory();

while (currentChild != null) {
    allChildCategories.add(currentChild);
    // Again, handle lazy loading if needed
    currentChild = session.createQuery(
            "SELECT c FROM Category c WHERE c.id = :categoryId", Category.class)
            .setParameter("categoryId", currentChild.getId())
            .uniqueResult().getChildCategory();
}

Key Takeaway #

HQL doesn't support imperative loops like do...while because it's designed to query data, not execute procedural logic. For recursive traversal, use database-side recursive CTEs (if available) for efficiency, or handle the loop in your Java code if you need compatibility with older databases.

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