I am interested in using Hibernate to query a table that is a Linked List. One row in my database represents a client. This client can have a parent client and so on. The Linked List relationship is a one to many. One parent can have many children, but one child can have only one parent. The parent may be null.

I really want to query the database with one clientID and then retrieve the client and all the children clients.

The query seems strait-forward for the reverse... Having a child and pulling all the parent records. I am confused on how to traverse the tree going the opposite direction.

Here is an example of the table that I would use:

Client Table:
clientID int
description String
parentID int (This field represents the parent client and may be null if there is no parent client)

Example Data:


I would really like to do a search for clientID = 2 and get
both the Fred's Hamburger Joint Swing and Lola's Swing Shift or search with ClientID = 1 and get all records in this case.

Hi,

If you have already a mapping for this table Client that works, this hql query should work

Thanks for the query. That will work for clients at the 2nd level of the tree. But in some instances, the children will have children, and they may have children, etc... I don't believe this query would work in that case.

OK I see, it's a kind of recursive sql query
Do you have the sql query already ?