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