Find user by role using spring data jpa

Question

My User Entity

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String username;

    @Column(nullable = false)
    private String password;

    @ElementCollection
    private List<String> roles = new ArrayList<>();
}

Every User can have many roles. Given a role (represented in String datatype), I want to get all the users that have this role.

For example

User1 with role : "admin"

User2 with role : "user"

User3 with role : "admin"

For the role "admin", I want to get as a result the User1 and User2.

What I have tried with Spring Data Jpa:

public interface UserRepository extends JpaRepository<User, Integer> {
    public List<User> findByRoles( String role);
}

But i'm getting an exeption

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: com.spring.certificatie.securityconfig.User.roles, could not initialize proxy - no Session

Possible duplicate of [How to resolve LazyInitializationException in Spring Data JPA?](https://stackoverflow.com/questions/26507446/how-to-resolve-lazyinitializationexception-in-spring-data-jpa) — K.Nicholas, Jul 10 '19 at 17:53
From the discussion you mentionned, i picked the solution that ask to change from List to Set. And it works fine. But why, there is no explaination from the others discution. — xmen-5, Jul 10 '19 at 18:07
could you mention the part that explains why Set works weel against the List. I'm not talking about the cause of the exeption. thanks — xmen-5, Jul 10 '19 at 22:25

dasunse · Accepted Answer · 2019-07-17T08:19:22.527

In your UserRepository use in this way

import org.springframework.data.jpa.repository.JpaRepository;

import java.util.Collection;
import java.util.List;

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByRolesIn(Collection<String> names, Pageable pageable);
}

In your Controller

@GetMapping(value = "/api/usersByRole/{userRole}")
public List<User> getUser(@PathVariable String userRole, Pageable pageable){
    return userRepository.findByRolesIn(Arrays.asList(userRole), pageable);
}

And You will have result like this

score 2 · Answer 2 · answered Jul 17 '19 at 08:47

Here from User to Role is ONE-TO-MANY mapping
i.e list of Roles is an element of User entity, and you are passing a String as List of Roles.
This is the reason you are getting Exception.

Solutions:

Use findByRolesIn(List<String> roles) instead of findByRoles(String role)

Or, Make one-to-one mapping as below:

@Column(nullable = false)
private String role;

Or, Use JPA Query or Native query as below.

@Query( "select u from User u  where u.roles in :roles" )
public List<User> findByRoles(@Param("roles") List<String> roles);

Find user by role using spring data jpa

2 Answers2

Linked