How to avoid saving duplicates in @ManyToMany, but insert into mapping table?

Question

I have 2 entites Post and PostTag with many-to-many relationship. Also I have table post_tag_mapping with post_id and post_tag_id.

 @ManyToMany(cascade = {CascadeType.ALL})
  @JoinTable(
      name = "post_tag_mapping",
      joinColumns = @JoinColumn(name = "post_id"),
      inverseJoinColumns = @JoinColumn(name = "tag_id")
  )
  @Getter
  @Builder.Default
  private Set<PostTag> postTagSet = new HashSet<>();

If I create Post with Set<PostTag> - I save post, 1 or more post_tag, and table post_tag_mapping like post_id tag_id - (1, 1), (1, 2), (1, 3), etc.

But if I save post with post_tag name that already exists in database - I want to not save it to post_tag(I have unique index on post_tag.name), but create new post_tag_mapping for another post.

Now I get exception SQLIntegrityConstraintViolationException: Duplicate entry 'tag1' for key 'post_tag.idx_post_tag_name'

Don't really understand how to implement it.

Answer 1

If I understand well your predicament, your problem is that you are trying to insert new PostTag when saving your Post entity.

Since you are doing a cascade save due to CascadeType.ALL, your EntityManager is roughly doing this:

• Saving Post
• Saving PostTag which cause your exception
• Saving Post <-> PostTag

You should

1. Have a service (for example: PostTag findOrCreateTagByName(String)) that fetch existing PostTag by name and eventually create them. Thus returning existing PostTag.
2. Save the Post after with the association with said existing tags.

---

Edit (as answer to comment):

The JPA is only a mapping to a relational database.

In your code, you only shows the mapping which says that a Post is linked to several PostTag (and that PostTag are linked to several Post).

You added a unique constraint which apply on all tags: in all your database, there must be one tag "A", one tag "B", and so on.

If you populate your object like this (I don't use lombok, so I assume a minimal constructor here):

Post post = new Post();
post.setXXX(...); 
post.getPostTagSet().add(new PostTag("A"));
post.getPostTagSet().add(new PostTag("B"));

This means that you create two new tags named A and B.

The JPA Implementation (Hibernate, EclipseLink) are not magic: they won't fetch for you the existing tag and that where it will fail. If you violate the unicity constraint on table post_tag, this means you are inserting twice the same value. Either in the same transaction, either because the tag is already present in the table.

For example:

post.getPostTagSet().add(new PostTag("A"));
post.getPostTagSet().add(new PostTag("A"));

If you did not define correctly the hashCode(), then only the object identity hashCode would be used and there would be an attempt to add (insert) two tag A.

The only thing you can do here, is to restrict the PostTag by implementing correctly hashCode()/equals so that the PostTagSet ensure unicity only for the related Post.

Let's say now that you first fetch them and have a new tag C:

Post post = new Post();
post.setXXX(...); 
for (String tagName : asList("A", "B", "C")) {
  post.getPostTagSet().add(tagRepository.findByName(tagName)
                                     .orElseGet(() -> new PostTag(tagName ));
}
postRepository.save(post);

The tagRepository is simply a Spring JPA Repository - which I think you are using - and the findByName signature is:

Optional<String> findByName(String tagName);

The code will do:

• Find tag A: it is in database as in PostTag(1, "A")
• Find tag B: it is in database as in PostTag(2, "B")
• Find tag C: it is not in database, create it.

This should then work because the cascade will perform a save on the Post, then on the PostTag, then on the relation Post <-> PostTag.

In term of SQL query, you should normally see something like this:

insert into post_tag (tag_id, name) (3, "C")
insert into post (post_id, ...) (<some id>, ...)
insert into post_tag_mapping (tag_id, post_id) (1, <some id>)
insert into post_tag_mapping (tag_id, post_id) (2, <some id>)
insert into post_tag_mapping (tag_id, post_id) (3, <some id>)

The other problem here is with the hashCode() and equals() provided by PostTag which ensure unicity of PostTag for one single Post:

If you use the id in hashCode() (and equals use id and name):

• If you use the id, then the set will have PostTag(1, "A"), PostTag(2, "B") and PostTag("C")
• When you save, PostTag("C") will have an id assigned -> PostTag(3, "C")
• With standard HashSet, the PostTag("C") will no longer be in its valid bucket and you will fail to find it again.

This may not be problematic if you don't use the object after the set but I think it is best to first save the PostTag (assigning it an id) then add it to the set.

If you use the name in hashCode() and equals: as long as you don't update the name after insertion in the set, you won't have a problem.

Answer 2

Just do it.

@SpringBootApplication
public class DemoApplication implements ApplicationRunner{

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Autowired
    PostRepository postRepository;
    @Autowired
    PostTagRepository postTagRepository;
    @Override
    public void run(ApplicationArguments args) throws Exception {
        init();
        testException("name");
        addNewPost("name");
        addNewPost("other");
        readPosts();
    }
    private void init() {
        postTagRepository.save(PostTag.builder().name("name").build());
    }
    private void testException(String name) {
        try {
            PostTag postTag = postTagRepository.save(PostTag.builder().name(name).build());
            postRepository.save(Post.builder().tags(Collections.singleton(postTag)).build());
        } catch ( DataIntegrityViolationException ex ) {
            System.out.println("EX: " + ex.getLocalizedMessage());
        }
    }
    private void addNewPost(String name) {
        PostTag postTag = postTagRepository.findByName(name)
                .orElseGet(()->postTagRepository.save(PostTag.builder().name(name).build()));
        postRepository.save(Post.builder().tags(Collections.singleton(postTag)).build());
    }
    private void readPosts() {
        System.out.println(postRepository.findAll());
    }

}

And don't use things you don't understand

@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Post {
    @Id @GeneratedValue
    private Long id;
    @ManyToMany
    private Set<PostTag> tags;
}

And get grammerly.

And handle eager fetch in the repo.

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {
    @EntityGraph(attributePaths = { "tags" })
    List<Post> findAll();
}