Hi All,

Here is what I have

Table1 = Item
Table2 = WorkspaceMember
Table3 = Trustee

Item.java, WorkspaceMember.java and Trustee.java exist along with their XML files.

Item has 1..M WorkspaceMember which has 1..M Trustees

I want to construct a findByExample query that will find all Items which contain Trustee x

Here is the SQL as I am seeing it.

select a.* from Item a, WorkspaceMember b, Trustee c
where a.rID = b.parentID
and b.trusteeID = x

How do I structure the sequence of calls. Do I use Criteria? How do I build the join up.

Just an opinion, but you really need to move from thinking about db joins to thinking in terms of objects. You don't really show your object fields or relationships, but let's say that Item has a members field that holds a set of WorkspaceMember objects and WorkfspaceMember has a trustees member that holds a set of Trustee objects.

One approach would be to query in HQL, something like this:

"SELECT * FROM Item WHERE members.trustees.trusteeID = :tid"

Another approach would be to use Criteria, adding the path. Something like this:

Criteria c = session.createCriteria(Item.class)
.createAlias("members", "member")
.createAlias("trustees", "trustee")
.add(Restriction.eq("member.trustee.trusteeID", id);

Just take a look at the doc on the Criteria interface for examples.