0

Since 2h I'm trying to come up with the solution for the following problem, and I just can't find a proper way to do it:

I have three tables in MySQL db:

Role [role_id] [role_name]

Permission [permission_id] [permission_name]

PermissionToRole [permission_id] [role_id]

I have the Role class as the owner of the relationship:

@ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
@JoinTable(name="permissiontorole", 
            joinColumns={@JoinColumn(name="role_id")}, 
            inverseJoinColumns={@JoinColumn(name="permission_id")})

The Permission class mapping defined as:

@ManyToMany(mappedBy="permissions")
private Set<UserRole> userRoles = new HashSet<UserRole>();

Now, when I delete either permission or role those respective records are removed from their tables (this is expected and desired), but the relationship still remains in PermissionToRole table and I have no clue how to remove that whenever I delete either Role or Permission record. when i change the cascade type to ALL, then when i remove role or permission the relationship is removed but so is the other record e.g. if i remove role the permission is also removed.

Any ideas how to deal with it?

Stugal
  • 850
  • 1
  • 8
  • 24

1 Answers1

2

You simply need to remove the permission from the Role.permissions collection, to remove the association between the role and the permission:

for (UserRole role: permissionToDelete.getUserRoles()) {
    role.getPermissions().remove(permissionToDelete);
}
em.remove(permissionToDelete);
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • Yeah, that works, but meanwhile I found other solution and I wonder which one is better. I set foreign keys in the table with the on delate action = cascade, that also did the job without the additional code, so which one would be better and why? Obviously on one hand there's less code in my solution but on other it adds extra complexity to the DB – Stugal Dec 22 '13 at 11:00
  • Usually, you want an exception to be thrown when you delete a permission that is still referenced by a role, because it means that you probably forgot something. Blindly deleting the permission and its associations with roles thanks to the cascade allows deleting a permission without even checking if a role still references it, which might be a problem. – JB Nizet Dec 22 '13 at 12:22
  • Yep You're right. I have one more question though, in order to update the UserRole record (add additional permissions) I first need to pull it out from DB and append the new permission(s) to the list? Or is there better/faster way? – Stugal Dec 22 '13 at 13:06
  • There is no faster way, other than using SQL, or mapping the association table and transforming the AnyToMany association into 2 OneToMany/ManyToOne associations. – JB Nizet Dec 22 '13 at 13:09