java - 重复输入异常(exception):Spring Hibernate/JPA级联保存多对一

这是一个spring应用程序(没有spring boot)。
我正在使用的数据库是MySQL。
我遇到的问题是保存Driver和Carrier上都具有多对一关系的实体Location时。

我要做的是，当我保存驱动程序时。驱动程序以及位置和运营商将持久保存到数据库中。我遇到的问题是尝试保存时。我收到重复的密钥冲突

堆栈跟踪:

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

实体/模型类:(已删除吸气剂/设置剂)

@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<Driver> drivers = new ArrayList<Driver>();


}

代码准备实体

private List<Driver> prepareEntityList(Result result) {
        List<Driver> drivers = new ArrayList<Driver>();

        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关联时，希望冬眠处理这些关系，如果已经保存而不是尝试再次保存它。
如果没有，建议的保存这些实体的方法是什么？

数据源配置

@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<Driver, Long> {

}

要保存:

repository.saveAll(drivers);

Github链接
https://github.com/mukulgoel1989/greyhound

我添加了github链接，以防有人愿意尝试一下。

最佳答案

我准备了工作的解决方案:Cepr0/greyhound-demo。
我稍微修改了您的项目-使用Spring-Boot，Lombok和H2数据库完成了该项目，仅出于演示目的并简化它。

因此，如果我没记错的话，任务是转换“分配”(来自灵缇网站):

{
    "results": [
        {
            "oper_nbr": 1,
            "carrier_cd": "GLX ",
            "last_name": "JOHN",
            "first_name": "SMITH",
            "middle_init": null,
            "home_loc_6": 12345,
            "home_loc_3": "NLX",
            "oper_class": "T"
        },
        {
            "oper_nbr": 2,
            "carrier_cd": "GLX ",
            "last_name": "JOHN",
            "first_name": "DOE",
            "middle_init": null,
            "home_loc_6": 67890,
            "home_loc_3": "NLX",
            "oper_class": "T"
        }
    ]
}

到三个实体: Driver ， Location 和 Carrier ，它们具有以下关系:

Location -1---*- Driver -*---1- Carrier

即Driver与Location和Carrier具有“多对一”的关系。

此任务的主要问题是，在保存Driver实体的同时，我们需要使用已持久保存的的 Location和Carrier实体，或者使用的新。因此，要解决此问题，我们必须:

为这些实体准备3个存储库。

对于每个“分配” ，找到与Location和Carrier相关的。

如果找不到Location和Carrier，则创建新的。

创建一个新的Driver并设置找到的Location和Carrier或已创建的新Driver。

保留Location(如果未找到，则级联的会保留Carrier和GreyhoundService.process())。

方法Location的最终代码:

@Transactional
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选择的数量，我对初始实体进行了如下转换:

驱动程序

@Getter
@Setter
@ToString
@EqualsAndHashCode(of = "id")
@Entity
@Table(name = "drivers")
public class Driver implements Persistable<Long> {

    @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(
            value = {@JoinColumn(name = "locationId"), @JoinColumn(name = "locationName")},
            foreignKey = @ForeignKey(name = "drivers_locations")
    )
    private Location location;

    @Override
    public boolean isNew() {
        return true;
    }
}

位置

@Data
@NoArgsConstructor
@Entity
@Table(name = "locations")
@IdClass(Location.PK.class )
public class Location {

    @Id private Long locationId;
    @Id private String locationName;

    public PK getId() {
        return new PK(locationId, locationName);
    }

    public void setId(PK id) {
        this.locationId = id.getLocationId();
        this.locationName = id.getLocationName();
    }

    public Location(final Long locationId, final String locationName) {
        this.locationId = locationId;
        this.locationName = locationName;
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class PK implements Serializable {
        private Long locationId;
        private String locationName;
    }
}

运营商

@Data
@NoArgsConstructor
@Entity
@Table(name = "carriers")
public class Carrier {
    @Id private String carrierId;

    public Carrier(final String carrierId) {
        this.carrierId = carrierId;
    }
}

如您所见，我使用了Carrier和Carrier的自然标识符(以及Location中的复合标识符)。这样不仅可以减少数据的大小，而且可以减少Hibernate在存储复杂实体时执行的其他SQL查询的数量。填充Carrier和Location表时，Hibernate不会执行不必​​要的查询来查找它们，而是从其自己的缓存中获取其数据(您可以在应用程序日志中看到此信息)。

附言请注意，此解决方案不是最佳的。 IMO为了使它更好，您可以将主要过程分为两个部分:第一个部分保留不同的Carrier和Driver，第二个部分保留持久的Location，而没有找到Carrier和ojit_code。这两部分都使用batch insert执行。

更新

分支与最佳解决方案:Cepr0/greyhound-demo:async_and_batch_insert

由于位置和承运人的异步保留以及批处理插入，处理仅需约5秒钟。