Handle null values within SQL IN clause

Question

I have following sql query in my hbm file. The SCHEMA, A and B are schema and two tables.

              select
                *
              from SCHEMA.A os
              inner join SCHEMA.B o
                on o.ORGANIZATION_ID = os.ORGANIZATION_ID
              where
                case
                  when (:pass = 'N' and os.ORG_ID in (:orgIdList)) then 1
                  when (:pass = 'Y') then 1
                end = 1
                and (os.ORG_SYNONYM like :orgSynonym or :orgSynonym is null)

This is a pretty simple query. I had to use the case - when to handle the null value of "orgIdList" parameter(when null is passed to sql IN it gives error). Below is the relevant java code which sets the parameter.

        if (_orgSynonym.getOrgIdList().isEmpty()) {
            query.setString("orgIdList", "pass");
            query.setString("pass", "Y");
        } else {
            query.setString("pass", "N");
            query.setParameterList("orgIdList", _orgSynonym.getOrgIdList());
        }

This works and give me the expected output. But I would like to know if there is a better way to handle this situation(orgIdList sometimes become null).

Answer 1

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

In other words, regardless of Hibernate's ability to parse the query and to pass an IN(), regardless of the support of this syntax by particular databases (PosgreSQL doesn't according to the Jira issue), Best practice is use a dynamic query here if you want your code to be portable (and I usually prefer to use the Criteria API for dynamic queries).

If not need some other work around like what you have done. or wrap the list from custom list et.