查询限制关联的实体 [英] Query for restricting associated entities

问题描述

我希望形成一个查询，其中关联的集合已被
限制，理想情况下使用Hibernate Criteria或HQL，但是我希望
对如何在SQL中执行此操作感兴趣。例如，假设我有一个Boy
类，它与Kites类具有双向一对多关联。
我想得到一个列表，其中的风筝的长度在一个范围内。

I would like to form a query where an associated collection has been restricted, ideally with Hibernate Criteria or HQL, but I'd be interested in how to do this in SQL. 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.

问题是，我所知道的HQL /标准只会让我Boy用这两个
例子（HQL是猜测）给出了一个完整（无限制）的风筝集合
。也就是说，我得到了在正确范围内有风筝
的男孩，但是对于每个这样的男孩，我都会购买风筝的所有，而不是
只是范围内的那些风筝。 / p>

The problem is that the HQL/Criteria I know only gets me Boy objects with a complete (unrestricted) Set of Kites, as given in these two examples (the HQL is a guess). I.e., I get the Boys who have Kites in the right range, but for each such Boy I get all of the Kites, not just the ones in the range.

select new Boy(name) from Boy b 
       inner join Kite on Boy.id=Kite.boyId 
             where b.name = "Huck" and length >= 1;

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();

现在我必须得到正确的风筝长度集合的唯一方法是
来迭代通过男孩名单和每个人重新查询范围内的风筝
。我希望一些SQL / HQL / Criteria向导
知道更好的方法。我更喜欢得到一个Criteria解决方案，因为我的
真正的Boy构造函数有很多参数，如果有初始化的Boys，那将是方便的
。

Right now the only way I have to get the correct Kite length Sets is to iterate through the list of Boys and for each one re-query Kites for the ones in the range. I'm hoping some SQL/HQL/Criteria wizard knows a better way. I'd prefer to get a Criteria solution because my real "Boy" constructor has quite a few arguments and it would be handy to have the initialized Boys.

我的底层数据库是MySQL。不要以为我对
SQL或Hibernate有很多了解。感谢！

My underlying database is MySQL. Do not assume that I know much about SQL or Hibernate. Thanks!

推荐答案

事实证明，最好通过反转连接完成：

It turns out that this is best done by reversing the join:

Criteria crit = session.createCriteria(Kite.class);
crit.add(Restrictions.ge("length", new BigDecimal(1.0))
.createCriteria("boy")
.add(Restrictions.eq("name", "Huck")));
List<Kite> list = crit.list();

请注意，列表中的风筝需要汇总到Boys中，这
可以轻松完成与一个HashMap。

Note that the list's Kites need to be aggregated into Boys, this can be done easily with a HashMap.

这篇关于查询限制关联的实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！