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语句。