Hibernate Criteria query Collections contains certain elements

Question

I have some problems with Criteria. I have a Job class that contains a set of Skills.

The problem comes when I want to filter jobs that contain 2 skills, for example, all jobs that contains skill with id 1 and 3.

Now I have this:

for (Skill skill : job.getSkills()) {
    ids.add(skill.getId());
}

criteria.createAlias("skills", "skill");
criteria.add(Restrictions.in("skill.id", ids));

but it gives me jobs that contain skill 1 or 3, not only those with both skills. How can I do this?

UPDATE:

criteria.createAlias("Job.skills", "skill");    
Conjunction and = Restrictions.conjunction();   

for (Skill skill : job.getSkills()) {
    and.add(Restrictions.eq("skill.id", skill.getId()));
}
criteria.add(and);

I've tried this but the sql is and (skill1_.ID=? and skill1_.ID=?) with no results

Please post Hibernate version. – chrisinmtown Aug 22 '18 at 14:18 — chrisinmtown, Aug 22 '18 at 14:18

WeMakeSoftware · Answer 1 · 2015-01-05T15:24:42.280

1

Try this one:

criteria.createAlias("skills", "skill");

for(Skill skill:job.getSkills()){
    List<Long> wrappedParameter = new ArrayList<Long>();
    wrappedParameter.add(skill.getId());
    criteria.add(Restrictions.in("skill.id", wrappedParameter)));
}

edited Jan 05 '15 at 15:24

answered Jan 05 '15 at 14:03

WeMakeSoftware

9,039
5
34
52

same as @Farhang Amary solution. empty list when i select 2 skills for filter – maracili Jan 05 '15 at 14:37
Restrictions.in(String,Object[]). I've tried with "new Long[]{skill.getId()}" and now the query is "skill1_.ID in (?) and skill1_.ID in (?)".same thing ,empty result – maracili Jan 05 '15 at 15:03
what do you mean by "new Long[]{skill.getId()}" ? – WeMakeSoftware Jan 05 '15 at 15:17
Restrictions.in() requires a collection of objects as second parameter so i tried to create a collection with that skill id. – maracili Jan 05 '15 at 15:20
same behavior..empty result. query: and skill1_.ID in (?) and skill1_.ID in (?) – maracili Jan 05 '15 at 15:31

void · Answer 2 · 2015-01-05T14:22:17.827

0

the result you are getting is expected. you are using Restrictions.in you can use Criterion

List<Criterion> restrictionList;
for(Skill skill :job.getSkills()){
    //ids.add(skill.getId());
    Criterion ctn=Restrictions.eq("skill.id", skill.getId());
    restrictionList.add(ctn);
}

criteria.createAlias("skills", "skill");
for (Criterion crit : restrictionList){
   criteria.add(crit);
}

edited Jan 05 '15 at 14:22

answered Jan 05 '15 at 14:14

void

7,760
3
25
43

Thanks for response but this returns me an empty list. query generated for skills is : "and skill1_.ID=? and skill1_.ID=?" – maracili Jan 05 '15 at 14:27

score 0 · Accepted Answer · answered Jan 12 '15 at 14:26

for(Skill skill:job.getSkills()){
                    DetachedCriteria subquery = DetachedCriteria.forClass(Skill.class,"skill");
                    subquery.add(Restrictions.eq("id",skill.getId()));
                    subquery.setProjection(Projections.property("id"));
                    subquery.createAlias("jobs", "job");
                    subquery.add(Restrictions.eqProperty("job.id", "Job.id"));
                    criteria.add(Subqueries.exists(subquery));  
                }

I managed to solve it.now it works.

The Skill class has a reference back to the Job class (bidirectional)? Can this solution be adapted to a unidirectional configuration? I.e., a Job has a collection of Skill, but Skill knows nothing about Job? What is the value of "Job.id" ? — chrisinmtown, Aug 22 '18 at 17:27

Hibernate Criteria query Collections contains certain elements

3 Answers3

Linked