Hibernate Criteria query with restricted associations
The use case here is to find some restricted set of Boys who have Kites with lengths in a certain range, and then optionally retrieve all of the Kites in that range belonging to those boys.
Do not assume that I know much about SQL or Hibernate.
I would like to form a Hibernate Criteria query where an associated collection has been restricted. For example, say I have a Boy class with a bidirectional one-to-many association to the Kites class. I want to get a List of the Boys whose kites' lengths are in a range.
I can get fields (but not Boys) satisfying this goal with an HQL query:
select Boy.name from Boy
inner join Kite on Boy.id=Kite.boyId
where length >= 1;
However this attempt with Criteria always returns list.size() of zero (even though Boy Huck has a range of kites with lengths 0.1-2.0):
Criteria crit = session.createCriteria(Boy.class);
crit.add(Restrictions.eq("name", "Huck"))
.createCriteria("kites")
.add(Restrictions.ge("length", new BigDecimal(1.0)));
List list = crit.list();
Ideally and additionally, for each boy in this list (assuming my code worked!) I would like all of the Kites in boy.getKites() to satisfy the length restriction. This second goal may be wishful thinking.
My guess at the problem with my Criteria attempt is that the join isn't correct: I'm seeing the ff., but don't know how to fix it.
Hibernate:
select
this_.id as id3_1_,
this_.name as name3_1_,
...
kites1_.id as id4_0_,
kites1_.boyId as boyid2_4_0_,
kites1_.length as length3_4_0_,
...
from
Boy this_
inner join
Kite kites1_
on this_.id=kites1_.id <--- Should be =boyid?
where
this_.name=?
and kites1_.length>=?
Try to change to
Criteria crit = session.createCriteria(Boy.class);
crit.add(Restrictions.eq("name", "Huck"))
.createAlias("kites", "k")
.add(Restrictions.ge("k.length", new BigDecimal(1.0)));
List list = crit.list();
or write explicitly in HQL:
Query query = HibernateUtil.getSession().createQuery("SELECT b FROM Boy b INNER JOIN b.kites k WHERE b.name=:name AND k.length>=:length");
query.setString("name", "Huck");
query.setBigDecimal("length", BigDecimal.ONE);
If it doesn't work, you have an issue in Boys-Kites mapping (hibernate beleive that join column in both tables is "id").
Thanks for the answers. This was really a two part question: why didn't my Criteria query work and how do I get restricted associated Sets. The first part is solved by fixing the join error I pointed out and FoxyBOA encouraged me to find.
The second, more interesting question I've re-asked here.
链接地址: http://www.djcxy.com/p/36974.html上一篇: Hibernate标准和行数限制