Java 如何获取 PreparedStatement 的 SQL？

How can I get the SQL of a PreparedStatement?

提问by froadie

I have a general Java method with the following method signature:

我有一个具有以下方法签名的通用 Java 方法：

private static ResultSet runSQLResultSet(String sql, Object... queryParams)

It opens a connection, builds a PreparedStatementusing the sql statement and the parameters in the queryParamsvariable length array, runs it, caches the ResultSet(in a CachedRowSetImpl), closes the connection, and returns the cached result set.

它打开一个连接，PreparedStatement使用 sql 语句和queryParams可变长度数组中的参数构建一个，运行它，缓存ResultSet（在 a 中CachedRowSetImpl），关闭连接，并返回缓存的结果集。

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 sqllogs the template statement with ?'s instead of actual values. I want to log the actualstatement that was executed (or tried to execute).

我在记录错误的方法中有异常处理。我将 sql 语句记录为日志的一部分，因为它对调试非常有帮助。我的问题是记录 String 变量会sql记录带有 ? 而不是实际值的模板语句。我想记录已执行（或尝试执行）的实际语句。

So... Is there any way to get the actual SQL statement that will be run by a PreparedStatement? (Withoutbuilding it myself. If I can't find a way to access the PreparedStatement'sSQL, I'll probably end up building it myself in my catches.)

那么......有没有办法获得将由 a 运行的实际 SQL 语句PreparedStatement？（没有自己构建它。如果我找不到访问PreparedStatement'sSQL 的方法，我可能最终会在我的catches 中自己构建它。）

采纳答案by Pascal MARTIN

Using prepared statements, there is no "SQL query" :

使用准备好的语句，没有“SQL 查询”：

• 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

• 你有一个声明，包含占位符
  • 它被发送到数据库服务器
  • 并在那里准备
  • 这意味着 SQL 语句被“分析”、解析，一些表示它的数据结构是在内存中准备的
• 然后，你有绑定变量
  • 发送到服务器
  • 并执行准备好的语句——处理这些数据

But there is no re-construction of an actual real SQL query -- neither on the Java side, nor on the database side.

但是没有重新构建实际的 SQL 查询——无论是在 Java 端，还是在数据库端。

So, there is no way to get the prepared statement's SQL -- as there is no such SQL.

因此，无法获得准备好的语句的 SQL —— 因为没有这样的 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".

• 输出语句的代码，包含占位符和数据列表
• 或者“手动”“构建”一些 SQL 查询。

回答by sidereal

To do this you need a JDBC Connection and/or driver that supports logging the sql at a low level.

为此，您需要一个支持在低级别记录 sql 的 JDBC 连接和/或驱动程序。

Take a look at log4jdbc

看看log4jdbc

回答by BalusC

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.

JDBC API 合同中没有定义它，但如果幸运的话，有问题的 JDBC 驱动程序可能会通过调用PreparedStatement#toString(). IE

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 Log4jdbcor P6Spy.

至少 MySQL 5.x 和 PostgreSQL 8.x JDBC 驱动程序支持它。但是，大多数其他 JDBC 驱动程序不支持它。如果你有这样的人，那么你最好的选择是使用Log4jdbc或P6Spy。

Alternatively, you can also write a generic function which takes a Connection, a SQL string and the statement values and returns a PreparedStatementafter logging the SQL string and the values. Kickoff example:

或者，您也可以编写一个通用函数，该函数采用Connection、SQL 字符串和语句值，并PreparedStatement在记录 SQL 字符串和值后返回 a 。开场示例：

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 PreparedStatementwhich wraps (decorates) the realPreparedStatementon construction and overrides all the methods so that it calls the methods of the realPreparedStatementand 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.

另一种选择是实现一个自定义PreparedStatement，它在构造时包装（装饰）实数PreparedStatement并覆盖所有方法，以便它调用实数的方法PreparedStatement并收集所有setXXX()方法中的值，并在任何时候懒惰地构造“实际”SQL 字符串该executeXXX()方法被称为（相当的工作，但大多数IDE的装饰方法提供autogenerators，Eclipse一样）。最后只是用它代替。这基本上也是 P6Spy 和配偶已经在幕后做的事情。

回答by Ionu? G. Stan

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.

如果您使用 MySQL，您可以使用MySQL 的 query log 记录查询。我不知道其他供应商是否提供此功能，但他们很有可能提供。

回答by userlond

I'm using Java 8, JDBC driver with MySQL connector v. 5.1.31.

我正在使用 Java 8、JDBC 驱动程序和 MySQL 连接器 v. 5.1.31。

I may get real SQL string using this method:

我可以使用这种方法得到真正的 SQL 字符串：

// 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:

所以它像这样返回 smth：

INSERT INTO oc_manufacturer SET manufacturer_id = 23, name = 'Google', sort_order=0;

回答by Elad Stern

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:

如果您正在执行查询并期望 a ResultSet（至少在这种情况下），那么您可以简单地调用ResultSet's ，getStatement()如下所示：

ResultSet rs = pstmt.executeQuery();
String executedQuery = rs.getStatement().toString();

The variable executedQuerywill contain the statement that was used to create the ResultSet.

该变量executedQuery将包含用于创建ResultSet.

Now, I realize this question is quite old, but I hope this helps someone..

现在，我意识到这个问题已经很老了，但我希望这可以帮助某人..

回答by Hobson

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.

对于preparedStatement 也很好。

回答by Diego Tercero

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.

我正在使用 Oralce 11g 并且无法从 PreparedStatement 获得最终的 SQL。阅读@Pascal MARTIN 的回答后，我明白了原因。

I just abandonned the idea of using PreparedStatement and used a simple text formatter which fitted my needs. Here's my example:

我只是放弃了使用 PreparedStatement 的想法，并使用了一个适合我需求的简单文本格式化程序。这是我的例子：

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

您发现可以在 (for,while) 循环中动态构建 sqlInParam 我只是简单地介绍了使用 MessageFormat 类作为 SQL 查询的字符串模板格式化程序。

回答by Diego Tercero

I implemented the following code for printing SQL from PrepareStatement

我实现了以下代码，用于从 PrepareStatement 打印 SQL

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

    }

回答by Harsh Maheswari

Code Snippet to convert SQL PreparedStaments with the list of arguments. It works for me

使用参数列表转换 SQL PreparedStaments 的代码片段。这个对我有用

  /**
         * 
         * 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;
        }