Duplicate value in the bank when inserting new users with an already used Role (Spring-Security)

Question

Hello

I’m creating a simple web application with Springmvc and Spring Security. I did the User and Roles class as follows:

@Entity
public class Usuario implements UserDetails {
  private static final long serialVersionUID = 1L;

  @Id
  private String username;

  private String password;
  private String nome;

  @OneToMany(fetch=FetchType.EAGER)
  private List<Role> roles = new ArrayList<Role>();

...
}

---

@Entity
public class Role implements GrantedAuthority {
  private static final long serialVersionUID = 1L;

  @Id
  private String role;
  private String name;

...
}

I am letting Hibernate create the tables automatically. The code generated by the table creation Hibernate is this:

Hibernate: create table Usuario (username varchar(255) not null, nome varchar(255), password varchar(255), primary key (username))
Hibernate: create table Usuario_Role (Usuario_username varchar(255) not null, roles_role varchar(255) not null)
Hibernate: alter table Usuario_Role add constraint UK_9ljdlf4fugq6jh14x7obwpi37 unique (roles_role)
Hibernate: alter table Usuario_Role add constraint FKb32xr1fddmr4pxdxuj5u14f56 foreign key (roles_role) references Role (role)
Hibernate: alter table Usuario_Role add constraint FKm7abqk7krlrd3bb61ecux2fnx foreign key (Usuario_username) references Usuario (username)

I added 3 Roles in the database and in the user’s registration they are displayed and the person can select which permissions of that user. When I add a user and select a Role that was not used works normally, however, when selecting one that is being used by another user, the duplicity error in the table 'usuario_role' (This table is being created automatically by Hibernate by the @Onetomany annotation). Even if I try to insert the user in the hand by the bank of the same error, then I think it is a problem to be creation of the tables because neither the "roles_role" nor the "Username" should be unique in the table, but I do not know what to do to fix.

Bank tests:

mysql> show tables;
+-----------------+
| Tables_in_kmcdb |
+-----------------+
| atendimento     |
| role            |
| usuario         |
| usuario_role    |
+-----------------+
4 rows in set (0.00 sec)

mysql> select * from role;
+------------+---------------+
| role       | name          |
+------------+---------------+
| ROLE_ADMIN | Administrador |
| ROLE_SUP   | Supervisor    |
| ROLE_TEC   | Técnico      |
+------------+---------------+
3 rows in set (0.00 sec)

mysql> insert into usuario(username, nome, password) value('admin','admin','admin');
Query OK, 1 row affected (0.03 sec)

mysql> insert into usuario(username, nome, password) value('fillipe','fillipe','fillipe');
Query OK, 1 row affected (0.00 sec)

mysql> insert into usuario_role(usuario_username, roles_role) value('admin','ROLE_ADMIN');
Query OK, 1 row affected (0.05 sec)

mysql> insert into usuario_role(usuario_username, roles_role) value('fillipe','ROLE_SUP');
Query OK, 1 row affected (0.00 sec)

mysql> insert into usuario_role(usuario_username, roles_role) value('fillipe','ROLE_TEC');
Query OK, 1 row affected (0.00 sec)

mysql> select * from usuario_role;
+------------------+------------+
| Usuario_username | roles_role |
+------------------+------------+
| admin            | ROLE_ADMIN |
| fillipe          | ROLE_SUP   |
| fillipe          | ROLE_TEC   |
+------------------+------------+
3 rows in set (0.00 sec)

mysql> insert into usuario_role(usuario_username, roles_role) value('fillipe','ROLE_ADMIN');
ERROR 1062 (23000): Duplicate entry 'ROLE_ADMIN' for key 'UK_9ljdlf4fugq6jh14x7obwpi37'
mysql> insert into usuario_role(usuario_username, roles_role) value('admin','ROLE_SUP');
ERROR 1062 (23000): Duplicate entry 'ROLE_SUP' for key 'UK_9ljdlf4fugq6jh14x7obwpi37'

What I need to modify to be able to insert a new user with a role that is already being used?

Thank you

Answer 1

I was able to resolve by changing the user annotation to @Manytomany

As explained very well above by Matheus,

Onetomany would be if the roles were only one user. For example: Table person and phone, would have a phone attribute in person annotated with Onetomany, a person has several phones, but the phone only belongs to one person. - Matheus Silva