Resolve SQL errorCode from underlying SQLException [SPR-10260]

[spring-projects-issues]

Dmitry Katsubo opened SPR-10260 and commented

In case when java.sql.DataTruncation wraps java.sql.SQLException the error code is 0. However underlying SQLException holds the error code value ("8152" in my example).

Current SQLErrorCodeSQLExceptionTranslator#doTranslate() implementation does not allow to handle this case for sqlErrorCodes.customTranslations.

1. If sqlErrorCodes.useSqlStateForTranslation is true, then custom translation bound to SQL state ("22001" in my example) will match, however other translations from sql-error-codes.xml will not work.
2. If sqlErrorCodes.useSqlStateForTranslation is true, then custom translation will not match, however default translations will work.

It's not possible to combine these two policies. The solution could be to loop through exception hierarchy to find first non-zero error code (code spot is highlighted in my example).

Intended XML configuration:

<bean id="exceptionTranslator" class="org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator">
	<property name="dataSource">
		<ref bean="myDaoDataSource" />
	</property>
	<property name="sqlErrorCodes.customTranslations">
		<array>
			<bean class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
				<property name="errorCodes" value="8152" />
				<property name="exceptionClass" value="org.mycompany.dao.DataTruncationException" />
			</bean>
		</array>
	</property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
	p:dataSource-ref="myDaoDataSource"
	p:exceptionTranslator-ref="exceptionTranslator"
/>

Workarounds:

• Extend SQLErrorCodeSQLExceptionTranslator and override customTranslate().
• Define sqlErrorCodes.customSqlExceptionTranslator().

---

Affects: 3.1.2

Attachments:

• example.png (81.06 kB)

Referenced from: commits 8a4ce14

[spring-projects-issues]

Dmitry Katsubo commented

Juergen, thanks for the fix.

Could you advise me what should be the tactics, if I would like to throw a custom exception for SQL state "22001" (this code is universal / cross-database), while the rest of exceptions be resolved by error code? Namely:

<bean class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
	<property name="errorCodes" value="22001" />
	<property name="exceptionClass" value="org.mycompany.dao.DataTruncationException" />
</bean>