JDBC - way to print prepared statement SQL?

[smiles78]

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?

[trisberg]

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

Set this in your log4j.properties

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

[venercogo]

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?

[venercogo]

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.

[qqura1]

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

Set this in your log4j.properties

log4j.logger.org.springframework.jdbc.core=DEBUG

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

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:

		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.

[qqura1]

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

[wilkystorm]

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.

[anuraggupta.mca@gmail.com]

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

[vipnat]

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());