Spring JdbcTemplate how to log parameters on exceptions?

Question

Using Spring's JdbcTemplate, I've been trying to figure out a clean way to log exceptions in the DAO layer, but can't seem to figure it out. I want to log the SQL statement that was used and the parameters.

For example, where addStoreSql is a parameterized statement

public int addStore(Store store) {
    return jdbcTemplate.update(addStoreSql, store.getId(), store.getName());        
}

I'm doing something like..

public int addStore(Store store) {
    try{
        return jdbcTemplate.update(addStoreSql, store.getId(), store.getName());        
    } catch (DataAccessException ex) {
        logger.error("exception on deleting store - " + store.toString(), ex);
        throw ex;
    }
}

My question, is there a way to write this any cleaner across many dao methods? Possibly at the logger level or some Spring library? Or is this the cleanest way (Or is the above code even bad)?

I have multiple methods that do basically the same thing, take in a object, pass the fields to a query and return the result.

Answer 1

The difficulty of doing this with Spring is that the JDBC objects that you would want to get this information from are not Spring-managed objects, they're created by the driver. So Spring AOP won't apply (without using AspectJ).

Spring can supply the query and parameters separately for you, if you log the category "org.springframework.jdbc.core.JdbcTemplate" at DEBUG level and "org.springframework.jdbc.core.StatementCreatorUtils" at TRACE level.

There are existing libraries log4jdbc and p6spy that implement a wrapper around the JDBC driver, in order to generate a SQL statement with the parameters inserted in place. See this question. Using either of these should be a matter of adding the jar to the project, changing your jdbc url to point to the wrapper, and tweaking the logging to get the level of information you want.

The existing logging code is not good because it is repetitious cut-n-paste code, and it will result in exceptions being logged multiple times. The logs will be harder to read and will roll more frequently.