java跟踪执行的sql语句示例分享

代码：

package com.lwj.test.proxy;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.SQLException;

public class DBManager {
    private final static ThreadLocal<Connection> conns = new ThreadLocal<Connection>();
    private static boolean show_sql = true;

    public final static Connection getConnection() throws SQLException {
        Connection conn = (Connection) conns.get();
        if(conn ==null || conn.isClosed()){
            // 这里使用我定义的一个简单的 ConnectionProvider 替代 dataSource 获取Connection
            conn = ConnectionProvider.getConnection();
            conns.set(conn);
        }
        return (show_sql && !Proxy.isProxyClass(conn.getClass()))?
                      new _DebugConnection(conn).getConnection():conn;
    }

    /**
     * 关闭连接
     */
    public final static void closeConnection() {
        Connection conn = (Connection) conns.get();
        try {
            if(conn != null && !conn.isClosed()){
                conn.setAutoCommit(true);
                conn.close();
            }
        } catch (SQLException e) {
        }
        conns.set(null);
    }

    /**
     * 用于跟踪执行的SQL语句
     */
    static class _DebugConnection implements InvocationHandler {
        private Connection conn = null;

        public _DebugConnection(Connection conn) {
            this.conn = conn;
        }
        public Connection getConnection() {
            return (Connection) Proxy.newProxyInstance(conn.getClass().getClassLoader(),new Class[]{Connection.class}, this);
        }
        public Object invoke(Object proxy, Method m, Object[] args) throws Throwable
        {
            try
            {
                String method = m.getName();
                if("prepareStatement".equals(method) || "createStatement".equals(method))
                {
                    System.out.println(method);
                    System.out.println(args[0]);
                }
                return m.invoke(conn, args);
            } catch (InvocationTargetException e) {
                throw e.getTargetException();
            }
        }
    }
}

package com.lwj.test.proxy;

import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectionProvider {
public static Connection getConnection()
    {
Connection connection = null;
       try{

           Class.forName("oracle.jdbc.OracleDriver").newInstance();
           connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.101:1521:orcl", "scott", "tiger");
       }catch(Exception e){
       }
       return connection;
    }
}

package com.lwj.test.proxy;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestMain {

public static void main( String[] args )
    {
            Connection conn = null;
            Statement stmt = null;
            PreparedStatement pstmt = null;
            try
            {
                conn = DBManager.getConnection();

                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                stmt.executeUpdate( "insert into test1(id,name,card,age,address) values(9,'liuwj','1234567890988777',24,'hubeitianmen')" );

                /*pstmt = conn.prepareStatement( "insert into test1(id,name,card,age,address) values(?,?,?,?,?)");
                pstmt.setString(1, "10");
                pstmt.setString(2, "liuwj2");
                pstmt.setString(3, "1234567890988777");
                pstmt.setString(4, "22");
                pstmt.setString(5, "123456");
                pstmt.execute();*/
            }catch(SQLException e){

            }finally{
                 try{
                   if( pstmt != null ){
                    pstmt.close();
                    pstmt = null;
                   }
                 }catch(SQLException e){

                 }

                 DBManager.closeConnection();
            }
    }
}

论坛上看到用下列语句：

复制代码代码如下:

pstmt = conn.prepareStatement( "insert into test1(id,name,card,age,address) values(?,?,?,?,?)"); 
pstmt.setString(1, "10");
pstmt.setString(2, "liuwj2");
pstmt.setString(3, "1234567890988777");
pstmt.setString(4, "22");
pstmt.setString(5, "123456");
pstmt.execute();

才能打印出sql语句。