[FIXED] JPA duplicate entry error on EntityManager.remove and then EntityManager.persist

Issue

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?

Solution

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.

Answered By - Michal Krasny
Answer Checked By - Timothy Miller (JavaFixing Admin)