java Hibernate Criteria Api 子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5902898/
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
Hibernate Criteria Api Subqueries
提问by BuffaloBuffalo
I am currently working on a project to transfer some legacy jdbc select statements over to using Hibernate and it's criteria api.
我目前正在研究一个项目，将一些旧的 jdbc select 语句转移到使用 Hibernate 和它的标准 api。
The two relevant table columns and the SQL query looks like:
两个相关的表列和 SQL 查询如下所示：
-QUERIES-
primaryId
-QUERYDETAILS-
primaryId
linkedQueryId -> Foreign key references queries.primaryId
value1
value2
select *
from queries q
where q.primaryId not in (SELECT qd.linkedQueryId
FROM querydetails qd
WHERE (qd.value1 LIKE 'PROMPT%'
OR qd.value2 LIKE 'PROMPT%'));
My entity relationships look like:
我的实体关系如下所示：
@Table("queries")
public class QueryEntity{
@Id
@Column
private Long primaryId;
@OneToMany(targetEntity = QueryDetailEntity.class, mappedBy = "query", fetch = FetchType.EAGER)
private Set<QueryDetailEntities> queryDetails;
//..getters/setters..
}
@Entity
@Table(name = "queryDetails")
public class QueryDetailEntity {
@Id
@Column
private Long primaryId;
@ManyToOne(targetEntity = QueryEntity.class)
private QueryEntity query;
@Column(name="value1")
private String value1;
@Column(name="value2")
private String value2;
//..getters/setters..
}
I am attempting to utilize the criteria api in this way:
我试图以这种方式利用标准 api：
Criteria crit = sessionFactory.getCurrentSession().createCriteria(QueryEntity.class);
DetachedCriteria subQuery = DetachedCriteria.forClass(QueryDetailEntity.class);
LogicalExpression hasPrompt = Restrictions.or(Restrictions.ilike("value1", "PROMPT%"),
Restrictions.ilike("value2", "PROMPT%"));
subQuery.add(hasPrompt);
Criterion subQueryCrit = Subqueries.notIn("queryDetails", subQuery);
crit.add(subQueryCrit);
List<QueryMainEntity> entities = (List<QueryMainEntity>) crit.list();
System.out.println("# of results = " + entities.size());
I am getting a NullPointerException on the crit.list() line that looks like
我在 crit.list() 行上收到一个 NullPointerException，看起来像
Exception in thread "main" java.lang.NullPointerException
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:362)
at org.hibernate.criterion.SubqueryExpression.createAndSetInnerQuery(SubqueryExpression.java:153)
at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:69)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:380)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:114)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:83)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:92)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1687)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
Now, I think its pretty safe to say I'm using the Criteria Api/Detached Query Api incorrectly, but I'm not sure what the 'correct' way to do it is since the Hibernate Docsonly briefly cover criteria api subqueries.
现在，我认为可以肯定地说我错误地使用了 Criteria Api/Detached Query Api，但我不确定“正确”的方法是什么，因为Hibernate Docs只简要介绍了标准 api 子查询。
I realize this is a pretty long question, but I figure its appear to put it all the relevant aspects of the question (query I'm attempting to represent via Criteria API, tables, entities).
我意识到这是一个很长的问题，但我认为它似乎包含了问题的所有相关方面（我试图通过 Criteria API、表、实体表示的查询）。
回答by javagruffian
Give this a shot:
试一试：
DetachedCriteria d = DetachedCriteria.forClass(QueryDetailEntity.class, "qd");
d.setProjection(Projections.projectionList().add(Projections.property("qd.query")));
d.add(Restrictions.or(Restrictions.like("qd.value1", "PROMPT%"), Restrictions.like("qd.value2", "PROMPT%")));
criteria = session.createCriteria(QueryEntity.class, "q");
criteria.add(Subqueries.propertyNotIn("q.primaryId", d));
criteria.list();
The use of the following are property names, not column names:
以下使用的是属性名称，而不是列名称：
qd.query
qd.value1
qd.value2
q.primaryId
As a side note, if this is not a dynamically generated query, have you given thought to using HQL instead?
作为旁注，如果这不是动态生成的查询，您是否考虑过使用 HQL？
