Announcement Announcement Module
Collapse
No announcement yet.
Unable to translate SQLException with SQL state Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Unable to translate SQLException with SQL state

    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

    Code:
    <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>
    Code:
    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
    Code:
    	<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>
    Code:
    public class UniqueConstraintException extends DataIntegrityViolationException implements{
    
    	public UniqueConstraintException(String msg) {
    		super(msg);
    	}
    
    	public UniqueConstraintException(String msg, Exception e) {
    		super(msg, e);
    	}
    }
    Code:
    <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>

  • #2
    Unable to translate SQLException with SQL state

    Here is the exception stack

    Regards,
    Vijay


    Code:
    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)
    Last edited by vijaykmr; May 8th, 2011, 02:13 AM. Reason: Misplaced code tag

    Comment

    Working...
    X