收集值的Hibernate标准 [英] Hibernate criteria on collection values
问题描述
我试图用Hibernate来组合一个复杂的查询。我一直倾向于Criteria，但我开始怀疑这是不可能的，所以任何建议都会有帮助。
我有一个像下面这样的实体结构：
public class Attribute {
private Integer id;
私人字符串名称;
private Set< Value>值;
}
公共类实例{
私有整数ID;
private int instanceRef;
private Set< Value>值;
}
public class Value {
private Integer id;
私有属性属性;
private String localAttributeName;
私有实例实例;
私有字符串值;
$ / code> 这些实体与您预期的相关：
value.attribute_id - > attribute.id
value.instance_id - > instance.id
现在，我希望能够获取一组属性/值对（字符串）并查找包含它们的所有 的所有实例。在Value中，只有一个属性和localAttributeName是非空的，所以属性名称可以匹配localAttributeName或attribute.name。最后一件事情复杂化了，Value上的唯一索引是on（instance，attribute，value）或（instance，localAttributeName，value） - 也就是说，在一个实例中，任何给定的Attribute都可能有多个值。
这是我到目前为止：
public List< Instance> getMatchingInstances（Map< String，String> attrValues）{
Criteria crit = session.createCriteria（Instance.class，i）;
for（Map.Entry< String，String> entry：attrValues）{
DetachedCriteria valueCrit = DetachedCriteria.forClass（Value.class，v）;
//用valueCrit
做一些事情b crit.add（Subqueries.exists（valueCrit））;
}
return crit.list（）;
}
基于我所做的研究，我为此尝试过做一些部分是：
//这只会检查localAttributeName而不检查attribute.name。
//没关系 - 一旦我让剩下的工作，我可以弄清楚。
valueCrit.add（Restrictions.eq（localAttributeName，entry.getKey（））;
valueCrit.add（Restrictions.eq（value，entry.getValue（））;
valueCrit.add（Restrictions.eqProperty（v.instance_id，i.id））;
但是这引发了下面的异常，我怀疑是告诉我我不能用Criteria做到这一点，但是我很想学习另外的东西：
在org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes 显示java.lang.NullPointerException
（CriteriaQueryTranslator.java:341）
最好的方法是做什么？ 我在数小时后就想出了解决方案，希望这对其他人有用处。我需要解决以下三个主要问题：
- 添加投影
- 创建合适的连接 正确地映射子查询返回主标准
我在下面的代码中突出显示了每一个。
首先，为了摆脱这个异常，我发现子查询需要一个投影，下面突出显示。我只是在Instance的id属性上做了一个投影。其次，为了获得连接，我使用Criteria.createCriteria（）方法创建了一个左外层加入。因为我在连接的不同级别有多个条件，所以我必须分别保存连接的Criteria和附加表达式。这让我在子查询中执行OR表达式。
最后，我必须添加一个eqProperty（）子句来将子查询映射回主标准。就像它需要在结果SQL中一样，我使用了：instance.id = i.id.因为我已经将Instance Criteria映射到i，并将此子句添加到Value Criteria中，所以将其转换为SQL：v.instance_id = i.id。
以下是工作代码：
public List< Instance> getMatchingInstances（Map< String，String> attrValues）{
Criteria crit = session.createCriteria（Instance.class，i）; （Map.Entry< String，String>条目：attrValues）{
字符串attrName = entry.getKey（）;
String val = entry.getValue（）;
//创建子查询
DetachedCriteria valueCrit = DetachedCriteria.forClass（Value.class，v）;
//加入属性对象（左外连接）
DetachedCriteria attrCrit =
valueCrit.createCriteria（attribute，CriteriaSpecification.LEFT_JOIN）;
//将OR语句放在属性加入的标准上。
Criterion localAttr = Restrictions.eq（v.localAttributeName，attrName）;
Criterion globalAttr = Restrictions.eq（name，attrName）;
attrCrit.add（Restrictions.or（localAttr，globalAttr））;
//子查询条件上的简单列平等。
valueCrit.add（Restrictions.eq（value，val））;
//将子查询映射回外部查询。
valueCrit.add（Restrictions.eqProperty（instance.id，i.id））;
//添加缺失的投影。
valueCrit.setProjection（Projections.property（id））;
//将此子查询添加到外部查询中。
crit.add（Subqueries.exists（valueCrit））;
}
return crit.list（）;
}
I'm trying to put together a complicated query using Hibernate. I've been leaning toward Criteria, but I'm beginning to suspect it's not possible, and so any suggestions would be helpful.
I have an entity structure like the following:
public class Attribute {
private Integer id;
private String name;
private Set<Value> values;
}
public class Instance {
private Integer id;
private int instanceRef;
private Set<Value> values;
}
public class Value {
private Integer id;
private Attribute attribute;
private String localAttributeName;
private Instance instance;
private String value;
}
These entities are related as you'd expect:
value.attribute_id --> attribute.id
value.instance_id --> instance.id
Now, I would like to be able to take a set of attribute/value pairs (Strings) and find all instances that contain all of them. In Value, only one of attribute and localAttributeName are non-null, so the attribute name may match either localAttributeName or attribute.name. And to complicate things one last time, the unique index on Value is on (instance, attribute, value) or (instance, localAttributeName, value) -- that is, within an Instance, any given Attribute may have multiple Values.
This is what I have so far:
public List<Instance> getMatchingInstances(Map<String, String> attrValues) {
Criteria crit = session.createCriteria(Instance.class, "i");
for(Map.Entry<String, String> entry : attrValues) {
DetachedCriteria valueCrit = DetachedCriteria.forClass(Value.class, "v");
// Do something here with valueCrit
crit.add(Subqueries.exists(valueCrit));
}
return crit.list();
}
Based on the research I've done, what I've tried for that Do something section is:
// This would only check localAttributeName and not attribute.name.
// That's okay -- once I get the rest to work, I can figure this out.
valueCrit.add(Restrictions.eq("localAttributeName", entry.getKey());
valueCrit.add(Restrictions.eq("value", entry.getValue());
valueCrit.add(Restrictions.eqProperty("v.instance_id", "i.id"));
But this throws the exception below, which I suspect is telling me I can't do this with Criteria, but I'd love to learn otherwise:
java.lang.NullPointerException
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:341)
What would be the best way to go about doing this?
I figured out the solution after a few hours of banging on it. Hopefully, this is of use to others. There were three main points that I needed to solve to make this feasible:
- Add a Projection
- Create the proper joins
- Properly map the subquery back to the main criteria
I've highlighted each of these in the below code.
First, to get rid of the exception, I discovered that the subquery needed a projection, highlighted below. I just did a projection on the "id" property of Instance.
Second, to get the join, I used the Criteria.createCriteria() methods to create a left outer join. Because I had multiple conditions at different levels of the join, I had to save the joined Criteria and attach expressions to them separately. This let me do my OR expression in the subquery.
Finally, I had to add an eqProperty() clause to map the subquery back to the main Criteria. Just like it would need to be in the resulting SQL, I used: instance.id = i.id. Because I had already mapped the Instance Criteria to "i" and was adding this clause to the Value Criteria, this translated to the SQL: v.instance_id = i.id.
Here's the working code:
public List<Instance> getMatchingInstances(Map<String, String> attrValues) {
Criteria crit = session.createCriteria(Instance.class, "i");
for(Map.Entry<String, String> entry : attrValues) {
String attrName = entry.getKey();
String val = entry.getValue();
// Create the subquery
DetachedCriteria valueCrit = DetachedCriteria.forClass(Value.class, "v");
// Join the Attribute object (left outer join)
DetachedCriteria attrCrit =
valueCrit.createCriteria("attribute", CriteriaSpecification.LEFT_JOIN);
// Put together the OR statement on the Attribute joined criterion.
Criterion localAttr = Restrictions.eq("v.localAttributeName", attrName);
Criterion globalAttr = Restrictions.eq("name", attrName);
attrCrit.add(Restrictions.or(localAttr, globalAttr));
// Simple column equality on the subquery criterion.
valueCrit.add(Restrictions.eq("value", val));
// Map the subquery back to the outer query.
valueCrit.add(Restrictions.eqProperty("instance.id", "i.id"));
// Add the missing projection.
valueCrit.setProjection(Projections.property("id"));
// Add this subquery to the outer query.
crit.add(Subqueries.exists(valueCrit));
}
return crit.list();
}
这篇关于收集值的Hibernate标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
