Main Tutorials

How to display hibernate sql parameter values – P6Spy

Question

There are many developers asking about Hibernate SQL parameter value question. How to display the Hibernate SQL parameter values that passed to database? Hibernate just display all parameter values as question mark (?). With show_sql property, Hibernate will shows all generated SQL statements, but not the SQL parameter values.

For example


Hibernate: insert into mkyong.stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
values (?, ?, ?, ?, ?, ?)

Is there a way to log or display the exact Hibernate SQL parameter values?

Solution – P6Spy

Well, if there is a question there is an answer ~

The P6Spy is a useful library to log all SQL statement and parameter values before send it to database. The P6Spy is free, it’s use to intercepts and logs all your database SQL statements into a log file, and it works for any application that uses JDBC driver.

1. Download P6Spy library

Get the “p6spy-install.jar“, you can download it from

  1. P6Spy official website.
  2. P6Spy at Sourceforge.net

2. Extract it

Extract the p6spy-install.jar file, look for p6spy.jar and spy.properties

3. Add library dependency

Add p6spy.jar into your project library dependency

4. Modify P6Spy properties file

Modify your database configuration file. You need to replace your existing JDBC driver with P6Spy JDBC driver – ” com.p6spy.engine.spy.P6SpyDriver

Original is MySQL JDBC driver – “com.mysql.jdbc.Driver”


<session-factory>
  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="show_sql">true</property>
</session-factory>

Changed it to P6Spy JDBC driver – “com.p6spy.engine.spy.P6SpyDriver”


<session-factory>
  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
  <property name="hibernate.connection.driver_class">com.p6spy.engine.spy.P6SpyDriver
  </property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="show_sql">true</property>
</session-factory>

5. Modify P6Spy properties file

Modify the P6Spy properties file – “spy.properties

Replace the “real driver” with your existing MySQL JDBC driver


realdriver=com.mysql.jdbc.Driver

#specifies another driver to use
realdriver2=
#specifies a third driver to use
realdriver3=

Change the Log file location
Change the log file location in logfile property, all SQL statements will log into this file.

Windows


logfile     = c:/spy.log

*nix


logfile     = /srv/log/spy.log

6. Copy “spy.properties” to project classpath

Copy “spy.properties” to your project root folder, make sure your project can locate “spy.properties”, else it will prompt “spy.properties” file not found exception.

7. Done

Run your application and do some database transaction, you will notice all the SQL statements sent from application to database will be logged into a file you specified in “spy.properties”.

Sample log file as following.


insert into mkyong.stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
values (?, ?, ?, ?, ?, ?)|
insert into mkyong.stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
values (10.0, 1.1, '2009-12-30', 1.2, 11, 1000000)

Conclusion

Frankly, the P6Spy is really useful in reducing the developers’ debugging time. As long as your project is using JDBC driver for connection , P6Sqp can fir into it and log all SQL statements and parameter values for you.

For Maven User

You can use Maven to download the P6Spy dependency into your pom.xml


        <dependency>
		<groupId>p6spy</groupId>
		<artifactId>p6spy</artifactId>
		<version>1.3</version>
	</dependency>

However the “spy.properties” file is not come in package, you have to create it yourself. You can download the template here – spy.properties

Reference

  1. P6Spy configuration

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
18 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Goutam kumar mishra
6 years ago

Hi when i am working though PostgreSQL database it is not working. it printed with placeholder like
insert into mkyong.stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME)
values (?, ?, ?, ?, ?, ?)

????? ?????
8 years ago

didn’t work for me because
jdbc:mysql://localhost:3306/mkyong
must be
jdbc:p6spy:mysql://localhost:3306/mkyong
I am using p6spy 2.1.4

Ravi
3 months ago

When I use the P6Spy the queries(queries and parameter values) related to associations(OneToMany) are not getting printed. Could you please help in finding a solution.

Suvha Ratno Mazumder
2 years ago

I have followed all, but can see select statement but can not able to see insert statement. Any help please?

Amr Lotfy
4 years ago

Is there any tool that would mock JDBC with capture/simulate option ?

Shoot3R
8 years ago

Hello. Could you provide please you spy.properties. Because I’ve done all points and got not a pretty log :

08:46:13|3|2|statement|select client0_.client_id as client_i1_2_0_, client0_.age as age2_2_0_, client0_.last_name as last_nam3_2_0_, client0_.name as name4_2_0_ from CLIENT client0_ where client0_.client_id=?|select client0_.client_id as client_i1_2_0_, client0_.age as age2_2_0_, client0_.last_name as last_nam3_2_0_, client0_.name as name4_2_0_ from CLIENT client0_ where client0_.client_id=1

08:46:13|-1||resultset|select client0_.client_id as client_i1_2_0_, client0_.age as age2_2_0_, client0_.last_name as last_nam3_2_0_, client0_.name as name4_2_0_ from CLIENT client0_ where client0_.client_id=1|age2_2_0_ = 25, last_nam3_2_0_ = Igor, name4_2_0_ = 1

Programmer
10 years ago

I’m working with spring 3 and hbernate 4. is it possible to put spy.properties to other place? or does it have to be in root classpath? thanks

Programmer
10 years ago
Reply to  Programmer

It didn’t work. I’ve set the driver to “com.p6spy.engine.spy.P6SpyDriver” and put spy.properties in root classpath. the spy.properties is a one liner :
realdriver=net.sourceforge.jtds.jdbc.Driver
but nothing changed. any help?
thanks

Programmer
10 years ago
Reply to  Programmer

ok, ignore that. apparently after restarting my PC, efverything works just fine. thanks

longchamp en solde
10 years ago

Pretty component to content. I just stumbled upon your web site and in accession capital to claim that I acquire in fact loved account your blog posts. Any way I’ll be subscribing on your augment or even I fulfillment you get right of entry to persistently rapidly.

Pablo Thiele
11 years ago

How about logging using the p6spy on JUnit tests? I tried some ways and had no luck.

Krishna
11 years ago

We are using Oracle connection Pooling mechanism in our project as our application uses some oracle specific features.

The configuration of our datasource in jetty.xml is as follows:

<Call name="addService">
<Arg>
  <New class="org.mortbay.jetty.plus.DefaultDataSourceService">
    <Set name="Name">DataSourceService</Set>

    <Call name="addDataSource">
        <Arg>app_ds</Arg><!--java:comp/env-->
        <Arg>
         <New class="oracle.jdbc.pool.OracleConnectionPoolDataSource">
            <Set name="description">xxxx</Set>
            <Set name="user">xxx</Set>
            <Set name="password">xxxx</Set>
            <Set name="loginTimeout">xxx</Set>
            <Set name="URL">jdbc:oracle:thin:@localhost:1521:xxx</Set>
        </New>
      </Arg>
    </Call>
     <Call name="start"/>
 </New>

Now How do we integrate this datasource with P6Spy, so that P6Spy can print out all the SQL statements on to the console…?

I have previously used P6spy with other datasources like spring’s DriverManagerDataSource, other datasources like as

(In Tomcat)

Resource name="jdbc/test" auth="Container"
      type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver"
      url="jdbc:oracle:thin:@xxx"
      username="xxx" password="xxx" maxActive="65" maxIdle="10"
      maxWait="-1" removeAbandoned="true"/> 

..etc.

All these datasources take driverClassName as argument where we can provide the “com.p6spyengine.spy.P6SpyDriver” in the place of “oracle.jdbc.driver.OracleDriver” and provide the real driver name in spy.properties. The all worked fine.

But with oracle.jdbc.pool.OracleConnectionPoolDataSource, there is no such property called driverClassName to provide a proxy driver to.

In this case how can i integrate P6Spy with it?

Please help…

Thanks in Advance,
Krish

geeth
11 years ago

Thankz mkyong. It is very helpful!

Anish
11 years ago

Very nice and simple way to monitor native sql. Thanks for the time spend for sharing your knowledge with the world.

Javier ROCA
14 years ago

Hello there,

Thanks for your article. It seems the spy.properties requires other parameters to work properly in some environments (at least in my case Eclipse 3.5, Hibernate 3), in my case I needed to have Log4J as the main output. After reviewing the P6Spy doc I found this setup working fine:

#################################################”
realdriver=com.mysql.jdbc.Driver # or your driver

deregisterdrivers = true #needed in certain circumstances!!!
autoflush = true
appender=com.p6spy.engine.logging.appender.Log4jLogger # Log4J
includecategories = info, statement # statement == SQL statements
module.log=com.p6spy.engine.logging.P6LogFactory # required!!!
#################################################”

In log4j.properties you can proceed as follows:

log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.logger.p6spy=DEBUG,STDOUT

Regards,

Javier

Panda
11 years ago
Reply to  mkyong

Nice findings. It help. Keep up blogging.