用 p6spy 来观察 Java 程序中执行的所有 SQL 语句

既然提到 p6spy 的输出，那就有必要说明一下 p6spy 输出日志的格式了。从上一篇 用 p6spy 来观察 Java 程序中执行的所有 SQL 语句(二. Tomcat 下的配置） 中把输出的一段内容拿过来，如下：

03-16-09 15:12:06:656|16|4|statement|SELECT * FROM OM_CUSTOMERS　 WHERE CUSTOMER_ID=? ORDER BY CUSTOMER_ID ASC|SELECT * FROM OM_CUSTOMERS　 WHERE CUSTOMER_ID=2194 ORDER BY CUSTOMER_ID ASC
03-16-09 15:12:06:671|15|3|statement|SELECT * FROM OM_ORDER_TYPE WHERE TYPE_ID=?|SELECT * FROM OM_ORDER_TYPE WHERE TYPE_ID=25
03-16-09 15:12:06:687|16|1|statement|select * from sys_lookups where lookup_type=?　 and lookup_code=? |select * from sys_lookups where lookup_type='OM_ORDER_STATUS'　 and lookup_code='70'
03-16-09 15:12:06:812|-1||resultset|select * from sys_lookups where lookup_type='OM_ORDER_STATUS'　 and lookup_code='70' |meaning = 已安排生产

　　再看 p6spy 官方文档的关于日志文件(控制台输出/Log4J也一样)格式的说明 -- http://www./documentation/other.htm#log。日志格式是：

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

　　current time -- 当前时间

　　execution time -- 执行时长，包括执行 SQL 和处理结果集的时间(可以参考来调优)

　　category　 -- 语句分类，statement、resultset 等

　　statement SQL string -- 查询语句。可能是 prepared statement，表现为 select * from table1 where c1=?，问号参数形式

effective SQL string -- 代入参数值的查询语句，如 select * from from table1 where c1=7

　　看到上面的日志输出，我们可能会有如下需求：

　　1) 对于 category 为 resultset 的输出你可能并不关心，查询了什么字段，取哪个字段或许早心理有数。上面例子中的 resultset 是 select *，然而只取了 meaning 一个字段，这是不推荐的。(不输出 resultset 语句)

　　2) 你可能不想被那些带问号参数的 prepared statement 干扰，而想直接看最终被执行的语句。(statement SQL string 不显示)

　　3) 你可能会想把控制台或日志文件中的一连串几个语句直接复制，贴到数据库客户端就能执行。(只输出 effective SQL string，并以分号隔开)

　　对于第一个要求，我们可以利用 p6spy 的显示过滤功能，可在 p6spy.properties 中配置。p6spy 有 resultset 这样一个 category，却未完善对其的过滤控制，为此我们需要修改源代码 com.p6spy.engine.spy.P6ResultSet.Java，找到 152 行的

P6LogQuery.log("resultset", query, buffer.toString());

　　把其改为

//P6LogQuery.log("resultset", query, buffer.toString());　　
P6Connection p6connection = (P6Connection)this.statement.getConnection();　　
P6LogQuery.logElapsed(p6connection.getId(), System.currentTimeMillis(),"resultset", preparedQuery, query);　

　　编译(最好用 1.4 或 1.5 的JDK 来编译，因为 JDK 1.6 的 ResultSet 多了些要实现的方法，修改起来要麻烦很多)，把该类替换掉原来 p6spy.jar 中的相应 class。编译好的 FormattedLogger 在附件 changed_p6spy_classes.rar 中有，是 P6ResultSet.class 文件。

然后修改 p6spy.properties 文件，找到

excludecategories=info,debug,result,batch

　　加上对 resultset 的排除，改为

excludecategories=info,debug,result,batch,resultset

　　这样，在输出的语句中就没有眼花缭乱的 resultset 语句了，清爽了许多。

　　对于第二个要求，我们还要修改的是源代码 com.p6spy.engine.logging.appender.FormattedLogger，在 72 行找到

String logEntry = now + "|"+ elapsed + "|"+(connectionId==-1 ? "" : String.valueOf(connectionId))+"|"+category+"|"+prepared+"|"+sql;

　　我们不希望输出 prepared，所以把它改为

String logEntry = now + "|"+ elapsed + "|"+(connectionId==-1 ? "" : String.valueOf(connectionId))+"|"+category+"|"+sql;

　　编译，替换掉原 p6spy.jar 中相应类，编译好的 FormattedLogger 是附件 changed_p6spy_classes.rar 中有，是 FormattedLogger.class 文件。

　　要满足第三个条件，还是修改 com.p6spy.engine.logging.appender.FormattedLogger 的同一行代码，改为

String logEntry = sql + ";";

　　编译，替换掉原 p6spy.jar 中相应类，编译好的 FormattedLogger 是附件 changed_p6spy_classes.rar 中有，是 FormattedLogger1.class 文件，替换的时候请更名。那么由它指示输出的 SQL 语句就是以分号分隔的一条条了，基本是连续几条一起复制出来，放到 SQL 客户端工具中就能执行了。当然字符串的参数还是要稍加处理的。