One of the most popular questions asked at JDBC forums is how to debug Statements which are issued to the jdbc driver. If you don't have money to spend in vendor tools there are still valid free alternatives, one of these is P6Spy.
The P6Spy tool can be used to monitor effectively all the statements which are issued against a database, including the time to execute the statement and the actual SQL, which means that if you are binding parameters in your PreparedStatements, you get also the query with the actual parameters.
On the site documentation I have seen that the JBoss installation guide relates to the 2.x and 3.x release. Here we will show how to install this tool on the newer releases as well.
At first download the file p6spy-install.zip from here:
http://sourceforge.net/projects/p6spy/files/p6spy/1.3/p6spy-install.zip/download
Step # 1
Unzip the file and look for the file p6spy.jar. This file needs to be placed in JBoss lib directory (For JBoss 4.x you can use JBOSS_HOME/server/<server>/lib. With JBoss 5.x or later you can place it under JBOSS_HOME/common/lib)
Step #2
Place the file spy.properties in a folder inside your JBoss distribution. (For example, I have placed into the folder JBOSS_HOME/server/<server>/db )
#Step 3
Add to your server's CLASSPATH, the folder where you have placed the spy.properties. In our example this would be:
set JBOSS_CLASSPATH=%RUN_CLASSPATH%;C:\jboss-4.2.2.GA\server\default\db
Step #4
Edit the DataSource file that points the connections that needs to be traced and change the driver's class to use P6Spy driver. Example, for an Oracle Datasource:
On the site documentation I have seen that the JBoss installation guide relates to the 2.x and 3.x release. Here we will show how to install this tool on the newer releases as well.
At first download the file p6spy-install.zip from here:
http://sourceforge.net/projects/p6spy/files/p6spy/1.3/p6spy-install.zip/download
Step # 1
Unzip the file and look for the file p6spy.jar. This file needs to be placed in JBoss lib directory (For JBoss 4.x you can use JBOSS_HOME/server/<server>/lib. With JBoss 5.x or later you can place it under JBOSS_HOME/common/lib)
Step #2
Place the file spy.properties in a folder inside your JBoss distribution. (For example, I have placed into the folder JBOSS_HOME/server/<server>/db )
#Step 3
Add to your server's CLASSPATH, the folder where you have placed the spy.properties. In our example this would be:
set JBOSS_CLASSPATH=%RUN_CLASSPATH%;C:\jboss-4.2.2.GA\server\default\db
Step #4
Edit the DataSource file that points the connections that needs to be traced and change the driver's class to use P6Spy driver. Example, for an Oracle Datasource:
Notice the modified value for the attribute <driver-class><
#Step 5
From inside the spy.properties, specify the real Driver name, that you have just replaced in your datasource file. For example:
realdriver=oracle.jdbc.driver.OracleDriver
That's all!
When you execute any query with the Datasource ORACLEDS, P6Spy will log each statement with the following format:
The log file format of spy.log follows:
current time|execution time|category|statement SQL String|effective SQL string
For example:
1278343428665|32|8|statement|select count(*) from meta_parametri where cod_tipo = ?|select count(*) from meta_parametri where cod_tipo = 3
Here we have issued a Statement at the timestamp 1278343428665, which took 32ms. The following number indicates the logging category (you can manage your log by including and excluding categories, which is described in Common Property File Settings). Finally you have two sql statements: at first the one which you have coded and then the actual statement which has been executed by the DB.
What about Hibernate users ? Hibernate users can debug sql statements in two ways: the simple solution is setting the property show_sql to true in your Session Factory Configuration.
<property name="show_sql">true</property>
However, this approach is not very flexible and produces too much logging. If you want to redirect your SQL logs in a customized file, then you need simply create a category for your logs which references the packages org.hibernate.SQL:
1278343428665|32|8|statement|select count(*) from meta_parametri where cod_tipo = ?|select count(*) from meta_parametri where cod_tipo = 3
Here we have issued a Statement at the timestamp 1278343428665, which took 32ms. The following number indicates the logging category (you can manage your log by including and excluding categories, which is described in Common Property File Settings). Finally you have two sql statements: at first the one which you have coded and then the actual statement which has been executed by the DB.
What about Hibernate users ? Hibernate users can debug sql statements in two ways: the simple solution is setting the property show_sql to true in your Session Factory Configuration.
<property name="show_sql">true</property>
However, this approach is not very flexible and produces too much logging. If you want to redirect your SQL logs in a customized file, then you need simply create a category for your logs which references the packages org.hibernate.SQL:
However, this doesn't solve the problem of binding parameters which are sent to the DB and also you don't get time statistics. Enabling P6Spy with Hibernate is pretty easy anyway, all you have to do is referencing the P6Spy Driver class from your Session Factory configuration and specify the real driver name in the file spy.properties.
