[FIXED] Dynamic HQL query using hql expressions rather than Criteria?

Issue

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:

SELECT customer 
FROM Customer as customer 
INNER JOIN customer.profile as profile 
WHERE profile.status IN :statusCodes
AND   profile.orgId IN :orgIds

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:

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)

This didn't work unfortunately, but is there any other approach that might work, either with using different expressions or passing in default values?

EDIT: Just to be clear I'm looking for a way to use a NamedQuery, not dynamically building the query in any way.

SOLUTION: I used the extra query parameters to accomplish it. I created two helper methods:

private void setRequiredParameter(TypedQuery<?> query, String name, Object value) {
    query.setParameter(name, value);
}

private void setOptionalParameter(TypedQuery<?> query, String name, Object value) {
    query.setParameter(name, value);
    query.setParameter(name + "Optional", value == null ? 1 : 0);
}

And the query like so:

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)

Solution

If you absolutely must avoid dynamic queries, you can do so at the expense of two additional parameters:

SELECT customer 
  FROM Customer AS customer 
  JOIN customer.profile AS profile 
 WHERE (profile.status IN :statusCodes OR :statusCodeCount = 0)
   AND (profile.orgId IN :orgIds OR :orgIdCount = 0)

In your Java code you would then do something like:

session.getNamedQuery("your.query.name")
       .setParameterList("statusCodes", statusCodes)
       .setParameter("statusCodeCount", statusCodes.length)
       .setParameterList("orgIds", orgIds)
       .setParameter("orgIdCount", orgIds.length);

You'll need to ensure arrays are zero-length rather than null or supply additional if checks to handle null scenario.

All that said, HQL is really better suited for well-defined (e.g. static) queries. You can work around dynamic parameters, you won't be able to work around dynamic sorting.

Answered By - ChssPly76
Answer Checked By - Marie Seifert (JavaFixing Admin)