Search code examples
javahibernatespring-data-jpahql

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.

Solution

  • 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.