Unable to translate SQLException with SQL state

[vijaykmr]

Hi there,
Tried all the solution in this forum to figure out the sql translation issue still i am not successful. I am using Hibernate 3 and spring with transaction management. I am unable to capture the DB exception (unique constraint violated) and pass it on to my custom exception handler. Help me to identify the issue.

Regards,
Vijay

<bean
<bean class="org.apache.commons.dbcp.BasicDataSource" id="_dataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
		<property name="url" value="jdbc:oracle:thin:@111.111.111.111:1521:VS" />
		<property name="username" value="user" />
		<property name="password" value="xxxx" />
	</bean>		class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

	<bean
		class="org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator"
		id="_jdbcExceptionTranslator">
		<property name="dataSource">
			<ref bean="_dataSource" />
		</property>
	</bean>
<bean class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"
		id="_sessionFactory">
		<property name="dataSource" ref="_dataSource" />
		<property name="jdbcExceptionTranslator">
			<ref bean="_jdbcExceptionTranslator" />
		</property>
		<property name="mappingLocations">
			<list>
				<value>classpath*:resources/*.hbm.xml
				</value>
			</list>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect
				</prop>
				<prop key="hibernate.show_sql">false</prop>
				<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory
				</prop>
			</props>
		</property>

Database Name: Oracle
Database Version: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Driver Name: Oracle JDBC driver
Driver Version : 11.1.0.7.0-Production

	<bean id="txManager"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="_sessionFactory" />
	</bean>
<bean id="txProxyTemplate" abstract="true"
		class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
		<property name="transactionManager" ref="txManager" />
		<property name="transactionAttributes" >
			<props>
				<prop key="*">PROPAGATION_REQUIRED,-DataIntegrityViolationException</prop>
			</props>
		</property>
	</bean>

public class UniqueConstraintException extends DataIntegrityViolationException implements{

	public UniqueConstraintException(String msg) {
		super(msg);
	}

	public UniqueConstraintException(String msg, Exception e) {
		super(msg, e);
	}
}

<beans>
    <bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes">
    <property name="badSqlGrammarCodes">
      <value>900,903,904,917,936,942,17006</value>
    </property>
    <property name="useSqlStateForTranslation">
    <value>true</value>
    </property>
    
    <property name="customTranslations">
      <list>
        <bean class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
          <property name="errorCodes">
            <value>1</value>
          </property>
          <property name="exceptionClass">
            <value>com.gts.service.support.UniqueConstraintException</value>
          </property>
        </bean>
        <bean class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
          <property name="errorCodes">
            <value>2291</value>
          </property>
          <property name="exceptionClass">
            <value>com.gts.service.support.NoParentRowFkException</value>
          </property>
        </bean>
        <bean class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
        <property name="errorCodes">
          <value>2292</value>
        </property>
        <property name="exceptionClass">
          <value>com.gts.service.support.ChildRowExistsFkException</value>
        </property>
      </bean>
      </list>
    </property>
  </bean>
 </beans>

[vijaykmr]

Here is the exception stack

Regards,
Vijay

main TransactionSynchronizationManager.getResource - <Retrieved value [org.springframework.orm.hibernate3.SessionHolder@b49b19] for key [org.hibernate.impl.SessionFactoryImpl@1ad6c98] bound to thread [main]>
main SQLErrorCodeSQLExceptionTranslator.doTranslate - <Using nested SQLException from the BatchUpdateException>
main SQLErrorCodeSQLExceptionTranslator.doTranslate - <Unable to translate SQLException with SQL state '23000', error code '1, will now try the fallback translator>
main SQLStateSQLExceptionTranslator.doTranslate - <Extracted SQL state class '23' from value '23000'>
main TransactionAspectSupport.completeTransactionAfterThrowing - <Completing transaction for [com.gts.sample.service.ISampleService.insertSample] after exception: org.springframework.dao.DataIntegrityViolationException: Hibernate operation: Could not execute JDBC batch update; SQL [insert into PETROUSER.SAMPLE (NAME, DESCRIPTION, LOV, IS_ENABLED, IS_DELETED, LAST_MODIFIED, CREATED_BY, CREATED_ON, LAST_UPDATED_BY, SAMPLE_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; ORA-00001: unique constraint (PETROUSER.NAME_UNIQ) violated
; nested exception is java.sql.BatchUpdateException: ORA-00001: unique constraint (PETROUSER.NAME_UNIQ) violated
>
main RuleBasedTransactionAttribute.rollbackOn - <Applying rules to determine whether transaction should rollback on org.springframework.dao.DataIntegrityViolationException: Hibernate operation: Could not execute JDBC batch update; SQL [insert into PETROUSER.SAMPLE (NAME, DESCRIPTION, LOV, IS_ENABLED, IS_DELETED, LAST_MODIFIED, CREATED_BY, CREATED_ON, LAST_UPDATED_BY, SAMPLE_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; ORA-00001: unique constraint (PETROUSER.NAME_UNIQ) violated
; nested exception is java.sql.BatchUpdateException: ORA-00001: unique constraint (PETROUSER.NAME_UNIQ) violated
>
main RuleBasedTransactionAttribute.rollbackOn - <Winning rollback rule is: RollbackRuleAttribute with pattern [DataIntegrityViolationException]>
main AbstractPlatformTransactionManager.triggerBeforeCompletion - <Triggering beforeCompletion synchronization>
main AbstractPlatformTransactionManager.processRollback - <Initiating transaction rollback>
main HibernateTransactionManager.doRollback - <Rolling back Hibernate transaction on Session [org.hibernate.impl.SessionImpl@54570a]>
Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: Hibernate operation: Could not execute JDBC batch update; SQL [insert into PETROUSER.SAMPLE (NAME, DESCRIPTION, LOV, IS_ENABLED, IS_DELETED, LAST_MODIFIED, CREATED_BY, CREATED_ON, LAST_UPDATED_BY, SAMPLE_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; ORA-00001: unique constraint (PETROUSER.NAME_UNIQ) violated
; nested exception is java.sql.BatchUpdateException: ORA-00001: unique constraint (PETROUSER.NAME_UNIQ) violated

	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)main AbstractPlatformTransactionManager.triggerAfterCompletion - <Triggering afterCompletion synchronization>
main TransactionSynchronizationManager.clearSynchronization - <Clearing transaction synchronization>
main TransactionSynchronizationManager.unbindResource - <Removed value [org.springframework.orm.hibernate3.SessionHolder@b49b19] for key [org.hibernate.impl.SessionFactoryImpl@1ad6c98] from thread [main]>
main TransactionSynchronizationManager.unbindResource - <Removed value [org.springframework.jdbc.datasource.ConnectionHolder@739aa3] for key [org.apache.commons.dbcp.BasicDataSource@11563ff] from thread [main]>
main HibernateTransactionManager.doCleanupAfterCompletion - <Closing Hibernate Session [org.hibernate.impl.SessionImpl@54570a] after transaction>
main SessionFactoryUtils.closeSession - <Closing Hibernate Session>

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.convertHibernateAccessException(AbstractSessionFactoryBean.java:300)
	at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.translateExceptionIfPossible(AbstractSessionFactoryBean.java:282)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:62)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:212)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:146)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy13.persist(Unknown Source)
	at com.gts.sample.service.SampleService.insertSample(SampleService.java:38)
	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:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at com.gts.service.security.SecurityInterceptor.invoke(SecurityInterceptor.java:50)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy38.insertSample(Unknown Source)
	at com.gts.sample.Test.main(Test.java:74)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (PETROUSER.NAME_UNIQ) violated

	at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:629)