p6spy簡單測試

P6Spy是一個可以用來在應用程序中攔截和修改數據操作語句的開源框架。

下載地址：http://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22p6spy%22%20AND%20a%3A%22p6spy%22

建議下載zip包，里面jar比較全。

網上的幾個p6spy例子都比較老，2.0版以上spy.properties配置有些改動。

當前最新版是v2.1，入門和配置，建議看官網文檔： http://p6spy.github.io/p6spy/2.0/configandusage.html

sql輸出，默認就兩種：com.p6spy.engine.spy.appender.SingleLineFormat（單行）和 com.p6spy.engine.spy.appender.MultiLineFormat（多行），兩者沒多大區別，覺的不夠用的 自行擴展com.p6spy.engine.spy.appender.MessageFormattingStrategy。

執行P6spyTest1得到如下結果（多行格式--MultiLineFormat）：

#1403080487947 | took 63ms | statement | connection 0|select userid,username from t_user where id=?

select userid,username from t_user where id=1;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import com.p6spy.engine.spy.P6DataSource;
import com.p6spy.engine.spy.P6SpyDriver;


public class DBManager {
    static{
        initDatabase();
    }

    static String driverclass = "com.p6spy.engine.spy.P6SpyDriver";
//  static String url = "jdbc:mysql://192.168.2.54/test_sl?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
    static String url = "jdbc:p6spy:mysql://192.168.2.54/test_sl?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
    static String dbuser = "root";
    static String passwd = "123456";

    /**
     * 不需要<br/>
     * P6SpyDriver內已執行DriverManager.registerDriver(driver);
     */
    static void initDatabase(){     
//      try {
//          Driver driver = (Driver) Class.forName(driverclass).newInstance();
//          DriverManager.registerDriver(driver);
//      } catch (Exception e) {     
//          throw new RuntimeException(e);
//      }
    }

//  public static Connection getConnect() throws SQLException{
//      P6DataSource ds = new P6DataSource();
//      return ds.getConnection(dbuser, passwd);
//  }

    /**
     * 與getConnect()結果相同
     * @return
     * @throws SQLException
     */
    public static Connection getConnect1() throws SQLException{
        P6SpyDriver driver = new P6SpyDriver();     
        Properties prop = new Properties();
        prop.setProperty("user", "root");
        prop.setProperty("password", "hkgt123");        
        return driver.connect(url, prop);
//      return DriverManager.getConnection(url, prop);
    }

    public static Connection getConnect() throws SQLException{
        Properties prop = new Properties();
        prop.setProperty("user", "root");
        prop.setProperty("password", "hkgt123");
        return DriverManager.getConnection(url, prop);
    }
}