Spring将不同数据库的唯一约束异常统一为DuplicateKeyException分析

Spring将不同数据库的唯一约束异常统一为DuplicateKeyException分析 背景

项目某需求需要通过程序定时将数据从A系统同步至B、C等系统。数据同步过程中常见的一种错误是是唯一性冲突（主键或者其它唯一约束），开发中一般直接使用org.springframework.dao.DuplicateKeyException判断冲突是否出现了唯一性冲突，如果是则忽略该条数据，但是使用国产达梦数据库后该判断方式失效了，默认返回了dm.jdbc.driver.DMException。因此需要将达梦等国产数据库错误码适配Spring

整体伪代码

String sql="insert into TABLE(EMPLOYEEID, EMPLOYEENAME, IDENTITYCARD) values(?,?,?)";
List params = new ArrayList();
try {
    for(int i=0;i<1000;i++){
        Object param[]={"001", "adaivskenan", "140000202201018888"};
     	params.add(param);
   	}
	jdbcTemplate.batchUpdate(sql,params);
   } catch (Exception e) {
	//判断批量异常，退化为单条处理 
	if(NestedExceptionUtils.getRootCause(e).equals(BatchUpdateException.class)){
		for(int i=0;i<1000;i++){
	        Object param[]={"001", "adaivskenan", "140000202201018888"};
	     	params.add(param);
	   		try{
	   			jdbcTemplate.update(sql,params);
	   		}catch(DuplicateKeyException e){
	   		//忽略唯一索引错误
	   		}
	}
  }

解决办法

在资源目录下放置sql-error-codes.xml文件，增加数据库错误码映射

分析过程 1. DuplicateKeyException产生过程

Spring JDBC模块发生数据库异常时会执行org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator#doTranslate方法，将不同数据库的errorCode进行翻译，转换为自定义的框架异常，如下

// 根据errorCode通过sqlErrorCodes判断是否为DuplicateKey冲突，转换为DuplicateKeyException
else if (Arrays.binarySearch(sqlErrorCodes.getDuplicateKeyCodes(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new DuplicateKeyException(buildMessage(task, sql, sqlEx), sqlEx);
				}

2. SQLErrorCodes生成方式

Spring服务启动时将资源该资源文件org/springframework/jdbc/support/sql-error-codes.xml生成org.springframework.jdbc.support.SQLErrorCodes对象，程序出现异常时将根据错误码解析并转义为Spring定义的数据库异常。

// 解析数据库自定义错误码
//org.springframework.jdbc.support.SQLErrorCodesFactory#resolveErrorCodes
synchronized (this.dataSourceCache) {
				// Double-check within full dataSourceCache lock
				sec = this.dataSourceCache.get(dataSource);
				if (sec == null) {
					// We could not find it - got to look it up.
					try {
					// 获取数据库名称如Orcle、MariaDB、MySQL等
						String name = JdbcUtils.extractDatabasemetaData(dataSource,
								DatabasemetaData::getDatabaseProductName);
						if (StringUtils.hasLength(name)) {
							return registerDatabase(dataSource, name);
						}
					}
					catch (metaDataAccessException ex) {
						logger.warn("Error while extracting database name", ex);
					}
					return null;
				}
			}


// 注册数据库并将SQLErrorCodoes与之对应
//org.springframework.jdbc.support.SQLErrorCodesFactory#registerDatabase
	SQLErrorCodes sec = getErrorCodes(databaseName);
		if (logger.isDebugEnabled()) {
			logger.debug("Caching SQL error codes for DataSource [" + identify(dataSource) +
					"]: database product name is '" + databaseName + "'");
		}
		this.dataSourceCache.put(dataSource, sec);
		return sec;

3. 判断解决方案

sql-error-codes.xml中关键的一句描述" Can be overridden by definitions in a “sql-error-codes.xml” file - in the root of the class path." 。该文件可以被覆盖，直接复制该文件增加对应数据库需要的错误码映射。

4. 错误码与数据库名称的确定过程

• 发生异常时通过java.sql.SQLException#vendorCode可获取数据库错误码(JSR规范)
• JdbcUtils.extractDatabasemetaData(dataSource,Databasemetadata::getDatabaseProductName)方法可获取数据库名称

参考 sql-error-codes.xml

	
		
			DB2*
		
		
			-007,-029,-097,-104,-109,-115,-128,-199,-204,-206,-301,-408,-441,-491
		
		
			-803
		
		
			-407,-530,-531,-532,-543,-544,-545,-603,-667
		
		
			-904,-971
		
		
			-1035,-1218,-30080,-30081
		
		
			-911,-913
		
	

	
		
			Apache Derby
		
		
			true
		
		
			42802,42821,42X01,42X02,42X03,42X04,42X05,42X06,42X07,42X08
		
		
			23505
		
		
			22001,22005,23502,23503,23513,X0Y32
		
		
			04501,08004,42Y07
		
		
			40XL1
		
		
			40001
		
	

	
		
			42000,42001,42101,42102,42111,42112,42121,42122,42132
		
		
			23001,23505
		
		
			22001,22003,22012,22018,22025,23000,23002,23003,23502,23503,23506,23507,23513
		
		
			90046,90100,90117,90121,90126
		
		
			50200
		
	

	
	
		
			
				SAP HANA
				SAP DB
			
		
		
			
				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
			
		
		
			10,258
		
		
			301
		
		
			461,462
		
		
			-813,-709,-708,1024,1025,1026,1027,1029,1030,1031
		
		
			-11210,582,587,588,594
		
		
			131
		
		
			138,143
		
		
			133
		
	

	
		
			HSQL Database Engine
		
		
			-22,-28
		
		
			-104
		
		
			-9
		
		
			-80
		
	

	
		
			Informix Dynamic Server
		
		
			-201,-217,-696
		
		
			-239,-268,-6017
		
		
			-692,-11030
		
	

	
		
			Microsoft SQL Server
		
		
			156,170,207,208,209
		
		
			229
		
		
			2601,2627
		
		
			544,8114,8115
		
		
			4060
		
		
			1222
		
		
			1205
		
	

	
		
			
			 	
				MySQL
				MariaDB
			
		
		
			1054,1064,1146
		
		
			1062
		
		
			630,839,840,893,1169,1215,1216,1217,1364,1451,1452,1557
		
		
			1
		
		
			1205,3572
		
		
			1213
		
	

	
		
			900,903,904,917,936,942,17006,6550
		
		
			17003
		
		
			1
		
		
			1400,1722,2291,2292
		
		
			17002,17447
		
		
			54,30006
		
		
			8177
		
		
			60
		
	

	
		
			true
		
		
			03000,42000,42601,42602,42622,42804,42P01
		
		
			21000,23505
		
		
			23000,23502,23503,23514
		
		
			53000,53100,53200,53300
		
		
			55P03
		
		
			40001
		
		
			40P01
		
	

	
		
			
				Sybase SQL Server
				Adaptive Server Enterprise
				ASE  
				SQL Server
				sql server  
			
		
		
			101,102,103,104,105,106,107,108,109,110,111,112,113,116,120,121,123,207,208,213,257,512
		
		
			2601,2615,2626
		
		
			233,511,515,530,546,547,2615,2714
		
		
			921,1105
		
		
			12205
		
		
			1205
		
	

	
	
		
			
				
				DM DBMS
			
		
		
			-6602