Initialize all entities at once in a tree structure with hibernate

Question

I have a tree structure that I need to convert to a DTO starting from root node. Currently I query the root node with JPA findById and I have to traverse the whole tree through a DFS to convert each node to a DTO, resulting in a lot of SQL selects, one for each time I call getChildren. It get even worse with the other associations that I also need to retrieve.

I would like to initialize the whole tree at once, with every associations also loaded, then do my DFS without worrying about triggering too much selects.

The node entity looks like this.

import jakarta.persistence.*;
import org.hibernate.annotations.BatchSize;

import java.util.LinkedHashSet;
import java.util.Set;

@Entity
public class TreeNode {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "treenode_seq")
    @SequenceGenerator(name = "treenode_seq")
    @Column(name = "id", nullable = false)
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_id")
    private TreeNode parent;

    @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @BatchSize(size = 100)
    private Set<TreeNode> children = new LinkedHashSet<>();

    @OneToMany(mappedBy = "treeNode", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @BatchSize(size = 100)
    private Set<Association> associations = new LinkedHashSet<>();

    // Getters and setters
}

As you can see it also has an oneToMany association, the association itself has another one to many association resulting in multiple n+1 selects while traversing the tree.

And this is the DTO

import java.util.List;

public class TreeNodeDTO {
    private Long id;
    private Long parentId;
    private List<TreeNodeDTO> children;
    private List<AssociationDTO> associations;

    //getters and setters
}

I thinked about doing a CTE query with inner joins but when I select the root node, only direct children are initialized. It seems to make batching more efficient because all treeNode are in persistence context before I traverse the tree.

@Query(value = "WITH RECURSIVE TreeCTE AS (" +
            "  SELECT * FROM tree_node WHERE id = :parentId" +
            "  UNION ALL" +
            "  SELECT t.* FROM tree_node t JOIN TreeCTE c ON c.id = t.parent_id" +
            ")" +
            "SELECT DISTINCT t.* FROM TreeCTE t where t.id = :parentId INNER JOIN tree_node tchildren on tchildren.parent_id = t.id", nativeQuery = true)
    TreeNode findTreeWithChildren(@Param("parentId") Long parentId);

How can I initialize all my tree (including other associations in the object graph) before I traverse it with a DFS, so accessing unloaded entities and collections won't cause database roundtrips?

It will be really hard to solve your problem just by using native CTE query in JPA. I am not sure if you already have come across this link https://stackoverflow.com/questions/3638082/recursive-jpa-query but do check this once. It provides one solution of Blaze-Persistence or considering changing the data model for almost same problem. — rahulP, Aug 02 '23 at 16:50
JPQL queries with fetch joins instead of CTE queries would probably solve the problem, I don't think CTE queries would be even more efficient because you somehow have to make sure it recursively initializes the children all the same — banan3'14, Aug 08 '23 at 08:09
I don't think Hibernate has this ability, as there is no way to know that a TreeNode's children are all in memory or which ones they really are without a DB query to find them. EclipseLink, with forced (or only) in-memory queries, could be configured to handle this in a single query, but I don't know that JPA or other providers can support it. If you are going to attempt it, you are better off reading in TreeNode instances and having the parent_id column be a basic mapping that you can sort and arrange yourself. — Chris, Aug 08 '23 at 14:27

score 0 · Answer 1 · answered Aug 09 '23 at 19:51

0

Which database are you using? If using oracle you can do

SELECT * 
FROM tree_node 
CONNECT BY PRIOR parent_id = id
START WITH parent_id = :parentId

answered Aug 09 '23 at 19:51

lance-java

25,497
4
59
101

Initialize all entities at once in a tree structure with hibernate

1 Answers1