Mapping the result of a recursive query in Hibernate-CodePudding

Let's assume we have a very basic entity, say Category. Each category can have zero or more sub-categories (field subcategories of type Set<Category>) . I want to fetch an entire tree of categories starting at a given root. For this I use a common table expression which returns the categories that make up the tree (I tested it separately and it works fine).

Is there a way to automatically map this result to just one root Category including all subcategories present at any level? I know I can just process the tuples manually, but I'm curious if there is a more elegant way. It seems like @SqlResultSetMapping or entity graphs wouldn't do the trick.

Some simplified code below. First, the category entity looks like this :

@Entity
@Table(name = "CATEGORY")
public class Categoryd {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "C_I_IDF")
    protected Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="C_PARENTCATEGORY")
    private CategoryEntity parentCategory;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "parentCategory", cascade = { CascadeType.MERGE, CascadeType.REMOVE })
    @Fetch(value = FetchMode.SELECT)
    @BatchSize(size=10)
    private List<CategoryEntity> subCategories = new ArrayList<>();

}

My recursive query is something like this :

WITH categoryrecursive (id) AS (
    SELECT C_I_IDF
    FROM CATEGORY parent
    WHERE parent.C_I_IDF = <some number>
    UNION ALL
    SELECT sub.C_I_IDF
    FROM categoryrecursive, CATEGORY sub
    WHERE sub.C_PARENTCATEGORY = categoryrecursive.id
)
SELECT cat.*
FROM categoryrecursive rc
INNER JOIN CATEGORY cat ON cat.C_I_IDF = rc.id

The query returns a list of categories starting at a given root.

CodePudding user response：

Since Hibernate has no knowledge (and you also can't tell it about it), that all subCategories are "loaded", you roughly only have one option that avoids messing with the object tree.

Configure @BatchSize(size = 1000) for subCategories with a batch size big enough (e.g. 1000), so that lazy loading can load all collections in one query. Then you can use @SqlResultSetMapping for the query you have and trigger the initialization of one collection, to initialize most/all collections with a single query (in addition to your recursive CTE query).

The downside here is that you need a second query, but if you don't want to reconstruct the tree yourself in memory, that's the best you can do.

I would recommend you to go the extra mile and implement the treeifying yourself with custom DTOs or even with just entities. Something like the following should work out:

List<Category> list = ...
for (Category c : list) {
    c.setSubCategories(new ArrayList<>());
}
Category root = null;
for (Category c : list) {
    if (c.getParentCategory() == null) {
        root = c;
        continue;
    }
    c.getParentCategory().getSubCategories().add(c);
}
return root;