0

I am using PreparedStatement to build my query but i want to print the final statement after replacing all the variables (replacing the '?') before executing it, apparently the method i need is PreparedStatement#toString() like this for example

PreparedStatement query = connection.prepareStatement(select ? from ?);
System.out.println("Before : " + query.toString());
query.setString(1, column);
query.setString(2, table);
System.out.println("After : " + query.toString());

and the output should be something like this :

Before : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf: SELECT ** NOT SPECIFIED ** FROM ** NOT SPECIFIED **
After : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf: SELECT column FROM table

but what i get is

Before : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf
After : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf

i don't know what is wrong

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
imstuckaf
  • 87
  • 8
  • Use `.asSql()`. – luk2302 Sep 10 '20 at 10:08
  • 1
    By the way: you cannot have a ? for the table name: https://stackoverflow.com/questions/11312155/how-to-use-a-tablename-variable-for-a-java-prepared-statement-insert - the table name has to be determined before creating a prepared statement. – luk2302 Sep 10 '20 at 10:16
  • Often the database allows logging of all queries to some degree. Also `ParameterMetaData meta = stmt.getParameterMetaData();` might be useful. – Joop Eggen Sep 10 '20 at 10:27
  • 1
    Are you trying to do this via plain jdbc and you need to log this statement or you are using any framework like spring jpa/hibernate. Depending on usage of framework, you can enable trace logs for sql. – Soni Sep 10 '20 at 10:27
  • Oracle with mysql.jdbc? honestly no idea i'm using someone esle's PC – imstuckaf Sep 10 '20 at 10:47
  • and ParameterMetaData meta = stmt.getParameterMetaData(); it gives the same output – imstuckaf Sep 10 '20 at 10:48

1 Answers1

0

... and the output should be something like this ...

That is what you want the output to be. Unfortunately (for you) the toString() method is not declared by the PreparedStatement interface (javadoc). So what you appear to be getting is the output from the toString() implementation inherited from Object.

Is there a way to get the expanded SQL?

There isn't a way to do it portably. The standard JDBC API does not support this.

However, if you cast the PreparedStatement instance to:

  • com.mysql.jdbc.PreparedStatement (Connector/J 5.x), or
  • com.mysql.cj.jdbc.ClientPreparedStatement (Connector/J 8.x)

you can then call asSql() on the prepared statement to get the expanded SQL.

(Note that for some versions of Connector/J 5.x, the asSql() method is declared as protected so you can't call it from application code. This was apparently fixed in 5.1.26.)

Alternatively, from my cursory reading of the code, it looks like calling toString() should work as you want it to with Connector/J 8.x, and 5.1.26 or later.

Of course, all of this makes your code dependent on the JDBC driver that you are using.

Another option would be to log the SQL on the database server side.

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216