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

User · Accepted Answer

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

User · Answer

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

User · Answer

I m using Oralce 11g and couldn t manage to get the final SQL from the PreparedStatement  After reading  Pascal MARTIN answer I understand why    I just abandonned the idea of using PreparedStatement and used a simple text formatter which fitted my needs  Here s my example     I jump to the point after connexion has been made     java sql Statement stmt   cnx createStatement    String sqlTemplate    SELECT   FROM Users WHERE Id IN   0     String sqlInParam    21 34 3434 32     some random ids String sqlFinalSql   java text MesssageFormat sqlTemplate sqlInParam   System out println  SQL       sqlFinalSql   rsRes   stmt executeQuery sqlFinalSql     You figure out the sqlInParam can be built dynamically in a  for while  loop I just made it plain simple to get to the point of using the MessageFormat class to serve as a string template formater for the SQL query

User · Answer

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

User · Answer

Using PostgreSQL 9 6 x with official Java driver 42 2 4      myPreparedStatement execute    myPreparedStatement toString     Will show the SQL with the   already replaced  which is what I was looking for  Just added this answer to cover the postgres case   I would never have thought it could be so simple

User · Answer

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 lt String  String gt  extractSql PreparedStatement preparedStatement        Map lt String  String gt  extractedParameters   new HashMap lt  gt         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 - gt  line replaceAll                            collect Collectors joining                    return extractedParameters        This method returns map where we have key-value pairs    query  - gt   INSERT INTO TABLE NAME COLUMN NAME  COLUMN NAME  COLUMN NAME  VALUES            values  - gt   value   value   value    Now - if you want values as list you can just simply use    List lt String gt  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

User · Answer

I implemented the following code for printing SQL from PrepareStatement  public void printSqlStatement PreparedStatement preparedStatement  String sql  throws SQLException          String   sqlArrya  new String preparedStatement getParameterMetaData   getParameterCount             try                  Pattern pattern   Pattern compile                        Matcher matcher   pattern matcher sql                  StringBuffer sb   new StringBuffer                   int indx   1      Parameter begin with index 1                while  matcher find                   matcher appendReplacement sb String valueOf sqlArrya indx                                     matcher appendTail sb                 System out println  Executing Query      sb toString        with Database                                catch  Exception ex                       System out println  Executing Query      sql      with Database

User · Answer

If you re using MySQL you can log the queries using MySQL s query log  I don t know if other vendors provide this feature  but chances are they do

User · Answer

Very late    but you can get the original SQL from an OraclePreparedStatementWrapper by    OraclePreparedStatementWrapper  preparedStatement  getOriginalSql

User · Answer

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

User · Answer

To do this you need a JDBC Connection and or driver that supports logging the sql at a low level   Take a look at log4jdbc

User · Answer

Simply function   public static String getSQL  Statement stmt       String tempSQL   stmt toString           please cut everything before sql from statement       javadb          int i1   tempSQL indexOf      2      tempSQL   tempSQL substring i1        return tempSQL      It s fine aswell for preparedStatement

User · Answer

Code Snippet to convert SQL PreparedStaments with the list of arguments  It works for me                               formatQuery Utility function which will convert SQL                         param sql             param arguments             return                     public static String formatQuery final String sql  Object    arguments                if  arguments    null  amp  amp  arguments length  lt   0                    return sql                            String query   sql              int count   0              while  query matches                                   query   query replaceFirst              count                         count                              String formatedString   java text MessageFormat format query  arguments               return formatedString

[java] How can I get the SQL of a PreparedStatement?

Examples related to java

Examples related to sql

Examples related to jdbc

Examples related to prepared-statement