A logging wrapper around PreparedStatement to provide detailed info upon error

In my java web application I use JDBC to store data in batches into a database. When there is a problem the whole batch insert fails and it’s difficult to find out what data caused it to fail. Therefore I’ve created a wrapper around PreparedStatement that remembers values passed into the various set* methods and can provide a comma-separated listing of all rows in the batch upon failure.

This is my LoggingStatementDecorator that stores values for later logging; based on java.lang.reflect.Proxy:

package eu.ibacz.example;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.PreparedStatement;
import java.util.LinkedList;
import java.util.List;

/**
 * Remember values passed into a sql statement via setString etc. for later logging. 
 */
class LoggingStatementDecorator implements InvocationHandler {
    
    /** File's Subversion info (version etc.). */
    public static final String SVN_ID = "$id$";
    
    private List<List<Object>> batch = new LinkedList<List<Object>>();
    private List<Object> currentRow = new LinkedList<Object>();
    private PreparedStatement target;
    private boolean failed = false;
    
    public LoggingStatementDecorator(PreparedStatement target) {
        if (target == null) throw new IllegalArgumentException("'target' can't be null.");
        this.target = target;
    }


     // @see java.lang.reflect.InvocationHandler#invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object[]) */
    public Object invoke(Object proxy, Method method, Object[] args)
            throws Throwable {
        
        final Object result; 
        
        try {
            result = method.invoke(target, args);
            failed = false;
        } catch (InvocationTargetException e) {
            failed = true;
            throw e.getTargetException();
        } catch (Exception e) {
            failed = true;
            throw e;
        }
        
        if ( method.getName().startsWith("setNull") 
                && (args.length >=1 && Integer.TYPE == method.getParameterTypes()[0] ) ) {
            handleSetSomething((Integer) args[0], null);
        } else if ( method.getName().startsWith("set") 
                && (args.length >=2 && Integer.TYPE == method.getParameterTypes()[0] ) ) {
            handleSetSomething((Integer) args[0], args[1]);
        } else if ("addBatch".equals(method.getName())) {
            handleAddBatch();
        }
        
        return result;
    }
    
    private void handleSetSomething(int index, Object value) {
        currentRow.add(value);
    }
    
    private void handleAddBatch() {
        batch.add(currentRow);
        currentRow = new LinkedList<Object>();
    }
    
    public List<List<Object>> getValues() {
        return batch;
    }
    
    public PreparedStatement getTarget() { return target; }
    
    /** Has the last method called on the Statement caused an exception? */
    public boolean isFailed() { return failed; }
    
    public String toString() { return "LoggingHandler[failed="+failed+"]"; }
    
    /** Values as comma-separated values. */
    public String getValuesAsCsv() {
        StringBuilder csv = new StringBuilder();
        for (List<Object> row : getValues()) {
            for (Object field : row) {
                // Escape Strings
                if (field instanceof String) {
                    field = "'" + ((String) field).replaceAll("'", "''") + "'";
                }
                csv.append(field).append(",");
            }
            csv.append("\n");
        }
        return csv.toString();
    } /* getValuesAsCsv */
    
    public PreparedStatement createProxy() {
        return (PreparedStatement) Proxy.newProxyInstance(
                PreparedStatement.class.getClassLoader(),
                new Class[] { PreparedStatement.class },
                this);
    };
    
}

And this is how you use it:

        // ...
        PreparedStatement stmt = null;
        try {
            LoggingStatementDecorator stmtHandler = new LoggingStatementDecorator( connection.prepareStatement("insert into mytable values(?,?)") );
            stmt =  stmtHandler.createProxy();
            
            // add data to the batch
            for(int i=0; i<10; ++i) {
                stmt.setInt(1, i);
                stmt.setString(2, "Row number " + i);
                stmt.addBatch();
            }
            
            stmt.executeBatch();
            
        } catch (SQLException e) {
            // ... some rollback etc.
            
            LoggingStatementDecorator stmtHandler = (LoggingStatementDecorator)
                    ((stmt instanceof Proxy)? Proxy.getInvocationHandler(stmt) : null);
                // TODO include the insert sql in the log!!!
                StringBuilder log = new StringBuilder();
                log = buildFailureInfo("mytable", stmtHandler, log);
                LOG.error("Failure while processing data:" + log, e);
            }
        }
            

    private StringBuilder buildFailureInfo(String table, LoggingStatementDecorator stmtHandler, StringBuilder details) {
        
        if (stmtHandler != null && stmtHandler.isFailed()) {
            // Already insertion of records failed
            details.append("\nInsert of records failed. Table=").append(table)
                .append("), values=[\n").append(stmtHandler.getValuesAsCsv()).append("]");
            
        }
        
        return details;
    } /* buildFailureInfo */

When an excepion occures, you get nice log that shall tell you all you need to detect the problem or reproduce it.

Fotnote: Of course I could have perhaps used the open-source P6Spy but I’m afraid it would log more than I need (I believe it to be bound to a data source, not a particular webapp’s PreparedStatement).

Advertisement