p6spy-部署在系统中，输出sql语句

1、添加p6spy.jar。

2、将spy.properties放到src下，最后部署到类路径下。

3、修改database.properties中的数据库驱动，如：database.connection.driver=com.p6spy.engine.spy.P6SpyDriver

4、修改spy.properties中的属性：

# 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

根据自己的数据库类型，解开注释，这里使用oracle。

5、修改sql日志的输出方式，第三个是输出到控制台，第四个是输出到文件。
#specifies the appender to use for logging
#appender=com.p6spy.engine.logging.appender.Log4jLogger
appender=com.p6spy.engine.logging.appender.StdoutLogger
#appender=com.p6spy.engine.logging.appender.FileLogger

6、如果指定将日志输出到文件，则需指定文件的目录。
# name of logfile to use, note Windows users should make sure to use forward slashes in their pathname (e:/test/spy.log) (used for file logger only)
logfile = d:/p6spy.log

为过滤眼花缭乱的结果集resultset，需对原有的jar进行改造，件中的jar已经将resultset过滤掉了。另外还要修改：

excludecategories=info,debug,result,batch,resultset

附

spy.properties中 参数说明：

 （1）       module.log的属性必须配置，如果不配置，P6SPY将不起任何作用，典型配置为：module.log=com.p6spy.engine.logging.P6LogFactory

（2）       realdriver表示真实的驱动，上面配置的com.p6spy.engine.spy.P6SpyDriver会最终调用真是的驱动程序执行SQL

Oracle数据库的配置为：

realdriver=oracle.jdbc.driver.OracleDriver

（3）       appender配置，一般分为三种

#appender=com.p6spy.engine.logging.appender.Log4jLogger

# appender=com.p6spy.engine.logging.appender.StdoutLogger

#appender=com.p6spy.engine.logging.appender.FileLogger

 

其中，appender=com.p6spy.engine.logging.appender.StdoutLogger表示将输出的SQL或者日志信息输出到Console窗口。

（4）       log4j.logger.p6spy一般需要将log4j的相关参数从默认的

log4j.logger.p6spy=info,STDOUT

修改为

log4j.logger.p6spy=warn,STDOUT

以减少p6spy信息的输出

（5）       可以将dateformat=修改为dateformat=hh:mm:ss,SSS有利用时间信息的查看。

（6）       尽量根据P6Spy的标准的示例的配置文件进行修改，不用随便删除配置信息。

 

打印语句如下：

0:44:10,303  INFO [AbstractPoolBackedDataSource] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 120000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 17f1841, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.p6spy.engine.spy.P6SpyDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 17f1841, idleConnectionTestPeriod -> 3600, initialPoolSize -> 3, jdbcUrl -> jdbc:oracle:thin:ccfsdata/ccfsdata123@10.25.18.26:1524:pcc, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 50, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 180, usesTraditionalReflectiveProxies -> false ]
|statement|SELECT categories_id, title, display_order, moderated FROM jforum_categories ORDER BY display_order
|statement|SELECT forum_id, forum_name, categories_id, forum_desc, forum_order, forum_topics, forum_last_post_id, moderated FROM jforum_forums ORDER BY forum_order ASC
|statement|SELECT COUNT(1) FROM jforum_posts WHERE forum_id = 1
|statement|SELECT config_name, config_value, config_id FROM jforum_config WHERE config_name = 'most.users.ever.online'
|statement|SELECT config_name, config_value, config_id FROM jforum_config WHERE config_name = 'most.users.ever.online.date'
|statement|SELECT * FROM ( SELECT user_id, username, ROW_NUMBER() OVER(ORDER BY user_regdate DESC) - 1 LINENUM FROM jforum_users ORDER BY user_regdate DESC ) WHERE LINENUM = 0
|statement|SELECT COUNT(1) as total_users FROM jforum_users
|statement|SELECT * FROM jforum_ranks ORDER BY rank_min
|statement|SELECT * FROM jforum_smilies ORDER BY smilie_id
|statement|SELECT * FROM jforum_banlist ORDER BY banlist_id
|commit|
10:44:14,274  INFO [ConfigLoader        ] Loading clickstream config from D:\software\apache-tomcat-5.5.20\webapps\jforum/WEB-INF/config/clickstream-jforum.xml
sessionCreated
|statement|SELECT u.*, (SELECT COUNT(1) FROM jforum_privmsgs pm WHERE pm.privmsgs_to_userid = u.user_id AND pm.privmsgs_type = 1) AS private_messages FROM jforum_users u WHERE u.user_id = 1
|statement|SELECT ug.group_id, g.group_name FROM jforum_user_groups ug, jforum_groups g WHERE ug.group_id = g.group_id AND ug.user_id = 1 ORDER BY g.group_id
|statement|SELECT r.name, '0' AS role_value FROM jforum_roles r WHERE r.group_id IN (1) UNION SELECT r.name, rv.role_value FROM jforum_roles r, jforum_role_values rv WHERE r.role_id = rv.role_id AND r.group_id IN (1) ORDER BY name
net.jforum.view.forum.ForumAction
templateName is------forums.list
|statement|SELECT COUNT(1) as total_messages FROM jforum_posts WHERE need_moderate = 0
输出模板-------template
|statement|SELECT post_time, p.topic_id, t.topic_replies, post_id, u.user_id, username FROM jforum_posts p, jforum_users u, jforum_topics t , jforum_forums f WHERE t.forum_id = f.forum_id AND t.topic_id = p.topic_id AND f.forum_last_post_id = t.topic_last_post_id AND t.topic_last_post_id = p.post_id AND p.forum_id = 1 AND p.user_id = u.user_id AND p.need_moderate = 0
|statement|SELECT t.*, p.user_id AS last_user_id, p.post_time, p.attach AS attach FROM jforum_topics t, jforum_posts p WHERE t.topic_id = 201 AND p.post_id = t.topic_last_post_id
|statement|SELECT user_id, username FROM jforum_users WHERE user_id IN (1,1)
|commit|