SQL Tracing

在 Proxool 网页中看到 Proxool 提供 SQL Tracing 的功能。
不过以 Log 设定的方式一直没试成功。那也没关系，反正写程序一样能 Tracing

包装 Proxool 为 ConnectionProvider
并有 enableSqlTracing 与 disableSqlTracing 的功能

 

package connection.pool;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.logging.FileHandler;

import java.util.logging.Logger;

import org.logicalcobwebs.proxool.ConnectionListenerIF;

import org.logicalcobwebs.proxool.ProxoolException;

import org.logicalcobwebs.proxool.ProxoolFacade;

import org.logicalcobwebs.proxool.configuration.PropertyConfigurator;

class ConnectionListenerIFImpl implements ConnectionListenerIF {

  private static Logger log = Logger.getLogger("ConnectionListenerIFImpl");

  public ConnectionListenerIFImpl() {

  }

  public ConnectionListenerIFImpl(FileHandler f) {

    // add fh as you want

  }

  public void onBirth(Connection arg0) throws SQLException {

  }

  public void onDeath(Connection arg0) throws SQLException {

  }

  public void onExecute(String sql, long ms) {

    log.info(sql + "(" + ms + ")");

  }

  public void onFail(String arg0, Exception arg1) {

  }

}

public class ConnectionProvider {

  private static Logger log = Logger.getLogger(ConnectionProvider.class

      .getName());

  private static ConnectionListenerIF connectionListener = new ConnectionListenerIFImpl();

  private static ConnectionProvider self = new ConnectionProvider();

  public static ConnectionProvider getInstance() {

    return self;

  }

  /**

   * Constructor is private. you can get an instance by getInstance method

   */

  private ConnectionProvider() {

    try {

      Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");

      PropertyConfigurator.configure("Proxool.properties");

    } catch (ProxoolException e) {

      log.severe("Proxool driver is broken");

    } catch (ClassNotFoundException e) {

      log.severe("Cannot found driver class");

    }

  }

  /**

   * @return connection Get a database connection by proxool

   */

  public Connection getConnection() {

    Connection c = null;

    try {

      c = DriverManager.getConnection("proxool.db");

    } catch (SQLException e) {

      log.severe("Cannot get a connection");

    }

    return c;

  }

  /**

   * enable the sql tracing function

   */

  public void enableSqlTracing() {

    try {

      ProxoolFacade.addConnectionListener("db", connectionListener);

    } catch (ProxoolException e) {

      // TODO Auto-generated catch block

      e.printStackTrace();

    }

  }

  /**

   * disable the sql tracing function

   */

  public void disableSqlTracing() {

    try {

      ProxoolFacade.removeConnectionListener("db", connectionListener);

    } catch (ProxoolException e) {

      // TODO Auto-generated catch block

      e.printStackTrace();

    }

  }

}

 

联机所使用的设定档：

jdbc-0.proxool.alias=db

jdbc-0.proxool.driver-url=jdbc:hsqldb:hsql://localhost:16384/mydb

jdbc-0.proxool.driver-class=org.hsqldb.jdbcDriver

jdbc-0.user=SA

jdbc-0.password=

 

PS. 主要焦点放在 sql tracing。若您真的要使用这个小工具，可以将联机部分改成比较有弹性的实作方式。

写个程序测看看：

package connection.pool;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

public class TestP {

  public static void main(String[] args) throws SQLException{

    ConnectionProvider cp = ConnectionProvider.getInstance();

    Connection c = cp.getConnection();

    Statement s = c.createStatement();

    s.executeQuery("select * from books");

    cp.enableSqlTracing();

    s.executeQuery("select * from books");

    cp.disableSqlTracing();

    s.executeQuery("select * from books");

    s.close();

  }

}