Spring JdbcTemplate how to register parameters on exceptions?
Using Spring JdbcTemplate I'm trying to find a clean way to log exceptions at the DAO layer but can't seem to figure it out. I want to record the SQL query 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 am 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, is there a way to write this facility to clean up many dao methods? Perhaps at the registrar level or in some Spring library? Or is this the cleanest way (or the code above is even bad)?
I have several methods that do basically the same thing, take in an object, pass fields to a request, and return a result.
source to share
The tricky thing with Spring is that the JDBC objects from which you want to get this information are not Spring-managed objects, they are created by the driver. Thus, Spring AOP will not apply (without using AspectJ).
Spring can provide you with the request and parameters separately if you register the "org.springframework.jdbc.core.JdbcTemplate" category at the DEBUG level and "org.springframework.jdbc.core.StatementCreatorUtils" at the TRACE level.
There are existing libraries log4jdbc and p6spy that wraps around a JDBC driver to generate an SQL statement with the parameters entered. See this question . Using any of these should be to add the jar to the project by changing your jdbc url to a shell pointer and changing the registration to get the level of information you want.
The existing logging code is not good because it is repetitive cut-n-paste code and it will cause exceptions to be logged multiple times. The magazines will be harder to read and will roll more often.
source to share
Definitely don't use this pattern:
logger.error("exception on deleting store - " + store.toString(), ex);
throw ex;
because if often leads to duplicate log entries. There must be some kind of global exception trap, and its responsibility is to log the error.
EDIT
By a global exception trap, I mean every application should have some mechanism to find most (ideally all) exceptions from Java code and log them. Imagine not catching and missing the log for some important error. You are not blind when trying to figure out what happened in production.
So pretend we have such a mechanism for registering exceptions. Your template will log an SQL error and an exception exception, which will be caught by the global exception trap and logged again. You don't want this to happen, so don't write it in your code, keep one line of code, and don't create a duplicate log entry.
source to share