001/* 002 * Copyright 2002-2019 the original author or authors. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * https://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017package org.springframework.jdbc.support; 018 019import java.sql.SQLException; 020import java.util.HashSet; 021import java.util.Set; 022 023import org.springframework.dao.ConcurrencyFailureException; 024import org.springframework.dao.DataAccessException; 025import org.springframework.dao.DataAccessResourceFailureException; 026import org.springframework.dao.DataIntegrityViolationException; 027import org.springframework.dao.QueryTimeoutException; 028import org.springframework.dao.TransientDataAccessResourceException; 029import org.springframework.jdbc.BadSqlGrammarException; 030import org.springframework.lang.Nullable; 031 032/** 033 * {@link SQLExceptionTranslator} implementation that analyzes the SQL state in 034 * the {@link SQLException} based on the first two digits (the SQL state "class"). 035 * Detects standard SQL state values and well-known vendor-specific SQL states. 036 * 037 * <p>Not able to diagnose all problems, but is portable between databases and 038 * does not require special initialization (no database vendor detection, etc.). 039 * For more precise translation, consider {@link SQLErrorCodeSQLExceptionTranslator}. 040 * 041 * @author Rod Johnson 042 * @author Juergen Hoeller 043 * @author Thomas Risberg 044 * @see java.sql.SQLException#getSQLState() 045 * @see SQLErrorCodeSQLExceptionTranslator 046 */ 047public class SQLStateSQLExceptionTranslator extends AbstractFallbackSQLExceptionTranslator { 048 049 private static final Set<String> BAD_SQL_GRAMMAR_CODES = new HashSet<>(8); 050 051 private static final Set<String> DATA_INTEGRITY_VIOLATION_CODES = new HashSet<>(8); 052 053 private static final Set<String> DATA_ACCESS_RESOURCE_FAILURE_CODES = new HashSet<>(8); 054 055 private static final Set<String> TRANSIENT_DATA_ACCESS_RESOURCE_CODES = new HashSet<>(8); 056 057 private static final Set<String> CONCURRENCY_FAILURE_CODES = new HashSet<>(4); 058 059 060 static { 061 BAD_SQL_GRAMMAR_CODES.add("07"); // Dynamic SQL error 062 BAD_SQL_GRAMMAR_CODES.add("21"); // Cardinality violation 063 BAD_SQL_GRAMMAR_CODES.add("2A"); // Syntax error direct SQL 064 BAD_SQL_GRAMMAR_CODES.add("37"); // Syntax error dynamic SQL 065 BAD_SQL_GRAMMAR_CODES.add("42"); // General SQL syntax error 066 BAD_SQL_GRAMMAR_CODES.add("65"); // Oracle: unknown identifier 067 068 DATA_INTEGRITY_VIOLATION_CODES.add("01"); // Data truncation 069 DATA_INTEGRITY_VIOLATION_CODES.add("02"); // No data found 070 DATA_INTEGRITY_VIOLATION_CODES.add("22"); // Value out of range 071 DATA_INTEGRITY_VIOLATION_CODES.add("23"); // Integrity constraint violation 072 DATA_INTEGRITY_VIOLATION_CODES.add("27"); // Triggered data change violation 073 DATA_INTEGRITY_VIOLATION_CODES.add("44"); // With check violation 074 075 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("08"); // Connection exception 076 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("53"); // PostgreSQL: insufficient resources (e.g. disk full) 077 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("54"); // PostgreSQL: program limit exceeded (e.g. statement too complex) 078 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("57"); // DB2: out-of-memory exception / database not started 079 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("58"); // DB2: unexpected system error 080 081 TRANSIENT_DATA_ACCESS_RESOURCE_CODES.add("JW"); // Sybase: internal I/O error 082 TRANSIENT_DATA_ACCESS_RESOURCE_CODES.add("JZ"); // Sybase: unexpected I/O error 083 TRANSIENT_DATA_ACCESS_RESOURCE_CODES.add("S1"); // DB2: communication failure 084 085 CONCURRENCY_FAILURE_CODES.add("40"); // Transaction rollback 086 CONCURRENCY_FAILURE_CODES.add("61"); // Oracle: deadlock 087 } 088 089 090 @Override 091 @Nullable 092 protected DataAccessException doTranslate(String task, @Nullable String sql, SQLException ex) { 093 // First, the getSQLState check... 094 String sqlState = getSqlState(ex); 095 if (sqlState != null && sqlState.length() >= 2) { 096 String classCode = sqlState.substring(0, 2); 097 if (logger.isDebugEnabled()) { 098 logger.debug("Extracted SQL state class '" + classCode + "' from value '" + sqlState + "'"); 099 } 100 if (BAD_SQL_GRAMMAR_CODES.contains(classCode)) { 101 return new BadSqlGrammarException(task, (sql != null ? sql : ""), ex); 102 } 103 else if (DATA_INTEGRITY_VIOLATION_CODES.contains(classCode)) { 104 return new DataIntegrityViolationException(buildMessage(task, sql, ex), ex); 105 } 106 else if (DATA_ACCESS_RESOURCE_FAILURE_CODES.contains(classCode)) { 107 return new DataAccessResourceFailureException(buildMessage(task, sql, ex), ex); 108 } 109 else if (TRANSIENT_DATA_ACCESS_RESOURCE_CODES.contains(classCode)) { 110 return new TransientDataAccessResourceException(buildMessage(task, sql, ex), ex); 111 } 112 else if (CONCURRENCY_FAILURE_CODES.contains(classCode)) { 113 return new ConcurrencyFailureException(buildMessage(task, sql, ex), ex); 114 } 115 } 116 117 // For MySQL: exception class name indicating a timeout? 118 // (since MySQL doesn't throw the JDBC 4 SQLTimeoutException) 119 if (ex.getClass().getName().contains("Timeout")) { 120 return new QueryTimeoutException(buildMessage(task, sql, ex), ex); 121 } 122 123 // Couldn't resolve anything proper - resort to UncategorizedSQLException. 124 return null; 125 } 126 127 /** 128 * Gets the SQL state code from the supplied {@link SQLException exception}. 129 * <p>Some JDBC drivers nest the actual exception from a batched update, so we 130 * might need to dig down into the nested exception. 131 * @param ex the exception from which the {@link SQLException#getSQLState() SQL state} 132 * is to be extracted 133 * @return the SQL state code 134 */ 135 @Nullable 136 private String getSqlState(SQLException ex) { 137 String sqlState = ex.getSQLState(); 138 if (sqlState == null) { 139 SQLException nestedEx = ex.getNextException(); 140 if (nestedEx != null) { 141 sqlState = nestedEx.getSQLState(); 142 } 143 } 144 return sqlState; 145 } 146 147}