jakub holý

building the right thing, building it right, fast

A logging wrapper around PreparedStatement to provide detailed info upon error

2009-05-23Languages

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).