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|
分享到:
相关推荐
P6spy是一个JDBC Driver的包装工具,p6spy通过对JDBC Driver的封装以达到对SQL语句的监听和分析,以达到各种目的。 p6spy的安装步骤: 1. 下载p6spy的安装包 2. 把p6spy的jar包放到Classpath中,如果是WEB App...
与hibernate配合使用实现sql语句参数的打印及其他的一些性能方面的监控,有利于优化sql代码,用法上网随便一搜很多的
虽然在hibernate中有show_sql选项,但是显示出来的语句大多类似 select * from xxx where value=? 但是有时候我们需要得到完整的SQL语句,怎么办呢?使用P6SPY就可以完成这个任务
p6spy dataSource 配置 监听真实sql语句
p6spy.jar 博文链接:https://lxy19791111.iteye.com/blog/131293
用 p6spy 来观察 Java 程序中执行的所有 SQL 语句.docx
p6spy简介,p6spy完整显示hibernate的SQL语句 包括参数,spring p6spy 配置
P6Spy 是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架。通过 P6Spy 我们可以对 SQL 语句进行拦截,相当于一个 SQL 语句的记录器,这样我们可以用它来作相关的分析,比如性能分析。P6Spy 用 Log4J 来...
NULL 博文链接:https://hqhe-nj.iteye.com/blog/342372
NULL 博文链接:https://28680360-qq-com.iteye.com/blog/1673095
用p6spy来观察Java程序中执行的所有SQL语句[整理].pdf
利用P6SPY +SQL Profiler调试、记录、统计web app对数据库的操作, 可以查看到PreparedStatment的执行最终的SQL语句。 有详细的操作说明 下面的这个是集成的完整版。。太大了就不上传了。自己下吧 jahia_v6_...
p6spy是一种可以在控制台上打印出sql语句的工具类,可以和多种数据库交互框架结合在一起,调试程序非常方便
P6spy是一个JDBC Driver的包装工具,p6spy通过对JDBC Driver的封装以达到对SQL语句的监听和分析,以达到各种目的。
主要用于打印出执行的sql语句,用来判断程序的正确性。简单使用!
用于在tomcat/conf中配置连接数据库驱动。 P6Spy是一个可以用来在应用程序中...通过P6Spy可以对SQL语句进行拦截,相当于一个SQL语句的记录器,这样我们可以用它来作相关的分析,比如性能分析。 此压缩包包含文件和说明
p6psy P6SPY 是一个监控JDBC执行语句的开源产品,利用P6SPY很容易的就能监控到JDBC中执行的SQL语句,便于系统调试和性能调优。 参考:https://blog.csdn.net/weixin_30641567/article/details/90108718
4、使用p6spy 打印出hibernate 中已经替换了占位符参数的sql语句,为hibernate的调试不在感到苦恼 系统部署我使用的ant 部署,代码包含ant部署文件 skyline\coding\src\config\jdbc.properties 为数据库配置...
JDBC-Performance-Logger, 一个JDBC驱动程序包装器和用于分析 性能记录器命令行目测试通过JDBC执行的sql语句的性能。( 点击下面的图片了解这些...虽然在JDBC性能监视( log4jdbc,P6Spy, JDbMonitor 。) 周围已经经存