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

darioo · Accepted Answer · 2010-12-18 15:25:42Z

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, 2010 at 15:25

darioo

47.4k10 gold badges79 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Cheshar Over a year ago

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 Over a year ago

@darioo: Thanks for your help...got it why I was getting the error...was confused with Unique Index and Primary Key!! :)

Cheshar Over a year ago

@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!!

darioo Over a year ago

@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 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.

Cheshar Over a year ago

@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!

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related