title: Hibernate Criteria Api Subqueries
body: <p>I am currently working on a project to transfer some legacy jdbc select statements over to using Hibernate and it's criteria api.</p>
  
  <p>The two relevant table columns and the SQL query looks like:</p>
  
  <pre><code>-QUERIES-
  primaryId
  
  -QUERYDETAILS-
  primaryId
  linkedQueryId -&gt; 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%'));
  </code></pre>
  
  <p>My entity relationships look like:</p>
  
  <pre><code>@Table("queries")
  public class QueryEntity{
  
      @Id
      @Column
      private Long primaryId;
      @OneToMany(targetEntity = QueryDetailEntity.class, mappedBy = "query", fetch = FetchType.EAGER)
      private Set&lt;QueryDetailEntities&gt; 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..
  
  }
  </code></pre>
  
  <p>I am attempting to utilize the criteria api in this way:</p>
  
  <pre><code>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&lt;QueryMainEntity&gt; entities = (List&lt;QueryMainEntity&gt;) crit.list();
  System.out.println("# of results = " + entities.size());
  </code></pre>
  
  <p>I am getting a NullPointerException on the crit.list() line that looks like</p>
  
  <pre><code>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.&lt;init&gt;(CriteriaJoinWalker.java:114)
      at org.hibernate.loader.criteria.CriteriaJoinWalker.&lt;init&gt;(CriteriaJoinWalker.java:83)
      at org.hibernate.loader.criteria.CriteriaLoader.&lt;init&gt;(CriteriaLoader.java:92)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1687)
      at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
  </code></pre>
  
  <p>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 <a href="http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-detachedqueries" rel="nofollow">Hibernate Docs</a> only briefly cover criteria api subqueries.</p>
  
  <p>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).</p>