Optimization Inserts Hibernate when there is relationship @Manytomany

Question

Imagine the relationships:

User has Many Permissions

Permission has Many Users

We can create a relationship of N para N as follows:

User class.

public class User {
  /*Many attributes here*/
  private List permissions;
  
  @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy =         "users")
  public List getPermissions() {
    return permissions;
  }
  public void setPermissions(List permissions){
    this.permissions = permissions;
  }
}

Permission.class

public class Permission{
  /*Many attributes here..*/
  private List users;

  @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    public List<User> getUsers() {
    return users;
  }

  public void setUsers(List<User> users) {
    this.users = users;
  }
}

Thus one can affirm a relationship of N pra N whereas the Owner is the class Permission.

When I mean the user 1 has the permissions 3 and 5, for example, I do something like this:

User u = (User)session.get(User.class,  1);

List permissions = new ArrayList<>();
permissions.add(new Permission(3));
permissions.add(new Permission(5));

for(Object permission : permissions){
  Permission p = 
        (Permission) session.get(Permission.class, ((Permission)permission).getId());
  p.getUsers().add(u);
}

session.getTransaction().commit();

All functioning correctly.

However, when I visualize logs, I see that the Hibernate is first deleting all records from the associative table permission_user related that permission, then adding everything again. (Once we have access to all previously associated users when we call p.getUsers(); //pega todos os usuários já associados)

Let’s assume the permission 3 already have the user 100 and the user 101 associated with it, and so I now want to associate the user 1. Us logs, the Hibernate is running more or less like this...

delete from permission_user where id_permission = ? //deve ser id 3
insert into permission_user (id_permission, id_user) values (?, ?) //deve ser 3 e 100 respectivamente
insert into permission_user (id_permission, id_user) values (?, ?) //deve ser 3 e 101 respectivamente
insert into permission_user (id_permission, id_user) values (?, ?) //deve ser 3 e 1 respectivamente

The point is, as I’m associating a new user, my expectation is that the Hibernate can execute the insert only once (Since the user 100 and 101 are already present in the associative table). And this occurs for each permission. So, if I say the user 1 will have the permissions, 3, 5, 10, 11, 12, 13, 15, 16 and 19 and each of these permissions already has users associated with it, about 10 users for example, the Hibernate will perform more than 100 instructions to perform what I wish. This is really very problematic.

Someone could give me a light?

Thanks in advance.

Answer 1

This is because Hibernate supports an additional feature of a collection called Bag. One Bag is a collection that may have duplicated members, but is not ordered. The best feature of a Bag is that you can get the number of occurrences of an object through the API using the method public int occurrences(Object o).

In a List, there is no way to do the same without iterating through all its elements.

So when a List is mapped without a column for indexing, Hibernate treats it as a Bag. In essence, a Listwhich has a column for indexing is a Bag indexed.

With Hibernate treating the List as a Bag, He believes there may be duplicate elements in the Join table. Also, he knows that the elements in the Join table are not sorted. So, there is no way to know which lines should be deleted from the java object.

Then Hibernate needs to continue and re-insert all lines he knows from java that should not be deleted.

If you don’t need duplicate objects, you can use one Set. A Set does not need a column for indexing and Hibernate can distinguish a row from its table because it knows that a Set has no duplicate elements.

Source

That is, either you use a column to sort your Join table, or you use a Set instead of a List. In case you keep the List would look like this:

Permission:

@Entity
public class Permission {

    @Id
    private int id;

    @OrderColumn(name="order_id")
    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<User> users;

    public Permission() {}

    public Permission(int id) {
        this.id = id;
    }

    public int getId() {
        return id;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }
}

User:

@Entity
public class User {

    @Id
    private int id;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "users")
    private List<Permission> permissions;

    public User() {}

    public User(int id) {
        this.id = id;
    }

    public List<Permission> getPermissions() {
        return permissions;
    }

    public void setPermissions(List<Permission> permissions) {
        this.permissions = permissions;
    }
}

Testing:

public class Teste {

    public static void main(String[]args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("pt-stackoverflow");
        EntityManager em = emf.createEntityManager();
    
        em.getTransaction().begin();
    
        User u = em.find(User.class, 1);
     
        int permissions[] = {1, 2};
    
        for(int permission : permissions) {
            Permission p = em.find(Permission.class, permission);
            p.getUsers().add(u);
        }
    
        em.getTransaction().commit();
    
        em.close();
        emf.close();
    }
}

With the annotation:

selectuser0_.id asid1_2_0_ from User user0_Whereuser0_.id=?

select permission0_.id as id1_0_0_ from Permission permission0_ Where permission0_.id=?

select users0_.permissions_id as permissi1_1_0_, users0_.users_id as users_id2_1_0_, users0_.permissions_id_index_column as permissi3_0_, user1_.id as id1_2_1_ from Permission_user users0_InnerJoin User user1_on users0_.users_id=user1_.id Where users0_.permissions_id=?

select permission0_.id as id1_0_0_ from Permission permission0_ Where permission0_.id=?

select users0_.permissions_id as permissi1_1_0_, users0_.users_id as users_id2_1_0_, users0_.permissions_id_index_column as permissi3_0_, user1_.id as id1_2_1_ from Permission_user users0_InnerJoin User user1_on users0_.users_id=user1_.id Where users0_.permissions_id=?

Insert into Permission_user (permissions_id, permissions_id_index_column, users_id) values (?, ?, ?)

Insert into Permission_user (permissions_id, permissions_id_index_column, users_id) values (?, ?, ?)

None DELETE!