How can I use Hibernate Criteria's to query nested tables?
- by cbmeeks
I've looked all over SO and Google but I guess I'm not using the right search terms or something.
Anyway, say I have three tables:
Companies
-----------------------------------------
id
name
user_id
Users
-----------------------------------------
id
username
usertype_id
UserTypes
-----------------------------------------
id
typeofuser
So ACME would be a company, it would have a user Moe and Moe would be a usertype of Stooge.
In SQL, I would do something like:
select
*
from companies c
join users u on (u.id = c.user_id)
join usertypes ut on (ut.id = u.usertype_id)
where
ut.typeofuser = 'Stooge'
But I can't seem to figure out how to do that in a Criteria. I have tried:
Criteria crit = io.getSession().createCriteria(Company.class);
List<Company> list =
crit.createCriteria("users")
.createCriteria("usertypes")
.add(Restriction.eq("typeofuser", "Stooge").list();
But I get back way too many records. And the results don't even come close to being accurate.
I've also tried:
Criteria crit = io.getSession().createCriteria(Company.class);
List<Company> list =
crit.createAlias("users", "u")
.createAlias("u.usertypes", "ut")
.add(Restriction.eq("ut.typeofuser", "Stooge").list();
Seems to bring back the exact same result set. I actually have read the user manual. And when I nest only one level deep (ie, searching by users is fine) but when I get two layers deep, I can't quite get it. And the manual is no help. I just can't relate cats and kittens to business objects. Maybe they should use cats, kittens and fleas? :-/
Thanks for any suggestions.
