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}