删除然后创建记录导致Spring Data JPA重复键冲突 [英] Delete then create records are causing a duplicate key violation with Spring Data JPA

问题描述

因此，在这种情况下，我需要记录头记录，删除它的详细信息，然后以另一种方式重新创建详细信息.更新细节会带来太多麻烦.

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.

我基本上有:

@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

好的，当我运行它并传递2、3、20等客户时，只要以前没有任何记录，它就会创建所有Detail记录.

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.

如果我再次运行它，传递了不同的客户列表，我希望先删除ALL详细信息，然后再创建NEW详细信息列表.

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.

如果我用大量详细信息手动填充数据库并注释掉CREATE details部分(仅运行删除操作)，则将记录删除就可以了.因此删除有效.创建作品.只是两者不能一起工作.

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.

我可以提供更多需要的代码.我正在使用Spring Data JPA.

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

谢谢

更新

我的实体基本上带有以下注释:

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;
...
}

更新2

@Klaus Groenbaek

@Klaus Groenbaek

实际上，我最初并没有提到这一点，但我是第一次这样做.另外，我使用的是Cascading.ALL，我假设它包括PERSIST.

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.

推荐答案

请耐心等待，因为这是一个相当长的解释，但是当我查看您的代码时，似乎您缺少了几个关键概念关于JPA的工作原理.

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.

首先，将实体添加到集合中或从集合中删除实体并不意味着将在数据库中发生相同的操作，除非使用级联或orphanRemoval传播持久性操作.

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.

要将实体添加到数据库中，必须直接调用EntityManager.persist()或通过级联持久化调用.这基本上是JPARepository.save()

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()

如果要删除实体，则必须直接或通过级联操作或通过JpaRepository.delete()调用EntityManager.remove().

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

如果您有一个受管实体(一个已加载到持久性上下文中的实体)，并且您在事务内部修改了一个基本字段(非实体，非集合)，则此更改将在事务处理时写入数据库中即使您没有调用persist/save也会提交.持久性上下文会保留每个已加载实体的内部副本，并且在提交事务时，它将遍历该内部副本并与当前状态进行比较，并且任何基本的归档更改都会触发更新查询.

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.

如果已将新实体(A)添加到另一个实体(B)上的集合中，但尚未在A上调用持久化，则A将不会保存到数据库中.如果在B上调用persist，则将发生以下两种情况之一:如果持久操作是级联的，则A也将保存到数据库中.如果persist没有进行级联，则会出现错误，因为托管实体引用了非托管实体，这会在EclipseLink上显示此错误:在同步期间，通过未标记为层叠PERSIST的关系找到了新对象".级联持久是很有意义的，因为您经常创建一个父实体和它的子实体.

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.

当您要从另一个实体B上的集合中删除实体A时，您不能依靠级联，因为您没有删除B.相反，您必须直接调用A上的remove，将其从集合上的on删除. B没有任何作用，因为尚未在EntityManager上调用持久性操作.您也可以使用orphanRemoval触发删除，但是我建议您在使用此功能时要格外小心，尤其是因为您似乎缺少有关持久性操作的基本知识.

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);
    }
}

首先，没有理由保留标头，它是一个托管实体，并且在提交事务时，您修改的任何基本字段都会更改.标头恰好是Details实体的外键，这意味着重要的是detail.setHeader(header);和em.persist(details)，因为您必须设置所有外部关系，并保留所有新的Details. 同样，从Header中删除现有详细信息与Header无关，定义关系(外键)在Details中，因此从持久性上下文中删除详细信息就是将其从数据库中删除的原因.您也可以使用orphanRemoval，但是这对于每个事务都需要附加的逻辑，而且我认为，如果每个持久性操作都是显式的，则代码更易于阅读，因此您无需返回到实体来阅读批注.

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.

最后:代码中的持久性操作顺序不会改变为对数据库执行的查询顺序. Hibernate和EclipseLink都将首先插入新实体，然后删除现有实体.以我的经验，这是主键已存在"的最常见原因.如果删除具有特定主键的实体，然后添加具有相同主键的新实体，则插入将首先发生，并导致键冲突.可以通过告诉JPA将当前的持久性状态刷新到数据库来解决此问题. em.flush()会将删除查询推送到数据库，因此您可以插入另一行，其主键与已删除的键相同.

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.

这篇关于删除然后创建记录导致Spring Data JPA重复键冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！