I’m looking for assistance in finding an efficient way to store and access hierarchal objects in Hibernate.

I have a class Post, Post has a List of other post objects. The Post class has a body, children, id, parentId and rootId fields. When I store these in the DB I have a single table called Post which has columns for Body, id, parentId and rootId.

Every new Post object has the potential of being the root ancestor of an ever expanding subset of child Posts.

Using a one-to-many relationship on the children attribute (the attribute is a list) I can store and retrieve these with hibernate, the problem is that the number of queries it performs to retrieve one of these hierarchies is unacceptable. I’d like to be able to read an entire hieratical branch with a single query.

For that reason I store the rootId on each Post. This way the DB has the information necessary to unify the group of posts on a per hierarchy basis, the problem I have now is trying to form HQL to perform this lookup. Querying the DB for all of the Posts matching a provided rootId is easy enough the problem is the retrieved classes are flat and when you access the child nodes recursively it queries the DB for the children.

Does anyone have any thoughts on how this could be done?

So I guess the "materialized path" pattern is the answer to my question. Does anyone know if there is anything in hibernate to automate that? Google hasn't lead me to much but i did find an article from a few years ago that someone wrote sugesting that hibernate add new annotations for hierarchy but i dont think that has happened.