Debugging SQL Queries with Spring JDBC

Question

I'm working with the Spring Framework, and I'm following a test driven development. I'm getting an exception but I'm not entirely sure why so I'd like to see what the query jdbc is actually running. The attempted query is the following:

public OrderEntity addOrderEntity(OrderEntity orderEntity) {
    String query = "INSERT INTO ORDERS(ID,REVISION,CONTRACT_ID,PROJECT_ID,WORKSITE_ID,DROPZONE_ID,DESCRIPTION_ID,MANAGER_ID,DELIVERY_DATE,VOLUME) VALUES(?,?,?,?,?,?,?,?,?,?)";
    String id = (orderEntity.get_id() != null) ? orderEntity.get_id() : UUID.randomUUID().toString();
    jdbcTemplate.update(id,1,orderEntity.getContractNo(),orderEntity.getProjectID(),orderEntity.getWorksiteID(),orderEntity.getDropzoneID(),orderEntity.getDescriptionID(),orderEntity.getManagerID(),orderEntity.getDeliveryDate(),orderEntity.getVolume());

    return getOrderEntityById(id);
}

So, what's the best way to see what the query JDBC is running or get some useful information? It currently throws org/springframework/dao/QueryTimeoutException (which I find infinitely unhelpful) so I don't really know what could be going wrong.

EDIT: Have now added log4j but still not getting a useful query. Property file is below:

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=[%-5t] %-5p %c - %m%n
log4j.rootLogger=trace, stdout

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

And that isn't helpful because? Your Query takes too long to complete. Check if there aren't any locks on your database. — M. Deinum
– M. Deinum, Commented Mar 11, 2014 at 11:47
I've found it throws this no matter what the problem was - foreign key constraint failures caused this as well. — DrugCrazed
– DrugCrazed, Commented Mar 11, 2014 at 13:00

Laabidi Raissi · Accepted Answer · 2014-03-12 12:25:21Z

8

You can enable tracing of the queries using:

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

Especially

log4j.logger.org.springframework.jdbc.core.StatementCreatorUtils=TRACE

this will show messages like this:

 TRACE StatementCreatorUtils:206 - Setting SQL statement parameter value: column index 4, parameter value [TheValueWillBeHere]

edited Mar 12, 2014 at 12:25

answered Mar 11, 2014 at 12:39

Laabidi Raissi

3,3411 gold badge25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DrugCrazed Over a year ago

Unless I've missed a step, it didn't tell me what the actual query it ran was - it gives something like INSERT INTO PROJECTS (ID,REVISION,PROJECT_NAME) VALUES(?,?,?) instead of something like INSERT INTO PROJECTS (ID,REVISION,PROJECT_NAME) VALUES("proj1",1,"project")

DrugCrazed Over a year ago

(it also didn't help with this actual query, it hasn't even appeared in my log)

Laabidi Raissi Over a year ago

as I mentioned in my answer, the log to show params is TRACE.

Laabidi Raissi Over a year ago

Especially for StatementCreatorUtils which will show you the query params

Collectives™ on Stack Overflow

Debugging SQL Queries with Spring JDBC

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related