How to use SQLErrorCodeSQLExceptionTranslator and DAO class with @Repository in Spring?

Question

I'm using Spring 3.0.2 and I have a class called MovieDAO that uses JDBC to handle the db. I have set the @Repository annotations and I want to convert the SQLException to the Spring's DataAccessException I have the following example:

   @Repository
    public class JDBCCommentDAO implements CommentDAO {

        static JDBCCommentDAO instance;
        ConnectionManager connectionManager;

        private JDBCCommentDAO() {
            connectionManager = new ConnectionManager("org.postgresql.Driver", "postgres", "postgres");
        }

        static public synchronized JDBCCommentDAO getInstance() {
            if (instance == null)
                instance = new JDBCCommentDAO();
            return instance;
        }

        @Override
        public Collection<Comment> getComments(User user) throws DAOException {
            Collection<Comment> comments = new ArrayList<Comment>();
            try {
                String query = "SELECT * FROM Comments WHERE Comments.userId = ?";
                Connection conn = connectionManager.getConnection();
                PreparedStatement stmt = conn.prepareStatement(query);
                stmt = conn.prepareStatement(query);
                stmt.setInt(1, user.getId());
                ResultSet result = stmt.executeQuery();
                while (result.next()) {
                    Movie movie = JDBCMovieDAO.getInstance().getLightMovie(result.getInt("movie"));
                    comments.add(new Comment(result.getString("text"), result.getInt("score"), user, result.getDate("date"), movie));
                }
                connectionManager.closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
                        //CONVERT TO DATAACCESSEXCEPTION
            }
            return comments;
        }
}

I Don't know how to get the Translator and I don't want to extends any Spring class, because that is why I'm using the @Repository annotation

Answer 1

You must provide a bean-post processor to get your goal

<bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"/>

Or use SQLExceptionSubclassTranslator

private SQLExceptionTranslator sqlExceptionTranslator = new SQLExceptionSubclassTranslator();

catch(SQLException e) {
    throw sqlExceptionTranslator.doTranslate("<WHICH_TASK>", "<WHICH_SQL_QUERY>", e);
}

instead

Answer 2

JDBC doesn't work very well with @Repository and automatic exception translation, because SQLException is not a runtime exception. @Repository-style exception translation only really works with data access APIs that use runtime exceptions, e.g. Hibernate and JPA.

The @Repository annotation is used by the Spring context to auto-generate a proxy wrapper around your DAO, translating the exceptions as they get thrown. This only works with runtime exceptions, though. Specifically, if your DAO implementation class methods throw SQLException, then so must your interface method signatures, and so must the proxy wrapper, and so the client code must handle that exception, which all rather defeats the point of exception translation.

For JDBC, some coupling to the Spring API is usually necessary, either by extending JdbcDaoSupport and using getExceptionTranslator(), or manually constructing your own SQLExceptionTranslator instance. Either way, you need to catch SQLException inside the DAO and translate it into a DataAccessException.