JPA Hibernate select distinct over @ElementCollection and compare

Question

I am using Hibernate with JpaRepositories.

The relevant part of the Entity class is:

@Entity
public class Person {
   //.. id and other attributes

   @Column(name = "function")
   @ElementCollection
   private Set<String> functions;

  // .. getter setter
}

I needed to change my Entity class from having only one function to be able to handle multiple functions. There is a search function in one of my DAOs that can compare all existing functions with a string, in hope to find already defined functions.

the original JPA Query was:

select DISTINCT(p.function) from Person p where UPPER(p.function) like UPPER(:term) order by p.function

how can I archive the same result with the new @ElementCollection?

Answer 1

You need to join the collection to Person and then select. Try this query:

select DISTINCT(f) from Person p join p.functions f where UPPER(f) like UPPER(:term) order by f