How to get values of bind parameters from Oracle JDBC PreparedStatement object

Question

I want to implement logging of all executed statements with actual bind parameters when using Oracle JDBC. And I would prefer that I could create such logging method only passing PreparedStatement object as parameter.

For example I have created PreparedStatement and have bound one parameter

PreparedStatement ps = conn.prepareStatement(
    "SELECT * FROM employees WHERE employee_id = ?");
ps.setInt(1,1);

Now I would like to be able to get from ps the actual SQL statement "SELECT * FROM employees WHERE employe_id = 1" that I could put in log file.

So far I found that I can use

((oracle.jdbc.driver.OracleStatement) ps).getOriginalSql()

to get

SELECT * FROM employees WHERE employe_id = ?

Now I need some way to get the list of current bind variables from ps so that I could replace ? with bind parameter values.

I tried to look in ps.getClass().getDeclaredFields() and ps.getClass().getSuperclass().getDeclaredFields() but so far couldn't find the place where bind parameter values and their types are stored.

Any suggestions where to look for them?

Answer 1

Most logging framework have the notion for Nested Diagnostic Context. You could save your query and its parameters there when you fill up the prepared statement.

Or, perhaps, do it in one step:

PreparedStatement fillAndLog(Connection conn, String query, Object... args) {
    int i = 0;
    PreparedStatement pstmt = conn.prepareStatement(query);
    for (Object o : args) {
       if (o instanceof String) {
           pstmt.setString(i, (String)o);
       } // else...
       i++;
    }
    log.debug(String.format(query.replaceAll("\\?", "%s"), args));
    return pstmt;
}

Answer 2

You can have a look at p6spy , it's a proxy to your database driver that allows monitoring and logging.

Answer 3

Hi,

I always have the same problem, and I have to write a proxy class to do it.

This is a very annoying and old issue :(