5
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【Java】SQLやSQL結果をログに出すためのプロキシ

Last updated at Posted at 2018-02-22

log4jdbcとかあるんで再発明ではあるんすけど…。
slf4j前提です。

こうするだけでプロキシが使える
conn = ConnectionLogProxy.createProxy(conn);

こんな感じでログが出ます

2018-02-22 18:58:10:123 INFO ConnectionLogProxy - 
### [INFO] query ###
com.mysql.jdbc.JDBC4PreparedStatement@16d2da0: SELECT * from userTbl
### [TRACE] result ###
userId	userName
100	taro
200	yamad

### [INFO] execute time ###
0.91824 ms

ログを出すプロキシクラス

import java.io.IOException;
import java.io.StringWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/** SQLログを出力するコネクションのプロキシ */
public class ConnectionLogProxy {
    /** SELECT結果のレコードをログに出力する最大件数 */
    private static final int MAX_LOG_RECORD = 100;

    private Connection connection;

    private final static Logger LOGGER = LoggerFactory.getLogger(ConnectionLogProxy.class);

    /**
     * プロキシ作成.
     */
    public static Connection createProxy(Connection connection) {
        ConnectionLogProxy proxy = new ConnectionLogProxy();
        proxy.connection = connection;
        return Connection.class.cast(Proxy.newProxyInstance(
                Connection.class.getClassLoader(),
                new Class[]{ Connection.class },
                proxy.new ConnectionHandler()));
    }

    private class ConnectionHandler implements InvocationHandler {

        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            Object obj = method.invoke(connection, args);

            if (obj instanceof PreparedStatement) {
                obj = new Delegate<PreparedStatement>(PreparedStatement.class.cast(obj), PreparedStatement.class).proxy;
            } else if (obj instanceof Statement) {
                obj = new Delegate<Statement>(Statement.class.cast(obj), Statement.class).proxy;
            }

            return obj;
        }
    }

    private class Delegate<T extends Statement> implements InvocationHandler {

        private T original;
        private Object proxy;

        private Delegate(T original, Class<T> clazz) {
            this.original = original;
            this.proxy = Proxy.newProxyInstance(
                clazz.getClassLoader(),
                new Class[]{ clazz },
                this);
        }

        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            Class<?>[] paramTypes = method.getParameterTypes();

            long start = System.nanoTime();
            if ( "executeUpdate".equals(method.getName()) ){
                String query;
                if (paramTypes.length > 0) {
                    query = original + ": " + args[0];
                } else {
                    query = original + "";
                }
                Object resObj = method.invoke(original, args);
                long end = System.nanoTime();
                LOGGER.info("\n### [INFO] query ###\n{}\n### [INFO] execute time ###\n{} ms", query, Integer.parseInt("" + resObj), (end - start) / 1000000f );
                return resObj;
            }
            if( "executeQuery".equals(method.getName()) ) {
                Object resObj = method.invoke(original, args);
                ResultSet rs = ResultSet.class.cast(resObj);

                long end = System.nanoTime();
                //トレースログがONになっているときのみ、SQL結果が出ます
                if( LOGGER.isTraceEnabled() ) {
                    String csv = rsToCSV(rs);
                    LOGGER.info("\n### [INFO] query ###\n{}\n### [TRACE] result ####\n{}\n### [INFO] execute time ###\n{}ms",
                            original, csv, (end - start) / 1000000f );
                }else{
                    LOGGER.info("\n### [INFO] query ###\n{}\n### [INFO] execute time ###\n{} ms",
                            original, (end - start) / 1000000f );
                }
                return rs;
            }
            return method.invoke(original, args);
        }
    }

    /** ResultSetをCSVにして返す */
    private String rsToCSV(ResultSet rs) throws SQLException, IOException {    
        ResultSetMetaData metaData = rs.getMetaData();    
        int columnCount = metaData.getColumnCount();    
        try (StringWriter pw = new StringWriter()) {
            final String SEPARATOR = "\t";            
            for (int i = 1; i <= columnCount; i++) {    
                pw.write(metaData.getColumnName(i));        
                if (i < columnCount) {
                    pw.write(SEPARATOR);
                    pw.flush();
                }    
                if (i == columnCount) {
                    pw.write("\n");
                    pw.flush();
                }
            }    
            int rowCnt = 0;
            while (rs.next() ) {
                if( rowCnt >= MAX_LOG_RECORD ) {
                    pw.write("ommit over " + MAX_LOG_RECORD + " record log...\n");
                    pw.flush();
                    break;
                }    
                for (int i = 1; i <= columnCount; i++) {
                    Object val = rs.getObject(i);
                    if( null == val ){
                        pw.write(""+val);
                    }else{
                        pw.write(val.toString().replaceAll("\r", "\\r").replaceAll("\n", "\\n"));
                    }        
                    if (i < columnCount) {
                        pw.write(SEPARATOR);
                        pw.flush();
                    }        
                    if (i == columnCount) {
                        pw.write("\n");
                        pw.flush();
                    }
                }
                rowCnt++;
            }
            rs.first();
            rs.previous();
            return pw.toString();
        }
    }

}
5
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?