Logging SQL for Prepared Statement with C3P0

The C3P0 database connection pooling library has worked very well for me, but I recently ran into a problem when I wanted to log the SQL that was being generated for a PreparedStatement if an insert failed. Then, if database problems caused inserts to fail beyond an automated retry period, I could easily harvest the SQL statements from the log file and retry them later. Also, if a data problem caused the inserts to fail, I could harvest the statements from the log, use a script to adjust the data and then retry them.

C3P0 wraps the JDBC driver’s PreparedStatement class with its C3P0ProxyStatement. Unfortunately, it doesn’t override toString() to return something useful. So, you just get the default of a class name and the hash code for the instance. Not helpful.

Unfortunately, there is nothing in the API that gives you an obvious means of getting at the real PreparedStatement, which I can see in the debugger inside a private variable named inner. I knew that the rawStatementOperation() method might be the key, but it wasn’t obvious. However, a bit of googling turned up a solution in the unit test code for C3P0.

Unit tests are awesome. Not just for testing your code, but also for providing example code.

So, here’s some example code. First, I created a simple table in the test database of a local MySQL install.

create table rs (a int);

And here’s some code that connects to the database, sets up a prepared statement and then extracts the SQL from the prepared statement with the parameters bound:

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.mchange.v2.c3p0.C3P0ProxyStatement;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Demo {

  public static void main(String[] args) throws Exception {

    // Get a connection
    String url = "jdbc:mysql://localhost:3306/test";
    ComboPooledDataSource ds = new ComboPooledDataSource();
    ds.setDriverClass("com.mysql.jdbc.Driver");
    ds.setJdbcUrl(url);
    ds.setUser("myuser");
    ds.setPassword("mypassword");
    Connection conn = ds.getConnection();

    // Prepare the PreparedStatement
    PreparedStatement ps = conn.prepareStatement("insert into rs values (?)");
    ps.setInt(1, 10);

    // Extract the SQL
    String sql = "";
    try {
      C3P0ProxyStatement c3p0Stmt = (C3P0ProxyStatement) ps;
      Method toStringMethod = Object.class.getMethod("toString", new Class[] {});
      Object toStr = c3p0Stmt.rawStatementOperation(toStringMethod,
          C3P0ProxyStatement.RAW_STATEMENT, new Object[] {});
      if (sql instanceof String) {
        sql = (String) toStr;
        sql = sql.substring(sql.indexOf('-') + 1).trim() + ";";
        System.out.println(sql);
      }
    } catch (SQLException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (SecurityException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (NoSuchMethodException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (IllegalArgumentException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (IllegalAccessException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    } catch (InvocationTargetException e) {
      System.out.println("Exception extracting SQL: " + e.getMessage());
    }
  }
}

Inside the C3P0ProxyStatement is a com.mysql.jdbc.ServerPreparedStatement. For my example, ServerPreparedStatement.toString() returns:

com.mysql.jdbc.ServerPreparedStatement[1] - insert into rs values (10)