用p6spy来进行jdbc sql日志记录和分析

1.相关工具下载：
p6spy:   http://heanet.dl.sourceforge.net/sourceforge/p6spy/p6spy-install.zip
http://www.p6spy.com/download.html
2.解压p6spy-install.zip，把p6spy.jar和spy.properties放到classpath中。
web项目放在 WEB-INF/lib/ 目录下
修改spy.properties， 将spy.properties 文件放在 WEB-INF/class/ 目录下
logfile    = log/dbspy.log
realdriver=oracle.jdbc.driver.OracleDriver   （设置数据库真实的驱动，此处为oracle，如果是其它数据库，按目标数据库为准）
3.在设置数据源或者配置数据库连接的地方，
修改driver为：
drivers=com.p6spy.engine.spy.P6SpyDriver

（

修改应用的数据库连接，只需要修改驱动这块，其它url ， 用户名，密码等，都不需要修改

比如

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
 

修改成

 

jdbc.driverClassName=com.p6spy.engine.spy.P6SpyDriver
 

）

 

4.启动应用，观察应用的运行日志，能正常连接数据库，启动正常。

5.观察 p6spy的日志文件 （log/dbspy.log） 

   应用与数据库的sql操作，都被记录下来。通过日志文件，可以监视应用与数据库的交互情况，知道某个操作会触发什么样的语句，对什么表进行了什么操作等 。

 

1592556691302|0|1|commit||
1592556691309|3|2|statement|select this_.segmentid as segmentid9_0_, this_.vpndomain as vpndomain9_0_, this_.segmentname as segmentn3_9_0_, this_.startip as startip9_0_, this_.endip as endip9_0_, this_.subnetmask as subnetmask9_0_, this_.segment as segment9_0_, this_.startnum as startnum9_0_, this_.endnum as endnum9_0_, this_.ipcount as ipcount9_0_, this_.moduser as moduser9_0_, this_.modtime as modtime9_0_, this_.areacode as areacode9_0_ from VPDNIPSEGMENT this_ where this_.vpndomain in (?)|select this_.segmentid as segmentid9_0_, this_.vpndomain as vpndomain9_0_, this_.segmentname as segmentn3_9_0_, this_.startip as startip9_0_, this_.endip as endip9_0_, this_.subnetmask as subnetmask9_0_, this_.segment as segment9_0_, this_.startnum as startnum9_0_, this_.endnum as endnum9_0_, this_.ipcount as ipcount9_0_, this_.moduser as moduser9_0_, this_.modtime as modtime9_0_, this_.areacode as areacode9_0_ from VPDNIPSEGMENT this_ where this_.vpndomain in ('mafh.com.cn')
1592556691311|1|2|commit||
1592556691315|1|3|commit||
1592556691320|2|1|statement|select count(*) as col_0_0_ from MDNAPN mdnapnbean0_ where mdnapnbean0_.MDN is not null|select count(*) as col_0_0_ from MDNAPN mdnapnbean0_ where mdnapnbean0_.MDN is not null
1592556691320|-1||resultset|select count(*) as col_0_0_ from MDNAPN mdnapnbean0_ where mdnapnbean0_.MDN is not null|
1592556691322|1|1|commit||
1592556691328|2|2|statement|select count(*) as col_0_0_ from VPDNIMSIMDN vpdnimsimd0_ where vpdnimsimd0_.VPNUSERMDN is not null or vpdnimsimd0_.VPNUSERIMSI is not null|select count(*) as col_0_0_ from VPDNIMSIMDN vpdnimsimd0_ where vpdnimsimd0_.VPNUSERMDN is not null or vpdnimsimd0_.VPNUSERIMSI is not null
1592556691328|-1||resultset|select count(*) as col_0_0_ from VPDNIMSIMDN vpdnimsimd0_ where vpdnimsimd0_.VPNUSERMDN is not null or vpdnimsimd0_.VPNUSERIMSI is not null|
1592556691332|2|2|statement|select count(*) as col_0_0_ from VPDNENTUSER vpdnentuse0_ where VPNUSERBIND=0|select count(*) as col_0_0_ from VPDNENTUSER vpdnentuse0_ where VPNUSERBIND=0
1592556691332|-1||resultset|select count(*) as col_0_0_ from VPDNENTUSER vpdnentuse0_ where VPNUSERBIND=0|
1592556691334|1|2|commit||
1592556691339|1|3|statement| update USERINFOS set LTELICENSEUSER = ?,LTEREGISTUSER = ?,LIC4GSTATUS = ?,VPDNLICENSEUSER = ?,VPDNREGISTER = ?,VPDNSTATUS = ?| update USERINFOS set LTELICENSEUSER = 20000,LTEREGISTUSER = 0,LIC4GSTATUS = 0,VPDNLICENSEUSER = 16000,VPDNREGISTER = 0,VPDNSTATUS = 0
1592556691355|15|3|commit||
1592556698598|1|1|commit||
1592556700072|209|2|statement|select ALARMID from ALARM where ALARMID= 10817|select ALARMID from ALARM where ALARMID= 10817
1592556700082|8|2|statement|insert into ALARM(ALARMID, TYPE, IP, DEVICETYPE, OID, SEVERITY, UPTIME, ACKSTATUS,ADDITIONALINFO) values(?, ?, ?, ?, ?, ?, ?, ?, ?)|insert into ALARM(ALARMID, TYPE, IP, DEVICETYPE, OID, SEVERITY, UPTIME, ACKSTATUS,ADDITIONALINFO) values(10817, -1, 'telecom_vpdn', 'Connection Alarm', '.1.2.3.4.5.6.7.8.9.10.0', 1, '2020-06-19 16:51:39.862', 2, ' Connection to Host[172.17.0.181]  Error,Ping From OMC Check Failed!')
1592556700088|1|3|commit||

 

补充：

如果是resin：

也可能通过查看 resin.conf 配置的  <class-loader>  ，将p6spy.jar 放置到此加载类目录下， spy.properties 则放置到 项目根目录下。