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();
  
  

  }
  
  

}