Get Query from Java.Sql.Preparedstatement

Get query from java.sql.PreparedStatement

This is 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);

To my experience, the ones which currently do so are at least the PostgreSQL 8.x and MySQL 5.x JDBC drivers.

In the case that your JDBC driver doesn't support it, your best bet is using a statement wrapper which records all calls to setXxx() methods and finally populates a SQL string on toString() based on the recorded information. An existing library which does that is P6Spy. In the meanwhile, post an enhancement request to the development team of your JDBC driver and hope that they'll implement the desired toString() behavior as well.

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

Is there a way we can print prepared statement as a string with all the placeholders replaces with its actual values

As you are using Oracle's JDBC driver, you will get OraclePreparedStatement object when you call connection.preparedStatement(sql). Here's the javadoc for it and as it does not override toString() method, you will see a hash of the object on calling toString() (default implementation).

In this case, the only option is to write your own method that accepts String sql and parameters and prints pretty String.

Can I get the full query that a PreparedStatement is about to execute?

It's not mandated by the JDBC spec, but several JDBC drivers let the toString of a PreparedStatement return sort-of the query that will be run, and MySQL's Connector/J happens to have this behavior (or at least it did a few years ago).

String myQuery = "select id from user where name = ?";
PreparedStatement stmt  = sqlConnection.prepareStatement(myQuery);
stmt.setString(1, "test");
System.out.println(stmt); // May do what you want!

Java SQL PreparedStatement is not working

You cannot use the ? operator on tables.

You can do something like "SELECT ? FROM mytable;" but it does not work the other way.

I recommand using stored procedures, not using prepared statements, changing your db structure or create prepared statements for every query and cache them:

Map<String,PreparedStatement> cache=new HashMap<>();

and

PreparedStatement stmt;
if(cache.contains(query)){
    stmt= cache.get(query)
}else{
     stmt=new PreparedStatement("SELECT * FROM `"+query.replace("`","\\`")+"`");
    cache.put(query,stmt);
}
return stmt.executeQuery();

How to see PreparedStatement's SQL string?

The easiest way (that works with any JDBC driver) is to use log4jdbc. It's a proxy that wraps the driver, creates a readable SQL string by combining the SQL and its parameters and logs it, then passes the SQL and parameters on to the underlying driver.

---

Related Topics