重复条目异常：Spring Hibernate/JPA级联保存多对一

12 浏览
0 Comments

重复条目异常：Spring Hibernate/JPA级联保存多对一

这是一个Spring应用程序（不使用Spring Boot）。

我使用的数据库是MySQL。

我遇到的问题是在保存实体Driver时出现了问题，它在Carrier和Location上都有一对多的关系。

我想要做的是，当我保存Driver时，Driver与Location和Carrier一起被持久化到数据库中。我遇到的问题是在尝试保存时。我得到了重复键违反的错误。

堆栈跟踪：

org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1062, SQLState: 23000
Feb 18, 2019 1:25:42 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Duplicate entry '910327' for key 'UK_lheij6i9eldhfhyu9j1q5fjls'
Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [UK_lheij6i9eldhfhyu9j1q5fjls]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:296)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy47.saveAll(Unknown Source)
    at greyhound.service.GreyhoundServiceImpl.process(GreyhoundServiceImpl.java:38)
    at greyhound.Main.main(Main.java:17)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
    at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3073)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3666)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:332)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:196)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:127)
    at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:192)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:135)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:828)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:795)
    at org.hibernate.engine.spi.CascadingActions$7.cascade(CascadingActions.java:298)
    at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:490)
    at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:415)
    at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:216)
    at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:149)
    at org.hibernate.event.internal.AbstractSaveEventListener.cascadeBeforeSave(AbstractSaveEventListener.java:428)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:266)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:196)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:127)
    at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:192)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:135)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:62)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:804)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:789)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:308)
    at com.sun.proxy.$Proxy44.persist(Unknown Source)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:489)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.saveAll(SimpleJpaRepository.java:521)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.saveAll(SimpleJpaRepository.java:73)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:359)
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:644)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:608)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    ... 11 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '910327' for key 'UK_lheij6i9eldhfhyu9j1q5fjls'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1109)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1057)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1377)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1042)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    ... 69 more
Process finished with exit code 1

实体/模型类：（已删除getter / setter）

@Entity
@Table(name = "Driver")
public class Driver {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Version
    @Column(name = "version")
    private int version;
    @Column(name = "driver_id")
    private Long driverId;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    @Column(name = "middle_init")
    private String middleInitial;
    @ManyToOne(fetch = FetchType.EAGER)
    @Cascade({CascadeType.ALL})
    private Carrier carrier;
    @ManyToOne(fetch = FetchType.EAGER)
    @Cascade({CascadeType.ALL})
    private Location location;
@Entity
@Table(name="Carrier")
public class Carrier {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Version
    @Column(name = "version")
    private int version;
    @PrimaryKeyJoinColumn
    @Column(name = "carrier_name")
    private String carrierName;
    @OneToMany
    @JoinColumn(name = "carrier_id", referencedColumnName = "id")
@Entity
@Table(name="Locations")
public class Location {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    @Version
    private Long version;
    @Column(name = "location_id")
    private Long locationId;
    @Column(name = "location_name")
    private String locationName;
    @OneToMany
    @JoinColumn(name = "location_id", referencedColumnName = "location_id")
    private List drivers = new ArrayList();
}

准备实体的代码：

private List prepareEntityList(Result result) {
        List drivers = new ArrayList();
        for(DriverAssignment driverAssignment : result.getDriverAssignments()) {
            Location location = new Location();
            location.setLocationName(driverAssignment.getHomeLocation3());
            location.setLocationId(driverAssignment.getHomeLocation());
            Carrier carrier = new Carrier();
            carrier.setCarrierName(driverAssignment.getCarrierId());
            Driver driver = new Driver();
            driver.setDriverId(driverAssignment.getDriverId());
            driver.setFirstName(driverAssignment.getFirstName());
            driver.setLastName(driverAssignment.getLastName());
            driver.setMiddleInitial(driverAssignment.getMiddleInitial());
            driver.setCarrier(carrier);
            driver.setLocation(location);
            drivers.add(driver);
        }
        return drivers;
    }

问题：我想要实现的目标是否可行？我希望当我尝试保存并将location与driver关联起来时，如果它已经被保存，Hibernate能够处理这些关系，而不是再次尝试保存。

如果不行，有什么建议的方法来保存这些实体？

数据源配置：

@Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource());
        em.setPackagesToScan(new String[] { "greyhound" });
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(additionalProperties());
        return em;
    }
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/greyhound1");
        dataSource.setUsername("root");
        dataSource.setPassword("");
        return dataSource;
    }
    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }
    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }
    Properties additionalProperties() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.hbm2ddl.auto", "create-drop");
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
        return properties;
    }

更新 #2

有一个DriverRepository如下：

@Repository
public interface DriverRepository extends JpaRepository {
}

保存：

repository.saveAll(drivers);

Github链接

https://github.com/mukulgoel1989/greyhound

我添加了Github链接，以防有人愿意尝试这个。

0

3 答案

0 Comments

Duplicate Entry Exception: Spring Hibernate/JPA cascade save Many To One

在Spring Hibernate/JPA中，当使用级联保存（cascade save）的Many To One关系时，可能会出现重复条目异常（Duplicate Entry Exception）的问题。这个问题的出现原因是在保存Driver实体时，需要使用已经持久化的Location和Carrier实体，或者使用新建的实体。为了解决这个问题，可以按照以下步骤进行操作：

1. 准备3个实体的repository：DriverRepository、LocationRepository和CarrierRepository。

2. 对于每个'assignment'，找到相关的Location和Carrier。如果找不到对应的Location和Carrier，则创建新的实体。

3. 创建一个新的Driver实体，并设置找到的Location和Carrier或新建的实体。

4. 持久化Driver实体，并且级联保存Location和Carrier（如果它们不存在）。

下面是方法GreyhoundService.process()的最终代码：

public void process() {
  client.getAssignments()
      .stream()
      .forEach(a -> {
        log.debug("[d] Assignment: {}", a);
        Driver driver = new Driver();
        driver.setId(a.getDriverId());
        driver.setFirstName(a.getFirstName());
        driver.setLastName(a.getLastName());
        driver.setMiddleName(a.getMiddleName());
        driver.setLocation(
            locationRepo.findById(new Location.PK(a.getLocationId(), a.getLocationName()))
                .orElse(new Location(a.getLocationId(), a.getLocationName()))
        );
        driver.setCarrier(
            carrierRepo.findById(a.getCarrierId().trim())
                .orElse(new Carrier(a.getCarrierId().trim()))
        );
        driverRepo.saveAndFlush(driver);
        log.debug("[d] Driver: {}", driver);
      });
}

为了减少数据库中数据的大小和SQL查询的数量，对初始实体进行了如下改进：

Driver实体：

@Entity(name = "drivers")
public class Driver implements Persistable {
    @Id
    private Long id;
    private String firstName;
    private String lastName;
    private String middleName;
    
    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "carrierId", foreignKey = @ForeignKey(name = "drivers_carriers"))
    private Carrier carrier;
    
    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumns({
            @JoinColumn(name = "locationId"),
            @JoinColumn(name = "locationName")
    })
    @ForeignKey(name = "drivers_locations")
    private Location location;
    
    public boolean isNew() {
        return true;
    }
}

Location实体：

@Entity(name = "locations")
public class Location {
    @Embeddable
    public static class PK implements Serializable {
        private Long locationId;
        private String locationName;
    }
    
    @EmbeddedId
    private PK id;
    
    public PK getId() {
        return id;
    }
    
    public void setId(PK id) {
        this.id = id;
    }
    
    public Location(final Long locationId, final String locationName) {
        this.id = new PK(locationId, locationName);
    }
}

Carrier实体：

@Entity(name = "carriers")
public class Carrier {
    @Id
    private String carrierId;
    
    public Carrier(final String carrierId) {
        this.carrierId = carrierId;
    }
}

可以看到，我使用了自然标识符（natural identifiers）来表示Location和Carrier（以及Carrier中的复合标识符）。这不仅减小了数据的大小，还减少了Hibernate在存储复杂实体时执行的额外SQL查询的数量。当Location和Carrier表已经填充时，Hibernate不会执行不必要的查询来查找它们，而是从自己的缓存中获取它们的数据（可以在应用程序日志中看到）。

需要注意的是，这个解决方案不是最优的。可以将主要过程分成两部分：第一部分持久化不同的Location和Carrier实体，第二部分只持久化Driver实体而不查找Location和Carrier。两个部分都可以使用批量插入（batch insert）进行操作。

最后，通过使用异步持久化和批量插入，可以将处理时间缩短到大约5秒。

有关最优解的分支：Cepr0/greyhound-demo:async_and_batch_insert

感谢您花时间给出解释并提供了一个可工作的解决方案！

0
0 Comments

问题原因：在进行双向关联插入时，出现了重复插入的问题。

解决方法：将一侧标记为'mappedby'另一侧。

在Carrier类中使用以下代码：

@OneToMany(mappedBy="carrier")
@JoinColumn(name = "carrier_id", referencedColumnName = "id")
private List drivers = new ArrayList();

在Location类中使用以下代码：

@OneToMany(mappedBy="location")
@JoinColumn(name = "location_id", referencedColumnName = "location_id")
private List drivers = new ArrayList();

这样就能解决重复插入的问题。

0
0 Comments

在使用Spring Hibernate/JPA的级联保存（cascade save）时，可能会出现Duplicate Entry Exception的问题。这个问题的原因是由于Location和Carrier在版本控制中，并且在创建实例时没有设置版本信息，Hibernate可能会将它们视为新的实例并尝试插入数据库（否则，如果需要更新它们，Hibernate也不知道要与哪个版本进行比较，因为更新的实例中缺少版本信息）。

要解决这个问题，首先你需要做以下两点之一：

1. 从数据库中获取已存在的Location和Carrier实例，并在它们不是新的情况下进行更新（可以通过检查id是否已设置来判断）；

2. 或者在创建实例时，同时正确传播和设置版本属性（version attribute），以及id和业务属性。

其次，根据你当前的实体映射和目标（让Hibernate正确保存整个对象图），有两种选择：

1. 如果你不使用事务，则对于上述两种解决方法，你必须使用entityManager.merge(driver)，以确保所有内容都被正确地插入或更新；

2. 如果你选择了上述第一种解决方法，并且在同一个事务中读取了已存在的Location和Carrier实例，并调用repository.saveAll(drivers)，那么它将起作用，因为驱动程序实例将被持久化，PERSIST操作将被级联到仍然附加的location和carrier实例。

根据项目中使用的架构选择和约定，还有其他很多可能性，例如，我不会将级联操作从many一侧级联到one一侧（如果被删除，则可能会导致意外后果），并且在大多数情况下，我都会显式地单独保存one一侧的实例，但这取决于你自己的选择。

0