Problems to remove @Manytomany element with @Jointable

Question

I’m having trouble deleting a @Manytomany relationship on JPA. In short, I have 3 tables: USER, PERMISSION and USUARIO_PERMISSAO which is the relationship of the previous ones, the relationship is N to N. The problem is that I do not know how to do to remove or add a new permission to a user existing on the basis, follows simplified model:

@Entity
@Table(name="TB_USUARIO")
public class Usuario {

    @Id
    @Column(name="NO_USUARIO", unique=true, nullable=false, length=50)
    private String noUsuario;

    @ManyToMany
    @JoinTable(
        name="TB_USUARIO_PERMISSAO"
        , joinColumns={
            @JoinColumn(name="NO_USUARIO", nullable=false)
            }
        , inverseJoinColumns={
            @JoinColumn(name="NO_METODO", referencedColumnName="NO_METODO", nullable=false),
            @JoinColumn(name="NO_PERMISSAO", referencedColumnName="NO_PERMISSAO", nullable=false))
            }
        )
    private List<Permissao> permissoes;
}



@Entity
@Table(name="TB_PERMISSAO")
public class Permissao {

    @EmbeddedId
    private PermissaoPK id;

    @ManyToMany(mappedBy="permissoes")
    private List<Usuario> usuarios;
}

My problem is with field mapping permissions class User, when I insert a new User into the database already adding the permission works, follow code that works:

Usuario usuario = new Usuario("USUARIO_01");
usuario.getPermissoes().add(permissao); //permissao ja cadastrada no banco
entityManager.persist(usuario);

This code correctly inserts a new user and also the relationship with the permission in the table TB_USUARIO_PERMISSAO. The problem now is to delete permissions, let’s assume that a user has 2 permissions and I want to delete one of them, how do I do that? It follows that doesn’t work:

Usuario usuario = entityManager.find(Usuario.class, "USUARIO_01");
usuario.getPermissoes().remove(0);
entityManager.merge(usuario);

This code above does not work, do I have to change the mapping, I know there, maybe creating a user entity permission? The mapping is the way the reverse engineering engine created it.. If I remove a user, the permissions are automatically deleted. My problem is in rule out and also add a new permission of a user already registered in the bank. I have also used Cascade and unsuccessfully.

Can help?

Note: To simplify changing the code by the editor, there may be an error that if you put it in the IDE it will not compile, the idea is to show the base. I also tried other ways to remove/add that I thought it best not to mention since they didn’t work

Grateful!

Answer 1

First of all I believe that your mapping can be simplified, mainly the join table.

To centralize the mapping of the primary key of the entities I created a BaseEntity, thus:

@MappedSuperclass
public abstract class BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

}

The entity Usuario was like this:

@Entity
@Table(name = "tb_usuario")
public class Usuario extends BaseEntity {

    @Column(name = "no_usuario", unique = true, nullable = false, length = 50)
    private String nome;

    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}) 
    @JoinTable(name = "tb_usuario_permissao", joinColumns = {
                @JoinColumn(name = "id_usuario", nullable = false)
            }, inverseJoinColumns = {
                @JoinColumn(name = "id_permissao", referencedColumnName = "id", nullable = false)
            }
    )
    private Set<Permissao> permissoes;

}

Here we point out the following:

• do not mix business logic with your data model, so do not use the user name as the primary key, consider it a unique key and have
• Cascade inclusion: I don’t know if you’ll need this, just include so you don’t have to persist each permission separately. For more details on the behavior of each type see specification of JPA
• since we have a primary key in tb_permissao it doesn’t make sense to have two merge columns referencing business columns. There is usually more than one join column when it is a foreign key to another table, for example.

The entity Permissao, was like this:

@Entity
@Table(name = "tb_permissao")
public class Permissao extends BaseEntity {

    @Column(name = "no_permissao", unique = true, nullable = false, length = 50)
    private String nome;

    @Column(name = "no_metodo", unique = true, nullable = false, length = 50)
    private String metodo;

}

Unless you really need to know all users have a certain permission do not need to map, even in this case I would prefer to make a query.

Now, the removal part, let’s look at this excerpt:

usuario.getPermissoes().remove(0);

Well, with this model above the removal occurred as desired, logically when there are one or more permissions.

as an example (I am using Lombok), the insertion test was like this:

final Permissao permissao1 = Permissao.builder().nome("permissao1").metodo("metodo1").build();
em.persist(permissao1);

final Permissao permissao2 = Permissao.builder().nome("permissao2").metodo("metodo2").build();
em.persist(permissao2);

final Usuario usuario = Usuario.builder().nome("Bruno").permissao(permissao1).permissao(permissao2).build();
em.persist(usuario);

And so:

final Permissao permissao3 = Permissao.builder().nome("permissao3").metodo("metodo3").build();
final Permissao permissao4 = Permissao.builder().nome("permissao4").metodo("metodo4").build();

final Usuario usuario = Usuario.builder().nome("César").permissao(permissao3).permissao(permissao4).build();
em.persist(usuario);

Removal so:

final Usuario usuario = em.find(Usuario.class, 1L);
usuario.getPermissoes().remove(0);
em.merge(usuario);

And adding a new permission to an existing user like this:

final Permissao permissao = em.find(Permissao.class, 1L);
final Usuario usuario = em.find(Usuario.class, 1L);
usuario.getPermissoes().add(permissao);
em.merge(usuario);

And one not yet so existent:

final Permissao permissao5 = Permissao.builder().nome("permissao5").metodo("metodo5").build();
final Usuario usuario = em.find(Usuario.class, 1L);
usuario.getPermissoes().add(permissao5);
em.merge(usuario);

As you did not provide more details in your question, if you do not solve your problem I ask you to provide more details, such as your PermissaoPK (to know if you really need).

Answer 2

Assuming you get the id of user and permission that wants to be removed, can be done the delete manually via a query.

Ex:

entityManager.createQuery("DELETE FROM USUARIO_PERMISSAO up WHERE up.usuarioId = :usuarioId AND up.permissaoId = :permissaoId");

Because by using remove(index) as it is trying to use, even partially functioning, the position order List<T> to receive the values for that user, may end up not the desired permission thus having inconsistency when deleting.

In the above solution, the change occurs only in table that keeps the relationships, having the ids, it is explicit what you want to remove.