CODEWITHSUNDEEP
javamysqlhibernatejpajpql
brain storm
brain storm

Reputation: 31252

how to perform parameter binding for in clause for Collection in JPQL without using JOIN?

Here is the data modelling scenario:

For illustrative purposes consider this simple data model:

ManyToMany From Project to Department

ManyToMany From Project to Employee

Consider this entitiy:

@Entity
@NamedQueries( {
@NamedQuery(name = "project.getProjectByDepartmentAndEmployee", 
query = "SELECT p from Project p WHERE (:dept) in p.department and (:emp) in p.employees"),
})
public class ConfigFile extends BaseUpdatableEntity implements Serializable {

    private Long id;

    @ManyToMany
    private List<Department> departments;


    @ManyToMany
    private List<Employee> employees;

}

in My Dao Layer, I want to execute the following:

public Project getProject(Department dept, Employee emp){
       Query query = getEntityManager().createNamedQuery("project.getProjectByDepartmentAndEmployee");
        query.setParameter("dept", dept)
             .setParameter("emp",emp);
            return (UserSession) query.getResultList().get(0);
}

The problem I see with NamedQueryConstruction above. How will I bind parameters in Where clause on left side.

Thanks for help

NOTE

Answer below will work. but I am looking for a solution without using JOIN if possible

Upvotes: 1

Views: 321

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153780

Try changing your JPQL query to:

SELECT p 
from Project p 
inner join p.department d
inner join p.employees e
WHERE d = :dept and e = :emp

Upvotes: 1

Related Questions