java - 写入数据库时​​出现异常(org.hibernate.exception.ConstraintViolationException)

Question

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

getHibernateTemplate().saveOrUpdate(femtoFactoryData);

其中 femtoFactoryData 是一个 java 对象 (POJO)，必须将其写入由文件中的制表符分隔值构建的数据库表中。表的主键就是Femto的ID和状态(Temporary/Permanent)。

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

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

您没有正确处理重复项，因为在唯一键方面显然违反了约束。

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

请记住:数据库是您的最后一道防线。忽略诗意方面，这基本上是说您应该尽一切可能将所需的内容准确地插入到数据库中。仅此而已。在将其发送到数据库之前，在您的服务器端过滤、删除重复项。