Shulou(Shulou.com)06/03 Report--

This article mainly introduces "the conversion method of sql exception in Spring different databases". In the daily operation, I believe that many people have doubts about the conversion method of sql exception in different Spring database. The editor consulted all kinds of data and sorted out the simple and useful operation method. I hope it will be helpful to answer the doubt of "conversion method of sql exception in different Spring database". Next, please follow the editor to study!

Catalogue

Preface

Code implementation

Processing flow chart

What design patterns are used?

Combination mode

Singleton mode

Strategy mode

Summary:

Preface

In the case of using Spring-Jdbc, in some scenarios, we need to write our business code according to the type of exception reported by the database. For example, we have a logic that if our newly inserted record has a unique constraint conflict, it will be returned to the client to describe: the record already exists, do not repeat the operation.

The code usually goes like this:

@ Resourceprivate JdbcTemplate jdbcTemplate;public String testAdd () {try {jdbcTemplate.execute ("INSERT INTO user_info (user_id, user_name, email, nick_name, status, address) VALUES (80002, 'Zhang Sanfeng', 'xxx@126.com',' Zhang Zhenren', 1, 'Wudang Mountain');"); return "OK";} catch (DuplicateKeyException e) {return "record already exists, do not repeat operations";}}

Test it:

As indicated in the above figure, and no matter what database is changed (supported by Spring-Jdbc), the code does not need to be changed.

So how does Spring help us abstract exceptions when Spring-Jdbc uses different databases?

Code implementation

Let's take a forward look at the code:

First, enter the JdbcTemplate.execute method:

Public void execute (final String sql) throws DataAccessException {if (this.logger.isDebugEnabled ()) {this.logger.debug ("Executing SQL statement [" + sql + "]");}. / / the actual execution entry, calling the internal method this.execute (new ExecuteStatementCallback (), true);}

Internal method execute

Nullableprivate T execute (StatementCallback action, boolean closeResources) throws DataAccessException {Assert.notNull (action, "Callback object must not be null"); Connection con = DataSourceUtils.getConnection (this.obtainDataSource ()); Statement stmt = null; Object var12; try {.} catch (SQLException var10) {.... / after an exception occurs in SQL, all exceptions are converted here to throw this.translateException ("StatementCallback", sql, var10);} finally {if (closeResources) {JdbcUtils.closeStatement (stmt); DataSourceUtils.releaseConnection (con, this.getDataSource ());}} return var12;}

Exception conversion method translateException

Protected DataAccessException translateException (String task, @ Nullable String sql, SQLException ex) {/ / gets the exception converter, and then performs the conversion operation / / conversion according to the information related to the database return code. There are also exceptions UncategorizedSQLException DataAccessException dae = this.getExceptionTranslator (). Translate (task, sql, ex); return (DataAccessException) (dae! = null? Dae: new UncategorizedSQLException (task, sql, ex);}

Get the converter method getExceptionTranslator

Public SQLExceptionTranslator getExceptionTranslator () {/ / gets the converter attribute, and if empty, generates a SQLExceptionTranslator exceptionTranslator = this.exceptionTranslator; if (exceptionTranslator! = null) {return exceptionTranslator;} else {synchronized (this) {SQLExceptionTranslator exceptionTranslator = this.exceptionTranslator; if (exceptionTranslator = = null) {DataSource dataSource = this.getDataSource () / / shouldIgnoreXml is a tag that does not load bean through xml. The default false if (shouldIgnoreXml) {exceptionTranslator = new SQLExceptionSubclassTranslator () } else if (dataSource! = null) {/ / if DataSource is not empty, generate converter SQLErrorCodeSQLExceptionTranslator, usually get the converter exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator (dataSource) first;} else {/ / other cases, generate SQLStateSQLExceptionTranslator converter exceptionTranslator = new SQLStateSQLExceptionTranslator () } this.exceptionTranslator = (SQLExceptionTranslator) exceptionTranslator;} return (SQLExceptionTranslator) exceptionTranslator;}

Conversion method:

Because the default converter is SQLErrorCodeSQLExceptionTranslator, the doTranslate method of SQLErrorCodeSQLExceptionTranslator is called here

The calling relationship of the class diagram is as above, and the method of AbstractFallbackSQLExceptionTranslator.translate is actually called first.

@ Nullablepublic DataAccessException translate (String task, @ Nullable String sql, SQLException ex) {Assert.notNull (ex, "Cannot translate a null SQLException"); / / this is where the SQLErrorCodeSQLExceptionTranslator.doTranslate method DataAccessException dae = this.doTranslate (task, sql, ex); if (dae! = null) {return dae } else {/ / if no response exception is found, call another converter and enter a recursive call, followed by SQLExceptionTranslator fallback = this.getFallbackTranslator (); return fallback! = null? Fallback.translate (task, sql, ex): null;}}

The method of actually converting the class SQLErrorCodeSQLExceptionTranslator:

Some irrelevant codes are omitted here, only the core code / / gets the SQLErrorCodes collection first, matches it according to the ErrorCode obtained in the returned SQLException, and returns the exception protected DataAccessException doTranslate (String task, @ Nullable String sql, SQLException ex) {.... SQLErrorCodes sqlErrorCodes = this.getSqlErrorCodes (); String errorCode = Integer.toString (ex.getErrorCode ()); / / the uniqueness constraint conflicts with 1062 here, so the logic here returns DuplicateKeyException if (Arrays.binarySearch (sqlErrorCodes.getDuplicateKeyCodes (), errorCode) > = 0) {this.logTranslation (task, sql, sqlEx, false). Return new DuplicateKeyException (this.buildMessage (task, sql, sqlEx), sqlEx); Return null;}

The above SQLErrorCodes is a collection of error codes, but not all the error codes of all databases, but only the error codes of the corresponding databases. How to ensure that the error codes of the currently used database are obtained instead of those of other databases? Of course, Spring has been implemented for us, in SQLErrorCodeSQLExceptionTranslator:

Public class SQLErrorCodeSQLExceptionTranslator extends AbstractFallbackSQLExceptionTranslator {private SingletonSupplier sqlErrorCodes;// default constructor, which sets that if the conversion fails, the next converter is SQLExceptionSubclassTranslator public SQLErrorCodeSQLExceptionTranslator () {this.setFallbackTranslator (new SQLExceptionSubclassTranslator ());} / / when the converter is generated earlier, exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator (dataSource); / / this constructor is used and DataSource is passed in, which contains database vendor information. In this paper, MYSQLpublic SQLErrorCodeSQLExceptionTranslator (DataSource dataSource) {this () This.setDataSource (dataSource);} / / obtain all the error codes public void setDataSource (DataSource dataSource) {this.sqlErrorCodes = SingletonSupplier.of (()-> {return SQLErrorCodesFactory.getInstance (). ResolveErrorCodes (dataSource);}); this.sqlErrorCodes.get ();}} from the error code factory SQLErrorCodesFactory.

The resolveErrorCodes method of error code factory SQLErrorCodesFactory:

/ / since it is a factory, there must be error codes for various databases. MYSQL is used in this article. Let's take a look at the implementation logic @ Nullablepublic SQLErrorCodes resolveErrorCodes (DataSource dataSource) {Assert.notNull (dataSource, "DataSource must not be null"); if (logger.isDebugEnabled ()) {logger.debug ("Looking up default SQLErrorCodes for DataSource [" + this.identify (dataSource) + "]"). } / / get SQLErrorCodes SQLErrorCodes sec = (SQLErrorCodes) this.dataSourceCache.get (dataSource) of MYSQL from cache; if (sec = = null) {synchronized (this.dataSourceCache) {sec = (SQLErrorCodes) this.dataSourceCache.get (dataSource); if (sec = = null) {try {String name = (String) JdbcUtils.extractDatabaseMetaData (dataSource, DatabaseMetaData::getDatabaseProductName) If (StringUtils.hasLength (name)) {SQLErrorCodes var10000 = this.registerDatabase (dataSource, name); return var10000;}} catch (MetaDataAccessException var6) {logger.warn ("Error while extracting database name", var6);} return null } if (logger.isDebugEnabled ()) {logger.debug ("SQLErrorCodes found in cache for DataSource [" + this.identify (dataSource) + "]");} return sec;}

How is the cache dataSourceCache generated?

Public SQLErrorCodes registerDatabase (DataSource dataSource, String databaseName) {/ / get the list of error codes SQLErrorCodes sec = this.getErrorCodes (databaseName) based on the database type name (here is MySQL); if (logger.isDebugEnabled ()) {logger.debug ("Caching SQL error codes for DataSource [" + this.identify (dataSource) + "]: database product name is'" + databaseName + "");} this.dataSourceCache.put (dataSource, sec); return sec } public SQLErrorCodes getErrorCodes (String databaseName) {Assert.notNull (databaseName, "Database product name must not be null"); / / get SQLErrorCodes SQLErrorCodes sec = (SQLErrorCodes) this.errorCodesMap.get (databaseName) from errorCodesMap according to key=MYSQL; if (sec = = null) {Iterator var3 = this.errorCodesMap.values (). Iterator () While (var3.hasNext ()) {SQLErrorCodes candidate = (SQLErrorCodes) var3.next (); if (PatternMatchUtils.simpleMatch (candidate.getDatabaseProductNames (), databaseName)) {sec = candidate; break } if (sec! = null) {this.checkCustomTranslatorRegistry (databaseName, sec); if (logger.isDebugEnabled ()) {logger.debug ("SQL error codes for'" + databaseName + "'found");} return sec } else {if (logger.isDebugEnabled ()) {logger.debug ("SQL error codes for'" + databaseName + "'not found");} return new SQLErrorCodes () In the SQLErrorCodesFactory constructor, the content of the generated errorCodesMap,map comes from the org/springframework/jdbc/support/sql-error-codes.xml file protected SQLErrorCodesFactory () {Map errorCodes; try {DefaultListableBeanFactory lbf = new DefaultListableBeanFactory (); lbf.setBeanClassLoader (this.getClass (). GetClassLoader () XmlBeanDefinitionReader bdr = new XmlBeanDefinitionReader (lbf); Resource resource = this.loadResource ("org/springframework/jdbc/support/sql-error-codes.xml"); if (resource! = null & & resource.exists ()) {bdr.loadBeanDefinitions (resource);} else {logger.info ("Default sql-error-codes.xml not found (should be included in spring-jdbc jar)") } resource = this.loadResource ("sql-error-codes.xml"); if (resource! = null & & resource.exists ()) {bdr.loadBeanDefinitions (resource); logger.debug ("Found custom sql-error-codes.xml file at the root of the classpath");} errorCodes = lbf.getBeansOfType (SQLErrorCodes.class, true, false) If (logger.isTraceEnabled ()) {logger.trace ("SQLErrorCodes loaded:" + errorCodes.keySet ());}} catch (BeansException var5) {logger.warn ("Error loading SQL error codes from config file", var5); errorCodes = Collections.emptyMap ();} this.errorCodesMap = errorCodes;}

The main error codes for each database are configured in the sql-error-codes.xml file.

The MYSQL part is listed here, and of course there are other parts. We can see that the uniqueness constraint error code is 1062, which can be translated into a DuplicateKeyException exception.

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

As you have seen, for example, some of the error code values above are listed. If there is an error code that is not in it, it must not match. Of course, Spring can think of this situation.

/ * * @ public-public-number: programmer A Niu * in AbstractFallbackSQLExceptionTranslator, you can get the next subsequent converter if the search fails * / @ Nullable public DataAccessException translate (String task, @ Nullable String sql, SQLException ex) {Assert.notNull (ex, "Cannot translate a null SQLException"); DataAccessException dae = this.doTranslate (task, sql, ex) If (dae! = null) {return dae;} else {SQLExceptionTranslator fallback = this.getFallbackTranslator (); return fallback! = null? Fallback.translate (task, sql, ex): null;}}

What is the post converter for SQLErrorCodeSQLExceptionTranslator?

/ / specified in the constructor, SQLExceptionSubclassTranslatorpublic SQLErrorCodeSQLExceptionTranslator () {this.setFallbackTranslator (new SQLExceptionSubclassTranslator ());}

The logic of the conversion method of SQLExceptionSubclassTranslator is as follows:

/ * * @ public-public-number: programmer A Niu * can see that it is actually judged according to the subclass type, and the corresponding error class is returned. If there is no match, the next processor is found. According to the construction method Qingsong, we can find * SQLStateSQLExceptionTranslator*/@Nullableprotected DataAccessException doTranslate (String task, @ Nullable String sql, SQLException ex) {if (ex instanceof SQLTransientException) {if (ex instanceof SQLTransientConnectionException) {return new TransientDataAccessResourceException (task, sql, ex), ex) } if (ex instanceof SQLTransactionRollbackException) {return new ConcurrencyFailureException (this.buildMessage (task, sql, ex), ex);} if (ex instanceof SQLTimeoutException) {return new QueryTimeoutException (task, sql, ex), ex) } else if (ex instanceof SQLNonTransientException) {if (ex instanceof SQLNonTransientConnectionException) {return new DataAccessResourceFailureException (this.buildMessage (task, sql, ex), ex);} if (ex instanceof SQLDataException) {return new DataIntegrityViolationException (this.buildMessage (task, sql, ex), ex) } if (ex instanceof SQLIntegrityConstraintViolationException) {return new DataIntegrityViolationException (this.buildMessage (task, sql, ex), ex);} if (ex instanceof SQLInvalidAuthorizationSpecException) {return new PermissionDeniedDataAccessException (this.buildMessage (task, sql, ex), ex);} if (ex instanceof SQLSyntaxErrorException) {return new BadSqlGrammarException (task, sql! = null? Sql: "", ex);} if (ex instanceof SQLFeatureNotSupportedException) {return new InvalidDataAccessApiUsageException (this.buildMessage (task, sql, ex), ex);}} else if (ex instanceof SQLRecoverableException) {return new RecoverableDataAccessException (this.buildMessage (task, sql, ex), ex);} return null;}

The conversion method of SQLStateSQLExceptionTranslator:

/ * * @ public-public-sign: programmer A Niu * can see that the exception is judged according to the first two digits of SQLState, and the corresponding exception information is returned * / @ Nullableprotected DataAccessException doTranslate (String task, @ Nullable String sql, SQLException ex) {String sqlState = this.getSqlState (ex); if (sqlState! = null & sqlState.length () > = 2) {String classCode = sqlState.substring (0,2) If (this.logger.isDebugEnabled ()) {this.logger.debug ("Extracted SQL state class'" + classCode + "'from value'" + sqlState + "'");} if (BAD_SQL_GRAMMAR_CODES.contains (classCode)) {return new BadSqlGrammarException (task, sql! = null? Sql: ", ex);} if (DATA_INTEGRITY_VIOLATION_CODES.contains (classCode)) {return new DataIntegrityViolationException (this.buildMessage (task, sql, ex), ex);} if (DATA_ACCESS_RESOURCE_FAILURE_CODES.contains (classCode)) {return new DataAccessResourceFailureException (task, sql, ex), ex) } if (TRANSIENT_DATA_ACCESS_RESOURCE_CODES.contains (classCode)) {return new TransientDataAccessResourceException (this.buildMessage (task, sql, ex), ex);} if (CONCURRENCY_FAILURE_CODES.contains (classCode)) {return new ConcurrencyFailureException (this.buildMessage (task, sql, ex), ex);} return ex.getClass (). GetName (). Contains ("Timeout")? New QueryTimeoutException (this.buildMessage (task, sql, ex), ex): null;}

Why can SQLState get the error type?

Because the database is defined according to the X/Open and SQL Access Group SQL CAE specifications (1992), SQLERROR returns a SQLSTATE value. The SQLSTATE value is a string of five characters. The five characters contain numeric values or uppercase letters and represent codes for various error or warning conditions. SQLSTATE has a hierarchical pattern: the first two characters identify the category of the condition that usually represents the error condition, and the last three characters represent the subclasses in the generic class. The status of success is identified by 00000. SQLSTATE code is defined in SQL standards in most places.

Processing flow chart

What design patterns are used?

Combination mode

Through the figure above, have you found the relationship between the three implementation classes-the combination relationship? the combination relationship becomes a recursive call in the parent class AbstractFallbackSQLExceptionTranslator, which is full of wisdom (Composite design pattern).

Singleton mode

In SQLErrorCodesFactory (singleton mode)

Strategy mode

Get different errorcodes collections depending on the database

Summary:

In the process of learning, we should pay attention not only to the way it is realized, but also to what we can learn from it. For example, from this abnormal abstraction, you can learn several design patterns, as well as the scenarios used, which can be applied to future work.

At this point, the study of "conversion methods for sql exceptions in different Spring databases" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.