Hooli Hooli - 6 months ago 44
Java Question

get PreparedStatement query from Derby

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

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