Resolving hibernate Duplicate Entry Issue

So the other day I encountered this issue with hibernate duplicate entry. The error is usually of this sort (See exception stack trace below. We use Spring with hibernate in our project and that explains the DataIntegrityViolationException? being thrown here. Spring categorizes the black box SQLException to more granular level sql exceptions).

I encountered this error when running my testcase. The situation looked fairly simple to resolve at first sight. The exception pointed me to the erroring model "save" call in the code and all I had to do was to ensure that the duplicated key being reported did not already exist in the corresponding model db table. However the model table actually did not contain the erroring key. Then I tried to flush my session in desperate aim to put the blame on hibernate. That did not resolve the issue.

In looking at detail I noticed that there were several other model inserts happening before the erroring model "save" call and given the fact that these objects are related to each other, any of the previous/related inserts might be contributing to this problem. The erroring model was further tough to track becuase the testcase initially cleans all the test related tables in the test database so pretty much all tables were starting with a key 1. So it pretty much boiled down to identifying the erroring model.

I enabled the hibernate logs to print the executing SQL statements (so basically in your log4j properties you can add this line (log4j.logger.net.sf.hibernate.SQL=DEBUG) and this will spit out the executing native sql queries to the root appender). By doing this I was able to find the exact insert statement that was causing the issue. In fact the table erroring out was not being cleaned before the test case got executed and that caused issue with existing data in that table.

Exception stack trace from duplicate entry key issue:

12:37:30,969 WARN  [main] JDBCExceptionReporter.logExceptions(38) - SQL Error: 1062, SQLState: 23000
    [junit] 12:37:30,970 ERROR [main] JDBCExceptionReporter.logExceptions(46) - Duplicate entry '1' for key 1
    [junit] 12:37:30,971 WARN  [main] JDBCExceptionReporter.logExceptions(38) - SQL Error: 1062, SQLState: 23000
    [junit] 12:37:30,971 ERROR [main] JDBCExceptionReporter.logExceptions(46) - Duplicate entry '1' for key 1
    [junit] 12:37:30,973 ERROR [main] JDBCException.<init>(38) - Could not execute JDBC batch update
    [junit] java.sql.BatchUpdateException: Duplicate entry '1' for key 1
    [junit] at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:647)
    [junit] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
    [junit] at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
    [junit] at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126)
    [junit] at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2440)
    [junit] at net.sf.hibernate.impl.SessionImpl.executeInserts(SessionImpl.java:2329)
    [junit] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:884)
    [junit] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:865)
    [junit] at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:783)
    [junit] at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:746)
    [junit] at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1396)
    [junit] at org.springframework.orm.hibernate.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:583)
    [junit] at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:357)
    [junit] at org.springframework.orm.hibernate.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:580)
[junit] Hibernate operation: Could not execute JDBC batch update; SQL []; Duplicate entry '2' for key 1; nested exception is java.sql.BatchUpdateException: Duplicate entry '1' for key 1
    [junit] org.springframework.dao.DataIntegrityViolationException: Hibernate operation: Could not execute JDBC batch update; SQL []; Duplicate entry '1' for key 1; nested exception is java.sql.BatchUpdateException: Duplicate entry '1' for key 1
    [junit] java.sql.BatchUpdateException: Duplicate entry '1' for key 1
    [junit] at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:647)
    [junit] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
    [junit] at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
    [junit] at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126)
    [junit] at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2440)
    [junit] at net.sf.hibernate.impl.SessionImpl.executeInserts(SessionImpl.java:2329)
    [junit] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:884)
    [junit] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:865)
    [junit] at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:783)
    [junit] at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:746)
    [junit] at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1396)
    [junit] at org.springframework.orm.hibernate.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:583)
    [junit] at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:357)
    [junit] at org.springframework.orm.hibernate.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:580)