在进行SQL优化时,很重要的一点就是要看到底层执行的具体SQL,虽然可以通过打印日志的方式获取具体的SQL和SQL的执行效率,但是这样做很不方便,尤其在是使用Hibernate作为持久层时,大量的占位符让人很是头痛
P6Spy
P6Spy是一款开源的SQL监控工具,可以记录所有数据库操作及其消耗的时间
下载地址:http://sourceforge.net/projects/p6spy/files/p6spy/
使用起来非常简单(以下配置只适用于单独使用Hibernate的,对于Hibernate+Spring的项目不适用):
1,将p6spy.jar加入项目工程classpath中
2,将p6spy配置文件spy.properties加入项目工程/src目录下
3,将项目工程的数据库驱动修改为P6Spy提供的驱动(修改Hibernate配置文件hibernate.cfg.xml):
<!--
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
-->
<property name="connection.driver_class">com.p6spy.engine.spy.P6SpyDriver</property>
4,修改p6spy配置文件:
realdriver项修改为项目工程实际使用的驱动
# oracle driver
# realdriver=oracle.jdbc.driver.OracleDriver
# mysql Connector/J driver
# realdriver=com.mysql.jdbc.Driver
# informix driver
# realdriver=com.informix.jdbc.IfxDriver
# ibm db2 driver
# realdriver=COM.ibm.db2.jdbc.net.DB2Driver
# the mysql open source driver
#realdriver=org.gjt.mm.mysql.Driver
realdriver=com.mysql.jdbc.Driver
deregisterdrivers项一定要修改为true,原因注释已经说的很清楚了
#the DriverManager class sequentially tries every driver that is
#it's possible to registered to find the right driver.In some instances,
#load up the realdriver before the p6spy driver, in which case
#your connections will not get wrapped as the realdriver will "steal"
#the connection before p6spy sees it. Set the following