Reputation: 7852
I am currently working on a project to transfer some legacy jdbc select statements over to using Hibernate and it's criteria api.
The two relevant table columns and the SQL query looks like:
-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:
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
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 Docs only briefly cover criteria api subqueries.
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).
Upvotes: 4
Views: 16191
Reputation: 627
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?
Upvotes: 7