它是一个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<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
关联(如果它已经被保存,而不是再次尝试保存它)时,期望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<Driver, Long> {
}
要保存的:
repository.saveAll(drivers);
Github链接 https://github.com/mukulgoel1989/greyhound
我已经添加了github链接,以防有人愿意尝试一下。
发布于 2019-02-26 00:02:24
我准备了working解决方案:Cepr0/greyhound-demo。我用Spring-Boot,Lombok和H2数据库修改了你的项目,只是为了演示和简化它。
所以,如果我没记错的话,我们的任务是转换“assignments”(来自灰狗网站):
{
"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个存储库,每个“entities.
Location
Carrier
。Location
和Carrier
,则创建新的Location
和Carrier
。Driver
,并将找到的Location
和Carrier
或已级联的新的设置为<代码>D45(以及<代码>E146E247如果未找到,则保留<代码>D48和<代码>D49)。<代码>H250<代码>G251方法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);
});
}
为了最小化数据库中的数据大小和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;
}
}
如您所见,我对Location
和Carrier
使用了自然标识符(在Carrier
中使用了一个复合标识符)。这不仅可以减少数据的大小,还可以减少Hibernate在存储复杂实体时执行的额外SQL查询的数量。当Location
和Carrier
表被填满时,Hibernate不会执行不必要的查询来查找它们,而是从自己的缓存中获取它们的数据(您可以在应用程序日志中看到这一点)。
附注:请注意,此解决方案不是最优的。为了使其更好,您可以将主进程分为两部分:第一部分持久化不同的Location
和Carrier
,第二部分仅持久化Driver
,而不查找Location
和Carrier
。这两部分都使用batch insert执行。
更新
具有最佳解决方案的分支:Cepr0/greyhound-demo:async_and_batch_insert
由于位置和运营商的异步持久化以及批量插入,处理只需要大约5秒。
发布于 2019-02-21 10:36:29
当它插入两边的条目时,会导致重复问题。解决方案是将一端标记为“mappedby”另一端。
所以在Carrier
类中使用:
@OneToMany(mappedBy="carrier")
@JoinColumn(name = "carrier_id", referencedColumnName = "id")
private List<Driver> drivers = new ArrayList<Driver>();
在Location
类中:
@OneToMany(mappedBy="location")
@JoinColumn(name = "location_id", referencedColumnName = "location_id")
private List<Driver> drivers = new ArrayList<Driver>();
发布于 2019-02-25 03:16:45
由于Location
和Carrier
是版本化的,并且您没有在创建的实例中设置版本,因此Hibernate可能只是将它们视为新的并尝试插入它们(否则,如果需要更新它们,Hibernate无论如何都不知道要比较哪些版本,因为更新的实例中缺少它们)。
首先,您需要执行以下任一操作:
Location
和Carrier
实例,并在它们不是新实例时对其进行更新(通过检查是否设置了id来了解这一点);其次,对于当前的实体映射,您还有两个选择(声明的目标是让Hibernate正确地保存整个图):
entityManager.merge(driver)
,以便正确地插入或执行updated;Location
和Carrier
实例并在同一事务中调用repository.saveAll(drivers)
时,它才会起作用,因为驱动程序实例将被持久化,并且PERSIST
操作将级联到仍然附加的位置和运营商实例。根据你的项目中使用的架构选择和约定,还有(许多)其他的可能性,即我永远不会把所有的东西从many
端级联到one
端(一个例子是移除),在大多数情况下,我总是会显式地单独保存one
端,但这取决于你。
https://stackoverflow.com/questions/54748475
复制相似问题