Hi all,

One of my periodic chores after modifying our web application is to then run through all the pages with an eye to tuning the number of SQL statements issued by Hibernate. It is very easy to change something in the hbm mapping file, or in the HQL, or how you model your objects, and all of a sudden you have a 10x increase in SQL statements requried..

I was thinking that instead of manually doing this, I could instead of have unit test that said that for Page A, 5 sql statements are okay, and for Page B 15 are okay. Then, when I make change, be able to run some unit tests and verify that these limits haven't been exceeded.

Since we have a relatively simple read/write webapp, without multi step pages etc, being able to count the number of SQL statements to load a page would be a fine measurement. Yes, it wouldn't take into account the effect of caching, which does help a lot, but even so, knowing Page 5 has crept from 5 to 6 statements is good information.

So, has anyone done anything like this? My thought is to write a Cactus testcase that would deploy the webapp into Tomcat, and then attach a listerner to something that recorded the sql statements. The testcase consists of generating the webpage. After the Cactus testcase finishes, assert the number of SQL statements issued was less then X.

I remember seeing an open source SQL tool that recorded every SQL statement, and then would play them back in reverse... I also thought about using the Interceptor function and using that as well. I could also do something like proxy the connection that Hibernate retrieves as well..

Any ideas? Suggestions?

Eric

It's pretty easy to implement a wrapper over java.sql.connection and add a counter in it.