集合值的休眠条件 [英] Hibernate criteria on collection values

问题描述

我正在尝试使用 Hibernate 组合一个复杂的查询.我一直倾向于标准，但我开始怀疑这是不可能的，因此任何建议都会有所帮助.

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

现在，我希望能够获取一组属性/值对(字符串)并找到包含全部的所有实例.在Value中，attribute和localAttributeName中只有一个是非空的，所以属性名可以匹配localAttributeName或者attribute.name.最后让事情复杂化的是，Value 的唯一索引是 (instance, attribute, value) 或 (instance, localAttributeName, value) —— 也就是说，在一个实例中，任何给定的 Attribute 都可能有多个值.

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.

这是我目前所拥有的:

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

但这会引发下面的异常，我怀疑这告诉我我不能用 Criteria 来做到这一点，但我很想了解其他情况:

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:

1. 添加投影
2. 创建合适的连接
3. 正确地将子查询映射回主条件

我在下面的代码中突出显示了其中的每一个.

I've highlighted each of these in the below code.

首先，为了摆脱异常，我发现子查询需要一个投影，在下面突出显示.我刚刚对Instance的id"属性做了一个投影.

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.

其次，为了获得连接，我使用了 Criteria.createCriteria() 方法来创建一个左外连接.因为我在连接的不同级别有多个条件，所以我必须保存连接的条件并将表达式分别附加到它们.这让我可以在子查询中执行 OR 表达式.

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.

最后，我不得不添加一个 eqProperty() 子句来将子查询映射回主标准.就像它需要在结果 SQL 中一样，我使用了:instance.id = i.id.因为我已经将实例标准映射到i"并将此子句添加到值标准中，所以这会转换为 SQL:v.instance_id = i.id.

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.

这是工作代码:

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

这篇关于集合值的休眠条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！