[Java] Proxy for logging SQL and SQL results

It's a reinvention because there is log4jdbc, but ... It is a prerequisite for slf4j.

You can use the proxy just by doing this

conn = ConnectionLogProxy.createProxy(conn);

The log will come out like this

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

Proxy class to log

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;

/**Proxy for connections that output SQL logs*/
public class ConnectionLogProxy {
    /**Maximum number of SELECT result records to be output to the log*/
    private static final int MAX_LOG_RECORD = 100;

    private Connection connection;

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

    /**
     *Proxy creation.
     */
    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();
                //SQL results are available only when the trace log is ON
                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);
        }
    }

    /**Return ResultSet as 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();
        }
    }

}