Foreword
Spring will abstract and convert all common database operation exceptions into its own exceptions. The base class of these exceptions is DataAccessException. DataAccessException is a subclass of RuntimeException (runtime exception), which is an exception that does not need to be detected, and does not require code to handle such exceptions
SQLErrorCodeSQLExceptionTranslator
Databases such as H2, mysql, oracle will be used in development, and each manufacturer defines different error codes; spring will analyze the collected error codes of different databases through SQLErrorCodeSQLExceptionTranslator
Spring error code path: org/springframework/jdbc/support/sql-error-codes.xml
How is Spring exception defined?
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>
You can see from the xml that a bean is defined for different databases, and the error codes in it correspond to the exceptions defined in the SQLErrorCodes class
SQLErrorCodes defines some exceptions such as databaseProductNames (database name), badSqlGrammarCodes (grammar error), duplicateKeyCodes (duplicate component), dataIntegrityViolationCodes (unique constraint), cannotAcquireLockCodes (unable to acquire lock) and so on
How to customize jdbc exception
For example, customize a repeated component exception:
The first step, write an exception class to inherit DuplicateKeyException
package com.springwork.high.common;
import org.springframework.dao.DuplicateKeyException;
/**
* @author wage earners
* @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);
}
}
The second step, customize your own sql-error-codes.xml
Define the file path yourself: sql-error-codes.xml under Classpath (will override the officially defined configuration)
Custom error code conversion class 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>
<!--Custom exception configuration-->
<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>
The third step, test
@SpringBootTest
@RunWith(SpringRunner. class)
public class MyCustomDuplicatedKeyExceptionTests {
@Resource
private JdbcTemplate jdbcTemplate;
@Test(expected = MyCustomDuplicatedKeyException. class)
public void testThrowingCustomException() {
jdbcTemplate.execute("INSERT INTO `user_info` (`id`, `name`, `age`) VALUES (222222, 'Zhang San', 18)");
jdbcTemplate.execute("INSERT INTO `user_info` (`id`, `name`, `age`) VALUES (222222, 'Lisi', 18)");
}
}
Here is a small pit to remind you
The solution is to replace @Autowired with @Resource. The difference between the two annotations is that @Autowired is Spring, and @Resource is J2EE. Using @Resource can reduce Spring coupling
@AutoWried is automatically injected by type, while @Resource is automatically injected by Name by default.
The query injection order of @Resource is to search for the Name in the Bean, and if you can’t find it, search for the Class, and then search for it from the attribute. If there is the same Name in the class we defined, an error may be reported because more than one query has been found.
Of course there are other solutions: http://t.csdn.cn/bhazS
Test results
Throw the expected MyCustomDuplicatedKeyException
The above is the custom jdbc exception code