Fetching ElementCollecion from Hibernate based on a condition



I have a class named User and it has a element collection groups like below:

public class User {

    @ElementCollection
    @CollectionTable(name = "user_groups", joinColumns = @JoinColumn(name = "id"))
    private Set<String> groups;

    //Other fields
}

Now I need to fetch list of users, based on group name. The query is something like this:

@Query("select distinct user from User user " +
        "left join fetch user.groups groups " +
        "where :groupName IN groups")
List<User> findUsersByGroupName(@Param("groupName") String groupName);

This works fine but in the fetched User objects, I am getting only one group(which I am passing). I need all the groups of a User in the fetched objects. How can I do that efficiently?
I have tried using elements and member of but as I have many users, it is taking lot of time(in minutes) to execute the query.

Answer

The problem with the above query is fetch keyword which fetches the data eagerly from the database so only one group was there. I have removed the fetch keyword and initialized the groups after fetching User. This gives all the groups of a user.



Source: stackoverflow