How to configure oneToMany Mapping in User -Role-Permission

Question

I have a spring boot app(Spring boot 2) with spring data jpa.i have 3 MYSQL tables to store user information ,roles and permissions

• User will contains basic user details like username ,password firstname,lastname.
• Role represent the user roles like Admin ,User,Staff,test (user can have many roles)
• Permission has 3 possibilities Read,Write,Customize(each role has many permissions)
• UserRole - joint table for User and Roles
• RolePermission - joint table for roles and permissions

Am looking for Spring boot service , which will return false if the user is not validated against the User table ,if the user is validated successfully then the response should contains the user roles and permissions .

I was able to build the sample rest service but failed to setup the below

1. How to configure the entity classes for the oneToMany mapping in this requirement
2. What would be the corresponding query in the repository interface

Please see the entity tables

@Entity
@Table( name = "TURBINE_USER" )
public class PortalUser {

public PortalUser() {

}

public PortalUser(long userID ,String userName , String password , String firstName, String lastName, String email) {
    this.userID = userID;
    this.userName = userName;
    this.password = password;
    this.firstName = firstName;
    this.lastName = lastName;
    this.email =  email;
}


@Id
@Column(name="USER_ID",unique=true)
private long userID;

@NotNull
@Column(name="LOGIN_NAME",unique=true)
private String userName;

@NotNull
@Column(name="PASSWORD_VALUE")
private String password;

@NotNull
@ColumnDefault("")
@Column(name="FIRST_NAME")
private String firstName;

@NotNull
@ColumnDefault("")
@Column(name="LAST_NAME")
private String lastName;

@Column(name="EMAIL")
private String email;

@Column(name="CONFIRM_VALUE")
private String confirmValue;

@NotNull

@Column(name="CREATED")
private Timestamp createdDt;

@NotNull

@Column(name="MODIFIED")
private Timestamp modified;

@NotNull
@Column(name="LAST_LOGIN")
private Timestamp lastLogin;

@Column(name="DISABLED")
private char disabled;

@Column(name="OBJECTDATA")
private byte[]  objectData;

@NotNull
@Column(name="PASSWORD_CHANGED")
private Timestamp passwordChanged;
 // getters and setters
}


@Entity
@Table(name =  "TURBINE_ROLE")
public class Role {
    @Id
    @Column(name=  "ROLE_ID",unique  = true)
    private long roleId;

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

}


@Entity
@Table(name ="TURBINE_PERMISSION")
public class Permission {

    @Id
    @Column(name=  "PERMISSION_ID")
    private long permissionId;

    @Column(name= "PERMISSION_NAME")
    private String name;
}

@Entity
@Table(name= "TURBINE_USER_GROUP_ROLE")
public class UserRoles {

    @Column(name="USER_ID")
    private PortalUser user;

    @Column(name="ROLE_ID")
    private Role roles;
}


@Entity
@Table(name="TURBINE_ROLE_PERMISSION")
public class RolePermission {

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

    @Column(name= "PERMISSIONID")
    private Permission permissionId;
}

Answer 1

It is ManyToMany relationship between User and Role, Role and Permission entities. You should not define Join tables as entities, and they are generated/managed by JPA implementation.

For example, ManyToMany annotation for User and Role entities:

public class PortalUser {
    ...

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

Tested with CMobileCom JPA.

Disclaimer: I am a developer of CMobileCom JPA, a light weight JPA implementation for Java and Android.