SQL 包含的 HQL 等效项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/592147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HQL Equivalent of SQL Contains
提问by Brian
I'm trying to write an HQL query to select objects which contain an object in a child collection.
我正在尝试编写一个 HQL 查询来选择包含子集合中的对象的对象。
Example:
例子：
Contest Object
比赛对象
ContestID
ContestName
RequiredCountries -> one to many collection of Country objects
Country Object
国家对象
CountryCode
CountryName
The sql equivalent of what i want:
相当于我想要的 sql：
SELECT * FROM CONTEST C
WHERE C.CONTESTID IN(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON
CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA')
OR
或者
SELECT * FROM CONTEST C
WHERE EXISTS(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON
CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA' AND CC.CONTESTID=C.CONTESTID)
I have this hql, which works, but seems like not a good solution-
我有这个 hql，它有效，但似乎不是一个好的解决方案-
from Contest C
where (from Country where CountryCode = :CountryCode) = some elements(C.RequiredCountries)
I also consider joining with Country, but since I don't have an object class to represent the relationship, I wasn't sure how to join in HQL.
我也考虑加入 Country，但由于我没有对象类来表示这种关系，我不确定如何加入 HQL。
Anyone have any ideas or suggestions? This should be easy.
任何人有任何想法或建议？这应该很容易。
回答by Maksym Gontar
try this:
尝试这个：
from Contest Ct, Country Cr
where Cr.CountryCode = :CountryCode
and Cr.Country in elements(Ct.RequiredCountries)
回答by ishi
The previous one will work (at least it works for me --- I'm using hibernate), but the 'proper way' is the 'member of' operator... like this:
前一个可以工作（至少它对我有用——我正在使用休眠），但“正确的方法”是“成员”运算符......像这样：
select ...
from Contest Ct, Country Cr
where Cr.CountryCode = :CountryCode
and Cr.Country member of Ct.RequiredCountries
(see http://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvk)
（参见http://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvk）
The elements() is HQL extension, I think. I think it's better to use the standard (JPQL) whenever possible.
我认为 elements() 是 HQL 扩展。我认为最好尽可能使用标准（JPQL）。
