写入数据库时​​出现异常（org.hibernate.exception.ConstraintViolationException）

Question

我有一个文件（该文件实际上具有制表符分隔值），必须将其写入数据库表之一。该文件也可以包含重复的条目。我正在处理文件中的 5000 条记录，因此我首先解析文件中的前 5000 条记录（如果它们包含任何重复条目）我只是忽略重复项并将其中的唯一记录写入数据库，然后再次处理下 5000 条记录以类似的方式记录，直到达到 EOF。现在，在写入这 5000 条记录时，5000 条记录的集合之间可能存在重复项，如果发生这种情况（然后数据库抛出 DomainObjectExistsException），那么我捕获异常并简单地更新记录。我正在执行如下更新操作：

getHibernateTemplate().saveOrUpdate(femtoFactoryData);

其中 femtoFactoryData 是一个 java 对象（PO​​JO），必须将其写入由文件中的制表符分隔值构造的数据库表中。该表的主键只是Femto的ID和状态（临时/永久）。

但是在执行此更新操作时，我得到：

org.hibernate.exception.ConstraintViolationException

这是我的程序的堆栈跟踪：

org.hibernate.exception.ConstraintViolationException: could not insert: [com.airvana.anp.model.db.domainobjects.FemtoFactoryData]
        at com.airvana.anp.model.db.impl.DbManagerGlobalUtils.convertException(DbManagerGlobalUtils.java:68)
        at com.airvana.anp.model.db.impl.FemtoFactoryDataDAOImplHelper.updateFemtoFactoryData(FemtoFactoryDataDAOImplHelper.java:302)
        at com.airvana.anp.model.db.impl.FemtoFactoryDataDAOImpl.updateFemtoFactoryData(FemtoFactoryDataDAOImpl.java:149)
        at com.airvana.anp.model.oss.imports.common.DataRecordDAOHelperImpl.updateRecord(DataRecordDAOHelperImpl.java:725)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
        at $Proxy89.updateRecord(Unknown Source)
        at com.airvana.anp.model.oss.imports.parser.ParseControllerImpl.saveDupFactoryRecordsInDb(ParseControllerImpl.java:477)
        at com.airvana.anp.model.oss.imports.parser.ParseControllerImpl.parseFile(ParseControllerImpl.java:111)
        at com.airvana.anp.model.oss.imports.FactoryOperationsManagerImpl.onAllocation(FactoryOperationsManagerImpl.java:192)
        at com.airvana.anp.model.resource.impl.CallbackWorkerJob.run(CallbackWorkerJob.java:43)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
        at java.lang.Thread.run(Thread.java:595)
Caused by: org.springframework.dao.DataIntegrityViolationException: could not insert: [com.airvana.anp.model.db.domainobjects.FemtoFactoryData]; nested exception is org.hibernate.exception.ConstraintViolationException: could not insert: [com.airvana.anp.model.db.domainobjects.FemtoFactoryData]
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:624)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
        at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
        at org.springframework.orm.hibernate3.HibernateTemplate.merge(HibernateTemplate.java:820)
        at com.airvana.anp.model.db.impl.FemtoFactoryDataDAOImplHelper.updateFemtoFactoryData(FemtoFactoryDataDAOImplHelper.java:299)
        ... 16 more
Caused by: org.hibernate.exception.ConstraintViolationException: could not insert: [com.airvana.anp.model.db.domainobjects.FemtoFactoryData]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2272)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:56)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1004)
        at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:420)
        ... 19 more
Caused by: java.sql.SQLException: ORA-00001: unique constraint (ANPDB.SYS_C008651) violated

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)
        at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:128)
        at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
        ... 29 more

Answer 1

您没有正确处理重复项，因为明显违反了有关唯一键的约束。

我建议在将这些重复项插入数据库之前完全消除它们。

请记住：数据库是您的最后一道防线。忽略诗意的方面，这基本上是说您应该尽一切可能将所需的内容插入到数据库中。不多不少。在将其发送到数据库之前，过滤、删除重复项以及服务器端的任何内容。