java 使用 Hibernate Criteria 查询多对多关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/264339/
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
Querying ManyToMany relationship with Hibernate Criteria
提问by Daniel Alexiuc
I'm not sure how to describe this problem, so I think an example is the best way to ask my question:
我不知道如何描述这个问题，所以我认为一个例子是问我问题的最好方法：
I have two tables with a manyToMany relationship:
我有两个具有 manyToMany 关系的表：
DriversLicence <-> LicenceClass
DriversLicence <-> LicenceClass
LicenceClass is things like "Car", "Motorbike", and "Medium Rigid".
LicenseClass 是诸如“汽车”、“摩托车”和“中刚性”之类的东西。
Using Hibernate Criteria, how can I find all licences that have both "Car" and "Motorbike" LicenceClasses?
使用 Hibernate Criteria，如何找到同时具有“Car”和“Motorbike”LicenceClasses 的所有许可证？
UPDATE 12/11/2008 I have discovered that this can easily be achieved by using a custom ResultTransformer. However the problem is that a result transformer only gets applied AFTER the query returns its results, it does not actually become part of the SQL. So I guess my question is now "Can you do what I initially described in SQL - and is there a Hibernate Criteria analog?"
2008 年 12 月 11 日更新我发现这可以通过使用自定义 ResultTransformer 轻松实现。然而问题是结果转换器只在查询返回结果后应用，它实际上并没有成为 SQL 的一部分。所以我想我现在的问题是“你能做我最初在 SQL 中描述的事情吗 - 是否有 Hibernate Criteria 模拟？”
采纳答案by Daniel Alexiuc
Here's how I finally achieved it using HQL:
这是我最终使用 HQL 实现的方法：
public List<DriversLicence> findDriversLicencesWith(List<LicenceClass> licenceClasses) {
String hqlString = "select dl from DriversLicenceImpl dl where 1=1 ";
for (int i = 0; i < licenceClasses.size(); i++) {
hqlString += " and :licenceClass" + i + " = some elements(dl.licenceClasses)";
}
Query query = getSession().createQuery(hqlString);
for (int i = 0; i < licenceClasses.size(); i++) {
query.setParameter("licenceClass" + i, licenceClasses.get(i));
}
return query.list();
}
Or using Hibernate Criteria with an sqlRestriction:
或者使用带有 sqlRestriction 的 Hibernate Criteria：
for (LicenceClass licenceClass : licenceClasses) {
criteria.add(Restrictions.sqlRestriction("? = some(select " + LicenceClass.PRIMARY_KEY + " from " +
LICENCE_CLASS_JOIN_TABLE + " where {alias}." +
DriversLicence.PRIMARY_KEY + " = " + DriversLicence.PRIMARY_KEY + ")",
licenceClass.getId(), Hibernate.LONG));
}
LICENCE_CLASS_JOIN_TABLE is the name of the table that hibernate generates to support the many-to-many relationship between driversLicence and LicenceClass.
LICENCE_CLASS_JOIN_TABLE 是hibernate 生成的表的名称，用于支持driversLicence 和LicenceClass 之间的多对多关系。
回答by Mara B
Another option is to chain joins (one join per each LicenseClass). I used criteria builder and predicates like this
另一种选择是链接联接（每个 LicenseClass 一个联接）。我使用了这样的标准构建器和谓词
List<Predicate> predicates = new ArrayList<>();
for(Integer lcId : licenceClassIdList) {
SetJoin<DriversLicence, LicenceClass> dlClasses = dlRoot.join(DriversLicence_.licenceClasses);
predicates.add(builder.equal(dlClasses.get(LicenseClass_.id), lcId));
}
Predicate predicate = builder.and(predicates.toArray(new Predicate[predicates.size()]));
Notice dlRoot is object of Root class and you can get it from CriteriaQuery class. Resulted predicate is what are you looking for...
注意 dlRoot 是 Root 类的对象，您可以从 CriteriaQuery 类中获取它。结果谓词是您要查找的内容...
回答by Nick
You can still use dot notation to work across the relations. For example, assuming you have a DriversLicence.licenceClass property and LicenceClass.type property, then:
您仍然可以使用点表示法来处理关系。例如，假设您有一个 DriversLicence.licenceClass 属性和 LicenceClass.type 属性，那么：
session.createCriteria(DriversLicence.class)
.add(Expression.or(
Expression.eq("licenceClass.type", "Car"),
Expression.eq("licenceClass.type", "Motorbike")
)
).list();
Personally though, I'd simply avoid using criteria in this case because it's not a dynamic query, but instead use:
不过就我个人而言，我只是避免在这种情况下使用标准，因为它不是动态查询，而是使用：
session.createQuery("from DriversLicence where licenceClass.type in (:types)")
.setParameterList("types", myListOfTypes)
.list();
回答by Nelson Miranda
I had a similar issue but fixed up using HQL, I have a class "Enterprise" that is related to class "User" and also related to class "Role", they hay a many to many relationship, when I need all the enterprises related to a specific user I do the following;
我有一个类似的问题，但使用 HQL 修复，我有一个类“企业”，它与“用户”类相关，也与“角色”类相关，当我需要所有相关的企业时，它们具有多对多的关系对于特定用户，我执行以下操作；
Select e from Enterprise As e inner join e.Users As u inner join u.Roles As r
Where u.UserCode=?
I suppose that in your case you should do something like;
我想在你的情况下你应该做类似的事情；
Select dl from LicenceClass As l inner join l.DriversLicences As dl
Where
l.LicenseClass.Name = ? OR
l.LicenseClass.Name=? OR
l.LicenseClass.Name=?
Hope it helps.
希望能帮助到你。
回答by Adisesha
'I don't think this going to work. I want to find all licences that have both"Car" and "Motorbike" '
'我不认为这会奏效。我想找到所有同时包含“汽车”和“摩托车”的许可证'
User Expression.and(....) instead of Expression.or(....) in the snippet provided by Nick
在 Nick 提供的代码段中，用户 Expression.and(....) 而不是 Expression.or(....)
