Debugging SQL Statements

Debugging SQL Statements

book

Article ID: KB0072722

calendar_today

Updated On:

Products

TIBCO Fulfillment Order Management

Description

How to debug SQL statements?

Issue/Introduction

Debugging SQL Statements

Resolution

In FOM we use 2 different third parties to access the database.
1) Hibernate
2) Spring JDBC

We can create new logs in OMSServerLog4j.xml to print query information. Please find attached OMSServerLog4j.xml for reference.
In Appenders section:
 <RollingFile fileName="logs/springJdbc.log" filePattern="logs/springJdbc-%i.log" name="springJdbcFileAppender">
            <PatternLayout pattern="%d{dd MMM yyyy HH:mm:ss,SSS} %5p [%X{businessTransactionId}] [%X{orderRef}] [%X{engineName}] [%c] [%X{operation}] - %m %X{stackTrace}%n" />
            <Policies>
                <SizeBasedTriggeringPolicy size="5MB" />
            </Policies>
            <DefaultRolloverStrategy max="20" />
 </RollingFile>
 <RollingFile fileName="logs/hibernate.log" filePattern="logs/hibernate-%i.log" name="HibernateLogFileAppender">
            <PatternLayout pattern="%d{dd MMM yyyy HH:mm:ss,SSS} %5p [%X{businessTransactionId}] [%X{orderRef}] [%X{engineName}] [%c] [%X{operation}] - %m %X{stackTrace}%n" />
            <Policies>
                <SizeBasedTriggeringPolicy size="5MB" />
            </Policies>
            <DefaultRolloverStrategy max="20" />
 </RollingFile>

In Loggers section:
 <Logger level="DEBUG" name="org.hibernate.SQL" additivity="false">
            <appender-ref ref="HibernateLogFileAppender" />
 </Logger>
 <Logger level="TRACE" name="org.hibernate.type" additivity="false">
            <appender-ref ref="HibernateLogFileAppender" />
 </Logger>
 <Logger level="DEBUG" name="org.springframework.jdbc.core.JdbcTemplate" additivity="false">
            <appender-ref ref="springJdbcFileAppender" />
 </Logger>
 <Logger level="TRACE" name="org.springframework.jdbc.core.StatementCreatorUtils" additivity="false">
            <appender-ref ref="springJdbcFileAppender" />
 </Logger>

PFA also hibernate.log and springJdbc.log files that are sample logs generated using above configuration.
 

Additional Information

https://dzone.com/articles/display-sqls-to-console-in-spring-jdbctemplate
https://stackoverflow.com/questions/1710476/how-to-print-a-query-string-with-parameter-values-when-using-hibernate

Attachments

Debugging SQL Statements get_app
Debugging SQL Statements get_app
Debugging SQL Statements get_app