hql query formation

Question

I want to construt a hql query like

select PLAN_ID from "GPIL_DB"."ROUTE_PLAN" where ASSIGNED_TO 
      in ('prav','sheet') and END_DATE > todays date

I am doing in this way but getting an error in setting parameters

s=('a','b');
Query q = getSession().createQuery("select planId from RoutePlan where assignedTo in REG ");
if(selUsers != null) {
   q.setParameter("REG", s);
}

where i am doing wrong? Please help in executing this hwl based query having in clause

Answer 1

You need to assign the parameter list in the query. Also note the brackets around the parameter because it is an 'in' query.

Query q = getSession()
      .createQuery("select planId from RoutePlan where assignedTo in (:REG) ");
if(selUsers != null) {
   q.setParameterList("REG", s);
}

You can read more about how to use parameters in HQL in the hibernate reference, but this is the relevant example pasted from there:

//named parameter list
List names = new ArrayList();
names.add("Izi");
names.add("Fritz");
Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)");
q.setParameterList("namesList", names);
List cats = q.list();