当前位置：首页 > news >正文

SpringJDBC异常抽象

news 2026/1/2 17:23:18

前言

spring会将所有的常见数据库的操作异常抽象转换成他自己的异常，这些异常的基类是DataAccessException。DataAccessException是RuntimeException的子类（运行时异常）,是一个无须检测的异常,不要求代码去处理这类异常

SQLErrorCodeSQLExceptionTranslator

开发中会使用到H2，mysql、oracle等数据库，每个厂商定义了不同的错误码；spring通过SQLErrorCodeSQLExceptionTranslator将收集起来的不同数据库错误码进行解析

spring错误码路径：org/springframework/jdbc/support/sql-error-codes.xml

Spring异常是怎么定义的？

springErrorCode：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd"><!--- Default SQL error codes for well-known databases.- Can be overridden by definitions in a "sql-error-codes.xml" file- in the root of the class path.-- If the Database Product Name contains characters that are invalid- to use in the id attribute (like a space) then we need to add a property- named "databaseProductName"/"databaseProductNames" that holds this value.- If this property is present, then it will be used instead of the id for- looking up the error codes based on the current database.-->
<beans><bean id="DB2" name="Db2" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductName"><value>DB2*</value></property><property name="badSqlGrammarCodes"><value>-007,-029,-097,-104,-109,-115,-128,-199,-204,-206,-301,-408,-441,-491</value></property><property name="duplicateKeyCodes"><value>-803</value></property><property name="dataIntegrityViolationCodes"><value>-407,-530,-531,-532,-543,-544,-545,-603,-667</value></property><property name="dataAccessResourceFailureCodes"><value>-904,-971</value></property><property name="transientDataAccessResourceCodes"><value>-1035,-1218,-30080,-30081</value></property><property name="deadlockLoserCodes"><value>-911,-913</value></property></bean><bean id="Derby" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductName"><value>Apache Derby</value></property><property name="useSqlStateForTranslation"><value>true</value></property><property name="badSqlGrammarCodes"><value>42802,42821,42X01,42X02,42X03,42X04,42X05,42X06,42X07,42X08</value></property><property name="duplicateKeyCodes"><value>23505</value></property><property name="dataIntegrityViolationCodes"><value>22001,22005,23502,23503,23513,X0Y32</value></property><property name="dataAccessResourceFailureCodes"><value>04501,08004,42Y07</value></property><property name="cannotAcquireLockCodes"><value>40XL1</value></property><property name="deadlockLoserCodes"><value>40001</value></property></bean><bean id="H2" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="badSqlGrammarCodes"><value>42000,42001,42101,42102,42111,42112,42121,42122,42132</value></property><property name="duplicateKeyCodes"><value>23001,23505</value></property><property name="dataIntegrityViolationCodes"><value>22001,22003,22012,22018,22025,23000,23002,23003,23502,23503,23506,23507,23513</value></property><property name="dataAccessResourceFailureCodes"><value>90046,90100,90117,90121,90126</value></property><property name="cannotAcquireLockCodes"><value>50200</value></property></bean><!-- http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a78d3275191014b41bae7c4a46d835/content.htm --><bean id="HDB" name="Hana" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductNames"><list><value>SAP HANA</value><value>SAP DB</value></list></property><property name="badSqlGrammarCodes"><value>257,259,260,261,262,263,264,267,268,269,270,271,272,273,275,276,277,278,278,279,280,281,282,283,284,285,286,288,289,290,294,295,296,297,299,308,309,313,315,316,318,319,320,321,322,323,324,328,329,330,333,335,336,337,338,340,343,350,351,352,362,368</value></property><property name="permissionDeniedCodes"><value>10,258</value></property><property name="duplicateKeyCodes"><value>301</value></property><property name="dataIntegrityViolationCodes"><value>461,462</value></property><property name="dataAccessResourceFailureCodes"><value>-813,-709,-708,1024,1025,1026,1027,1029,1030,1031</value></property><property name="invalidResultSetAccessCodes"><value>-11210,582,587,588,594</value></property><property name="cannotAcquireLockCodes"><value>131</value></property><property name="cannotSerializeTransactionCodes"><value>138,143</value></property><property name="deadlockLoserCodes"><value>133</value></property></bean><bean id="HSQL" name="Hsql" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductName"><value>HSQL Database Engine</value></property><property name="badSqlGrammarCodes"><value>-22,-28</value></property><property name="duplicateKeyCodes"><value>-104</value></property><property name="dataIntegrityViolationCodes"><value>-9</value></property><property name="dataAccessResourceFailureCodes"><value>-80</value></property></bean><bean id="Informix" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductName"><value>Informix Dynamic Server</value></property><property name="badSqlGrammarCodes"><value>-201,-217,-696</value></property><property name="duplicateKeyCodes"><value>-239,-268,-6017</value></property><property name="dataIntegrityViolationCodes"><value>-692,-11030</value></property></bean><bean id="MS-SQL" name="SqlServer" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductName"><value>Microsoft SQL Server</value></property><property name="badSqlGrammarCodes"><value>156,170,207,208,209</value></property><property name="permissionDeniedCodes"><value>229</value></property><property name="duplicateKeyCodes"><value>2601,2627</value></property><property name="dataIntegrityViolationCodes"><value>544,8114,8115</value></property><property name="dataAccessResourceFailureCodes"><value>4060</value></property><property name="cannotAcquireLockCodes"><value>1222</value></property><property name="deadlockLoserCodes"><value>1205</value></property></bean><bean id="MySQL" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="badSqlGrammarCodes"><value>1054,1064,1146</value></property><property name="duplicateKeyCodes"><value>1062</value></property><property name="dataIntegrityViolationCodes"><value>630,839,840,893,1169,1215,1216,1217,1364,1451,1452,1557</value></property><property name="dataAccessResourceFailureCodes"><value>1</value></property><property name="cannotAcquireLockCodes"><value>1205</value></property><property name="deadlockLoserCodes"><value>1213</value></property></bean><bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="badSqlGrammarCodes"><value>900,903,904,917,936,942,17006,6550</value></property><property name="invalidResultSetAccessCodes"><value>17003</value></property><property name="duplicateKeyCodes"><value>1</value></property><property name="dataIntegrityViolationCodes"><value>1400,1722,2291,2292</value></property><property name="dataAccessResourceFailureCodes"><value>17002,17447</value></property><property name="cannotAcquireLockCodes"><value>54,30006</value></property><property name="cannotSerializeTransactionCodes"><value>8177</value></property><property name="deadlockLoserCodes"><value>60</value></property></bean><bean id="PostgreSQL" name="Postgres" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="useSqlStateForTranslation"><value>true</value></property><property name="badSqlGrammarCodes"><value>03000,42000,42601,42602,42622,42804,42P01</value></property><property name="duplicateKeyCodes"><value>23505</value></property><property name="dataIntegrityViolationCodes"><value>23000,23502,23503,23514</value></property><property name="dataAccessResourceFailureCodes"><value>53000,53100,53200,53300</value></property><property name="cannotAcquireLockCodes"><value>55P03</value></property><property name="cannotSerializeTransactionCodes"><value>40001</value></property><property name="deadlockLoserCodes"><value>40P01</value></property></bean><bean id="Sybase" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductNames"><list><value>Sybase SQL Server</value><value>Adaptive Server Enterprise</value><value>ASE</value>  <!-- name as returned by jTDS driver --><value>SQL Server</value><value>sql server</value>  <!-- name as returned by jTDS driver --></list></property><property name="badSqlGrammarCodes"><value>101,102,103,104,105,106,107,108,109,110,111,112,113,116,120,121,123,207,208,213,257,512</value></property><property name="duplicateKeyCodes"><value>2601,2615,2626</value></property><property name="dataIntegrityViolationCodes"><value>233,511,515,530,546,547,2615,2714</value></property><property name="transientDataAccessResourceCodes"><value>921,1105</value></property><property name="cannotAcquireLockCodes"><value>12205</value></property><property name="deadlockLoserCodes"><value>1205</value></property></bean></beans>

可以从xml看到为不同的数据库都定义了一个bean，里面的错误码对应SQLErrorCodes类中定义的异常

SQLErrorCodes里定义了一些databaseProductNames(数据库名称)、badSqlGrammarCodes（语法错误）、duplicateKeyCodes（重复组件）、dataIntegrityViolationCodes（唯一性约束）cannotAcquireLockCodes（无法获取锁）等异常

怎样自定义jdbc异常

比如自定义一个重复组件异常：

第一步、写一个异常类继承DuplicateKeyException

package com.springwork.high.common;import org.springframework.dao.DuplicateKeyException;/*** @author 打工仔* @version 1.0.0* @date 2023/3/19 20:07*/
public class MyCustomDuplicatedKeyException extends DuplicateKeyException {public MyCustomDuplicatedKeyException(String msg) {super(msg);}public MyCustomDuplicatedKeyException(String msg, Throwable cause) {super(msg, cause);}
}

第二步、定制自己的sql-error-codes.xml

自己定义文件路径：Classpath下的 sql-error-codes.xml （会覆盖官方定义的配置）

自定义的错误码转换类CustomSQLErrorCodesTranslation

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"><bean id="MySQL" class="org.springframework.jdbc.support.SQLErrorCodes"><property name="databaseProductNames"><list><value>MySQL</value><value>MariaDB</value></list></property><property name="badSqlGrammarCodes"><value>1054,1064,1146</value></property><property name="duplicateKeyCodes"><value>1062</value></property><property name="dataIntegrityViolationCodes"><value>630,839,840,893,1169,1215,1216,1217,1364,1451,1452,1557</value></property><property name="dataAccessResourceFailureCodes"><value>1</value></property><property name="cannotAcquireLockCodes"><value>1205,3572</value></property><property name="deadlockLoserCodes"><value>1213</value></property><!--自定义异常配置--><property name="customTranslations"><bean class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation"><property name="errorCodes" value="1062" /><property name="exceptionClass"value="com.springwork.high.common.MyCustomDuplicatedKeyException" /></bean></property></bean>
</beans>

第三步、测试

@SpringBootTest
@RunWith(SpringRunner.class)
public class MyCustomDuplicatedKeyExceptionTests {@Resourceprivate JdbcTemplate jdbcTemplate;@Test(expected = MyCustomDuplicatedKeyException.class)public void testThrowingCustomException() {jdbcTemplate.execute("INSERT INTO `user_info` (`id`, `name`, `age`) VALUES (222222, '张三', 18)");jdbcTemplate.execute("INSERT INTO `user_info` (`id`, `name`, `age`) VALUES (222222, '李四', 18)");}
}

这里有一个小坑提醒一下

解决方法将@Autowired换成@Resource。两个注解的区别是一个是@Autowired是Spring，@Resource是J2EE的，使用@Resource能减少Spring耦合度

@AutoWried按by type自动注入，而@Resource默认按byName自动注入。

@Resource的查询注入顺序是，去Bean中查找Name，如果查不到就去查Class，其次再从属性去查找，如果我们定义的类中有相同的Name可能会报错，因为查询到了多个。

当然还有其他解决方法：http://t.csdn.cn/bhazS

测试结果

抛出期望的MyCustomDuplicatedKeyException异常

以上就是自定义jdbc异常码

前言