JDBC

amy

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(quot;INSERT INTO TABLE myTable VALUES (?,?,?,?)quot; , quot;aquot;, quot;bquot;, quot;cquot;, quot;dquot;);

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?

..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
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:

lt;appender name=quot;consoleAppenderquot; class=quot;org.apache.log4j.ConsoleAppenderquot;gt;
lt;param name=quot;Targetquot; value=quot;System.outquot; /gt;
lt;param name=quot;Thresholdquot; value=quot;INFOquot; /gt;
lt;layout class=quot;org.apache.log4j.PatternLayoutquot;gt;
lt;!-- The default pattern: Date Priority [Category] Message\n --gt;
lt;param name=quot;ConversionPatternquot; value=quot;%d %-5p [%c{1}] %m%nquot; /gt;
lt;/layoutgt;
lt;/appendergt;

lt;appender name=quot;rollingFileAppenderquot; class=quot;org.apache.log4j.DailyRollingFileAppenderquot;gt;
lt;param name=quot;Filequot; value=quot;TestData/completer.logquot; /gt;
lt;param name=quot;Appendquot; value=quot;truequot; /gt;
lt;param name=quot;DatePatternquot; value=quot;'.'yyyy-MM-ddquot; /gt;
lt;layout class=quot;org.apache.log4j.PatternLayoutquot;gt;
lt;param name=quot;ConversionPatternquot; value=quot;%d %-5p [%c] %m%nquot; /gt;
lt;/layoutgt;
lt;/appendergt;

lt;logger name=quot;org.quot;gt;
lt;level value=quot;DEBUGquot; /gt;
lt;appender-ref ref=quot;rollingFileAppenderquot; /gt;
lt;appender-ref ref=quot;consoleAppenderquot; /gt;
lt;/loggergt;

lt;logger name=quot;org..jdbc.corequot;gt;
lt;level value=quot;DEBUGquot; /gt;
lt;appender-ref ref=quot;rollingFileAppenderquot; /gt;
lt;appender-ref ref=quot;consoleAppenderquot; /gt;
lt;/loggergt;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..context.support.FileSystemXmlA  pplicationContext@8814e9: display name [org..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?

Ok, sorry about that, just found the problem, I set the quot;Thresholdquot; parameter of the consoleAppender to quot;INFOquot; causing my SQL statements to get filtered out.

..jdbc.core=DEBUG

I have tried few options but it just prints the SQL only (not the parameters).
log4j.propertiesCode:
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..jdbc.core=DEBUG
log4j.logger.org..jdbc.core.StatementCreatorUtils=DEBUG
Spring code:

Code:
String sql = quot;SELECT * FROM table1 WHERE code= :_actquot;;
Maplt;String, Objectgt; params = new HashMaplt;String, Objectgt;();
parameters.put(quot;_actquot;, actCode);
List rows = getSimpleJdbcTemplate().queryForList(sql, params);    ...
Output:

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

Let me know if I miss anything.

Thanks.

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

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.

I am also looking for the same;Is there any way to get the ready query with values from jdbctemplate?