Reproducing SQL Exception and correct handling in layer architecture

Question

Lets say I have this:

String sql = "DELETE FROM "+ TABLE_NAME +"  WHERE id=?";
       
try {
    int ra = jdbcTemplate.update(connection -> {
                PreparedStatement stmt = connection.prepareStatement(sql);
                stmt.setLong(1, id);
                stmt.executeUpdate();
                return stmt;
            });

    if (ra == 0)
        throw new SQLException(SQL_ERROR);
} catch (SQLException e){
    LOGGER.error(SQL_ERROR);
    throw new DataAccessLayerException("Entity could not be deleted due to SQL Exception");
}

I am deleting a entity from table with key(id). Now I want to be able to report INTERNAL_SERVER_ERROR to client without causing a RuntimeException.

However, I can't reproduce SQLException. If I for example delete a letter in my sql statement I only get a Syntax error that is thrown as Runtime error and it seems like I am not catching it.

Help me understand what is exactly meant here with SQLException and how do I reproduce it and report this to client without causing RuntimeException?

Answer 1

Your code is wrong. You're calling update(PreparedStatementCreator psc), and as the name suggests, your code should create a fully "prepared" statement object, but it should not "execute" that statement, so remove the executeUpdate() call.

The update() call throws a DataAccessException, not a SQLException, so the only source of SQLException inside that try block is your if statement, so you might as well just throw that DataAccessLayerException exception directly. I would recommend throwing a EmptyResultDataAccessException instead, though.

Since Spring specifically converts the checked SQLException into unchecked DataAccessException runtime exception, you will always get runtime exceptions when using Spring JDBC, and that is a good thing, because you now won't need to add checked exceptions to all the methods in your code.

An unhandled exception is handled by Spring MVC. By default, Spring MVC will redirect to an error page. If you don't want that, see e.g. question Spring Boot Remove Whitelabel Error Page.