Constructing a where exists subquery with hibernate

Question

I am attempting to create the following query using hibernate.

select * from item 
where exists (
  select 1
  from metadatavalue mv
  where mv.dspace_object_id=item.uuid
  and text_value='No Date'
);

I am having difficulty constructing the subquery using hibernate objects.

The following code is working for me

Session session = (Session) context.getDBConnection().getSession();
Criteria criteria = session.createCriteria(Item.class, "item");
criteria.add(Restrictions.sqlRestriction("exists (select 1 from metadatavalue mv where mv.dspace_object_id=this_.uuid and text_value='No Date')"));

I would like to create this query with hibernate objects.

Session session = (Session) context.getDBConnection().getSession();
Criteria criteria = session.createCriteria(Item.class, "item");
DetachedCriteria subcriteria = DetachedCriteria.forClass(MetadataValue.class);
subcriteria.add(???)
criteria.add(Subqueries.exists(subcriteria));

I am unsure of the proper way to reference item.uuid (property name "id") from the subquery.

If I try the following

Criteria criteria = session.createCriteria(Item.class, "item");
DetachedCriteria subcriteria = DetachedCriteria.forClass(MetadataValue.class,"mv");
subcriteria.add(Property.forName("mv.dspace_object_id").eqProperty("item.uuid"));
criteria.add(Subqueries.exists(subcriteria));
System.out.println("xx" + criteria.list().size());

I get the following error

java.lang.NullPointerException
    at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:401)
    at org.hibernate.criterion.SubqueryExpression.createAndSetInnerQuery(SubqueryExpression.java:152)
    at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:68)
    at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:419)
    at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:123)
    at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:92)
    at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:95)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1604)
    at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
    at org.dspace.core.Test.main(Test.java:37)

[

Didn't test yet, but this answer seems to be relevant: http://stackoverflow.com/questions/23519115/hibernate-criteria-with-exists-clause — Bram Luyten, Sep 26 '15 at 14:34
@BramLuyten, I had seen that example, but I was unable to make the example work for me. I added more details to illustrate the error that I am receiving. — terrywb, Sep 27 '15 at 06:16
You need to set a projection on the deteched criteria, to select only one column: `subcriteria.setProjection(Projections.id())` for example. — JB Nizet, Sep 27 '15 at 06:59

score 1 · Accepted Answer · answered Sep 28 '15 at 16:32

Per the recommendations above, I used property names rather than column names and I added the projection. This resolved the issue.

Criteria criteria = session.createCriteria(Item.class, "item");    
DetachedCriteria subcriteria = DetachedCriteria.forClass(MetadataValue.class,"mv");
subcriteria.add(Property.forName("mv.dSpaceObject").eqProperty("item.id"));
subcriteria.setProjection(Projections.property("mv.dSpaceObject"));
criteria.add(Subqueries.exists(subcriteria));
System.out.println("xx" + criteria.list().size());

Constructing a where exists subquery with hibernate

1 Answers1