Exception while writing to the Database (org.hibernate.exception.ConstraintViolationException)

Question

I have a file(this file actually has tab separated values) which has to be written into one of the database tables. This file can contain duplicate entries as well. I am processing the records in the file in set of 5000, so I first parse these first 5000 records in the file if they contain any duplicate entries I simply ignore the duplicates and write the unique records among them to the database and again process next 5000 records in the similar fashion till EOF is reached. Now while writing these 5000 records it is possible that there are duplicates between the sets of 5000 records and if so happens(then database throws the DomainObjectExistsException) then I catch the exception and simply update the record. I am performing the update operation like this:

getHibernateTemplate().saveOrUpdate(femtoFactoryData);

Where femtoFactoryData is a java Object(POJO) which has to be written into the database table constructed from the tab-separated values in the file. The Primary Key of the table is nothing but the ID of the Femto and the state(Temporary/Permanent).

But while performing this update operation I am getting:

org.hibernate.exception.ConstraintViolationException

Here is the stacktrace from my program:

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

score 4 · Accepted Answer · answered Dec 18 '10 at 15:25

4

You're not handling duplicates properly since there's obviously constraint violation regarding unique keys.

I'd suggest eliminating those duplicates entirely before inserting them into database.

Remember: the database is your last line of defense. Ignoring the poetic aspect, that's basically saying that you should do whatever possible to insert into your database exactly what's needed. Nothing more, nothing less. Filter, remove duplicates, whatever on your server side before sending it to database.

answered Dec 18 '10 at 15:25

darioo

46,442
10
75
103

Could you elaborate a little more on "since there's obviously constraint violation regarding unique keys"? Okay, what if suppose in my file I have two entries among which the unique part is same for both but the rest of the value differs, how do I do the update operation then? – Cheshar Dec 19 '10 at 10:23
@darioo: Thanks for your help...got it why I was getting the error...was confused with Unique Index and Primary Key!! :) – Cheshar Dec 20 '10 at 07:53
@darioo: The problem still isn't solved..I think the oracle database(which I am using) is behaving abnormally...I am trying do an update operation on an existing record and it still is showing me ConstraintViolationException...WTF?? Let me know if there is any way by which I can update my existing record without getting this exception..I have tried (delete+add) and Update both...but to no avail!! – Cheshar Dec 20 '10 at 15:20
@Cheshar: I can't guarantee that I will be able to help further without seeing your data and table definitions with their constraints... I'd suggest taking a look at [this post](http://stackoverflow.com/questions/2536829/hibernate-show-real-sql) so you can see generated SQL. Then copy & paste it in SQL*Plus, SQL developer or some other tool you use for connecting to Oracle and then see what happens when you run it. – darioo Dec 20 '10 at 15:28
@darioo: I am using Dbvisualizer tool to look into the database...the query works fine when I run it here but I don't know what is happening when I am doing the same operation using hibernate...I think myabe because Oracle caches the result before actually committing the data in the database schema that's where it is causing the problem...I even used Eclipse's debugger to track down the problem.....everything appears fine to me at the application level! – Cheshar Dec 20 '10 at 15:48
@Cheshar: "the query works fine when I run it here but I don't know what is happening when I am doing the same operation using hibernate" - the query being generated by Hibernate isn't necessary the query you use. I suspect the problem lies there. Please try what I said earlier; turn on Hibernate's SQL output and try to copy & paste it in Dbvisualizer. The result might surprise you... – darioo Dec 20 '10 at 15:54
@darioo: As far as the table definition is considered.....my table consists of 14 columns out of which one is ID(defined as the PK, automatically generated) also I have defined the combination of FEMTOID and STATE as Unique Index...and when I am updating the entry into the database I am actually passing the FEMTOID and STATE to the hibernate to uniquely identify each tuple – Cheshar Dec 20 '10 at 15:56

Exception while writing to the Database (org.hibernate.exception.ConstraintViolationException)

1 Answers1