SQLをspyする p6spy を使ってみる

java sql

インストール

http://www.p6spy.com/ から p6spy-install.jar をダウンロードして解凍する

mysql

>rem mysqlがデフォなのでmysqlでためす
>dir /B
p6spy.jar
spy.properties
mysql.jar
TestMySQL.java

>rem 簡単な検証サンプル
>type TestMySQL.java
import java.sql.*;

public class TestMySQL {

	public static void main(String[] args) throws Exception {
		Class.forName("com.p6spy.engine.spy.P6SpyDriver"); 
//		Class.forName("org.gjt.mm.mysql.Driver"); 
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "",""); 

		Statement stmt = con.createStatement(); 
		String sql = "SELECT * FROM test";
		stmt.executeQuery(sql);
		for (int i = 0; i < 3; i++) {
			PreparedStatement preparedStatement = con.prepareStatement("select * from test where a = ?");
			preparedStatement.setString(1, Integer.toString(i));;
			preparedStatement.executeQuery();
		}
	}

}

>javac *.java

>java -classpath .;p6spy.jar;mysql.jar TestMySQL

>type spy.log
1113831149286|-1||debug||com.p6spy.engine.common.P6SpyOptions reloading properties
1113831149306|-1||info||Using properties file: E:\eclipse\workspace\p6spy\spy.properties
1113831149306|-1||info||No value in environment for: getDeregisterDrivers, using: false
1113831149306|-1||info||No value in environment for: getUsePrefix, using: false
1113831149306|-1||info||No value in environment for: getExecutionThreshold, using: 0
1113831149306|-1||info||No value in environment for: getAutoflush, using: true
1113831149306|-1||info||No value in environment for: getExclude, using: 
1113831149306|-1||info||No value in environment for: getExcludecategories, using: info,debug,result,batch
1113831149306|-1||info||No value in environment for: getInclude, using: 
1113831149306|-1||info||No value in environment for: getIncludecategories, using: 
1113831149306|-1||info||No value in environment for: getLogfile, using: spy.log
1113831149306|-1||info||No value in environment for: getAppender, using: com.p6spy.engine.logging.appender.FileLogger
1113831149316|-1||info||No value in environment for: getRealdriver, using: org.gjt.mm.mysql.Driver
1113831149316|-1||info||No value in environment for: getRealdriver2, using: 
1113831149316|-1||info||No value in environment for: getRealdriver3, using: 
1113831149316|-1||info||No value in environment for: getAppend, using: true
1113831149316|-1||info||No value in environment for: getSpydriver, using: com.p6spy.engine.spy.P6SpyDriver
1113831149316|-1||info||No value in environment for: getDateformat, using: null
1113831149316|-1||info||No value in environment for: getDateformatter, using: null
1113831149316|-1||info||No value in environment for: getStringmatcher, using: com.p6spy.engine.common.SubstringMatcher
1113831149316|-1||info||No value in environment for: getStringMatcherEngine, using: com.p6spy.engine.common.SubstringMatcher@e09713
1113831149316|-1||info||No value in environment for: getStackTraceClass, using: 
1113831149316|-1||info||No value in environment for: getSQLExpression, using: null
1113831149316|-1||info||No value in environment for: getReloadProperties, using: false
1113831149316|-1||info||No value in environment for: getReloadPropertiesInterval, using: 60
1113831149316|-1||info||No value in environment for: getJNDIContextFactory, using: null
1113831149316|-1||info||No value in environment for: getJNDIContextProviderURL, using: null
1113831149316|-1||info||No value in environment for: getJNDIContextCustom, using: null
1113831149316|-1||info||No value in environment for: getRealDataSource, using: null
1113831149316|-1||info||No value in environment for: getRealDataSourceClass, using: null
1113831149316|-1||info||No value in environment for: getRealDataSourceProperties, using: null
1113831149316|-1||info||No value in environment for: getStackTrace, using: false
1113831149316|-1||info||No value in environment for: getFilter, using: false
1113831149476|10|0|statement||SELECT * FROM test
1113831149486|0|0|statement|select * from test where a = ?|select * from test where a = '0'
1113831149486|0|0|statement|select * from test where a = ?|select * from test where a = '1'

>rem なんかログですぎ、時刻も変だ。spy.properties をカスタマイズする
>diff spy.properties spy.properties.orig
132c132
< dateformat=yyyy/MM/dd
---
> dateformat=
172c172
< append=false
---
> append=true

>java -classpath .;p6spy.jar;mysql.jar TestMySQL

>type spy.properties
2005/04/18|10|0|statement||SELECT * FROM test
2005/04/18|0|0|statement|select * from test where a = ?|select * from test where a = '0'
2005/04/18|0|0|statement|select * from test where a = ?|select * from test where a = '1'

>type TestMySQL2.java
import java.sql.*;

public class TestMySQL2 {

	public static void main(String[] args) throws Exception {
		Class.forName("com.p6spy.engine.spy.P6SpyDriver"); 
//		Class.forName("org.gjt.mm.mysql.Driver"); 
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "",""); 

		Statement stmt = con.createStatement(); 
		String sql ;

		sql = "create table hoge (a varchar(10))";
		stmt.execute(sql);

		sql = "insert into hoge (a) VALUES('insert')";
		stmt.executeUpdate(sql);

		sql = "update hoge set a = 'update'";
		stmt.executeUpdate(sql);

		sql = "delete from hoge where a = 'update'";
		stmt.executeUpdate(sql);

		sql = "select * from hoge";
		stmt.executeQuery(sql);

		sql = "drop table hoge";
		stmt.execute(sql);
	}

}

>javac *.java

>java -classpath .;p6spy.jar;mysql.jar TestMySQL2

>rem もちろん create,insert,update,delete,select,drop も見れる
>type spy.log
2005/04/18|50|0|statement||create table hoge (a varchar(10))
2005/04/18|0|0|statement||insert into hoge (a) VALUES('insert')
2005/04/18|0|0|statement||update hoge set a = 'update'
2005/04/18|0|0|statement||delete from hoge where a = 'update'
2005/04/18|0|0|statement||select * from hoge
2005/04/18|0|0|statement||drop table hoge

エラー時対処

>rem spy.properties を見れなくする
>rename spy.properties spy.properties.back
Exception in thread "main" java.sql.SQLException: No suitable driver
        at java.sql.DriverManager.getConnection(DriverManager.java:532)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at TestDB.main(TestDB.java:8)
>rename spy.properties.back spy.properties

>rem 本当のドライバーを見れなくする
>java -classpath .;p6spy.jar TestDB
Warning: Error registering driver  [org.gjt.mm.mysql.Driver]
Caused By: java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver
Exception in thread "main" com.p6spy.engine.spy.P6DriverNotFoundError: Error reg
istering driver  [org.gjt.mm.mysql.Driver]
Caused By: java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver
        at com.p6spy.engine.spy.P6SpyDriverCore.initMethod(P6SpyDriverCore.java:
289)
        at com.p6spy.engine.spy.P6SpyDriver.initMethod(P6SpyDriver.java:78)
        at com.p6spy.engine.spy.P6SpyDriver.<clinit>(P6SpyDriver.java:74)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:141)
        at TestDB.main(TestDB.java:6)

HSQLDBで試してみる

HSQLDB は pureJava なのでさらに簡単にとることができる。
HSQLDB自体にSQLだけをとる機能が提供されているかもしれませんが。

>dir /B
hsqldb.jar
p6spy.jar
spy.properties

>diff spy.properties spy.properties.orig
54c54
< realdriver=org.hsqldb.jdbcDriver
---
> realdriver=org.gjt.mm.mysql.Driver
132c132
< dateformat=yyyy/MM/dd
---
> dateformat=
172c172
< append=false
---
> append=true

>rem データベースマネージャを起動
>start java -cp .;p6spy.jar;hsqldb.jar org.hsqldb.util.DatabaseManager
  • Driverのところを com.p6spy.engine.spy.P6SpyDriver に書き換えてOKボタンを押す(In Memory でいいでしょう)。
  • create table hoge (a varchar(10)); を入力して Executeボタンを押す。
  • select * from hoge;
>type spy.log
2005/04/18|10|0|statement||create table hoge (a varchar(10));
2005/04/18|0|0|statement||select * from hoge;

Tomcatから実行(Tomcat5.0.28+MySQL4.0.21)

  • %TOMCAT_HOME%\bin に spy.properties を置く
  • 解凍したファイルのなかから p6spy.war を %TOMCAT_HOME%\webapps にコピーしてTomcatを起動
  • %TOMCAT_HOME%\webapps\p6spy に test.jsp と WEB-INF\lib に mysql.jar を置く
  • %TOMCAT_HOME%\webapps\p6spy の構成は以下のようになる
>tree /f
│  p6spy.jsp
│  test.jsp
├─META-INF
│      MANIFEST.MF
└─WEB-INF
    │  web.xml
    └─lib
            mysql.jar
            p6spy.jar

>rem Javaで作ったものを単純にjspにしただけ。
>type test.jsp
<%@ page import="java.sql.*" %>
<%
Class.forName("com.p6spy.engine.spy.P6SpyDriver"); 
//		Class.forName("org.gjt.mm.mysql.Driver"); 
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "",""); 

Statement stmt = con.createStatement(); 
String sql = "SELECT * FROM test";
stmt.executeQuery(sql);
for (int i = 0; i < 3; i++) {
	PreparedStatement preparedStatement = con.prepareStatement("select * from test where a = ?");
	preparedStatement.setString(1, Integer.toString(i));;
	preparedStatement.executeQuery();
}
%>

http://localhost:8080/p6spy/test.jsp でアクセスすると %TOMCAT_HOME%\bin\spy.log に通常のJava版と同じようにSQLを取れる。