[FIXED] How to both delete and add value from a set

Issue

Considering the following entities:

@Entity
public class MainEntity {
  /*...*/
  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "mainEntity")
  private Set<SubEntity> subEntities = new LinkedHashSet<>();
  /*...*/
}

and

@Entity
@Table(uniqueConstraints = {
  @UniqueConstraint(columnNames = {"foo", "bar"})
})
public class SubEntity {
  /*...*/

  @ManyToOne(optional = false)
  private MainEntity mainEntity;

  @Column(nullable = false)
  private String foo;

   @Column(nullable = false)
  private String bar;
  /*...*/
}

I create a new MainEntity with 1 SubEntity with {foo:"a", bar:"b"} and persist it.

MainEntity mainEntity = new MainEntity();
SubEntity sub = new SubEntity();
sub.setFoo("a");
sub.setBar("b");
sub.setMainEntity(mainEntity);
mainEntity.setSubEntities(new LinkedHashSet<>(sub));
entityManager.persist(mainEntity);

Then I update the MainEntity by dropping the above SubEntity and adding a new one with the exact same foo and bar.

I try to persist the MainEntity again.

SubEntity anotherSub = new SubEntity();
anotherSub.setFoo("a");
anotherSub.setBar("b");
anotherSub.setMainEntity(mainEntity);
// replace
mainEntity.setSubEntities(new LinkedHashSet<>(Arrays.asList(anotherSub)));
entityManager.persist(mainEntity); // throws org.hibernate.exception.ConstraintViolationException: could not execute statement

I expect the initial SubEntity to be deleted and a new SubEntity to be added.

However, the very first query is adding the new SubEntity (before removing the old one) which does not work since there is a SQL unique constraint that is violated because the other SubEntity is not deleted yet.

What am I missing?

Solution

id	foo	bar	main_entry_id
1	a	b	1

if you have the table like this with unique foo and bar, first you have to get the id of the previous entry and set it to the current entry id.
For example:

MainEntity mainEntity = new MainEntity();
SubEntity sub = new SubEntity();
sub.setFoo("a");
sub.setBar("b");
sub.setMainEntity(mainEntity);
mainEntity.setSubEntities(new LinkedHashSet<>(sub));
MainEntity mainEntityResult = entityManager.persist(mainEntity);

SubEntity anotherSub = new SubEntity();
anotherSub.setFoo("a");
anotherSub.setBar("b");
anotherSub.setMainEntity(mainEntity);
Long subEntryId = SubEntryService.getIdbyFoo(mainEntityResult.getId()); // create a service to get the id of previous 'subEntity'
anotherSub.setId(subEntryId);

mainEntity.setSubEntities(new LinkedHashSet<>(Arrays.asList(anotherSub)));
entityManager.persist(mainEntity);

the concept is to update already existing entry we have to pass the id of that previous entry

Answered By - Mani