集成p6spy到spring中

网上好多帖子都是说，修改原有 JDBC Driver为：com.p6spy.engine.spy.P6SpyDriver
然后修改spy.properties 中的 realdriver值为原有的JDBC Driver
如果在spring开发环境中使用org.springframework.jdbc.datasource.DriverManagerDataSource来模拟dataSource是不会生效的，

	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property  name="driverClassName">
		<value>com.sybase.jdbc2.jdbc.SybDriver</value>
		</property>
		<property  name="url">
		<value>jdbc:sybase:Tds:192.168.0.2:6100/db</value>
		</property>
		<property  name="username">
		<value>usr</value>
		</property>
		<property  name="password">
		<value>pwd</value>
		</property>
	</bean>

因为在spring加载时我们无法控制先加载哪个，spring会报错不能使用com.p6spy.engine.spy.P6SpyDriver连接到数据库，
其实p6spy只是想做jdbc的代理，而不是真正想连数据库，而spring却不知道这些，所以应该采用另外一种方式来集成p6spy

	<bean id="dataSourceTarget" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property  name="driverClassName">
		<value>com.sybase.jdbc2.jdbc.SybDriver</value>
		</property>
		<property  name="url">
		<value>jdbc:sybase:Tds:192.168.0.2:6100/db</value>
		</property>
		<property  name="username">
		<value>usr</value>
		</property>
		<property  name="password">
		<value>pwd</value>
		</property>
	</bean>
	<bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource" 
                                  destroy-method="close">
        <constructor-arg>
            <ref local="dataSourceTarget"/>
        </constructor-arg>
   </bean>

这样就可以把p6spy集成到spring中去了。
令：网上好多文章都介绍使用sqlprofiler和p6spy配合，但是IronTrackSQL感觉更好一些！
关于如何使用sqlprofiler，IronTrackSQL，配置很简单
对于sqlprofiler只要把sqlprofiler自带的spy.properties放到classpath下就可以了，然后java -jar sqlprofiler.jar
对于IronTrackSQL，还要把irontracksql.jar也放到classpath下，然后java -jar irontracksql.jar

按你的方法做了，没看到log文件产生

检查一下你的spy.property里logfile的配置路径是否正确，还有appender是否正确

P6Spy也可以配置成DataSource形式进行监控的。

P6Spy & SQL Profiler & IronTrack SQL的配置也可以参阅
http://www.iteye.com/topic/28880

我也没有试成功，请指教：

部分spy.properties

realdriver=oracle.jdbc.driver.OracleDriver 
appender=com.p6spy.engine.logging.appender.FileLogger
logfile = E:\spy.log
# append to the p6spy log file. if this is set to false the
# log file is truncated every time. (file logger only)
append=true

#The following are for log4j logging only
log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n
log4j.logger.p6spy=DEBUG,STDOUT

realdatasourceproperties=port;1521,serverName;192.168.0.23,databaseName;CSDRES,user;pass

spring的配置：

<bean id="dataSourceTarget" 
		class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName"><value>oracle.jdbc.OracleDriver</value></property>
		<property name="url">
			<value>
				jdbc:oracle:thin:@192.168.0.23:1521:CSDRES
			</value>
		</property>
		<property name="username"><value>user</value></property>
		<property name="password"><value>pass</value></property>
	</bean>
<bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource"   destroy-method="close">  
       <constructor-arg>  
           <ref local="dataSourceTarget"/>  
       </constructor-arg>  
  </bean>  

我的配置：

<!--  P6Spy-->
	<bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource">
		<constructor-arg>
			<bean
				class="org.springframework.jdbc.datasource.DriverManagerDataSource">
				<property name="driverClassName"
					value="com.p6spy.engine.spy.P6SpyDriver" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
			</bean>
		</constructor-arg>
	</bean>

realdriver=oracle.jdbc.driver.OracleDriver
logfile = c:/spy.log
appender=com.p6spy.engine.logging.appender.Log4jLogger

结果：

p6spy - 1180506863484|0|1|statement|/* load collection com.staplesnrc.plugins.security.domain.Resource.permissions */ select permission0_.RESC_ID as RESC2_0_, permission0_.PERMIS_ID as PERMIS1_0_ from TPERMIS_RESC permission0_ where permission0_.RESC_ID in (?, ?, ?, ?, ?)|/* load collection com.staplesnrc.plugins.security.domain.Resource.permissions */ select permission0_.RESC_ID as RESC2_0_, permission0_.PERMIS_ID as PERMIS1_0_ from TPERMIS_RESC permission0_ where permission0_.RESC_ID in (7, 142, 169, 161, 168)
p6spy - 1180506863484|-1||resultset|/* load collection com.staplesnrc.plugins.security.domain.Resource.permissions */ select permission0_.RESC_ID as RESC2_0_, permission0_.PERMIS_ID as PERMIS1_0_ from TPERMIS_RESC permission0_ where permission0_.RESC_ID in (7, 142, 169, 161, 168)|PERMIS1_0_ = 7, RESC2_0_ = 7
p6spy - 1180506863484|-1||resultset|/* load collection com.staplesnrc.plugins.security.domain.Resource.permissions */ select permission0_.RESC_ID as RESC2_0_, permission0_.PERMIS_ID as PERMIS1_0_ from TPERMIS_RESC permission0_ where permission0_.RESC_ID in (7, 142, 169, 161, 168)|PERMIS1_0_ = 48, RESC2_0_ = 161
p6spy - 1180506863484|-1||resultset|/* load collection com.staplesnrc.plugins.security.domain.Resource.permissions */ select permission0_.RESC_ID as RESC2_0_, permission0_.PERMIS_ID as PERMIS1_0_ from TPERMIS_RESC permission0_ where permission0_.RESC_ID in (7, 142, 169, 161, 168)|PERMIS1_0_ = 51, RESC2_0_ = 168
p6spy - 1180506863484|-1||resultset|/* load collection com.staplesnrc.plugins.security.domain.Resource.permissions */ select permission0_.RESC_ID as RESC2_0_, permission0_.PERMIS_ID as PERMIS1_0_ from TPERMIS_RESC permission0_ where permission0_.RESC_ID in (7, 142, 169, 161, 168)|PERMIS1_0_ = 51, RESC2_0_ = 169

不知道这个东西有什么用。。本身hibernate就可以配置一下输入sql语句

可以监控sql效率呀和hibernate显示的sql不同

我用sqlprofiler的时候，想过滤掉category为resultset的信息，然后在spy.properties里面看到
excludecategories=info,debug,result,batch
应该是说category为resultset的信息已经被设置成exclude了，但是我现在还是能看到，为什么呢？因为我觉得这个信息似乎没有用。

我按照上面的方法做的，没有成功，提示——1 constructor arguments specified but no matching constructor found in bean 'dataSource'，好像P6DataSource没有一个参数的构造函数阿