I am learning about the wonders of JdbcTemplate and NamedParameterJdbcTemplate. I like what I see, but is there any easy way to see the underlying SQL that it ends up executing? I'd like to see this for debug purposes (in order to for example debug the resulting SQL in an outside tool).
7 Answers
The Spring documentation says they're logged at DEBUG level:
All SQL issued by this class is logged at the DEBUG level under the category corresponding to the fully qualified class name of the template instance (typically JdbcTemplate, but it may be different if you are using a custom subclass of the JdbcTemplate class).
In XML terms, you need to configure the logger something like:
<category name="org.springframework.jdbc.core.JdbcTemplate">
<priority value="debug" />
</category>
This subject was however discussed here a month ago and it seems not as easy to get to work as in Hibernate and/or it didn't return the expected information: Spring JDBC is not logging SQL with log4j This topic under each suggests to use P6Spy which can also be integrated in Spring according this article.
I use this line for Spring Boot applications:
logging.level.org.springframework.jdbc.core = TRACE
This approach pretty universal and I usually use it for any other classes inside my application.
Try adding in log4j.xml
<!-- enable query logging -->
<category name="org.springframework.jdbc.core.JdbcTemplate">
<priority value="DEBUG" />
</category>
<!-- enable query logging for SQL statement parameter value -->
<category name="org.springframework.jdbc.core.StatementCreatorUtils">
<priority value="TRACE" />
</category>
your logs looks like:
DEBUG JdbcTemplate:682 - Executing prepared SQL query
DEBUG JdbcTemplate:616 - Executing prepared SQL statement [your sql query]
TRACE StatementCreatorUtils:228 - Setting SQL statement parameter value: column index 1, parameter value [param], value class [java.lang.String], SQL type unknown
One can do this without any xml or yml config , but as simple as only pure java code ,like below:
import org.slf4j.LoggerFactory;
import ch.qos.logback.classic.Level;
import ch.qos.logback.classic.Logger;
Logger logger = (Logger) LoggerFactory.getLogger("org.springframework.jdbc.core.JdbcTemplate");
logger.setLevel(Level.valueOf("DEBUG"));
// Or ,for more datasource details,you can retrieve another logger like this
// Logger logger2 = (Logger) LoggerFactory.getLogger("org.springframework.jdbc");
//logger2.setLevel(Level.valueOf("DEBUG"));
Enjoy it, guys, see more from here https://www.bswen.com/2019/05/springboot-How-to-print-JPA-and-jdbcTemplate-SQL-logs.html
Related