I have a class name employee which has a set of associated parameters. I am trying to get the employees with a specific parameters. When i ask for employes with only one parameters it is working fine, however when i ask for employees with more than 1 parameters, the result is none. Here is my Criteria request.

List res = null;
Criteria criteria = HibernateSessionFilter.getSession().createCriteria(Employee.class);
Criteria criteriaParams = criteria.createCriteria("associatedParams");

int size = searchParamsList.size();
for(int i = 0; i < size; i++){
ParamTypeGUI ptg = (ParamTypeGUI)searchParamsList.get(i);

String newVal = ptg.getSearchEmployeeRootParamValue();
if(!newVal.equals("")){
newVal = newVal.toLowerCase();

criteriaParams.add( Restrictions.ilike("dataValue", "%"+newVal+"%"))
.add( Restrictions.eq("paramType.id", ptg.getId() ))
.addOrder( Order.asc("paramType"));
}
}

res = criteria.list();

what is wrong?

Your logic is faulty. When you have more than one parameter, you'll end up with more than one Restrictions.eq("paramType.id", value) restriction. That's the same as "x = A and x = B". Obviously nothing will ever match that. You need to add Restrictions.or(crit1, crit2) in the appropriate place.

P.S. You can improve the readablity of your code by 1) getting rid of the lowercase bit, seeing are you're doing a case-insensitive search anyway, and 2) by using Restrictions.ilike(propertyName, value, matchMode) instead of that % + str + % thing (ick).

Thanks for trying to help me so fast, however i still remain with the same problem, each match of a param has to be the combination of dataValue and paramType.id , so how can I create this query?
Thanks in advanced.

This might work:I'm sure there's a few optimizations that could be added to this, but it's a starting point.

Oops, as soon as I post.. d'oh! You'll have to add in a check for first time through loop (and just do an orList.add(newCrit) on the first loop, not an orList.add(Restrictions.or(...))). Also, I can't remember what happens when you add an or'd criterion to itself; what I've got there is probably wrong. You may have to play with that to get the rigth SQL, but it's a starting point.

thanks again for the answear, however there are 2 things that I need to know:
1. DetachedCriteria orList = DetachedCriteria.forList(Employee.class);
should be change to DetachedCriteria orList = DetachedCriteria.forClass(Employee.class); no?

2. Criterion newCrit =
Restrictions.and(Restrictions.ilike("dataValue", newVal, MatchMode.ANYWHERE),
Restrictions.eq("paramType.id", ptg.getId()));

means that the criteria is on the Employee not on the EmployeeRootParam (so i get an exception saying that dataValue was not found in Employee), how do i then make the newCrit of EmployeeRootParam?

1. Yep. I guess I'm too dependent on Eclipse to check all that stuff for me :)
2. Same as normal. Use DetachedCriteria orList = DetachedCriteria.forClass(Employee.class).createCriteria("associatedParams");

I still can't think of the correct syntax for the orList.add(...) line. Once you get something happening, can you post the SQL that orList.add(Restrictions.or(orList, x)) produces? And if you figure it out, post the working solution, too.

here is the correct code that I use, however now it works fine but only if you use 1 parameter (as in the begining), upon trying to find an employee that match more than 1 parameter, i get no result.

DetachedCriteria orList = DetachedCriteria.forClass(Employee.class).createCriteria("associatedParams");
int size = searchParamsList.size();
for(int i = 0; i < size; i++){
ParamTypeGUI ptg = (ParamTypeGUI)searchParamsList.get(i);

String newVal = ptg.getSearchEmployeeRootParamValue();
if(!newVal.equals("")){


Criterion newCrit =
Restrictions.and(Restrictions.ilike("dataValue", newVal, MatchMode.ANYWHERE),
Restrictions.eq("paramType.id", ptg.getId()));

orList.add(newCrit);

}// if(!newVal.equals("")){...
}// for(int i = 0; i < size; i++){...

orList.addOrder( Order.asc("paramType"));
res = orList.getExecutableCriteria(HibernateSessionFilter.getSession()).list();


The SQL that become out of it when I look for employee with 2 matching parameters is:
[STDOUT] Hibernate: select this_.EMPLOYEE_ID as EMPLOYEE1_1_, this_.EMAIL as EMAIL30_1_,
this_.IMAGE as IMAGE30_1_, this_.MANAGER as MANAGER30_1_, this_.DEPARTMENT_ID as DEPARTMENT5_30_1_, this_.
TEAM_ID as TEAM6_30_1_, associated3_.EMPLOYEE_ID as EMPLOYEE1___, employeero1_.EMPLOYEE_PARAM_ID as EMPLOYE
E2___, employeero1_.EMPLOYEE_PARAM_ID as EMPLOYEE1_0_, employeero1_.VALUE as VALUE32_0_, employeero1_.PARAM
_TYPE_ID as PARAM3_32_0_, employeero1_.CHANGEABLE_PROPERTY as CHANGEABLE4_32_0_ from EMPLOYEES this_ inner
join EMPLOYEES_PARAMS associated3_ on this_.EMPLOYEE_ID=associated3_.EMPLOYEE_ID inner join EMPLOYEE_PARAMS
employeero1_ on associated3_.EMPLOYEE_PARAM_ID=employeero1_.EMPLOYEE_PARAM_ID where (lower(employeero1_.VA
LUE) like ? and employeero1_.PARAM_TYPE_ID=?) and (lower(employeero1_.VALUE) like ? and employeero1_.PARAM_
TYPE_ID=?) order by employeero1_.PARAM_TYPE_ID asc

What is wrong?

Thanks again for the effort

You're still not ORing the criteria together, you're just adding them. You must use Restrictions.or(crit1, crit2) to add each new pair of parameters. You want to end up with something to the effect ofIf there was a version of Restrictions.or() that took a vararg argument, you'd be away in a hack. Hopefully crit1.add(Restrictions.or(crit1, newCrit)) does what you need.

That exactly what I need to do however I can not find something like crit1.add(Restrictions.or(crit1, newCrit)) since crit1 is actely etachedCriteria. Any idea?

You can go back to using regular criteria, if you like. But why does crit1 being DetachedCriteria affect the add method? What error do you get when when you use crit1.add(Restrictions.or(crit1, newCrit))?

I do not get error, it does not compile, orList.add(Restrictions.and(orList, newCrit)) ;
orList suppose to be also Criterion as the newCrit. :-( the following in red color is not being compiled.

DetachedCriteria orList = DetachedCriteria.forClass(Employee.class).createCriteria("associatedParams");
int size = searchParamsList.size();
for(int i = 0; i < size; i++){
ParamTypeGUI ptg = (ParamTypeGUI)searchParamsList.get(i);

String newVal = ptg.getSearchEmployeeRootParamValue();
if(!newVal.equals("")){


Criterion newCrit =
Restrictions.and(Restrictions.ilike("dataValue", newVal, MatchMode.ANYWHERE),
Restrictions.eq("paramType.id", ptg.getId()));

orList.add(Restrictions.and(orList, newCrit)) ;

}// if(!newVal.equals("")){...
}// for(int i = 0; i < size; i++){...

orList.addOrder( Order.asc("paramType"));
res = orList.getExecutableCriteria(HibernateSessionFilter.getSession()).list();

Research rocks.
I've just found a class that I've not encountered before. Try this code:

Thanks we are on the right way, however the query has 2 problems.
1. It returns employees that have atleast one match of the parameters and not all, i.e. Disjunction use the "Or" statement and I need "and".
2. An Employee that match both parameters is being added twice, i.e. a distinct is needed.

I solved the second problem by changing the last line to be
res = crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
Any Idea how to solve the first problem of the statment? I tried using Conjunction but the result of a query with 2 parameters was none :-(

Thanks again for the effort

If you want to match N different parameters for each Employee, you need to completely rethink your query. You're checking one associatedParams for all (or with Disjunction, any) of the search params. You need to check ALL of an Employee's associatedParams for all of the search params: I don't think that there's any way short of subqueries to do that.

I've never done this with Criteria, but the example is at the end of section 15.8 of the ref docs. We need to go back to DetachedCriteria to make use of Property.forName(x).in(DetachedCriteria). Don't mix that up with Restrictions.in(), which requires that the collection you're checking against is already in memory. in(DetachedCriteria) seems to be the only way to use subqueries with Criteria (can anyone tell me if there are other ways?). And because that uses a single property, you're going to have to use an AssociatedParam class. Do you have that already?

Anyway, everything you need is in section 15.8. Ignore the stuff about projections, you don't need it.