How can I get the SQL of a PreparedStatement?

Using prepared statements, there is no "SQL query" :

You have a statement, containing placeholders
- it is sent to the DB server
- and prepared there
- which means the SQL statement is "analysed", parsed, some data-structure representing it is prepared in memory
And, then, you have bound variables
- which are sent to the server
- and the prepared statement is executed -- working on those data

But there is no re-construction of an actual real SQL query -- neither on the Java side, nor on the database side.

So, there is no way to get the prepared statement's SQL -- as there is no such SQL.

For debugging purpose, the solutions are either to :

Ouput the code of the statement, with the placeholders and the list of data
Or to "build" some SQL query "by hand".

It's nowhere definied in the JDBC API contract, but if you're lucky, the JDBC driver in question may return the complete SQL by just calling PreparedStatement#toString(). I.e.

System.out.println(preparedStatement);

At least MySQL 5.x and PostgreSQL 8.x JDBC drivers support it. However, most other JDBC drivers doesn't support it. If you have such one, then your best bet is using Log4jdbc or P6Spy.

Alternatively, you can also write a generic function which takes a Connection, a SQL string and the statement values and returns a PreparedStatement after logging the SQL string and the values. Kickoff example:

public static PreparedStatement prepareStatement(Connection connection, String sql, Object... values) throws SQLException {
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
    logger.debug(sql + " " + Arrays.asList(values));
    return preparedStatement;
}

and use it as

try {
    connection = database.getConnection();
    preparedStatement = prepareStatement(connection, SQL, values);
    resultSet = preparedStatement.executeQuery();
    // ...

Another alternative is to implement a custom PreparedStatement which wraps (decorates) the real PreparedStatement on construction and overrides all the methods so that it calls the methods of the real PreparedStatement and collects the values in all the setXXX() methods and lazily constructs the "actual" SQL string whenever one of the executeXXX() methods is called (quite a work, but most IDE's provides autogenerators for decorator methods, Eclipse does). Finally just use it instead. That's also basically what P6Spy and consorts already do under the hoods.

I'm using Java 8, JDBC driver with MySQL connector v. 5.1.31.

I may get real SQL string using this method:

// 1. make connection somehow, it's conn variable
// 2. make prepered statement template
PreparedStatement stmt = conn.prepareStatement(
    "INSERT INTO oc_manufacturer" +
    " SET" +
    " manufacturer_id = ?," +
    " name = ?," +
    " sort_order=0;"
);
// 3. fill template
stmt.setInt(1, 23);
stmt.setString(2, 'Google');
// 4. print sql string
System.out.println(((JDBC4PreparedStatement)stmt).asSql());

So it returns smth like this:

INSERT INTO oc_manufacturer SET manufacturer_id = 23, name = 'Google', sort_order=0;

If you're executing the query and expecting a ResultSet (you are in this scenario, at least) then you can simply call ResultSet's getStatement() like so:

ResultSet rs = pstmt.executeQuery();
String executedQuery = rs.getStatement().toString();

The variable executedQuery will contain the statement that was used to create the ResultSet.

Now, I realize this question is quite old, but I hope this helps someone..

How can I get the SQL of a PreparedStatement?

Related

Recent Posts