Spring/hibernate improved SQL Logging with LOG4JDBC---reference

Hibernate provides SQL logging out of the box, but such logging only shows prepared statements, and not the actual SQL que Ries sent to the database.

It also does not log the execution time of each query, which are useful for performance troubleshooting. This blog post would go over how to setup Hibernate query logging, and then compare it to the logging so can be obtained With LOG4JDBC.

The Hibernate query logging functionality

Hibernate does not log the real SQL queries sent to the database. This was because Hibernate interacts with the database via the JDBC driver, to which it sends prepared statements and not t He actual queries.

So Hibernate can only log the prepared statements and the values of their binding parameters, but not the actual SQL Queri Es themselves.

This is what a query looks like when logged by Hibernate:

1. select  /*  load  your.package.Employee */ this_.code, ... 2. from  employee this_ 3. where  this_.employee_id=? 4.  5. TRACE [email protected]:06:02  BasicBinder - binding parameter [1]  as  [NUMBER] - 1000

See this post Hibernate debugging-finding The origin of a in a Query for what to setup this type of logging.

Using LOG4JDBC

For a developer it's useful to being able to copy paste a query from the log and being able to execute the query directly in an SQL client, but the variable placeholders make that ? unfeasible.

The LOG4JDBC in an open source tool is allows to does just that, and more. Log4jdbc is a spy driver that'll wrap itself around the real JDBC driver, logging queries as they go through it.

The version linked from this post provides Spring integration, unlike several other log4jdbc forks.

Setting up Log4jdbc

First include the Log4jdbc-remix library in your pom.xml. This library is a fork of the original log4jdbc:

1. < dependency > 2. < groupId >org.lazyluke</ groupId > 3. < artifactId >log4jdbc-remix</ artifactId 4. <version>0.2.7</ version > 5. </ dependency >

Next, find in the Spring configuration, the definition of the data source. As an example, when using the JNDI lookup element, the-the data source looks like:

1. < jee:jndi-lookup  id = "dataSource" 2. jndi-name = "java:comp/env/jdbc/some-db"  />

After finding the data source definition, rename it to the following name:

1. < jee:jndi-lookup  id = "originalDataSource" 2. jndi-name = "java:comp/env/jdbc/some-db"  />

Then define a new LOG4JDBC data source that wraps the real data source, and give it the original name:

01. < bean  id = "dataSource"  class = "net.sf.log4jdbc.Log4jdbcProxyDataSource" >         02. < constructor-arg  ref = "originalDataSource"  />         03. < property  name = "logFormatter" >                    04. < bean  class = "net.sf.log4jdbc.tools.Log4JdbcCustomFormatter"  > 05. < property  name = "loggingType"  value = "SINGLE_LINE"  /> 06. < property  name = "margin"  value = "19"  />   07. < property  name = "sqlPrefix"  value = "SQL:::"  />            08. </ bean >            09. </ property >     10. </ bean >

With this configuration, the query logging should already is working. It ' s possible to customize the logging level of the several LOG4JDBC loggers available.

The original LOG4JDBC documentation provides more information on the available loggers:

• jdbc.sqlonly: Logs only SQL
• jdbc.sqltiming: Logs the SQL, post-execution, including timing execution statistics
• jdbc.audit: Logs all JDBC calls except for resultsets
• jdbc.resultset: All calls to ResultSet objects is logged
• jdbc.connection: Logs connection open and close events

jdbc.auditThe logger is especially useful to validate the scope of transactions, as it logs the Begin/commit/rollback events O f a database transaction.

The proposed log4j configuration that would print only the SQL queries together with their execution time:

01. < logger  name = "jdbc.sqltiming"  additivity  = "false" >             02. < level  value = "info"  />                03. </ logger >  04. < logger  name = "jdbc.resultset"  additivity  = "false" >              05. < level  value = "error"  />        06. </ logger >  07. < logger  name = "jdbc.audit"  additivity  = "false" > 08. < level  value = "error"  />        09. </ logger >   10. < logger  name = "jdbc.sqlonly"  additivity  = "false" >              11. < level  value = "error"  />        12. </ logger >   13. < logger  name = "jdbc.resultsettable"  additivity  = "false" >           14. < level  value = "error"  />       15. </ logger >           16. < logger  name = "jdbc.connection"  additivity  = "false" >              17. < level  value = "error"  />        18. </ logger >  19. < logger  name = "jdbc.resultsettable"  additivity  = "false" >            20. < level  value = "error"  />        21. </ logger >Conclusion

Using LOG4JDBC does imply some initial setup, but once it's in place it's really convenient to has. Have a true query log is also useful for performance troubleshooting (to being described in a future post).

Reference From:http://java.dzone.com/articles/springhibernate-improved-sql