Checking Oracle availability with Spring jdbc 2.0

Spring Jdbc uses its own Runtime Exception hierarchy in order to provide Exceptions from the database layer. Hence, no Exception is thrown by database methods encapsuling queries and error control can be delegate to services.

Catching DataAccessResourceFailureException and CannotGetJdbcConnectionException should be enough to detect that database is down. However in Spring exceptions hierarchy exists UncategorizedDataAccessException too. Several Oracle critical errors are translated by Spring under this category.

By default, only error codes appearing in org.springframework.jdbc.support.sql-error-codes.xml are mapped to Spring customized exceptions. In order to detect another error codes SQLErrorCodeSQLExceptionTranslator class is provided. Every database exception its translated by this mechanism. The point is to include here the complete oracle error code list for database unavailability.

JdbcTemplate initialization

JdbcTemplate jt = new JdbcTemplate(dataSource);
CustomExceptionTranslator cet = new CustomExceptionTranslator();
fet.setDataSource(dataSource);
jt.setExceptionTranslator(fet);

Custom SQLErrorCodeSQLExceptionTranslator

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;

public class CustomExceptionTranslator extends SQLErrorCodeSQLExceptionTranslator {

  // Oracle error codes uncategorized by Spring
  private static final List ORACLE_CLOSED_ERROR_CODES =
    Arrays.asList(1012, 1033, 1034, 1041, 1089, 1090, 1114,
        12154, 12203, 12224, 12500, 12505, 12519, 12520,
        12535, 12541, 12545, 12571, 1688, 17002, 17008,
        17410, 17447, 23001, 27091, 27092, 27101, 3113, 3114);

  /**
   * Translate SQLExceptions to Spring RuntimeExceptions
   */
  public DataAccessException translate(String task, String sql, SQLException sqlex) {

    DataAccessException dae = super.translate(task, sql, sqlex);

    if (dae instanceof DataAccessResourceFailureException ||
      dae instanceof CannotGetJdbcConnectionException   ||
      ORACLE_CLOSED_ERROR_CODES.contains(sqlex.getErrorCode())) {

      // Database is DOWN!

    }

    return dae;
  }

}

Further information

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s