Logging SQL queries with parameters in Log4j

Akumadevil · **Joined:** Mon Apr 16, 2007 10:44 am **Posts:** 9

Hi,

Is it possible to log the SQL hibernate executes with Log4j (with parameters)?

I would like to gauge the effectiveness of second level caching by looking at when certian SQL statements are being executed.

Cheers.

himawan · **Joined:** Sat Aug 19, 2006 6:31 pm **Posts:** 139

Yeah.. use this in hibernate.cfg.xml

Code:

<property name="show_sql">true</property>
<property name="format_sql">true</property>

Akumadevil · **Joined:** Mon Apr 16, 2007 10:44 am **Posts:** 9

This method displays question marks instead of parameters:

... WHERE age = ?

Ideally I would like the following to be displayed:

... WHERE age = 21

dharmendra.pandey · **Posted:** Sun May 13, 2007 11:18 pm

Hi Akumadevil,

That’s the problem I agree. In one of my project, I was working with SQL Tuning and performance optimization. I found lot of problem in extracting Query and sending it to DBA . It was very complex JOB for me. If there is really any solution Plzz.. share it with me.

pramodkp · **Joined:** Mon Mar 26, 2007 12:38 am **Posts:** 119

Hi,

We might not get a query that can be directly fired at prompt.

Reason:
If I understand it correctly, the operations that take place are, ( done by Hibernate )
i) set the values into host bind variables.
ii) fire the query as-is with bind variables to underlying database.

A query of the form,
"select * from my_table where age = 21"
is never formed. ( assume 21 is the bind value )
What gets fired is
"select * from my_table where age = ?"
with bind variables holding the value 21.

Correct me if I am wrong.

However, to know the values of bind variables, you can use,
log4j.logger.org.hibernate.type=debug

------------------------------------------------------------------
Rate the reply if you find it helpful

dharmendra.pandey · **Posted:** Mon May 14, 2007 12:29 am

Hi pramodkp,

Are you talking to do it manually? In any application there are thousand of update insert statements .So if average one table contain 20 field and if 100 tables are there then may be binding value would be 20*100*1000 assuming in worst case. Is that possible to give report to DBA about that much Query.

jaikiran · **Joined:** Wed Jul 27, 2005 2:33 am **Posts:** 118

This was discussed, some time back at http://forum.hibernate.org/viewtopic.php?t=965749&postdays=0&postorder=asc&highlight=sql+log&start=0

dv_lakshmi · **Joined:** Tue Dec 12, 2006 9:07 am **Posts:** 13

To view the query with the values set in the where condition, configure P6Spy.

The installation can be downloaded from

http://www.p6spy.com

Read the instructions on the site to configure it for your environment.
If you are connecting using connection url in hibernate configuration file, then change the connection driver class to

Code:

com.p6spy.engine.spy.P6SpyDriver

and modify the realdriver property in spy.properties to your original database driver.
For eg: if it is an oracle driver then realdriver=oracle.jdbc.driver.OracleDriver.

Place p6spy.jar and spy.properties in the classpath and restart your application. You should find the queries with the values set logged
in spy.log.

dharmendra.pandey · **Posted:** Mon May 14, 2007 1:43 am

Hi jaikiran ,

Thanks to remind me that patch .But is it avaliable with Hibernate API.

jaikiran · **Joined:** Wed Jul 27, 2005 2:33 am **Posts:** 118

Quote:

But is it avaliable with Hibernate API

Not that i am aware of