Optional parameter with setParameterList in HQL

I have a query with optional parameter

"SELECT ClinicId,Name from Clinic where :ClinicIds is NULL OR ClinicId IN :ClinicIds"

List<int> ClinicIds = null;

I'm passing the pa开发者_Python百科rameter as following

q.SetParameterList("ClinicIds", ClinicIds);

Because ClinicId is an optional parameter. If I pass null to SetParameterList I'm getting exception. Any idea how I can pass an optional parameter(null value) to SetParameterList.

Thanks

Rather than using HQL, use a Criteria query. Its designed to be more programmatic than HQL, in that you use straight Java code to assemble your query, which enables you to use if-then logic. So, instead of either concatenating HQL, or having two different HQL queries which you need to independently maintain, you have one Criteria query which accounts for both situations. Example:

//SELECT ClinicId,Name from Clinic where :ClinicIds is NULL OR ClinicId IN :ClinicIds
Criteria criteria = getSession().createCriteria(Clinic.class);
if(ClinicIds == null) {
    criteria.add(Restrictions.eq("ClinicId", null));
} else {
    criteria.add(Restrictions.or(
            Restrictions.eq("ClinicId", null), 
            criteria.add(Restrictions.in("ClinicId", ClinicIds));
        )
    );
}
return criteria.list();

You can't. That would generate invalid SQL.

You need to change the HQL depending on whether there are ClinicIds.

I did it like:

"SELECT ClinicId,Name from Clinic where -1 in (:ClinicIds)  OR ClinicId IN :ClinicIds"

and

if(ClinicIds == null or ClinicIds.isEmpty()){
    ClinicIds = new List<int>();
    ClinicIds.add(-1);
}

q.SetParameterList("ClinicIds", ClinicIds);

just a trick