« Back

Debugging SQL queries with p6spy

Staff Blogs August 1, 2011 By Miguel Pastor Olivar Staff

Who has not ever had to fight with complex SQL queries and PreparedStatements? And, what about the ? symbol when enabling the sql log?

Last week I had to write a big migration process in order to complete a refactor and I have done some little hacking in order to use the p6spy driver when running a migration process from command line. This driver will resolve the values for the hated "?" symbols :).

My first step was to modify the portal/tools/db-upgrade/run.sh (I work on Linux) in order to launch the migration process from the command line witouth starting the application server.

Once the previous file has been modified we should include the p6spy.jar and the spy.properties files in the portal/tools/db-upgrade/lib/ folder.

We will need to configure three basic properties in the spy.properties file:

Basically we are configuring the path for the logged queries and the real driver we are using in our app (i am using MySQL at this moment)

The last step would be to tell the upgrade process to use a specific datasource (p6spy). We could achieve this by modifying the file portal/tools/db-upgrade/portal-ext.properties (pay attention to the jdbc.default.driverClassName property):

 

That's all! If you run your upgrade process by executing the run.sh file you will get logged all the executed SQL queries. And the most important thing; "?" symbols will be replaced with its corresponding values :).

We could use it for debugging SQL queries of the portal (hibernate.show_sql property does not replace ? ) but i left it for you as a homework ;).

Cheers,

Migue

PD: as far as I rembember, there is an eclipse plugin that displays the contents of the previous log much more beautiful than the plain text :)

Threaded Replies Author Date
Really good post...thnx for Sharing...I want to... Jay Patel August 1, 2011 9:29 PM
Excellent post Migual. A very useful feature... Ahmed Hasan August 8, 2011 3:07 AM
Nice write up Miguel! The p6spy section is very... Thiago Leão Moreira November 21, 2011 8:22 AM

Really good post...thnx for Sharing...I want to try your homework..emoticon
Posted on 8/1/11 9:29 PM.
Excellent post Migual. A very useful feature not only to see the SQL queries but also to optimize 'em.
Posted on 8/8/11 3:07 AM.
Nice write up Miguel! The p6spy section is very handy!
Posted on 11/21/11 8:22 AM.