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