[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 ###
[email protected]: 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(
                new Class[]{ Connection.class },
                proxy.new ConnectionHandler()));

    private class ConnectionHandler implements InvocationHandler {

        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(
                new Class[]{ clazz },

        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 );
                    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++) {    
                if (i < columnCount) {
                if (i == columnCount) {
            int rowCnt = 0;
            while (rs.next() ) {
                if( rowCnt >= MAX_LOG_RECORD ) {
                    pw.write("ommit over " + MAX_LOG_RECORD + " record log...\n");
                for (int i = 1; i <= columnCount; i++) {
                    Object val = rs.getObject(i);
                    if( null == val ){
                        pw.write(val.toString().replaceAll("\r", "\\r").replaceAll("\n", "\\n"));
                    if (i < columnCount) {
                    if (i == columnCount) {
            return pw.toString();


Recommended Posts

[Java] Proxy for logging SQL and SQL results
Java while and for statements
AWS SDK for Java 1.11.x and 2.x
Java for beginners, expressions and operators 1
Java for beginners, expressions and operators 2
Classes and instances Java for beginners
[For beginners] Difference between Java and Kotlin
[About JDBC that connects Java and SQL]
For JAVA learning (2018-03-16-01)
I studied for 3 weeks and passed Java Bronze
Java Dynamic Proxy
2017 IDE for Java
Kantai Collection Java # 1 Classes and Objects [For Beginners]
Java and JavaScript
XXE and Java
Java for statement
Sample (Java) for OAuth 2.0 authentication and access token acquisition
Learn for the first time java # 3 expressions and operators
This and that for editing ini in Java. : inieditor-java
List of frequently used Java instructions (for beginners and beginners)
Prepare the environment for java11 and javaFx with Ubuntu 18.4
[Java] for statement, while statement
Getters and setters (Java)
[Java] Thread and Runnable
[Java] Package for management
Java true and false
[Java] for statement / extended for statement
[Java] String comparison and && and ||
Countermeasures for Java OutOfMemoryError
NLP for Java (NLP4J) (2)
Java --Serialization and Deserialization
[Java] Arguments and parameters
(Memo) Java for statement
NLP for Java (NLP4J) (1)
[Java] Branch and repeat
[Java] Variables and types
java (classes and instances)
[Java] Overload and override
Store in Java 2D map and turn with for statement
[For beginners] Explanation of classes, instances, and statics in Java
[Java] Make variables in extended for statement and for Each statement immutable
Problems that can easily be mistaken for Java and JavaScript
Difficult to handle minimum values for Java8 LocalDateTime and Java6 Date
Tips for using Salesforce SOAP and Bulk API in Java
Eclipse installation and code completion enhancements (Mac for Java development)
Tools and commands that may be useful for Java troubleshooting
Java sets the background color and background image for PowerPoint documents