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

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 2

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 3

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 4

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 5

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 6

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