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

Question

It is a spring application (no spring boot). The database I am using is MySQL. The issue I am having is when saving the entity Driver which has a Many to one relationship on both Carrier and Location.

What I want to do is, when I do the save on Driver. Driver along with Location and Carrier is persisted to the database. The issue I am having is when trying to save. I get duplicate key violation

Stack trace:

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/Model classes: (Have removed getters/setters)

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


}

Code preparing the entities

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

Question: is it possible to achieve what I am trying to do? Expect hibernate to handle the relationships when I try to save and associate a location with a driver if it has already been saved instead of trying to save it again. If not, what is a suggested approach to save these entities?

Datasource configuration

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

Update #2

Have a DriverRepository like this

@Repository
public interface DriverRepository extends JpaRepository<Driver, Long> {

}

To save:

repository.saveAll(drivers);

Github link https://github.com/mukulgoel1989/greyhound

I have added the github link in case someone is willing to give this a try.

Answer 1

I prepared the working solution: Cepr0/greyhound-demo. I reworked your project "a little" - did it with Spring-Boot, Lombok and H2 database, just for demo purposes and to simplify it.

So, if I'm not mistaken, the task is to transform 'assignments' (from the greyhound site):

{
    "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"
        }
    ]
}

to three entities: Driver, Location, and Carrier with the relations:

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

i.e. Driver has 'many-to-one' relation with Location and Carrier.

The main problem of this task is that while saving the Driver entity, we need to use the already persisted Location and Carrier entities, or use new ones. So to solve it we have to:

1. Prepare 3 repositories for those entities.
2. For each 'assignment' find related Location and Carrier.
3. If Location and Carrier are not found then create new ones.
4. Create a new Driver and set the found Location and Carrier or new ones having been created.
5. Persist the Driver (and cascaded persist Location and Carrier if they are not found).

The final code of method GreyhoundService.process():

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

To minimize the size of the data in the database and the number of SQL selects I transformed the initial entities as follows:

Driver

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

Location

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

Carrier

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

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

As you can see I used natural identifiers for Location and Carrier (and a composite one in Carrier). This made it possible not only to reduce the size of the data but also reduce the number of additional SQL queries that Hibernate performs when storing complex entities. When Location and Carrier tables are filled, Hibernate does not perform unnecessary queries to find them but takes their data from its own cache (you can see this in the app log).

P.S. Note that this solution is not optimal. IMO to make it better you can split the main process into two parts: the first one persists distinct Locations and Carriers and the second one just persists Drivers without finding Locations and Carriers. Both parts perform with batch insert.

UPDATE

Branch with the optimal solution: Cepr0/greyhound-demo:async_and_batch_insert

Due to the asynchronous persisting of Locations and Carriers and with batch insert, processing takes only about 5 seconds.