001/* 002 * Copyright 2002-2014 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; 030 031/** 032 * {@link SQLExceptionTranslator} implementation that analyzes the SQL state in 033 * the {@link SQLException} based on the first two digits (the SQL state "class"). 034 * Detects standard SQL state values and well-known vendor-specific SQL states. 035 * 036 * <p>Not able to diagnose all problems, but is portable between databases and 037 * does not require special initialization (no database vendor detection, etc.). 038 * For more precise translation, consider {@link SQLErrorCodeSQLExceptionTranslator}. 039 * 040 * @author Rod Johnson 041 * @author Juergen Hoeller 042 * @author Thomas Risberg 043 * @see java.sql.SQLException#getSQLState() 044 * @see SQLErrorCodeSQLExceptionTranslator 045 */ 046public class SQLStateSQLExceptionTranslator extends AbstractFallbackSQLExceptionTranslator { 047 048 private static final Set<String> BAD_SQL_GRAMMAR_CODES = new HashSet<String>(8); 049 050 private static final Set<String> DATA_INTEGRITY_VIOLATION_CODES = new HashSet<String>(8); 051 052 private static final Set<String> DATA_ACCESS_RESOURCE_FAILURE_CODES = new HashSet<String>(8); 053 054 private static final Set<String> TRANSIENT_DATA_ACCESS_RESOURCE_CODES = new HashSet<String>(8); 055 056 private static final Set<String> CONCURRENCY_FAILURE_CODES = new HashSet<String>(4); 057 058 059 static { 060 BAD_SQL_GRAMMAR_CODES.add("07"); // Dynamic SQL error 061 BAD_SQL_GRAMMAR_CODES.add("21"); // Cardinality violation 062 BAD_SQL_GRAMMAR_CODES.add("2A"); // Syntax error direct SQL 063 BAD_SQL_GRAMMAR_CODES.add("37"); // Syntax error dynamic SQL 064 BAD_SQL_GRAMMAR_CODES.add("42"); // General SQL syntax error 065 BAD_SQL_GRAMMAR_CODES.add("65"); // Oracle: unknown identifier 066 067 DATA_INTEGRITY_VIOLATION_CODES.add("01"); // Data truncation 068 DATA_INTEGRITY_VIOLATION_CODES.add("02"); // No data found 069 DATA_INTEGRITY_VIOLATION_CODES.add("22"); // Value out of range 070 DATA_INTEGRITY_VIOLATION_CODES.add("23"); // Integrity constraint violation 071 DATA_INTEGRITY_VIOLATION_CODES.add("27"); // Triggered data change violation 072 DATA_INTEGRITY_VIOLATION_CODES.add("44"); // With check violation 073 074 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("08"); // Connection exception 075 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("53"); // PostgreSQL: insufficient resources (e.g. disk full) 076 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("54"); // PostgreSQL: program limit exceeded (e.g. statement too complex) 077 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("57"); // DB2: out-of-memory exception / database not started 078 DATA_ACCESS_RESOURCE_FAILURE_CODES.add("58"); // DB2: unexpected system error 079 080 TRANSIENT_DATA_ACCESS_RESOURCE_CODES.add("JW"); // Sybase: internal I/O error 081 TRANSIENT_DATA_ACCESS_RESOURCE_CODES.add("JZ"); // Sybase: unexpected I/O error 082 TRANSIENT_DATA_ACCESS_RESOURCE_CODES.add("S1"); // DB2: communication failure 083 084 CONCURRENCY_FAILURE_CODES.add("40"); // Transaction rollback 085 CONCURRENCY_FAILURE_CODES.add("61"); // Oracle: deadlock 086 } 087 088 089 @Override 090 protected DataAccessException doTranslate(String task, String sql, SQLException ex) { 091 // First, the getSQLState check... 092 String sqlState = getSqlState(ex); 093 if (sqlState != null && sqlState.length() >= 2) { 094 String classCode = sqlState.substring(0, 2); 095 if (logger.isDebugEnabled()) { 096 logger.debug("Extracted SQL state class '" + classCode + "' from value '" + sqlState + "'"); 097 } 098 if (BAD_SQL_GRAMMAR_CODES.contains(classCode)) { 099 return new BadSqlGrammarException(task, sql, ex); 100 } 101 else if (DATA_INTEGRITY_VIOLATION_CODES.contains(classCode)) { 102 return new DataIntegrityViolationException(buildMessage(task, sql, ex), ex); 103 } 104 else if (DATA_ACCESS_RESOURCE_FAILURE_CODES.contains(classCode)) { 105 return new DataAccessResourceFailureException(buildMessage(task, sql, ex), ex); 106 } 107 else if (TRANSIENT_DATA_ACCESS_RESOURCE_CODES.contains(classCode)) { 108 return new TransientDataAccessResourceException(buildMessage(task, sql, ex), ex); 109 } 110 else if (CONCURRENCY_FAILURE_CODES.contains(classCode)) { 111 return new ConcurrencyFailureException(buildMessage(task, sql, ex), ex); 112 } 113 } 114 115 // For MySQL: exception class name indicating a timeout? 116 // (since MySQL doesn't throw the JDBC 4 SQLTimeoutException) 117 if (ex.getClass().getName().contains("Timeout")) { 118 return new QueryTimeoutException(buildMessage(task, sql, ex), ex); 119 } 120 121 // Couldn't resolve anything proper - resort to UncategorizedSQLException. 122 return null; 123 } 124 125 /** 126 * Gets the SQL state code from the supplied {@link SQLException exception}. 127 * <p>Some JDBC drivers nest the actual exception from a batched update, so we 128 * might need to dig down into the nested exception. 129 * @param ex the exception from which the {@link SQLException#getSQLState() SQL state} 130 * is to be extracted 131 * @return the SQL state code 132 */ 133 private String getSqlState(SQLException ex) { 134 String sqlState = ex.getSQLState(); 135 if (sqlState == null) { 136 SQLException nestedEx = ex.getNextException(); 137 if (nestedEx != null) { 138 sqlState = nestedEx.getSQLState(); 139 } 140 } 141 return sqlState; 142 } 143 144}