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}