Getting the SQL text back out of a java.sql.Statement!

Discussions

EJB design: Getting the SQL text back out of a java.sql.Statement!

  1. Anyone know how to do this.

    I create a Statment (or sub class thereof)
    I call setString/Int/Whatever.

    I now want to extract the text of the SQL.

    Can't find anything in the java.sql package that does it. I would rather do this than find the ? characters myself, since the class will have done all the tedious work of quoting and escaping things for me.

    On the other hand, it's Thursday and I am half asleep, so hopefully this is easy for someone more awake! :-)

    Thanks in advance.

    Chz

    Tony
  2. Not sure yet that I understand what you want to do, but it sounds like you want a prepared statement (such as this):

       PreparedStatement myQuery = dbConnection.prepareStatement( "SELECT * FROM test WHERE field=?" );
       myQuery.setString( 1, "value" );
       Resultset queryResults = myQuery.executeQuery( );
       
  3. Nope, that's the easy bit.

    If I have set the value for the parameter, I now want to get the textual representation of the SQL the statement will execute.

    So, in your example, what I want is some way to get back:

    SELECT * FROM test WHERE field='value'

    I have a client requirement that in debug mode the code should log the SQL it is going to execute.

    I know I could do the work myself, searching for ? and adding in values, but the prepared statement will have taken care of quoting the texttual values, escaping special characters and anything else it feels like doing (in theory.)

    I am really just looking to avoid having to do the parsing myself, since the statment may well have done it already.

    Chz

    Tony

  4. Hi
    You will have to do it yourself. This is completely up to the drivers to implement it.

    What you have to do, to enable mimimal change to your code. Write 2 wrapper classes, using PreparedStatement as an example, WrappedConnection and WrappedPreparedStatement. As the names implied, WrappedConnection actually contains a java.sql.Connection, you instantiate this when you return a connection from a connection pool. Now this WrappedConnection class, will return a WrappedPreparedStatement when con.preparedStatement is called. You may even implement the Driver interface to return your special connection.

    This WrappedPreparedStatement contains a java.sql.PreparedStatement object. As you have guessed, just keep track of the ? and use the default toString() method replace the ? in the sql. So you can juz print out the sql using the WrappedPreparedStatement toString method.

    Of course, the shortcut will be to construct the WrappedPreparedStatement after a PreparedStatement is constructed. That will mean more changes to ur code.

    Hope this helps
  5. Ah, I understand what you wanted to do; yes, I've occasionally wanted that as well. From what I can see (and from your other response) this doesn't seem to be possible with the current API. Cross your fingers and perhaps it will show up in JDBC 4.0.

    It's always nice to have the exact SQL statement at hand, particularly for logging.