[FIXED] getting list of users by their role in spring boot

Issue

I'm using spring boot, hibernate, mysql database and I want to get list of users by their role I have two entities and one Enum: ERole.java

package com.gestionAbscences.entity;

public enum ERole {
  ADMIN,
  CHEF_DEPARTEMENT,
  ENSIEGNANT,
  ETUDIANT,
}

Role.java:

package com.gestionAbscences.entity;

import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity
@Table(name = "roles")
public class Role {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  @Enumerated(EnumType.STRING)
  @Column(length = 20)
  private ERole name;

  public Role(){

  }

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public ERole getName() {
    return name;
  }

  public void setName(ERole name) {
    this.name = name;
  }
}

User.java:

package com.gestionAbscences.entity;


import lombok.NoArgsConstructor;

import javax.persistence.*;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "users")
public class User {

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

  @NotBlank
  private String firstName;

  @NotBlank
  private String lastName;


  @NotBlank
  @Email
  private String email;

  @NotBlank
  private String password;


  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "user_roles",joinColumns = @JoinColumn(name = "user_id"),
  inverseJoinColumns = @JoinColumn(name = "role_id"))
  private Set<Role> roles = new HashSet<>();

  public User(){

  }

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }

  public String getPassword() {
    return password;
  }

  public void setPassword(String password) {
    this.password = password;
  }

  public Set<Role> getRoles() {
    return roles;
  }

  public void setRoles(Set<Role> roles) {
    this.roles = roles;
  }
}

My Admin controller is a REST Controller:

AdminController.java:

package com.gestionAbscences.controller;


import com.gestionAbscences.entity.Role;
import com.gestionAbscences.entity.User;
import com.gestionAbscences.services.RoleService;
import com.gestionAbscences.services.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class AdminController {

  @Autowired
  private UserService userService;

  @Autowired
  private RoleService roleService;


  @GetMapping("/users?={role}")
  public List<User> getUsers(@RequestParam int role){
     return userService.getUsersByRole(role);
  }


}

And for the userRepository.java I don't know how to write the sql statement to get list of users with specified role:

userRepository.java:

package com.gestionAbscences.repository;

import com.gestionAbscences.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface UserRepository extends JpaRepository<User,Long> {

  //for this Query I don't know how to write it
  @Query("")
  List<User> findUserByRole(int role);
}

And for the schema of the DataBase it's like this:

the java version that I'm using is openjdk11, and I'm using spring 2.6.4

Solution

From what I understand,this should work!

  @Query("SELECT user FROM User user LEFT JOIN user.roles role WHERE role.id = ?1")
    List<User> findUserByRole(int role);

This page is quite useful.

Answered By - user199805
Answer Checked By - Robin (JavaFixing Admin)