P6spy监控打印SQL话语_SQL

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的配置非常简单：

修改应用的数据源文件，把JDBC Driver替换为P6spy Driver
将p6spy.jar包放到classpath下
设置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 Driverrealdriver=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', [email protected]', '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拿掉?