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.

Answer 2

Definitely don't use this pattern:

    logger.error("exception on deleting store - " + store.toString(), ex);
    throw ex;

because if often leads to duplicates log entries. There should be some global trap for exceptions and its responsibility is to log the error.

EDIT

By global trap for exceptions, I mean that every application should have some mechanism for catching most (ideally all) exceptions from Java code and log them. Imagine that you don't catch and miss log for some important error. You are than blind when trying to figure out what happened in production.

So let's pretend that we have such exception logging mechanism in place. Your pattern would log SQL error and throw exception that would be catched by global exception trap and logged again. You don't want that to happen, so don't log it in your code, save one line of code and don't create duplicate log entry.