0

how could I log the sql query (with the actual parameters) in java + Spring.

My dev environment

  1. Java
  2. Spring JdbcTemplate.

Let me elaborate the question.

My pseudo code is like this. 

String sql = "SELECT COLUMN1 FROM MYTABLE WHERE COLUMN2=? "
List<MyVO> MyVOList = getJdbcTemplate().query(sql, new Object[] { date }, 
                           new RowMapper...

Now if I wanted to log the sql - with the actual date that was sent to it - I dont know how to do it.

Option 1 : I can always log the SQL and log the parameters seperately, but I want to have the actual SQL in the logs so the support staff could just copy paste the SQL and run it from a SQL client if need be.

Option 2 : I can think of doing a string substituition and creating the SQL in java and then logging it. However, that always leaves the possibility that because of some freak coding error the actual SQL and the logged SQL are different.

Has someone tried to solve this issue and made any progress?

Please share. Thanks.

Note: I have already read the post Log SQL that is communicated with the database. Using this solution is not an option for me. I am working within an enterprise setup and jdbcdslog-exp is not on the approved list of softwares.

Community
  • 1
  • 1
partha
  • 2,286
  • 5
  • 27
  • 37
  • See here http://stackoverflow.com/questions/15425377/how-to-show-sql-parameters-in-hibernate-log – Chowdappa Nov 21 '13 at 08:48
  • I thought I had clarified that I was not using Hibernate @CHowdappaM – partha Nov 21 '13 at 08:50
  • So instead of using a ready to use framework you need to hack together a solution yourself. Convince them otherwise, saves you time and frustration. – M. Deinum Nov 21 '13 at 08:51
  • Well - at the moment I am just checking with this forum to see if there is an easy solution - that works within my constraints. If it comes to having to hack my own solution I will go back and say no. But before I do that I just want to do my due diligence. – partha Nov 21 '13 at 09:05

2 Answers2

0

Logging SQL queries with Spring

artplastika
  • 1,972
  • 2
  • 19
  • 38
  • Thanks, this is good. But this still does not solve my original problem i.e. log the sql complete with the value substituitions so that the support folks - if needed could just copy the SQL from logs and run that on a SQL client without needing to do any transformation. – partha Nov 21 '13 at 08:58
  • @partha Try aspect-oriented approach then: http://www.gotoquiz.com/web-coding/programming/java-programming/log-sql-statements-with-parameter-values-filled-in-spring-jdbc/ – artplastika Nov 21 '13 at 09:52
0

JdbcTemplate.query() method finally calls execute method. Below is the execute method in JdbcTemplete spring class. Extend this class and log for this.val$sql this may give what you need.

public void execute(String sql) throws DataAccessException {
    if (this.logger.isDebugEnabled()) {
        this.logger.debug("Executing SQL statement [" + sql + "]");
    }

    execute(new StatementCallback(sql) {
        public Object doInStatement(Statement stmt) throws SQLException {
            stmt.execute(this.val$sql);
            return null;
        }

        public String getSql() {
            return this.val$sql;
        }
    });
}
Foolish
  • 3,952
  • 33
  • 46