Tag: many-to-many

When updating many-to-many relationships, the SQL executed by JPA may be quite inefficient.

Suppose we have a Post-Tag association: each post can have multiple tags, and each tag can also has multiple posts.

Java code:

// Post.java
@Entity
public class Post {
  @Id
  private Integer id;
  
  @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
  @JoinTable(
    name = "post_tag",
    joinColumns = @JoinColumn(name = "post_id"),
    inverseJoinColumns = @JoinColumn(name = "tag_id")
  )
  private List<Tag> tags = new ArrayList<>();

  // getters and setters

  public void addTag(Tag tag) {
    tags.add(tag);
    tag.getPosts().add(this);
  }

  public void removeTag(Tag tag) {
    tags.remove(tag);
    tag.getPosts().remove(this);
  }
}

// Tag.java
@Entity
public class Tag {
  @Id
  private Integer id;
  
  @ManyToMany(mappedBy = "tags")
  private List<Post> posts = new ArrayList<>();

  // getters and setters
}

Suppose a post(id = 1) has two tags(id = 1, 2). When adding a new tag(id = 3) to the post, JPA may issue the following SQL:

DELETE FROM post_tag WHERE post_id = 1;
INSERT INTO post_tag(post_id, tag_id) VALUES (1, 1);
INSERT INTO post_tag(post_id, tag_id) VALUES (1, 2);
INSERT INTO post_tag(post_id, tag_id) VALUES (1, 3);

While inserting a single record is enough, it at first deletes all existing association records, and then creates all "new" association records. Same problems applies when deleting tags from the post.

It's quite inefficient. And if you have additional columns ("createdAt" for example) in the association table, or you have some triggers based on inserting/deleting association records, you may encounter some problems.

How to solve this problem? There may be two solutions.