CODEWITHSUNDEEP
javaspringspring-jdbcjdbctemplate
Jon Thoms
Jon Thoms

Reputation: 10739

How do I get back a PreparedStatement query string when using Spring's JDBCTemplate?

I'm using Spring's JdbcTemplate (Spring version 4.1.4). With my class' JdbcTemplate instance, I'm doing a select query as a prepared statement. However, my query unexpectedly returns no results. As such, I really need to debug my query and make sure that the query is what I expect it ought to be.

How can I get back the actual SQL that is executed against the DB as part of the JdbcTemplate's internal PreparedStatement?

I'm familiar with the using the PreparedStatement's toString() method to accomplish this, but as JDBCTemplate uses PreparedStatement internally, I'm not sure how feasible it is using Spring.

Some sample code I am using is as follows:

private static final String PREPARED_QUERY =
    "select\n" +
        "  spm.amount\n" +
        "from\n" +
        "  search_price_modifier spm\n" +
        "where\n" +
        "  spm.search_id = ?\n" +
        "  and spm.search_date > to_date(?, 'MM-DD-YYYY HH24:MI:SS')\n" +
        "  and spm.search_date < to_date(?, 'MM-DD-YYYY HH24:MI:SS')\n";

public void runQuery(String searchId, String strSearchDateInfimum,
    String strSearchDateSupremum) {

  SqlRowSet amounts = this.jdbcTemplate.queryForRowSet(
      PREPARED_QUERY_FOR_FLAT_MARKUP_VERIFICATION,
      searchId, strSearchDateInfimum, strSearchDateSupremum);
  while (amounts.next()) {
    float amount = amounts.getFloat("AMOUNT");
    LOGGER.debug("amount=" + amount);
  }
}

Upvotes: 0

Views: 1304

Answers (1)

achabahe
achabahe

Reputation: 2565

i will give a general way to debug all your statement and see them in console when ever you want because spring does it by default add to your classpath

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

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration >
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
        </Console>
    </Appenders>
    <Loggers>
        <Root level="error">
            <AppenderRef ref="Console" />
        </Root>
        <Logger level="debug" name="org.springframework.jdbc">
            <AppenderRef ref="Console" />
        </Logger>
    </Loggers>
</configuration>

add to your lib log4j-api-2.5.jar log4j-core-2.5.jar log4j-jcl.2.5.jar and you are now ready to go .

switch OFF Debugging change level 

        <Logger level="error" name="org.springframework.jdbc">
            <AppenderRef ref="Console" />
        </Logger>

now spring won't debug ,but it will print only errors

Upvotes: 1

Related Questions