get PreparedStatement query from Derby

Question

I'm trying to connect to a database, run a query and print out the query. So far what I have works but I need to get the output and assign a particular part of it to a String

public static void main(String args[]) {
        BasicConfigurator.configure();
        Logger.getGlobal().setLevel(Level.INFO);
        PreparedStatement preparedStatement = null;
        try {
            connect();
            String sql = "SELECT * FROM foo WHERE ID = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    break;
                }
            }
            //String usedSql = "query should  go here";
        } catch (SQLException ex) {
            Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            disconnect();
        }
    }

I'm using log4jdbc to spy on my queries.

At the moment I get logged output like:

594 [main] DEBUG jdbc.foo  - 1. Connection.new Connection returned   java.sql.DriverManager.getConnection(DriverManager.java:664)
608 [main] DEBUG jdbc.foo  - 1. PreparedStatement.new PreparedStatement returned   com.example.Test.main(Test.java:63)
608 [main] DEBUG jdbc.foo  - 1. Connection.prepareStatement(SELECT * FROM foo WHERE ID = ?) returned net.sf.log4jdbc.PreparedStatementSpy@7d70d1b1  com.example.Test.main(Test.java:63)
608 [main] DEBUG jdbc.foo  - 1. PreparedStatement.setInt(1, 1) returned   com.example.Test.main(Test.java:64)
608 [main] DEBUG jdbc.foo  - 1. PreparedStatement.setMaxRows(1) returned   com.example.Test.main(Test.java:65)
609 [main] DEBUG jdbc.sqlonly  -  com.example.Test.main(Test.java:66)
1. SELECT * FROM foo WHERE ID = 1 

I want to assign SELECT * FROM foo WHERE ID = 1 to usedSql. How can I go about doing this?

Answer 1

Usually a preparedStatement.toString() will give you the query (incl. bound parameters). But it depends on the actual implementation of the PreparedStatement (with the PostgreSQL impl this works, for example).

You mentioned that preparedStatement.toString() returns net.sf.log4jdbc.PreparedStatementSpy@7d70d1b1 for you. I'm not familiar with log4jdbc but I looks like PreparedStatementSpy is wrapping your actual PreparedStatement. To get it from your preparedStatement try something like

if(preparedStatement instanceof PreparedStatementSpy)
     usedSql = ((PreparedStatementSpy) preparedStatement).getRealStatement().toString();

Edit: since you are using Derby a simple toString() won't do. A way around this could be to use PreparedStatementSpy.dumpedSql(), which will return the same string log4jdbc uses for logging. Unfortunately its a protected method and you have to use reflection:

if (preparedStatement instanceof PreparedStatementSpy) {
    Method m = PreparedStatementSpy.class.getDeclaredMethod("dumpedSql");
    m.setAccessible(true);
    usedSql = (String) m.invoke(preparedStatement);
}
// omitted exception handling