Display SQL to Console in Spring JdbcTemplate

I have been working with the Spring JdbcTemplate framework for the last year because a project needs maximum performance for a large volume of data in the database.

In this framework, we do not have an easy option to trace all the queries (as with Hibernate and the setShowSql property) to be able to debug them and identify improvements or faults in the code.

So, I thought it might be interesting to implement a similar mechanism so that the developer can choose to log all queries with their replaced parameters. In this way, you can reproduce the exact queries that have been executed against the database and reproduce the scenario of the error or performance problem.

To solve this, I've implemented wrapper classes for DAO classes that call methods that execute JdbcTemplate queries. The methods of this enveloping class invoke the same method of the superclass from which they inherit and that is in charge of the management of the operation with the database. But before calling, it executes the method that traces the queries in the system of the log that has the project.

Like you can see at the comments section, this is a basic solution if you don't have any other method cleaner than this like these:

1. A proxy for the JDBC driver (http://log4jdbc.brunorozendo.com/)

2. Tracing with the Spring's logger setting the properties to DEBUG or TRACE levels in your log's configuration file.

3. Using a personal solution with Aspect Oriented Programming (AOP), setting java classes for identify the jdbctemplate DAOs and intercept then for tracing the queries.

The reinvented solution

In my project, I have a class of its own — ExampleJDBCDaoSupport — that is responsible for managing the connection of the DataSource and instantiating the objects of Spring-JDBCTemplate needed to execute queries. This class imports the basic Spring framework JDBC classes. 

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

Taking this class, we can make a Wrapper for each of these object types, which have the execute, update, and batchUpdate query methods. For this case, we took the "namedParameterJdbcTemplate" and created a new class, "WrapperNamedParameterJdbcTemplate", inherited from the previous one:

package com.displaysql.util;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class WrapperNamedParameterJdbcTemplate extends NamedParameterJdbcTemplate {

    public WrapperNamedParameterJdbcTemplate(DataSource dataSource) {
        super(dataSource);
        // TODO Auto-generated constructor stub
    }

    public WrapperNamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate) {
        super(classicJdbcTemplate);
        // TODO Auto-generated constructor stub
    }

Then, with Eclipse, we open the new class and add all methods of the parent class by right-clicking "Source" to "Override / Implement Methods" and choosing to generate all methods that have to do with running queries (the rest will use those of the parent unmodified).

We return to the class "ExampleJdbcDaoSupport and now change the references to the class "namedParameterJdbcTemplate" with the new "WrapperNamedParameterJdbcTemplate. This way, we already have all the executions of queries going through our wrapper class.

public class ExampleJdbcDaoSupport {

    /** The simple jdbc insert. */
    private SimpleJdbcInsert simpleJdbcInsert;   
    /** The jdbc template. */
    private JdbcTemplate jdbcTemplate;
    /** The simple jdbc template. */
    private SimpleJdbcTemplate simpleJdbcTemplate;
    /** The named parameter jdbc template. */
    private WrapperNamedParameterJdbcTemplate namedParameterJdbcTemplate;
    /** Logger para trazar la informacion */
    private final Logger LOG = LoggerFactory.getLogger(getClass());

    /**
     * Sets the data source.
     *
     * @param dataSource the new data source
     */
    public void setDataSource(final DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
        simpleJdbcInsert = new SimpleJdbcInsert(dataSource);
        simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        namedParameterJdbcTemplate = new WrapperNamedParameterJdbcTemplate(dataSource);
    }

    /**
     * Gets the named parameter jdbc template.
     *
     * @return the named parameter jdbc template
     */
    public WrapperNamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    /**
     * Sets the named parameter jdbc template.
     *
     * @param namedParameterJdbcTemplate the new named parameter jdbc template
     */
    public void setNamedParameterJdbcTemplate(WrapperNamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

The next step is to go to the new class "WrapperNamedParameterJdbcTemplate" and, in all its methods, include a trace in the log. In order to not repeat, nor harcode anything, we can create a Spring @Component with UTIL methods or, in our case, a static method called "debugSql" within the DaoUtil class that we already have. At that time, we find that most methods of executing queries have a String for the query, but in the case of the parameters, we have some that receive a "Map <String,"> or a type "SqlParameterSource", so we will have to create different methods.

    @Override
    public <T> T execute(String sql, Map<String, ?> paramMap, PreparedStatementCallback<T> action)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.execute(sql, paramMap, action);
    }

@Override
    public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql);
        return super.execute(sql, action);
    }

@Override
    public <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.query(sql, paramSource, rse);
    }

    @Override
    public <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.query(sql, paramMap, rse);
    }

   @Override
    public <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForObject(sql, paramSource, rowMapper);
    }

    @Override
    public <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForObject(sql, paramMap, rowMapper);
    }
    @Override
    public int update(String sql, SqlParameterSource paramSource) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.update(sql, paramSource);
    }

    @Override
    public int update(String sql, Map<String, ?> paramMap) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.update(sql, paramMap);
    }
    @Override
    public int[] batchUpdate(String sql, Map<String, ?>[] batchValues) {
        SqlQueriesUtil.debugSQL(sql, batchValues);
        return super.batchUpdate(sql, batchValues);
    }

    @Override
    public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
        SqlQueriesUtil.debugSQL(sql, batchArgs);
        return super.batchUpdate(sql, batchArgs);
    }

Then within these methods, we control whether we want to leave traces in the log based on an environment variable. With Spring, you can get it easily with the annotation @Value, but since our DAO is not in the context of Spring, we have to do it the traditional way. This variable will be called "setShowSql", which can be configured in the Java application execution in the Environment window. We'll also create a "setReplaceSqlParameters" property that allows us to decide if we want to see the original query or the values of the parameters replaced in the query.

This is a summary of what was done, but if you want to implement it in your case, you can see the source code attached and apply it to your specific situation.

File: ExampleJdbcDaoSupport.java:

package com.displaysql.util;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

/**
 * 
 * @author dportilla
 *
 */
public class ExampleJdbcDaoSupport {

    /** The simple jdbc insert. */
    private SimpleJdbcInsert simpleJdbcInsert;   
    /** The jdbc template. */
    private JdbcTemplate jdbcTemplate;
    /** The simple jdbc template. */
    private SimpleJdbcTemplate simpleJdbcTemplate;
    /** The named parameter jdbc template. */
    private WrapperNamedParameterJdbcTemplate namedParameterJdbcTemplate;
    /** Logger para trazar la informacion */
    private final Logger LOG = LoggerFactory.getLogger(getClass());

    /**
     * Sets the data source.
     *
     * @param dataSource the new data source
     */
    public void setDataSource(final DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
        simpleJdbcInsert = new SimpleJdbcInsert(dataSource);
        simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        namedParameterJdbcTemplate = new WrapperNamedParameterJdbcTemplate(dataSource);
    }

    /**
     * Gets the named parameter jdbc template.
     *
     * @return the named parameter jdbc template
     */
    public WrapperNamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    /**
     * Sets the named parameter jdbc template.
     *
     * @param namedParameterJdbcTemplate the new named parameter jdbc template
     */
    public void setNamedParameterJdbcTemplate(WrapperNamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }


    /**
     * Gets the simple jdbc insert.
     *
     * @return the simple jdbc insert
     */
    public SimpleJdbcInsert getSimpleJdbcInsert() {        
        return simpleJdbcInsert;
    }

    /**
     * Sets the simple jdbc insert.
     *
     * @param simpleJdbcInsert the new simple jdbc insert
     */
    public void setSimpleJdbcInsert(final SimpleJdbcInsert simpleJdbcInsert) {
        this.simpleJdbcInsert = simpleJdbcInsert;
    }

    /**
     * Gets the jdbc template.
     *
     * @return the jdbc template
     */
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    /**
     * Sets the jdbc template.
     *
     * @param jdbcTemplate the new jdbc template
     */
    public void setJdbcTemplate(final JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Gets the simple jdbc template.
     *
     * @return the simple jdbc template
     */
    public SimpleJdbcTemplate getSimpleJdbcTemplate() {
        return simpleJdbcTemplate;
    }

    /**
     * Sets the simple jdbc template.
     *
     * @param simpleJdbcTemplate the new simple jdbc template
     */
    public void setSimpleJdbcTemplate(final SimpleJdbcTemplate simpleJdbcTemplate) {
        this.simpleJdbcTemplate = simpleJdbcTemplate;
    }


}

File WrapperNamedParameterJdbcTemplate.java:

package com.displaysql.util;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class WrapperNamedParameterJdbcTemplate extends NamedParameterJdbcTemplate {

    public WrapperNamedParameterJdbcTemplate(DataSource dataSource) {
        super(dataSource);
        // TODO Auto-generated constructor stub
    }

    public WrapperNamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate) {
        super(classicJdbcTemplate);
        // TODO Auto-generated constructor stub
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#execute(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.core.PreparedStatementCallback)
     */
    @Override
    public <T> T execute(String sql, SqlParameterSource paramSource, PreparedStatementCallback<T> action)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.execute(sql, paramSource, action);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#execute(java.lang.String, java.util.Map, org.springframework.jdbc.core.PreparedStatementCallback)
     */
    @Override
    public <T> T execute(String sql, Map<String, ?> paramMap, PreparedStatementCallback<T> action)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.execute(sql, paramMap, action);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#execute(java.lang.String, org.springframework.jdbc.core.PreparedStatementCallback)
     */
    @Override
    public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql);
        return super.execute(sql, action);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.core.ResultSetExtractor)
     */
    @Override
    public <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.query(sql, paramSource, rse);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, java.util.Map, org.springframework.jdbc.core.ResultSetExtractor)
     */
    @Override
    public <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.query(sql, paramMap, rse);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, org.springframework.jdbc.core.ResultSetExtractor)
     */
    @Override
    public <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql);
        return super.query(sql, rse);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.core.RowCallbackHandler)
     */
    @Override
    public void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        super.query(sql, paramSource, rch);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, java.util.Map, org.springframework.jdbc.core.RowCallbackHandler)
     */
    @Override
    public void query(String sql, Map<String, ?> paramMap, RowCallbackHandler rch) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        super.query(sql, paramMap, rch);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, org.springframework.jdbc.core.RowCallbackHandler)
     */
    @Override
    public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql);
        super.query(sql, rch);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.core.RowMapper)
     */
    @Override
    public <T> List<T> query(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.query(sql, paramSource, rowMapper);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, java.util.Map, org.springframework.jdbc.core.RowMapper)
     */
    @Override
    public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.query(sql, paramMap, rowMapper);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query(java.lang.String, org.springframework.jdbc.core.RowMapper)
     */
    @Override
    public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql);
        return super.query(sql, rowMapper);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForObject(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.core.RowMapper)
     */
    @Override
    public <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForObject(sql, paramSource, rowMapper);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForObject(java.lang.String, java.util.Map, org.springframework.jdbc.core.RowMapper)
     */
    @Override
    public <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForObject(sql, paramMap, rowMapper);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForObject(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, java.lang.Class)
     */
    @Override
    public <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForObject(sql, paramSource, requiredType);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForObject(java.lang.String, java.util.Map, java.lang.Class)
     */
    @Override
    public <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForObject(sql, paramMap, requiredType);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForMap(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource)
     */
    @Override
    public Map<String, Object> queryForMap(String sql, SqlParameterSource paramSource) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForMap(sql, paramSource);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForMap(java.lang.String, java.util.Map)
     */
    @Override
    public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForMap(sql, paramMap);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForLong(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource)
     */
    @Override
    public long queryForLong(String sql, SqlParameterSource paramSource) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForLong(sql, paramSource);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForLong(java.lang.String, java.util.Map)
     */
    @Override
    public long queryForLong(String sql, Map<String, ?> paramMap) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForLong(sql, paramMap);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForInt(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource)
     */
    @Override
    public int queryForInt(String sql, SqlParameterSource paramSource) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForInt(sql, paramSource);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForInt(java.lang.String, java.util.Map)
     */
    @Override
    public int queryForInt(String sql, Map<String, ?> paramMap) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForInt(sql, paramMap);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForList(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, java.lang.Class)
     */
    @Override
    public <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForList(sql, paramSource, elementType);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForList(java.lang.String, java.util.Map, java.lang.Class)
     */
    @Override
    public <T> List<T> queryForList(String sql, Map<String, ?> paramMap, Class<T> elementType)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForList(sql, paramMap, elementType);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForList(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource)
     */
    @Override
    public List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForList(sql, paramSource);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForList(java.lang.String, java.util.Map)
     */
    @Override
    public List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForList(sql, paramMap);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForRowSet(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource)
     */
    @Override
    public SqlRowSet queryForRowSet(String sql, SqlParameterSource paramSource) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.queryForRowSet(sql, paramSource);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForRowSet(java.lang.String, java.util.Map)
     */
    @Override
    public SqlRowSet queryForRowSet(String sql, Map<String, ?> paramMap) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.queryForRowSet(sql, paramMap);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#update(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource)
     */
    @Override
    public int update(String sql, SqlParameterSource paramSource) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.update(sql, paramSource);
    }


    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#update(java.lang.String, java.util.Map)
     */
    @Override
    public int update(String sql, Map<String, ?> paramMap) throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramMap);
        return super.update(sql, paramMap);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#update(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.support.KeyHolder)
     */
    @Override
    public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.update(sql, paramSource, generatedKeyHolder);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#update(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.support.KeyHolder, java.lang.String[])
     */
    @Override
    public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames)
            throws DataAccessException {
        SqlQueriesUtil.debugSQL(sql, paramSource);
        return super.update(sql, paramSource, generatedKeyHolder, keyColumnNames);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#batchUpdate(java.lang.String, java.util.Map[])
     */
    @Override
    public int[] batchUpdate(String sql, Map<String, ?>[] batchValues) {
        SqlQueriesUtil.debugSQL(sql, batchValues);
        return super.batchUpdate(sql, batchValues);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#batchUpdate(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource[])
     */
    @Override
    public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
        SqlQueriesUtil.debugSQL(sql, batchArgs);
        return super.batchUpdate(sql, batchArgs);
    }

    /* (non-Javadoc)
     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#getPreparedStatementCreator(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource)
     */
//    @Override
//    protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
//        DaoUtil.debugSQL(sql, paramSource);
//        return super.getPreparedStatementCreator(sql, paramSource);
//    }
//
//    /* (non-Javadoc)
//     * @see org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#getParsedSql(java.lang.String)
//     */
//    @Override
//    protected ParsedSql getParsedSql(String sql) {
//        DaoUtil.debugSQL(sql);
//        return super.getParsedSql(sql);
//    }


}

File SqlQueriesUtil.java:

package util;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

/**
 * The Class DaoUtil.
 *  * @author dportilla
 */
public class SqlQueriesUtil {

    /** Logger to trace info */
    private final static Logger LOG = LoggerFactory.getLogger(SqlQueriesUtil.class);


    /**
    * @return the setShowSql
    */
   public static boolean isSetShowSql() {
       String variable = System.getenv("setShowSql");
       if ("true".equals(variable) ){
           return true;
       }
    return false;
   }

   public static boolean isSetReplaceSqlParameters() {
       String variable = System.getenv("setReplaceSqlParameters");
       if ("true".equals(variable) ){
           return true;
       }
    return false;
   }


   private static String formatParamValue(String name, Object theValue){

       if (theValue == null){
           return ("NULL");
       } else if (Date.class.isInstance(theValue)){
           final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
           final String fecha = "'".concat(df.format(theValue)).concat("'");
           return fecha;
       } else if (Timestamp.class.isInstance(theValue)){
           final DateFormat df = new SimpleDateFormat("yyyy-MM--dd HH:mm:ss");
           final String fechaHora= "'".concat(df.format(theValue)).concat("'");
           return fechaHora;
       } else if (ArrayList.class.isInstance(theValue)){
          final String lista = theValue.toString();
          final String nuevaLista = lista.replaceAll("\\[", "").replaceAll("\\]", "");           
          return nuevaLista;
       }  else {
           LOG.debug("Parameter type not implemented=".concat(name).concat(" TYPE=").concat(theValue.getClass().toString()));
       }

       return(theValue.toString());        

   }  


    /**
     * Display the query with parameters if setShowSql=true
     * @param sql
     * @param parameter
     */
    public static void debugSQL(String sql, SqlParameterSource parameter) {
        MapSqlParameterSource paramMap=null;
        BeanPropertySqlParameterSource paramBean=null;
        EmptySqlParameterSource paramEmpty=null;

        if (isSetShowSql()){

            if (parameter!=null && isSetReplaceSqlParameters()){
                try {
                    if (MapSqlParameterSource.class.isAssignableFrom(parameter.getClass())){                    
                        paramMap = MapSqlParameterSource.class.cast(parameter);
                        debugSQL(sql, paramMap);
                    } else if (BeanPropertySqlParameterSource.class.isAssignableFrom(parameter.getClass())){    
                        paramBean = BeanPropertySqlParameterSource.class.cast(parameter);
                        debugSQL(sql, paramBean);
                    } else if (EmptySqlParameterSource.class.isAssignableFrom(parameter.getClass())){    
                        debugSQL(sql, paramEmpty);
                    } else {
                        LOG.debug("The parameter don't have a indentified type");
                    }                        
                } catch (ClassCastException e){
                    LOG.error("Casting error with the parameter: ?", parameter);
                    paramMap=null;
                }
            } else {
                debugSQL(sql);
            }
        }
    }

    /**
     * Display the query with parameters if setShowSql=true
     * @param sql
     */
    public static void debugSQL(String sql) {
        if (isSetShowSql() && sql!=null){
            LOG.debug("SQL_ORIGINAL: \t".concat(sql));       
        }

    }


    /**
     * Display the query with parameters if setShowSql=true
     * @param sql
     * @param parameter
     */
    private static void debugSQL(String sql, BeanPropertySqlParameterSource parameter) {
        String newSql = sql;
        StringBuilder paramResult = new StringBuilder();
        String[] paramsArray = parameter.getReadablePropertyNames();
        // Mientras haya parámetros en la lista o en la SQL por reemplazar
        for (String paramName : paramsArray) {
            final String paramValue = parameter.getValue(paramName).toString();
            newSql = newSql.replaceAll(":".concat(paramName), paramValue);
            paramResult.append(paramName).append("=").append(paramValue).append(", ");
        }
//        LOG.debug("SQL_ORIGINAL:".concat(sql));
        LOG.debug("SQL_BEAN_REPLACED: \t".concat(newSql));
//        LOG.debug("PARAMS_BEAN:".concat(paramResult.toString()));

    }


    /**
     * Display the query with parameters if setShowSql=true
     * 
     * @param sql
     * @param parameter
     */
    private static void debugSQL(String sql, MapSqlParameterSource parameter) {
        String newSql = sql;
        Iterator<Entry<String, Object>> parametros = parameter.getValues().entrySet().iterator();

        while (parametros.hasNext() && newSql.lastIndexOf(":") != -1) {
            final Entry<String, Object> nuevoParam = parametros.next();
            final String paramName = nuevoParam.getKey();
            final Object valor = nuevoParam.getValue();
            final String paramValue = formatParamValue(paramName, valor);
            newSql = newSql.replaceAll(":".concat(paramName).concat(" "), paramValue.concat(" "));
            newSql = newSql.replaceAll(":".concat(paramName).concat(","), paramValue.concat(","));
            newSql = newSql.replaceAll(":".concat(paramName).concat("\\)"), paramValue.concat(")"));
        }
//        LOG.debug("SQL_ORIGINAL:".concat(sql));
        LOG.debug("SQL_MAP_REPLACED: \t".concat(newSql));
//        LOG.debug("PARAMS_MAP:".concat(Arrays.toString(parameter.getValues().entrySet().toArray())));
    }

    /**
     * Display the query with parameters if setShowSql=true
     * @param sql
     * @param parameter
     */
    private static void debugSQL(String sql, EmptySqlParameterSource parameter) {    
        if (isSetShowSql()){
            LOG.debug("SQL_NO_PARAMETER: \t".concat(sql));
        }
    }

    /**
     * Display the query with parameters if setShowSql=true
     * @param sql
     * @param paramMap
     */
    public static void debugSQL(String sql, Map<String, ?> paramMap) {
        if (isSetShowSql()){
            LOG.debug("SQL_ORIGINAL: \t".concat(sql));
            if (paramMap!=null && !paramMap.isEmpty()){
                LOG.debug("PARAMS: \t".concat(Arrays.toString(paramMap.entrySet().toArray())));
            }
        }        
    }

    /**
     * Display the query with parameters if setShowSql=true
     * @param sql
     */
    public static void debugSQL(String[] listaSql) {
        for (int i = 0; i < listaSql.length; i++) {
            debugSQL(listaSql[i]);            
        }        
    }

    public static void debugSQL(String sql, Map<String, ?>[] batchValues) {
        if (isSetShowSql()){
            LOG.debug("SQL_ORIGINAL: \t".concat(sql));
            if (batchValues!=null && !(batchValues.length > 0)){
                LOG.debug("PARAMS: \t".concat(batchValues.toString()));
            }
        }
    }

    public static void debugSQL(String sql, SqlParameterSource[] batchArgs) {
        // TODO Auto-generated method stub

    }

}