How can I get the SQL of a PreparedStatement?

Question

I have a general Java method with the following method signature:

private static ResultSet runSQLResultSet(String sql, Object... queryParams)

It opens a connection, builds a PreparedStatement using the sql statement and the parameters in the queryParams variable length array, runs it, caches the ResultSet (in a CachedRowSetImpl), closes the connection, and returns the cached result set.

I have exception handling in the method that logs errors. I log the sql statement as part of the log since it's very helpful for debugging. My problem is that logging the String variable sql logs the template statement with ?'s instead of actual values. I want to log the actual statement that was executed (or tried to execute).

So... Is there any way to get the actual SQL statement that will be run by a PreparedStatement? (Without building it myself. If I can't find a way to access the PreparedStatement's SQL, I'll probably end up building it myself in my catches.)

Solution 1

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".

Solution 2

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.

Solution 3

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;

Solution 4

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..

Solution 5

I've extracted my sql from PreparedStatement using preparedStatement.toString() In my case toString() returns String like this:

org.hsqldb.jdbc.JDBCPreparedStatement@7098b907[sql=[INSERT INTO 
TABLE_NAME(COLUMN_NAME, COLUMN_NAME, COLUMN_NAME) VALUES(?, ?, ?)],
parameters=[[value], [value], [value]]]

Now I've created a method (Java 8), which is using regex to extract both query and values and put them into map:

private Map<String, String> extractSql(PreparedStatement preparedStatement) {
    Map<String, String> extractedParameters = new HashMap<>();
    Pattern pattern = Pattern.compile(".*\\[sql=\\[(.*)],\\sparameters=\\[(.*)]].*");
    Matcher matcher = pattern.matcher(preparedStatement.toString());
    while (matcher.find()) {
      extractedParameters.put("query", matcher.group(1));
      extractedParameters.put("values", Stream.of(matcher.group(2).split(","))
          .map(line -> line.replaceAll("(\\[|])", ""))
          .collect(Collectors.joining(", ")));
    }
    return extractedParameters;
  }

This method returns map where we have key-value pairs:

"query" -> "INSERT INTO TABLE_NAME(COLUMN_NAME, COLUMN_NAME, COLUMN_NAME) VALUES(?, ?, ?)"
"values" -> "value,  value,  value"

Now - if you want values as list you can just simply use:

List<String> values = Stream.of(yourExtractedParametersMap.get("values").split(","))
    .collect(Collectors.toList());

If your preparedStatement.toString() is different than in my case it's just a matter of "adjusting" regex.

View more solutions

Comments

[Ken Liu]

If you're writing straight JDBC code, I'd highly recommend looking at Apache commons-dbutils commons.apache.org/dbutils. It simplifies JDBC code greatly.

[Devanshu Mevada]

Dupe: stackoverflow.com/questions/218113/…

[sidereal]

Although this is functionally true, there's nothing preventing utility code from reconstructing an equivalent unprepared statement. For example, in log4jdbc: "In the logged output, for prepared statements, the bind arguments are automatically inserted into the SQL output. This greatly Improves readability and debugging for many cases." Very useful for debugging, as long as you're aware that it's not how the statement is actually being executed by the DB server.

[Jay]

This also depends on the implementation. In MySQL -- at least the version I was using a few years ago -- the JDBC driver actually built a conventional SQL query from the template and bind variables. I guess that version of MySQL didn't support prepared statements natively, so they implemented them within the JDBC driver.

[Pascal MARTIN]

@sidereal : that's what I meant by "build the query by hand" ; but you said it better than me ;;; @Jay : we have the same kind of mecanism in place in PHP (real prepared statements when supported ; pseudo-prepared statements for database drivers that don't support them)

[froadie]

That's similar to the method I'm using (your prepareStatement method). My question isn't how to do it - my question is how to log the sql statement. I know that I can do logger.debug(sql + " " + Arrays.asList(values)) - I'm looking for a way to log the sql statement with the parameters already integrated into it. Without looping myself and replacing the question marks.

[BalusC]

Then head to the last paragraph of my answer or look at P6Spy. They do the "nasty" looping and replacing work for you ;)

[O. R. Mapper]

"You have a statement" - ok, how can I print it to the console when I only have the PreparedStatement instance?

[Stephen P]

Link to P6Spy is now broken.

[AVA]

@Elad Stern It prints, oracle.jdbc.driver.OraclePreparedStatementWrapper@1b9ce4b instead of printing the the executed sql statement! Please guide us!

[Elad Stern]

@AVA, did you use toString()?

[AVA]

@EladStern toString() is used!

[Elad Stern]

@AVA, well I'm not sure but it may have to do with your jdbc driver. I've used mysql-connector-5 successfully.

[Preston]

If you're using java.sql.PreparedStatement a simple .toString() on the preparedStatement will include the generated SQL I've verified this in 1.8.0_60

[Mike Argyriou]

@Preston For Oracle DB the PreparedStatement#toString() does not show the SQL. Therefore I guess it depends from the DB JDBC driver.

[Hooli]

Take a look at log4jdbc and then what? How do you use it? You go to that site and see random rambling about the project with no clear example on how to actually use the technology.

[ticktock]

This sort of blows up the entire reason for using prepared statements, such as avoiding sql injection and improved performance.

[Joshua Stafford]

This should have all of the upvotes as it is exactly what the OP is looking for.

[Diego Tercero]

I agree with you 100%. I should have made clear that I made this code to be executed a couple of times at most for a massive bulk data integration and desperately needed a quick way to have some log output without going into the whole log4j enchilada which would have been overkill for what I needed. This should not go into production code :-)

[Daz]

rs.getStatement() just returns the statement object, so it's down to whether the driver you're using implements .toString() that determines if you'll get back the SQL

[Bhushan]

@BalusC I am newbie to JDBC. I have one doubt. If you write generic function like that, then it will create PreparedStatement every time. Won't that be not-so-efficient way coz whole point of PreparedStatement is to create them once and re-use them everywhere?

[davidwessman]

Is there a similar function for the Postgres-driver?

[latj]

This works for me with Oracle driver (but doesnt include parameters): ((OraclePreparedStatementWrapper) myPreparedStatement).getOriginalSql()

[k0pernikus]

This also works on the voltdb jdbc driver to get the full sql query for a prepared statement.

[Pere]

This is simply a .toString() with a couple extra lines to fool inexpert users, and was already answered ages ago.

[Gunasekar]

How to get it for Apache Derby?

[spectrum]

When I try to use the wrapper it says: oracle.jdbc.driver.OraclePreparedStatementWrapper is not public in oracle.jdbc.driver. Cannot be accessed from outside package. How are you using that Class?

[Ercan]

It doesn't work and throws ClassCastException: java.lang.ClassCastException: oracle.jdbc.driver.T4CPreparedStatement cannot be cast to com.mysql.jdbc.JDBC4PreparedStatement

[Ercan]

Doesn't work, shows only oracle.jdbc.driver.T4CPreparedStatement@6e3c1e69

[userlond]

@ErcanDuman, my answer is not universal, it covers Java 8 and MySQL JDBC driver only.

[Darrel Holt]

I'd like to add that if you are building the prepared statement using Kodo as your persistence manager, then you can enable TRACE level logging for Kodo and the SQL statement that you're looking for may appear in your console output logs. If debugging in an IDE then you should be able to see it in your live debugging console output when you hit the execute() method during your debug session.

[Nandakishore]

This works great for one prepared statement. But if we have a batch of 10 statements added as batch to a single prepared statement. Then this will only print the last sql query added as batch. Do you know how to print all sql statements under one batch of a prepared statement?

[Matthieu]

@HuckIt it works for MySQL and PostgreSQL JDBC drivers, but not for SQLServer, so it's not generic.

[Feng Zhang]

Hand construct should be good enough. I had a situation where I forgot to "commit" when excuting a sql on DB side. So the query on the sql developer shows more record than excuting from code using prepared statement. Cost me several hours.

[viebel]

Could you share a link to the documentation for asSql()? I am curious to see if it does parameter sanitisation to prevent malicious SQL injection.

[kAmol]

BTW above mentioned other answers(top voted ones) used PreparedStatment and this answer suggests to use rs.getStatement() which returns Statement. PreparedStatement just extends Statement interface.

[Anu]

@latj its giving the original sql without bind variables. We need with the bind variable values.