Delete then create records are causing a duplicate key violation with Spring Data JPA

Question

So, I have this scenario where I need to take a header record, delete the details for it, then re-create the details in a different way. Updating the details would be way too much trouble.

I basically have:

@Transactional
public void create(Integer id, List<Integer> customerIDs) {

    Header header = headerService.findOne(id);
    // header is found, has multiple details

    // Remove the details
    for(Detail detail : header.getDetails()) {
        header.getDetails().remove(detail);
    }

    // Iterate through list of ID's and create Detail with other objects
    for(Integer id : customerIDs) {
        Customer customer = customerService.findOne(id);

        Detail detail = new Detail();
        detail.setCustomer(customer);

        header.getDetails().add(detail);
    }

    headerService.save(header);
}

Now, the database has a constraint like the following:

Header
=================================
ID, other columns...

Detail
=================================
ID, HEADER_ID, CUSTOMER_ID

Customer
=================================
ID, other columns...

Constraint:  Details must be unique by HEADER_ID and CUSTOMER_ID so:

Detail  (VALID)
=================================
1, 123, 10
2, 123, 12

Detail  (IN-VALID)
=================================
1, 123, 10
1, 123, 10

OK, when I run this and pass in 2, 3, 20, etc. customers, it creates all Detail records just fine as long as there weren't any before.

If I run it again, passing in a different list of customers, I expect ALL details to be deleted first then a list of NEW details to be created.

But what's happening is that the delete doesn't seem to be honored before the create. Because the error is a duplicate key constraint. The duplicate key is the "IN-VALID" scenario above.

If I manually populate the database with a bunch of details and comment out the CREATE details part (only run the delete) then the records are deleted just fine. So the delete works. The create works. It's just that both don't work together.

I can provide more code is needed. I'm using Spring Data JPA.

Thanks

UPDATE

My entities are annotated with basically the following:

@Entity
@Table
public class Header {
...
    @OneToMany(mappedBy = "header", orphanRemoval = true, cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
    private Set<Detail> Details = new HashSet<>();

...
}

@Entity
@Table
public class Detail {
...
    @ManyToOne(optional = false)
    @JoinColumn(name = "HEADER_ID", referencedColumnName = "ID", nullable = false)
    private Header header;
...
}

UPDATE 2

@Klaus Groenbaek

Actually, I didn't mention this originally but I did it that way the first time. Also, I am using Cascading.ALL which I assume includes PERSIST.

Just for testing, I have updated my code to the following:

@Transactional
public void create(Integer id, List<Integer> customerIDs) {

    Header header = headerService.findOne(id);

    // Remove the details
    detailRepository.delete(header.getDetails());       // Does not work

    // I've also tried this:
    for(Detail detail : header.getDetails()) {
        detailRepository.delete(detail);
    }


    // Iterate through list of ID's and create Detail with other objects
    for(Integer id : customerIDs) {
        Customer customer = customerService.findOne(id);

        Detail detail = new Detail();
        detail.setCustomer(customer);
        detail.setHeader(header);

        detailRepository.save(detail)
    }
}

Again...I want to reiterate....that the delete WILL WORK if I don't have the create immediately afterwards. The create WILL WORK if I don't have the delete immediately before it. But neither will work if they are together because of the duplicate key constraint error from the database.

I've tried the same scenario WITH and WITHOUT cascading deletes.

Answer 1

Hold on to your hat, as this is a rather long explanation, but when I look at your code, it looks like you are missing a couple of key concepts about how JPA works.

First, adding Entities to a collection or removing entities from a collection does not mean that that the same operation will occur in the database, unless a persistence operation is propagated using cascadeding or orphanRemoval.

For an entity to be added to the database, you must call EntityManager.persist() either directly, or through cascading persist. This is basically what happens inside JPARepository.save()

If you wish to remove an entity, you must call EntityManager.remove() directly or by cascading the operation, or through JpaRepository.delete().

If you have a managed entity (one that is loaded into a persistence context), and you modify a basic field (non-entity, non-collection) inside a transaction, then this change is written to the database when the transaction commits, even if you did not call persist/save. The persistence context keeps a internal copy of every loaded entity, and when a transaction commits it loops through the internal copies and compares to the current state, and any basic filed changes triggers an update query.

If you have added a new Entity (A) to a collection on another entity (B), but have not called persist on A then A will not be saved to the database. If you call persist on B one of two things will happen, if the persist operation is cascaded, A will also be saved to the database. If persist is not cascaded you will get an error, because a managed entity refers to an unmanaged entity, which give this error on EclipseLink: "During synchronization a new object was found through a relationship that was not marked cascade PERSIST". Cascade persist makes sense because you often create a parent entity and it's children at the same time.

When you want to remove an Entity A from a collection on another Entity B, you can't rely on cascading, since you are not removing B. Instead you have to call remove on A directly, removing it from the collection on B does not have any effect, as no persistence operation has been called on the EntityManager. You can also use orphanRemoval to trigger delete, but I would advise you to be careful when using this feature, especially since you seem to be missing some basic knowledge about how persistence operations work.

Normally it helps to think about the persistence operation, and which entity it must be applied to. Here is how the code would have looked if I had written it.

@Transactional
public void create(Integer id, List<Integer> customerIDs) {

    Header header = headerService.findOne(id);
    // header is found, has multiple details

    // Remove the details
    for(Detail detail : header.getDetails()) {
        em.remove(detail);
    }

    // em.flush(); // In some case you need to flush, see comments below

    // Iterate through list of ID's and create Detail with other objects
    for(Integer id : customerIDs) {
        Customer customer = customerService.findOne(id);

        Detail detail = new Detail();
        detail.setCustomer(customer);
        detail.setHeader(header);  // did this happen inside you service?
        em.persist(detail);
    }
}

First there is no reason to persist the Header, it is a managed entity and any basic field you modify will be change when the transaction commits. Header happens to be the foreign key for the Details entity, which means the important thing is detail.setHeader(header); and em.persist(details), since you must set all foreign relations, and persist any new Details. Likewise, removing existing details from a Header, has nothing to do with the Header, the defining relation (foreign key) is in Details, so removing details from the persistence context is what removes it from the database. You can also use orphanRemoval, but this require additional logic for each transaction, and In my opinion the code is easier to read if each peristence operation is explicit, that way you don't need to go back to the entity to read the annotations.

Finally: The sequence of persistence operation in your code, does not transalte to the order of queries executed against the database. Both Hibernate and EclipseLink will insert new entities first, and then delete existing entities. In my experience this is the most common reason for "Primary key already exist". If you remove an entity with a specific primary key, and then add a new entity with the same primary key, then the insert will occur first, and cause a key violation. This can be fixed by telling JPA to flush the current Persistence state to the database. em.flush() will push the delete queries to the database, so you can insert another row with the same primary key as one you have deleted.

That was a lot of information, please let me know if there was anything you did not understand, or need me to clarify.

Answer 2

The cause is described by @klaus-groenbaek but I noticed something funny while working around it.

While using the Spring JpaRepository I was not able to get this to work when using a derived method.

So the following does NOT work:

void deleteByChannelId(Long channelId);

But specifying an explicit (Modifying) Query makes it work correctly, so the following works:

@Modifying
@Query("delete from ClientConfigValue v where v.channelId = :channelId")
void deleteByChannelId(@Param("channelId") Long channelId);

In this case the statements are committed / persisted in the correct order.

Answer 3

First of all, just performing header.getDetails().remove(detail); do not perform any kind of operations on DB. I suppose that in headerService.save(header); you invoke something like session.saveOrUpdate(header).

Basically it is some kind of logical collision, because Hibernate needs to delete and create entities with duplicate keys in one operation, but it doesn't know order in which these operations should be performed.

I would suggest at least invoke headerService.save(header); before adding new details, i.e. like this:

    // Remove the details
    for(Detail detail : header.getDetails()) {
        header.getDetails().remove(detail);
    }

    headerService.save(header);

    // Iterate through list of ID's and create Detail with other objects
    for(Integer id : customerIDs) {
        // ....
    }

    headerService.save(header);

in order to tell to Hibernate: yes, delete this entities, which I have removed from collection, and after that add new entites.

Answer 4

Probably em.flush() do the trick.

so the code could be:

@Transactional
public void create(Integer id, List<Integer> customerIDs) {

    Header header = headerService.findOne(id);

    // Remove the details
    detailRepository.delete(header.getDetails());       // Does not work

    // I've also tried this:
    for(Detail detail : header.getDetails()) {
        detailRepository.delete(detail);
    }
    detailRepository.flush();


    // Iterate through list of ID's and create Detail with other objects
    for(Integer id : customerIDs) {
        Customer customer = customerService.findOne(id);

        Detail detail = new Detail();
        detail.setCustomer(customer);
        detail.setHeader(header);

        detailRepository.save(detail)
    }
}
`