CODEWITHSUNDEEP
javaspringhibernatecriteria
maracili
maracili

Reputation: 181

Hibernate Criteria filter Entity where ManyToMany relation contains multiple objects

I need help with Hibernate Criteria API.

I have a class Job that contain a list of Skill in ManyToMany relationship.

I need to select jobs based on a skill list given as parameter.

I've tried with Restriction.in("skill.id",ids) but this gives me wrong list.If i've selected 2 skills in search form i want the jobs that contain BOTH of them,so i need somehow to implement AND clause.

I've tried with Conjunction:

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

And this:

Criteria crit = criteria.createCriteria("skills",JoinType.LEFT_OUTER_JOIN);
                 for(Skill skill:job.getSkills()){
                     crit.add(Restrictions.eq("id", skill.getId()));
                 }

but it creates same alias for skill and it gives me no result. sql is and (skill1_.ID=? and skill1_.ID=?)

Can anyone help me with this ?thanks

UPDATE:

HQL Query will be like: 

select a from Job a where exists (select skill1.id from Skill skill1 join skill1.jobs r where r.id=a.id and skill1.id=1) and exists (select skill2.id from Skill skill2 join skill2.jobs r where r.id=a.id and skill2.id=4)

I need Criteria based on this.

Upvotes: 2

Views: 1301

Answers (1)

maracili
maracili

Reputation: 181

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 perfect.

Upvotes: 1

Related Questions