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
