JPQL，Where condition on Set with values

Question

@Entity 
public class Person{

    private String firstName;
    private String lastName;

    @OneToMany(mappedBy="person" , fetch= FetchType.LAZY)
    private Set<Certificate> certificates;
}


@Entity 
public class Certificate{

    private String courseName;

    @ManyToOne 
    @JoinColumn(name="person_id")
    private Person person;

}

具有实体class(Person)，该实体保存使用如上所述的@OneToMany映射的相关实体(证书)的集合(集合)。现在我得去取all Persons having certificate.courseName IN ("OCPJP", "OCPWCD").了

在简单的SQL中，我的查询将如下所示。

select distinct p.firstName, p.lastName from Person p join Certificate c on p.Id = c.person_Id
where c.courseName IN ("OCPJP", "OCPWCD")

如何在JPQL中获得相似的输出？

Answer 1

这样的查询可以写成如下：

SELECT DISTINCT(c.person) FROM Certificate c WHERE c.courseName IN (:coursenames)

完整示例：

List<String> courseNames = Arrays.asList("OCPJP", "OCPWCD");

List<Person> result = em.createQuery(
        "SELECT DISTINCT(c.person) " +
        "FROM Certificate c " +
        "WHERE c.courseName IN (:coursenames)", Person.class)
        .setParameter("coursenames", courseNames)
        .getResultList();