The Holy Java

Building the right thing, building it right, fast

A logging wrapper around PreparedStatement to provide detailed info upon error

Posted by Jakub Holý on May 23, 2009

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."); = 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())) {
        return result;
    private void handleSetSomething(int index, Object value) {
    private void handleAddBatch() {
        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("'", "''") + "'";
        return csv.toString();
    } /* getValuesAsCsv */
    public PreparedStatement createProxy() {
        return (PreparedStatement) Proxy.newProxyInstance(
                new Class[] { PreparedStatement.class },

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

About these ads

Sorry, the comment form is closed at this time.

%d bloggers like this: