How to get parameters from PreparedStatement?

Question

I'm writing generic logger for SQLException and I'd like to get parameters that were passed into PreparedStatement, how to do it ? I was able to get the count of them.

ParameterMetaData metaData = query.getParameterMetaData();
parameterCount = metaData.getParameterCount();

Twyla

Answer 1

Short answer: You can't.

Long answer: All JDBC drivers will keep the parameter values somewhere but there is no standard way to get them.

If you want to print them for debugging or similar purposes, you have several options:

1. Create a pass-through JDBC driver (use p6spy or log4jdbc as a basis) which keeps copies of the parameters and offers a public API to read them.

2. Use Java Reflection API (Field.setAccessible(true) is your friend) to read the private data structures of the JDBC drivers. That's my preferred approach. I have a factory which delegates to DB specific implementations that can decode the parameters and that allows me to read the parameters via getObject(int column).

3. File a bug report and ask that the exceptions are improved. Especially Oracle is really stingy when it comes to tell you what's wrong.

Hauteloire

Answer 2

Solution 1: Subclass

Simply create a custom implementation of a PreparedStatement which delegates all calls to the original prepared statement, only adding callbacks in the setObject, etc. methods. Example:

public PreparedStatement prepareStatement(String sql) {
        final PreparedStatement delegate = conn.prepareStatement(sql);
        return new PreparedStatement() {
            // TODO: much more methods to delegate

            @Override
            public void setString(int parameterIndex, String x) throws SQLException {
                // TODO: remember value of X
                delegate.setString(parameterIndex, x);
            }
        };
    }

If you want to save parameters and get them later, there are many solutions, but I prefer creating a new class like ParameterAwarePreparedStatement which has the parameters in a map. The structure could be similar to this:

public class ParameterAwarePreparedStatement implements PreparedStatement {
    private final PreparedStatement delegate;
    private final Map<Integer,Object> parameters;

    public ParameterAwarePreparedStatement(PreparedStatement delegate) {
        this.delegate = delegate;
        this.parameters = new HashMap<>();
    }

    public Map<Integer,Object> getParameters() {
        return Collections.unmodifiableMap(parameters);
    }

    // TODO: many methods to delegate

    @Override
    public void setString(int parameterIndex, String x) throws SQLException {
        delegate.setString(parameterIndex, x);
        parameters.put(parameterIndex, x);
    }
}

Solution 2: Dynamic proxy

This second solution is shorter, but seems more hacky.

You can create a dynamic proxy by calling a factory method on java.lang.reflect.Proxy and delegate all calls on the original instance. Example:

public PreparedStatement prepareStatement(String sql) {
    final PreparedStatement ps = conn.prepareStatement(sql);
    final PreparedStatement psProxy = (PreparedStatement) Proxy.newProxyInstance(ClassLoader.getSystemClassLoader(), new Class<?>[]{PreparedStatement.class}, new InvocationHandler() {
        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            if (method.getName().equals("setLong")) {
                // ... your code here ...
            }
            // this invokes the default call
            return method.invoke(ps, args);
        }
    });
    return psProxy;
}

Then you intercept the setObject, etc. calls by looking at method names and looking to the second method arguments for your values.

Ursal