Have Users, Roles and User Roles tables.

Users

id
username

Roles

role_id
role_name

user_roles

user_id (fk users table)
role_id (fk roles table)

I defined my entities this way user_roles Entity

@Column(name = "USER_ID")
private int userId;

@Column(name = "ROLE_ID")
private int roleId;

@ManyToOne()
@JoinColumn(name="ROLE_ID", insertable = false, updatable = false)    
private RoleGroup userRole;

@ManyToOne()
@JoinColumn(name="id", insertable = false, updatable = false)    
private User user;

User entity:

@Id
@Column(name="ID")
@GeneratedValue(strategy = GenerationType.IDENTITY) 
private int id;
    
@Column(name = "USER_NAME")
private String userName;

Roles Entity:

@Id
@Column(name="ROLE_ID")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
    
@Column(name = "ROLE_NAME")
private String roleName;

@OneToMany(targetEntity=UserRoleGroup.class, mappedBy="userRole",cascade=CascadeType.ALL, 
    fetch = FetchType.LAZY)    
private List<UserRoleGroup> userGroup;

User table will store all lists of users. Roles table is the master table which is having 5 rows - (Admin, Read, manager..) User Role Table will have user_id from user table and role_id from roles table.

Example

USER:

id   user_name
1    test@gmail.com
2    abc@gmail.com

Roles

role_id     Name
1          Admin      
2          Manager
3          Read

User Roles

User_Id    Role_ID
1           1
1           2
2           3

Repository

@Query("FROM UserRoleGroup AS urg LEFT JOIN urg.userRole AS ur LEFT JOIN urg.user AS u WHERE u.userName = ?1") 
    public List<UserRoleGroup> findAllUserRoles(String userName);

No compilation error, no runtime error, but query isn't returning anything whereas I have data in table. when i run query in MySql I am getting data

CodePudding user response：

I was able to get this working with some minor changes to the entity classes and by adding a DTO. I think its not a good practice to pass around the entity to the client. Below is my implementation.

UserRoleGroup

@Entity
@Table@Data
public class UserRoleGroup {

    @EmbeddedId
    private UserRoleId id;

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("userId")
    private User user;

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("roleId")
    private Role role;
}

UserRoleId

@Embeddable
public static class UserRoleId implements Serializable {
    private static final long serialVersionUID = 1L;

    @Column(name = "user_id")
    private Integer userId;

    @Column(name = "role_id")
    private Integer roleId;

    public UserRoleId() {
    }

    public UserRoleId(Integer userId, Integer roleId) {
        super();
        this.userId = userId;
        this.roleId = roleId;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        UserRoleId that = (UserRoleId) o;

        if (!userId.equals(that.userId)) return false;
        return roleId.equals(that.roleId);
    }

    @Override
    public int hashCode() {
        int result = userId.hashCode();
        result = 31 * result   roleId.hashCode();
        return result;
    }
}

User

import javax.persistence.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

/**
 * user can have roles
 * */

@Entity
@Table
public class User {
    @Id
    @Column(name="id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "username")
    private String userName;

    @OneToMany(mappedBy = "user",
            cascade = CascadeType.ALL,
            orphanRemoval = true)
    private Set<UserRoleGroup> roles = new HashSet<>();

    public Integer getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

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

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

Role

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;


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

    @Column(name = "role_name")
    private String roleName;

    @OneToMany(mappedBy="role",
            cascade=CascadeType.ALL,
            fetch = FetchType.LAZY)
    private List<UserRoleGroup> users = new ArrayList<>();

    public Integer getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public List<UserRoleGroup> getUsers() {
        return users;
    }

    public void setUsers(List<UserRoleGroup> users) {
        this.users = users;
    }
}

UserGroupRepository

import com.chait.json.generate.entity.UserRoleGroup;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserGroupRepository extends JpaRepository<UserRoleGroup,Integer> {

    @Query(
            "FROM UserRoleGroup AS urg LEFT JOIN urg.role AS ur LEFT JOIN urg.user AS u WHERE u.userName = ?1"
    )
    List<UserRoleGroup> findAllUserRoles(@Param("userName") String userName);
}

UserRoleDTO

@Data
@AllArgsConstructor
public class UserRoleDTO {
    private String username;
    private Set<String> roles;
}

UserRestController

@RestController
@RequestMapping("/users")
@RequiredArgsConstructor
public class UserRestController {

    private final UserGroupRepository userGroupRepository;

    @GetMapping("/{username}")
    public ResponseEntity<UserRoleDTO> getUserRoleByUsername(
           @PathVariable("username") String username
    ) {
        List<UserRoleGroup> userRoleGroups = userGroupRepository.findAllUserRoles(username);

        return ResponseEntity.ok(
                new UserRoleDTO(
                        userRoleGroups.get(0).getUser().getUserName(),
                        userRoleGroups
                                .stream()
                                .map(UserRoleGroup::getRole)
                                .map(Role::getRoleName)
                                .collect(Collectors.toSet())
                )
        );
    }
}