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

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

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

Answer 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;

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

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

Answer 6

Very late :) but you can get the original SQL from an OraclePreparedStatementWrapper by

((OraclePreparedStatementWrapper) preparedStatement).getOriginalSql();

Answer 7

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.

Answer 8

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 && arguments.length <= 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;
        }

Answer 9

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[" +  "] ...");
            }

    }

Answer 10

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.

Answer 11

with Oracle:

public static String getSQLFromPreparedStatement(PreparedStatement preparedStatement) {
    if (preparedStatement instanceof OraclePreparedStatement) {
        OraclePreparedStatement oraclePreparedStatement = (OraclePreparedStatement) preparedStatement;
        try {
            return oraclePreparedStatement.getOriginalSql();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return null;
}

Answer 12

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.

Answer 13

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.

Answer 14

You can try to use javaagent to print SQL:

public class Main {

    private static final String mybatisPath = "org.apache.ibatis.executor.statement.PreparedStatementHandler";
    private static final String mybatisMethod = "parameterize";
    private static final String sqlPath = "java.sql.Statement";


    public static void premain(String arg, Instrumentation instrumentation) {

        instrumentation.addTransformer(new ClassFileTransformer() {
            @Override
            public byte[] transform(
                    ClassLoader loader,
                    String className,
                    Class<?> classBeingRedefined,
                    ProtectionDomain protectionDomain,
                    byte[] classfileBuffer) throws IllegalClassFormatException {

                if (!mybatisPath.replaceAll("\\.", "/").equals(className)) {
                    return null;
                }
                ClassPool pool = new ClassPool();
                pool.appendClassPath(new LoaderClassPath(loader));
                pool.appendSystemPath();

                try {
                    CtClass ctClass = pool.get(mybatisPath);
                    CtMethod method = ctClass.getDeclaredMethod(mybatisMethod, new CtClass[]{pool.get(sqlPath)});
                    method.insertAfter("cn.wjhub.Main#printSQL($1)");

                    return ctClass.toBytecode();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                return null;
            }
        });
    }

    /**
     * printSQL
     *
     * @param statement statement
     */
    private void printSQL(Statement statement) {
        String sqlSource = statement.toString();
        System.out.println(sqlSource);
    }
}

Answer 15

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