0

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);
  }
}
Community
  • 1
  • 1
entpnerd
  • 10,049
  • 8
  • 47
  • 68
  • 1
    Did you try a count(*) on the same request ? just to make sure that the request/parameters are valid and returning a result ? – VirtualTroll Feb 22 '16 at 19:50
  • 1
    You might be missing white space before the `from` and `where`, depending on how JDBCTemplate interprets `\n`. Are you sure that no exceptions are being thrown? – Mick Mnemonic Feb 22 '16 at 19:56
  • Not sure what GUI you are using. in eclipse you can debug to get the SQL statement and the parameters that are being sent. Also can you add space before from and where clause. Also try adding Try catch blocks – user3509208 Feb 22 '16 at 20:01
  • @VirtualTroll, thanks for the tip. Unfortunately, count(1) also returns 0 rows. But it shouldn't. – entpnerd Feb 22 '16 at 20:01
  • @MickMnemonic, I think that it is interpreting whitespace correctly because no errors are occurring, just getting back no data. – entpnerd Feb 22 '16 at 20:02
  • What kind of values for `strSearchDateSupremum` or `strSearchDateInfimum` are you passing in? Check that you haven't flipped these around. – Mick Mnemonic Feb 22 '16 at 20:05
  • @MickMnemonic, I checked that and it should be fine. But hence the reason part of the reason why I'm trying to figure out what the actual string is. – entpnerd Feb 22 '16 at 20:33
  • hint: if the query is correct and the result is not what you expect, then maybe the data (in the database) is different from what you believe they is – Ralph Feb 22 '16 at 20:40

1 Answers1

1

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

achabahe
  • 2,445
  • 1
  • 12
  • 21