Announcement Announcement Module
Collapse
No announcement yet.
JDBC - way to print prepared statement SQL? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC - way to print prepared statement SQL?

    I'm using Spring JDBC and the prepared statements and was wondering if there was a way to print the exact SQL and the parameter values.

    If I do
    getSimpleJdbcTemplate().update("INSERT INTO TABLE myTable VALUES (?,?,?,?)" , "a", "b", "c", "d");

    Is there a way that I can see
    INSERT INTO TABLE myTable VALUES ('a','b','c','d')
    Or even a message saying that it set parameter 1 to 'a' and parameter 2 to 'b' and so on?

  • #2
    If you turn on debug logging for the jdbc code you will see this informtion.

    Set this in your log4j.properties
    Code:
    log4j.logger.org.springframework.jdbc.core=DEBUG
    and you should see messages like:

    2008-04-11 16:36:51,125 DEBUG [JdbcTemplate] - Executing prepared SQL update
    2008-04-11 16:36:51,126 DEBUG [JdbcTemplate] - Executing prepared SQL statement [insert into product (id, description, price) values (?, ?, ?)]
    2008-04-11 16:36:51,128 DEBUG [StatementCreatorUtils] - Setting SQL statement parameter value: column index 1, parameter value [22], value class [java.lang.Long], SQL type unknown
    2008-04-11 16:36:51,129 DEBUG [StatementCreatorUtils] - Setting SQL statement parameter value: column index 2, parameter value [Spring Book], value class [java.lang.String], SQL type unknown
    2008-04-11 16:36:51,129 DEBUG [StatementCreatorUtils] - Setting SQL statement parameter value: column index 3, parameter value [42.95], value class [java.math.BigDecimal], SQL type unknown
    2008-04-11 16:36:51,136 DEBUG [JdbcTemplate] - SQL update affected 1 rows

    Comment


    • #3
      I can't get it to work as you describe. I have a class that extends JdbcDaoSupport and I call getJdbcTemplate().query(sql, new Object[] {argument}, new RowMapper() {....}) to perform my query.

      My log4j.xml looks as follows:

      <appender name="consoleAppender" class="org.apache.log4j.ConsoleAppender">
      <param name="Target" value="System.out" />
      <param name="Threshold" value="INFO" />
      <layout class="org.apache.log4j.PatternLayout">
      <!-- The default pattern: Date Priority [Category] Message\n -->
      <param name="ConversionPattern" value="%d %-5p [%c{1}] %m%n" />
      </layout>
      </appender>

      <appender name="rollingFileAppender" class="org.apache.log4j.DailyRollingFileAppender">
      <param name="File" value="TestData/completer.log" />
      <param name="Append" value="true" />
      <param name="DatePattern" value="'.'yyyy-MM-dd" />
      <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d %-5p [%c] %m%n" />
      </layout>
      </appender>

      <logger name="org.springframework">
      <level value="DEBUG" />
      <appender-ref ref="rollingFileAppender" />
      <appender-ref ref="consoleAppender" />
      </logger>

      <logger name="org.springframework.jdbc.core">
      <level value="DEBUG" />
      <appender-ref ref="rollingFileAppender" />
      <appender-ref ref="consoleAppender" />
      </logger>


      Other (Spring) items are getting logged, for example loading the configuration file gets printed to the console:
      2009-04-16 09:50:23,133 INFO [FileSystemXmlApplicationContext] Refreshing org.springframework.context.support.FileSystemXmlA pplicationContext@8814e9: display name [org.springframework.context.support.FileSystemXmlA pplicationContext@8814e9]; startup date [Thu Apr 16 09:50:23 CEST 2009]; root of context hierarchy

      But I'm not seeing the SQL statements that get executed. Any pointers?

      Comment


      • #4
        Ok, sorry about that, just found the problem, I set the "Threshold" parameter of the consoleAppender to "INFO" causing my SQL statements to get filtered out.

        Comment


        • #5
          JDBC - print simplejdbctemplate sql &amp; parameters

          [QUOTE=trisberg;174409]If you turn on debug logging for the jdbc code you will see this informtion.

          Set this in your log4j.properties
          Code:
          log4j.logger.org.springframework.jdbc.core=DEBUG

          I have tried few options but it just prints the SQL only (not the parameters).
          log4j.properties

          Code:
          log4j.appender.stdout=org.apache.log4j.ConsoleAppender
          log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
          log4j.appender.stdout.layout.ConversionPattern=[%-5t] %-5p %c - %m%n
          log4j.appender.stdout.threshold=DEBUG
          
          log4j.logger.org.springframework.jdbc.core=DEBUG
          log4j.logger.org.springframework.jdbc.core.StatementCreatorUtils=DEBUG
          Spring code:
          Code:
          		String sql = "SELECT * FROM table1 WHERE code= :_act";
          		Map<String, Object> params = new HashMap<String, Object>();
          		parameters.put("_act", actCode);
          		List rows = getSimpleJdbcTemplate().queryForList(sql, params);
                          ...
          Output:

          [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [SELECT * FROM table1 WHERE code= ?]

          Let me know if I miss anything.

          Thanks.
          Last edited by qqura1; Feb 15th, 2010, 03:04 PM.

          Comment


          • #6
            Spring 2.5.x print the SQL &amp; parameters

            In Spring 2.5.x the parameters are logged with trace level.


            log4j.logger.org.springframework.jdbc.core.JdbcTem plate=DEBUG, file
            log4j.logger.org.springframework.jdbc.core.Stateme ntCreatorUtils=TRACE, file

            Comment


            • #7
              can you get the sql statement with param values in it

              hello all,

              I love Spring. I think it is phenomenal.
              I am curious though, with the spring.core log level set to TRACE it does print out the param values after it prints the statement showing you what the values are but it would be more useful for troubleshooting the query, performance, result set etc if it would print the actual sql statement with the param values inserted into the statement. Is there any way to do that?

              Thanks very much in advance.

              Comment


              • #9
                Value: a = Avalue;b = Bvalue;c = Cvalue;

                select * from table where a= ? b= ? c= ?;

                List<Object> listParams = new ArrayList<Object>();
                listParams.add(a);
                listParams.add(b);
                listParams.add(c);

                PreparedStatement query = getJdbcTemplate().getDataSource().getConnection(). prepareStatement(sql);
                // Get params
                for (int i = 0; i < listParams.size(); i++) {
                query.setObject(i+1,listParams.get(i));
                }

                System.out.println(query.toString());

                >>> select * from table where a= Avalue b= Bvalue c= Cvalue ;

                getSimpleJdbcTemplate().queryForList(sql, listParams.toArray());
                getSimpleJdbcTemplate().queryForInt(sql, listParams.toArray());
                getSimpleJdbcTemplate().queryForObject(sql, listParams.toArray());

                Comment

                Working...
                X