How to write JPA query where parameter is a set?

Question

Assuming the following class, how do you find a Person with a particular email address?

public class Person implements Comparable<Person> {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="id")
    private long id = 0;

    @OneToMany(cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE}, fetch=FetchType.LAZY)
    private Set<String> email = new HashSet<String>();
}

Is it as simple as doing just this, or is there a proper way?

select p from Person p where p.email=:email

Answer 1

The SQL would look something like this:

WHERE email IN ('[email protected]', '[email protected]')

Unfortunately, I'm not aware of an easy way to do this. If you were doing this with raw SQL, you'd have to do it in two steps: create a bind parameter ? for each value in the set, then iterate through the set and bind each value to its bind parameter.

I'm not aware of a way to do it cleanly in JPA, but that's what you should be looking for.

Answer 2

It's not that easy. JPQL provides the IN operator for this:

select p from Person p, IN(p.email) m where m = :email

The 'old' way (read SQL-like) would be:

select p from Person p join p.email m where m = :email