title: Dynamic HQL query using hql expressions rather than Criteria?
body: <p>I am trying to write a partially dynamic HQL query without resorting to the Criteria API for various reasons. I wanted to know if there is an easy way to short circuit a where restriction using HQLs expressions. For example, here is the original query which works fine:</p>
  
  <pre><code>SELECT customer 
  FROM Customer as customer 
  INNER JOIN customer.profile as profile 
  WHERE profile.status IN :statusCodes
  AND   profile.orgId IN :orgIds
  </code></pre>
  
  <p>StatusCodes is a list of Strings and orgIds is a list of Integers. However, either one is optional and shouldn't restrict if null is passed instead of a collection. I've tried to accomplish this like so:</p>
  
  <pre><code>SELECT customer 
  FROM Customer as customer 
  INNER JOIN customer.profile as profile 
  WHERE (:statusCodes IS NULL OR profile.status IN :statusCodes)
  AND   (:orgIds IS NULL OR profile.orgId IN :orgIds)
  </code></pre>
  
  <p>This didn't work unfortunately, but is there any other approach that might work, either with using different expressions or passing in default values?</p>
  
  <p>EDIT: Just to be clear I'm looking for a way to use a NamedQuery, not dynamically building the query in any way.</p>
  
  <p>SOLUTION: I used the extra query parameters to accomplish it. I created two helper methods:</p>
  
  <pre><code>private void setRequiredParameter(TypedQuery&lt;?&gt; query, String name, Object value) {
      query.setParameter(name, value);
  }
  
  private void setOptionalParameter(TypedQuery&lt;?&gt; query, String name, Object value) {
      query.setParameter(name, value);
      query.setParameter(name + "Optional", value == null ? 1 : 0);
  }
  </code></pre>
  
  <p>And the query like so:</p>
  
  <pre><code>SELECT customer 
          FROM Customer as customer 
          INNER JOIN  customer.profile as profile 
          WHERE (:statusCodesOptional = 1 OR profile.status IN :statusCodes)
          AND (:orgIdsOptional = 1 OR profile.orgId  IN :orgIds)
  </code></pre>