0

Below is the expected table structure:

Users -> user_id (PK)
RoleA -> role_a_id (PK)
RoleB -> role_b_id (PK)
User_Roles -> user_id (FK), role_a_id (FK), role_a_id (FK)

Below are the Entity classes:

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

    public int getUserId() {
        return userId;
    }

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

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "user_id")
    private int userId;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinTable(
      name = "user_roles", 
      joinColumns = @JoinColumn(name = "user_id"), 
      inverseJoinColumns = @JoinColumn(name = "role_a_id"))
    Set<RoleA> rolesA;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinTable(
      name = "user_roles", 
      joinColumns = @JoinColumn(name = "user_id"), 
      inverseJoinColumns = @JoinColumn(name = "role_b_id"))
    RoleB rolesB;

    public Set<RoleA> getRolesA() {
        return rolesA;
    }

    public void setRolesA(Set<RoleA> rolesA) {
        this.rolesA = rolesA;
    }

    public RoleB getRolesB() {
        return rolesB;
    }

    public void setRolesB(RoleB rolesB) {
        this.rolesB = rolesB;
    }
}

@Table(name = "role_a")
@Entity
public class RoleA {

    public int getRoleAId() {
        return roleAId;
    }

    public void setRoleAId(int roleAId) {
        this.roleAId = roleAId;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "role_a_id")
    private int roleAId;
}

@Table(name = "role_b")
@Entity
public class RoleB {

    public int getRoleBId() {
        return roleBId;
    }

    public void setRoleBId(int roleBId) {
        this.roleBId = roleBId;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "role_b_id")
    private int roleBId;
}

Code to insert data:

RoleA roleA = new RoleA();
roleA.setRoleAId(1);
Set<RoleA> rolesA = new HashSet<>();
rolesA.add(roleA);

RoleB roleB = new RoleB();
roleB.setRoleBId(2);

Users user = new Users();
user.setUserId(10);
user.setRolesA(rolesA);
user.setRolesB(roleB);

userDAO.addUser(user);

While starting the server all the tables are getting created as expected but once data insertion is attempted as above, getting below error:

2020-04-01 17:14:35.203 DEBUG 30244 --- [p-nio-80-exec-2] org.hibernate.SQL                        : insert into users (user_id) values (?)
2020-04-01 17:14:35.206 TRACE 30244 --- [p-nio-80-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [95]
2020-04-01 17:14:35.249 DEBUG 30244 --- [p-nio-80-exec-2] org.hibernate.SQL                        : insert into user_roles (role_b_id, user_id) values (?, ?)
2020-04-01 17:14:35.250 TRACE 30244 --- [p-nio-80-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [2]
2020-04-01 17:14:35.251 TRACE 30244 --- [p-nio-80-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [INTEGER] - [95]
2020-04-01 17:14:35.310  WARN 30244 --- [p-nio-80-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23502
2020-04-01 17:14:35.311 ERROR 30244 --- [p-nio-80-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: null value in column "role_a_id" violates not-null constraint
  Detail: Failing row contains (2, 95, null).
2020-04-01 17:14:35.393 DEBUG 30244 --- [p-nio-80-exec-2] c.h.refoearn.controller.UserController   : DataIntegrityViolationException while adding user: could not execute statement; SQL [n/a]; constraint [role_a_id]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

As per logs insert statement for User_Roles is not considering role_a_id to be inserted and being declared as non null, the exception is throw.

Can someone please suggest the root cause why role_a_id is not being taken care while insertion in User_Roles ?
What is the missing piece stopping it to happen ?
Any other suggestion to fulfill the requirement if shared approach is not correct ?
Gaurav Verma
  • 1

2 Answers2

0

Looks like there may be some gap in the intention and implementation. The exact purpose of User_Roles table is not clear. What relationship is this table holding? Is it trying to relate Users table with

  1. RoleA
  2. RoleB
  3. both RoleA and RoleB

If it is 1st case than the role_b_id should not exist. If it is 2nd case than role_a_id should not exist.

For the 3rd case, you may need to rethink your design. Think about what relationship do you want to define, i.e., either of following may apply

  1. User is related to both RoleA and RoleB but RoleA and RoleB do not depend on each other.
  2. User is related to both RoleA and RoleB and RoleA and RoleB depend on each other.

From looking at the Users class since it has OneToOne relationship with RoleB my first guess would be that it is 1st point where RoleA and RoleB do not depend o each other. Considering that you should remove the role_b_id column in User_Roles table altogether and add it directly as a part of Users table, or have a different table created for it.

Abhishek
  • 63
  • 1
  • 5
0

After analysis and different suggestions, I found that User_Roles table will have redundant data if we consider 2 of its columns as primary key. This will be bad design from DB Normalization point of view. If all the three columns are unique and act as primary key then I don't see any specific use of the table.

So the better solution is to keep roleId having one to one mapping with the user table itself and one to many with User_Roles table. So, the final table structure would be like below:

Users -> user_id (PK), role_b_id (FK, One To One)
RoleA -> role_a_id (PK)
RoleB -> role_b_id (PK)
User_Roles -> user_id (FK), role_a_id (FK, One To Many)

This structure can be achieved by modifying the users table as below:

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

    public int getUserId() {
        return userId;
    }

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

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "user_id")
    private int userId;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinTable(
      name = "user_roles", 
      joinColumns = @JoinColumn(name = "user_id"), 
      inverseJoinColumns = @JoinColumn(name = "role_a_id"))
    Set<RoleA> rolesA;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "rolesB")
    RoleB rolesB;

    public Set<RoleA> getRolesA() {
        return rolesA;
    }

    public void setRolesA(Set<RoleA> rolesA) {
        this.rolesA = rolesA;
    }

    public RoleB getRolesB() {
        return rolesB;
    }

    public void setRolesB(RoleB rolesB) {
        this.rolesB = rolesB;
    }
}
Gaurav Verma
  • 1