Hibernate Parent/Child SELECT N+1 issue

Question

Hi Folks (and Pascal),

I jave the following mapped superclass that provides a basic implementation for a parent/child self relationship to create a parent/child list for unlimited nesting of items (i.e. Categories)

@MappedSuperclass
public abstract class ParentChildPathEntity<N extends ParentChild> implements MaterializedPath<N> {


    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "parent_id") 
    private N parent;

    @Column(name = "name", unique = true)
    private String name;

    @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL)  
    private Set<N> children = new HashSet<N>();

If I load the entire table with fetch join on the parent and children, a single select loads all the records and i can happily traverse the tree. my problem comes in when i specify to retrieve a node on the tree. i want the node and all its children in a single select. below is the hql for loading the entire table:

hql.append(String.format("tree from %s tree ", tableName));
hql.append("left join fetch tree.parent ");     
hql.append("left join fetch tree.children ");

if i specify the node name, i.e.:

where tree.name = :name

then hibernate retrieves the node, but when i access the children i get the SELECT N+1 issue. I realize why this is happening, (because of the tree.name = :name) but is there a way to write the HQL so it loads the specified node and all its children?

I'm just trying to figure out a way to support a simple nested item's list where i can retrieve any parent node and its children with a single select

thanks in advance,

Answer 1

Have you tried using the @BatchSize annotation?

@BatchSize(size = 20)

Ex:

@OneToMany(mappedBy = ..., fetch = FetchType.LAZY)
@BatchSize(size = 20)
public SortedSet<Item> getItems() { ... }

Then, if you specify the join to children in your HQL, you should be able to avoid n+1 select. I am not sure, offhand, if there is a way to specify the batch size in the HQL statement.