P6spy监控打印SQL语句

Java开发人员都知道，通过设置Hibernate属性hibernate.show_sql为true，可以打印出SQL语句用于Debug。但Hibernate输出的SQL都是用占位符‘?’来表示的SQL语句声明，如下，看不到实际执行的参数值：

insert into BILLING_SUM (LAST_ACT_DATE, LAST_ACT_TYP, LAST_ACT_USER, AIR_CODE, AMOUNT, BILLING_SUM_SEQ) values (?, ?, ?, ?, ?, ?)

 

这样的Show SQL对于Debug来说还是不太方便，无法直观地看到真正执行的SQL是什么。对于这种情况，我们来可借助P6spy这个开源框架来监控SQL语句的执行，作为Hibernate打印语句的有益补充

 

 2002年，开源项目P6spy在SourceForge上发起（http://sourceforge.net/projects/p6spy/），它可以在不改变应用代码的情况下，仅通过配置的方式就可以监控Java应用的SQL语句，记录下任何执行的JDBC SQL。

 

P6spy的配置非常简单：

1. 修改应用的数据源文件，把JDBC Driver替换为P6spy Driver
2. 将p6spy.jar包放到classpath下
3. 设置spy.properties配置文件，并放到classpath下

1. 修改应用的数据源文件

 以JPA的persistence.xml为例，将JDBC Driver设置为com.p6spy.engine.spy.P6SpyDriver

<persistence-unit name="bspGroup1">
    <properties>
        <property name="hibernate.archive.autodetection" value="class" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
        <property name="hibernate.connection.driver_class" value="com.p6spy.engine.spy.P6SpyDriver" />
        <property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/R:/test;MODE=Oracle;DB_CLOSE_DELAY=-1;" />
	<property name="hibernate.connection.username" value="sa" />
	<property name="hibernate.connection.password" value="" />
	<property name="hibernate.hbm2ddl.auto" value="update" />
	<property name="hibernate.show_sql" value="true" />
	<property name="hibernate.format_sql" value="true" />
    </properties>
</persistence-unit>

 

2.  将p6spy.jar包放到classpath下

 

3.  设置spy.properties并放到classpath下

 从SourceForge下载的安装程序包中，可以找到spy.properties并在此基础上修改即可。其中主要有以下几个参数配置：

# 设置应用真正使用的JDBC Driver
realdriver=org.h2.Driver

# 设置使用p6spy driver来做代理
deregisterdrivers=true

# 取消JDBC URL前缀，if=true then url must be prefixed with p6spy:
useprefix=false

# 配置记录Log例外
excludecategories=info,debug,result,batch

# 日志输出到控制台
appender=com.p6spy.engine.logging.appender.StdoutLogger

 注：详细的spy.properties配置说明，可以参考p6spy安装包下提供的other.htm文档。

 

 附录：

1. P6spy的Maven依赖

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>1.3</version>
    <scope>test</scope>
</dependency>

 

2. P6spy记录日志格式

Hibernate: 
    insert 
    into
        BILLING_DETAIL
        (LAST_ACT_DATE, LAST_ACT_TYP, LAST_ACT_USER, AIR_CODE, AMOUNT, BILLING_DETAIL_SEQ) 
    values
        (?, ?, ?, ?, ?, ?)
[201301-27 01:21:11]|1|0|statement|insert into BILLING_DETAIL (LAST_ACT_DATE, LAST_ACT_TYP, LAST_ACT_USER, AIR_CODE, AMOUNT, BILLING_DETAIL_SEQ) values (?, ?, ?, ?, ?, ?)|insert into BILLING_DETAIL (LAST_ACT_DATE, LAST_ACT_TYP, LAST_ACT_USER, AIR_CODE, AMOUNT, BILLING_DETAIL_SEQ) values ('2013-01-27 01:21:11.226', 'ADD', '[e-mail]', '555', 1280, 3750)
Hibernate: 
    select
        billingdet0_.BILLING_DETAIL_SEQ as BILLING1_4_,
        billingdet0_.LAST_ACT_DATE as LAST2_4_,
        billingdet0_.LAST_ACT_TYP as LAST3_4_,
        billingdet0_.LAST_ACT_USER as LAST4_4_,
        billingdet0_.AIR_CODE as AIR5_4_,
        billingdet0_.AMOUNT as AMOUNT4_ 
    from
        BILLING_DETAIL billingdet0_ 
    where
        billingdet0_.AIR_CODE=?
[201301-27 01:21:11]|1|0|statement|select billingdet0_.BILLING_DETAIL_SEQ as BILLING1_4_, billingdet0_.LAST_ACT_DATE as LAST2_4_, billingdet0_.LAST_ACT_TYP as LAST3_4_, billingdet0_.LAST_ACT_USER as LAST4_4_, billingdet0_.AIR_CODE as AIR5_4_, billingdet0_.AMOUNT as AMOUNT4_ from BILLING_DETAIL billingdet0_ where billingdet0_.AIR_CODE=?|select billingdet0_.BILLING_DETAIL_SEQ as BILLING1_4_, billingdet0_.LAST_ACT_DATE as LAST2_4_, billingdet0_.LAST_ACT_TYP as LAST3_4_, billingdet0_.LAST_ACT_USER as LAST4_4_, billingdet0_.AIR_CODE as AIR5_4_, billingdet0_.AMOUNT as AMOUNT4_ from BILLING_DETAIL billingdet0_ where billingdet0_.AIR_CODE='781'
[201301-27 01:21:11]|-1||resultset|select billingdet0_.BILLING_DETAIL_SEQ as BILLING1_4_, billingdet0_.LAST_ACT_DATE as LAST2_4_, billingdet0_.LAST_ACT_TYP as LAST3_4_, billingdet0_.LAST_ACT_USER as LAST4_4_, billingdet0_.AIR_CODE as AIR5_4_, billingdet0_.AMOUNT as AMOUNT4_ from BILLING_DETAIL billingdet0_ where billingdet0_.AIR_CODE='781'|AIR5_4_ = 781, LAST3_4_ = MOD, LAST4_4_ = yinyj
[201301-27 01:21:11]|-1||resultset|select billingdet0_.BILLING_DETAIL_SEQ as BILLING1_4_, billingdet0_.LAST_ACT_DATE as LAST2_4_, billingdet0_.LAST_ACT_TYP as LAST3_4_, billingdet0_.LAST_ACT_USER as LAST4_4_, billingdet0_.AIR_CODE as AIR5_4_, billingdet0_.AMOUNT as AMOUNT4_ from BILLING_DETAIL billingdet0_ where billingdet0_.AIR_CODE='781'|AIR5_4_ = 781, LAST3_4_ = add, LAST4_4_ = aaa

 以上输出中包含了Hiberate生成的SQL语句，即以Hiberate：开头。而p6spy记录的日志格式如下：

current time|execution time|category|statement SQL String|effective SQL string

 

 3. spy.properties样例文件

 参考附件

 

4. 如何取消打印SQL

• 设置JPA/Hibernate的show_sql = false
• 将数据源文件中p6spy的JDBC Driver拿掉 

 5. 监控JBoss4.2数据源JDBC的配置

参见How to trace JDBC statements with JBoss AS：http://www.mastertheboss.com/index.php?option=com_content&task=view&id=259&Itemid=27