With traditional JDBC-based applications, exception handling is based on java.sql.SQLException, which is a checked exception. It forces the developer to write catch and finally blocks carefully for proper handling and to avoid resource leakages such as leaving a database connection open. Spring, with its smart exception hierarchy based on RuntimeException, spares the developer from this nightmare. Having DataAccessException as the root, Spring bundles a bit set of meaningful exceptions, translating the traditional JDBC exceptions. Spring also covers Hibernate, JPA, and JDO exceptions in a consistent manner.

Spring uses SQLErrorCodeExceptionTranslator, which inherits SQLExceptionTranslator for translating SQLException to DataAccessExceptions. We can extend this class to customize the default translations. We can replace the default translator with our custom implementation by injecting into the persistence resources (such as JdbcTemplate, to be covered later). See the following code listing for how we define a SQLExceptionTranslator class in your code:

String userQuery = "select * from TBL_NONE where name = ?";
SQLExceptionTranslator excTranslator = new SQLExceptionTranslator() {

  @Override
  public DataAccessException translate(String task, String sql, SQLException ex) {
    logger.info("SUCCESS --- SQLExceptionTranslator.translate invoked !!");
    return new BadSqlGrammarException("Invalid Query", userQuery, ex){};
  }
};

The preceding code snippet catches any SQLException and converts it into a Spring-based BadSqlGrammarException instance. Then, this custom SQLExceptionTranslator needs to be passed to the Jdbctemplate before use, as shown in the following code:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setExceptionTranslator(excTranslator);
Map<String, Object> result = jdbcTemplate.queryForMap(userQuery, new Object[] {"abc"});

Now, any invalid query will invoke the custom SQLExceptionTranslator class. You can customize its behavior according to your requirements.