When we develop any application, after we finish it or when we end up any of its module, we start optimization process. Most applications contain database access, and if you are using an ORM, probably you will use hibernate. Optimizing hibernate persistence layer, requires to be prepared to read, understand and evaluate sql.

If we take an overview on hibernate configuration, two properties, hibernate.format_sql and hibernate.use_sql_comments, should be enabled to print performed sql code through console.

This is a good start but it seems that we need more information to make an accurate diagnosis of performance like connection events, returned data on queries, or parameters binding (hibernate shows parameters values with question mark ?) . Hence, we need another way to inspect generated sql. Log4jdbc is a jdbc driver that can log sql/jdbc calls. In fact log4jdbc is an implementation of proxy pattern which will automatically load popular jdbc drivers (Oracle, Derby, MySql, PostgreSql, H2, Hsqldb, ...), intercept calls, log information, and then send data to "spied" driver.

 

Read Full Post

Have you read the hibernate guide?

 

All you have to do is set the log level for org.hibernate.type to the right level.

 

log4j.logger.org.hibernate.type=trace

 

 

yes of course you could use hibernate in mode trace, but from my point of view log4jdbc offers a better way of showing data, that helps you analyze queries, moreover you can configure exactly which information are you interested on.

No I don't use Hibernate. Obviously you could have said that there are many implementations of JPA, and they all have settings for enabling logging of datastore operations. Some also show the parameter values passed in to SQL statements too.

hey, wheres the "I like it" button? This is a very useful tool. I used to always keep a copy of a similar tool (dont remember the name) on my USB stick, just in case.

I use p6spy. It does not care whether you use a logging framework or not.

Hi, we stated using , and it will help a lot. 

Thanks,

Burak

For all those who say that Hibernate gives you the same functionality - just try Log4jdbc. It gives you much better form of logged SQL (parameters are in place - for debugging you need to directly paste query from logs to your DB console; only drawback - I did not find option to get cute SQL formatting with log4jdbc) and gives you nice logs for profiling purposes (logs execution time of every query).

Log4jdbc variant (log4jdbc-remix) gives you also possiblility of logging result set output in tabular format - great feature ! Makes reading these query results in log files a breeze.

My suggestions for future enhancement:

# formatting SQL queries (like Hibernate does)

# allow enabling logging resultset without need to log e.g. audit information (in other words, it seems that loggers work incrementally - it would be better to be able to disable lower level logs - audit, sqlonly etc - but still have higher levels enabled)

# merge with remix branch.

There is also another variant, providing the same functionalities as log4jdbc-remix, but also allowing to dispatch different query types (select, update, ...) in different files (using Log4j2 and its marker functionality). This is much useful. (log4jdbc-log4j2: http://code.google.com/p/log4jdbc-log4j2/)