JPA duplicate entry error on EntityManager.remove and then EntityManager.persist-CodePudding

I am using Hibernate implementation of JPA. Let's say I have a list of objects which I have to persist in a table called Event. All these objects have the same zip code.

public class Event {
    String id;
    String zipCode;
    String locationCode;
    String eventName;
    String eventDesc;
}

Here id is the primary key and zipCode and locationCode together make a unique key (UK_zipCode_locationCode). The table might already have objects with the given zip code. So, instead of finding which ones should be added, deleted or updated, what I do is delete all the objects in the table with the given zip code first and then insert all the given objects.

// getEventsToAdd method returns the list of events to be added for the zipCode 1234
// getEventsFromTheDB method returns all the events in the db with the zipCode 1234 

List<Event> eventsToAdd = getEventsToAdd("1234");
List<Event> oldEvents = getEventsFromTheDB("1234");

for (Event e : oldEvents) {
    entityManager.remove(e);
}

for (Event e : eventsToAdd) {
    entityManager.persist(e);
}
entityManager.flush();
// ...

This works when the oldEvents list is empty or when all objects in the oldEvents are also in eventsToAdd list (by this I mean the event objects with the same id and same zip code).

However, if there are some event objects in oldEvents which have different id, i.e., does not match with the id of any object in eventsToAdd list, then it throws an exception

Duplicate Entry found for key UK_zipCode_locationCode

The error is as if the old events were not deleted from the table and now inserting the events with the same values of zipCode and locationCode is causing org.hibernate.exception.ConstraintViolationException.

However, if I call entityManager.flush() after deleting the old events, it works -

// This works!

for (Event e : oldEvents) {
    entityManager.remove(customizedProviderAttribute);
}
// flush after removing all the old events
entityManager.flush();
for (Event e : eventsToAdd) {
    entityManager.persist(e);
}

So, why does flushing at the end does not work but flushing after removing the old entities work?

CodePudding user response：

By default the EntityManager does all SQL commands at the point when transaction is committed. However it can decide in which order it does the SQL commands and in your case the inserts were done before delete, which caused the ConstraintViolationException. flush() causes all SQL to be done immediately, so you achieve deletion before insertion. World is not perfect, neither is Hibernate.

CodePudding user response：

The entity manager does not necessarily issue delete and insert statements when you call remove and persist, it waits and generates the SQL later, typically when you flush explicitly or implicitly. That means the order of the statements will be different, so some inserts may be performed before some deletes, thus triggering the constraint violation. Your workaround with the intermediate flush is common practice in cases like this.

CodePudding user response：

In your second working example when you flush after deletion ,Hibernate will change the state of those entities to REMOVED,to stay synchronized with the database AS if the deletion was physically done,and in your logs you'll see a delete sql query issued,that's why when you persist those same entities ,it'll work ,as for the first example not working,because those entities are still in MANAGED state and you're trying to persist them again,which causes duplicate entries,like @Michal said ,the insertions where issued before the deletion , because the order is not guaranteed.