从 java.sql.PreparedStatement 获取查询

Question

在我的代码中，我使用java.sql.PreparedStatement。

然后，我执行 setString() 方法来填充准备好的语句的通配符。

有没有办法让我在调用 executeQuery() 方法并执行查询之前检索（并打印出）最终查询？我只是想要这个用于调试目的。

Answer 1

JDBC API 契约中没有对此进行定义，但如果您幸运，相关 JDBC 驱动程序可能只需调用 PreparedStatement#toString() 即可返回完整的 SQL。即

System.out.println(preparedStatement);

根据我的经验，目前这样做的至少是 PostgreSQL 8.x 和 MySQL 5.x JDBC 驱动程序。

如果您的 JDBC 驱动程序不支持它，最好的选择是使用语句包装器，它记录对 setXxx() 方法的所有调用，并最终在 toString() 上填充 SQL 字符串 基于记录的信息。执行此操作的现有库是 P6Spy。同时，向 JDBC 驱动程序的开发团队发布增强请求，并希望他们也实现所需的 toString() 行为。

Answer 2

设置绑定值后，您可以尝试在准备好的语句上调用 toString() 。

PreparedStatement statement = connection.prepareStatement(aSQLStatement);
System.out.println("Before : " + statement.toString());
query.setString(1, "Hello");
query.setString(2, "World");
System.out.println("After : " + statement.toString());

当您使用 JDBC MySQL 驱动程序时，这有效，但我不确定在其他情况下是否有效。您可能必须跟踪您制作的所有装订，然后将其打印出来。

上述代码的示例输出。

Before : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf: SELECT * FROM test WHERE blah1=** NOT SPECIFIED ** and blah2=** NOT SPECIFIED **
After : com.mysql.jdbc.JDBC4PreparedStatement@fa9cf: SELECT * FROM test WHERE blah1='Hello' and blah2='World'

Answer 3

对于那些正在寻找 Oracle 解决方案的人，我从 Log4Jdbc 的代码中创建了一个方法。您将需要提供查询和传递给 preparedStatement 的参数，因为从中检索它们有点痛苦：

private String generateActualSql(String sqlQuery, Object... parameters) {
    String[] parts = sqlQuery.split("\\?");
    StringBuilder sb = new StringBuilder();

    // This might be wrong if some '?' are used as litteral '?'
    for (int i = 0; i < parts.length; i++) {
        String part = parts[i];
        sb.append(part);
        if (i < parameters.length) {
            sb.append(formatParameter(parameters[i]));
        }
    }

    return sb.toString();
}

private String formatParameter(Object parameter) {
    if (parameter == null) {
        return "NULL";
    } else {
        if (parameter instanceof String) {
            return "'" + ((String) parameter).replace("'", "''") + "'";
        } else if (parameter instanceof Timestamp) {
            return "to_timestamp('" + new SimpleDateFormat("MM/dd/yyyy HH:mm:ss.SSS").
                    format(parameter) + "', 'mm/dd/yyyy hh24:mi:ss.ff3')";
        } else if (parameter instanceof Date) {
            return "to_date('" + new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").
                    format(parameter) + "', 'mm/dd/yyyy hh24:mi:ss')";
        } else if (parameter instanceof Boolean) {
            return ((Boolean) parameter).booleanValue() ? "1" : "0";
        } else {
            return parameter.toString();
        }
    }
}

Answer 4

您可以将 log4jdbc 添加到您的项目中。这增加了 sql 命令执行时的日志记录以及许多其他信息。

http://code.google.com/p/log4jdbc/wiki/FAQ

Answer 5

如果您只想记录查询，则将 'logger' 和 'profileSQL' 添加到 jdbc url：

&logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true

然后您将得到以下 SQL 语句：

2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0 message: SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 2 connection: 19130945 statement: 13 resultset: 17 message: select 1
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 13 resultset: 17
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 15 resultset: 18 message: select @@session.tx_read_only
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 15 resultset: 18
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 2 connection: 19130945 statement: 14 resultset: 0 message: update sequence set seq=seq+incr where name='demo' and seq=4602
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 14 resultset: 0

默认记录器为：

com.mysql.jdbc.log.StandardLogger

Mysql jdbc 属性列表： https://dev.mysql.com/doc/connector-j/en /connector-j-reference-configuration-properties.html

Answer 6

我已经制定了解决方法来解决这个问题。
请访问以下链接了解更多详情
http://code-outofbox.blogspot。 com/2015/07/java-prepared-statement-print-values.html

解决方案：

// Initialize connection
PreparedStatement prepStmt = connection.prepareStatement(sql);

PreparedStatementHelper prepHelper = new PreparedStatementHelper(prepStmt);

// User prepHelper.setXXX(indx++, value);
// .....

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, prepHelper.getParameter(indx++));
   }
   matcher.appendTail(sb);
   LOGGER.debug("Executing Query [" + sb.toString() + "] with Database[" + /*db name*/ + "] ...");
   } catch (Exception ex) {
    LOGGER.debug("Executing Query [" + sql + "] with Database[" +  /*db name*/+ "] ...");
}

/****************************************************/

package java.sql;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.NClob;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;

public class PreparedStatementHelper implements PreparedStatement {

 private PreparedStatement prepStmt;
 private String[] values;

 public PreparedStatementHelper(PreparedStatement prepStmt) throws SQLException {
  this.prepStmt = prepStmt;
  this.values = new String[this.prepStmt.getParameterMetaData().getParameterCount()];
 }

 public String getParameter(int index) {
  String value = this.values[index-1];
  return String.valueOf(value);
 }

 private void setParameter(int index, Object value) {
  String valueStr = "";
  if (value instanceof String) {
   valueStr = "'" + String.valueOf(value).replaceAll("'", "''") + "'";
  } else if (value instanceof Integer) {
   valueStr = String.valueOf(value);
  } else if (value instanceof Date || value instanceof Time || value instanceof Timestamp) {
   valueStr = "'" + String.valueOf(value) + "'";
  } else {
   valueStr = String.valueOf(value);
  }
  this.values[index-1] = valueStr;
 }

 @Override
 public ResultSet executeQuery(String sql) throws SQLException {
  return this.prepStmt.executeQuery(sql);
 }

 @Override
 public int executeUpdate(String sql) throws SQLException {
  return this.prepStmt.executeUpdate(sql);
 }

 @Override
 public void close() throws SQLException {
  this.prepStmt.close();

 }

 @Override
 public int getMaxFieldSize() throws SQLException {
  return this.prepStmt.getMaxFieldSize();
 }

 @Override
 public void setMaxFieldSize(int max) throws SQLException {
  this.prepStmt.setMaxFieldSize(max);
 }

 @Override
 public int getMaxRows() throws SQLException {
  return this.prepStmt.getMaxRows();
 }

 @Override
 public void setMaxRows(int max) throws SQLException {
  this.prepStmt.setMaxRows(max);
 }

 @Override
 public void setEscapeProcessing(boolean enable) throws SQLException {
  this.prepStmt.setEscapeProcessing(enable);
 }

 @Override
 public int getQueryTimeout() throws SQLException {
  return this.prepStmt.getQueryTimeout();
 }

 @Override
 public void setQueryTimeout(int seconds) throws SQLException {
  this.prepStmt.setQueryTimeout(seconds);
 }

 @Override
 public void cancel() throws SQLException {
  this.prepStmt.cancel();
 }

 @Override
 public SQLWarning getWarnings() throws SQLException {
  return this.prepStmt.getWarnings();
 }

 @Override
 public void clearWarnings() throws SQLException {
  this.prepStmt.clearWarnings();
 }

 @Override
 public void setCursorName(String name) throws SQLException {
  this.prepStmt.setCursorName(name);
 }

 @Override
 public boolean execute(String sql) throws SQLException {
  return this.prepStmt.execute(sql);
 }

 @Override
 public ResultSet getResultSet() throws SQLException {
  return this.prepStmt.getResultSet();
 }

 @Override
 public int getUpdateCount() throws SQLException {
  return this.prepStmt.getUpdateCount();
 }

 @Override
 public boolean getMoreResults() throws SQLException {
  return this.prepStmt.getMoreResults();
 }

 @Override
 public void setFetchDirection(int direction) throws SQLException {
  this.prepStmt.setFetchDirection(direction);
 }

 @Override
 public int getFetchDirection() throws SQLException {
  return this.prepStmt.getFetchDirection();
 }

 @Override
 public void setFetchSize(int rows) throws SQLException {
  this.prepStmt.setFetchSize(rows);
 }

 @Override
 public int getFetchSize() throws SQLException {
  return this.prepStmt.getFetchSize();
 }

 @Override
 public int getResultSetConcurrency() throws SQLException {
  return this.prepStmt.getResultSetConcurrency();
 }

 @Override
 public int getResultSetType() throws SQLException {
  return this.prepStmt.getResultSetType();
 }

 @Override
 public void addBatch(String sql) throws SQLException {
  this.prepStmt.addBatch(sql);
 }

 @Override
 public void clearBatch() throws SQLException {
  this.prepStmt.clearBatch();
 }

 @Override
 public int[] executeBatch() throws SQLException {
  return this.prepStmt.executeBatch();
 }

 @Override
 public Connection getConnection() throws SQLException {
  return this.prepStmt.getConnection();
 }

 @Override
 public boolean getMoreResults(int current) throws SQLException {
  return this.prepStmt.getMoreResults(current);
 }

 @Override
 public ResultSet getGeneratedKeys() throws SQLException {
  return this.prepStmt.getGeneratedKeys();
 }

 @Override
 public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {
  return this.prepStmt.executeUpdate(sql, autoGeneratedKeys);
 }

 @Override
 public int executeUpdate(String sql, int[] columnIndexes) throws SQLException {
  return this.prepStmt.executeUpdate(sql, columnIndexes);
 }

 @Override
 public int executeUpdate(String sql, String[] columnNames) throws SQLException {
  return this.prepStmt.executeUpdate(sql, columnNames);
 }

 @Override
 public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {
  return this.prepStmt.execute(sql, autoGeneratedKeys);
 }

 @Override
 public boolean execute(String sql, int[] columnIndexes) throws SQLException {
  return this.prepStmt.execute(sql, columnIndexes);
 }

 @Override
 public boolean execute(String sql, String[] columnNames) throws SQLException {
  return this.prepStmt.execute(sql, columnNames);
 }

 @Override
 public int getResultSetHoldability() throws SQLException {
  return this.prepStmt.getResultSetHoldability();
 }

 @Override
 public boolean isClosed() throws SQLException {
  return this.prepStmt.isClosed();
 }

 @Override
 public void setPoolable(boolean poolable) throws SQLException {
  this.prepStmt.setPoolable(poolable);
 }

 @Override
 public boolean isPoolable() throws SQLException {
  return this.prepStmt.isPoolable();
 }

 @Override
 public <T> T unwrap(Class<T> iface) throws SQLException {
  return this.prepStmt.unwrap(iface);
 }

 @Override
 public boolean isWrapperFor(Class<?> iface) throws SQLException {
  return this.prepStmt.isWrapperFor(iface);
 }

 @Override
 public ResultSet executeQuery() throws SQLException {
  return this.prepStmt.executeQuery();
 }

 @Override
 public int executeUpdate() throws SQLException {
  return this.prepStmt.executeUpdate();
 }

 @Override
 public void setNull(int parameterIndex, int sqlType) throws SQLException {
  this.prepStmt.setNull(parameterIndex, sqlType);
  setParameter(parameterIndex, null);
 }

 @Override
 public void setBoolean(int parameterIndex, boolean x) throws SQLException {
  this.prepStmt.setBoolean(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setByte(int parameterIndex, byte x) throws SQLException {
  this.prepStmt.setByte(parameterIndex, x);
  // TODO Add to tree set
 }

 @Override
 public void setShort(int parameterIndex, short x) throws SQLException {
  this.prepStmt.setShort(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setInt(int parameterIndex, int x) throws SQLException {
  this.prepStmt.setInt(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setLong(int parameterIndex, long x) throws SQLException {
  this.prepStmt.setLong(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setFloat(int parameterIndex, float x) throws SQLException {
  this.prepStmt.setFloat(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setDouble(int parameterIndex, double x) throws SQLException {
  this.prepStmt.setDouble(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException {
  this.prepStmt.setBigDecimal(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setString(int parameterIndex, String x) throws SQLException {
  this.prepStmt.setString(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setBytes(int parameterIndex, byte[] x) throws SQLException {
  this.prepStmt.setBytes(parameterIndex, x);
  // TODO Add to tree set
 }

 @Override
 public void setDate(int parameterIndex, Date x) throws SQLException {
  this.prepStmt.setDate(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setTime(int parameterIndex, Time x) throws SQLException {
  this.prepStmt.setTime(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException {
  this.prepStmt.setTimestamp(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException {
  this.prepStmt.setAsciiStream(parameterIndex, x, length);
 }

 @SuppressWarnings("deprecation")
 @Override
 public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException {
  this.prepStmt.setUnicodeStream(parameterIndex, x, length);
 }

 @Override
 public void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException {
  this.prepStmt.setBinaryStream(parameterIndex, x, length);
 }

 @Override
 public void clearParameters() throws SQLException {
  this.prepStmt.clearParameters();
 }

 @Override
 public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException {
  this.prepStmt.setObject(parameterIndex, x, targetSqlType);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setObject(int parameterIndex, Object x) throws SQLException {
  this.prepStmt.setObject(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public boolean execute() throws SQLException {
  return this.prepStmt.execute();
 }

 @Override
 public void addBatch() throws SQLException {
  this.prepStmt.addBatch();
 }

 @Override
 public void setCharacterStream(int parameterIndex, Reader reader, int length) throws SQLException {
  this.prepStmt.setCharacterStream(parameterIndex, reader, length);
 }

 @Override
 public void setRef(int parameterIndex, Ref x) throws SQLException {
  this.prepStmt.setRef(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setBlob(int parameterIndex, Blob x) throws SQLException {
  this.prepStmt.setBlob(parameterIndex, x);
 }

 @Override
 public void setClob(int parameterIndex, Clob x) throws SQLException {
  this.prepStmt.setClob(parameterIndex, x);
 }

 @Override
 public void setArray(int parameterIndex, Array x) throws SQLException {
  this.prepStmt.setArray(parameterIndex, x);
  // TODO Add to tree set
 }

 @Override
 public ResultSetMetaData getMetaData() throws SQLException {
  return this.prepStmt.getMetaData();
 }

 @Override
 public void setDate(int parameterIndex, Date x, Calendar cal) throws SQLException {
  this.prepStmt.setDate(parameterIndex, x, cal);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {
  this.prepStmt.setTime(parameterIndex, x, cal);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException {
  this.prepStmt.setTimestamp(parameterIndex, x, cal);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setNull(int parameterIndex, int sqlType, String typeName) throws SQLException {
  this.prepStmt.setNull(parameterIndex, sqlType, typeName);
  setParameter(parameterIndex, null);
 }

 @Override
 public void setURL(int parameterIndex, URL x) throws SQLException {
  this.prepStmt.setURL(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public ParameterMetaData getParameterMetaData() throws SQLException {
  return this.prepStmt.getParameterMetaData();
 }

 @Override
 public void setRowId(int parameterIndex, RowId x) throws SQLException {
  this.prepStmt.setRowId(parameterIndex, x);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setNString(int parameterIndex, String value) throws SQLException {
  this.prepStmt.setNString(parameterIndex, value);
  setParameter(parameterIndex, value);
 }

 @Override
 public void setNCharacterStream(int parameterIndex, Reader value, long length) throws SQLException {
  this.prepStmt.setNCharacterStream(parameterIndex, value, length);
 }

 @Override
 public void setNClob(int parameterIndex, NClob value) throws SQLException {
  this.prepStmt.setNClob(parameterIndex, value);
 }

 @Override
 public void setClob(int parameterIndex, Reader reader, long length) throws SQLException {
  this.prepStmt.setClob(parameterIndex, reader, length);
 }

 @Override
 public void setBlob(int parameterIndex, InputStream inputStream, long length) throws SQLException {
  this.prepStmt.setBlob(parameterIndex, inputStream, length);
 }

 @Override
 public void setNClob(int parameterIndex, Reader reader, long length) throws SQLException {
  this.prepStmt.setNClob(parameterIndex, reader, length);
 }

 @Override
 public void setSQLXML(int parameterIndex, SQLXML xmlObject) throws SQLException {
  this.prepStmt.setSQLXML(parameterIndex, xmlObject);
  setParameter(parameterIndex, xmlObject);
 }

 @Override
 public void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength) throws SQLException {
  this.prepStmt.setObject(parameterIndex, x, targetSqlType, scaleOrLength);
  setParameter(parameterIndex, x);
 }

 @Override
 public void setAsciiStream(int parameterIndex, InputStream x, long length) throws SQLException {
  this.prepStmt.setAsciiStream(parameterIndex, x, length);
 }

 @Override
 public void setBinaryStream(int parameterIndex, InputStream x, long length) throws SQLException {
  this.prepStmt.setBinaryStream(parameterIndex, x, length);
 }

 @Override
 public void setCharacterStream(int parameterIndex, Reader reader, long length) throws SQLException {
  this.prepStmt.setCharacterStream(parameterIndex, reader, length);
 }

 @Override
 public void setAsciiStream(int parameterIndex, InputStream x) throws SQLException {
  this.prepStmt.setAsciiStream(parameterIndex, x);
  // TODO Add to tree set
 }

 @Override
 public void setBinaryStream(int parameterIndex, InputStream x) throws SQLException {
  this.prepStmt.setBinaryStream(parameterIndex, x);
 }

 @Override
 public void setCharacterStream(int parameterIndex, Reader reader) throws SQLException {
  this.prepStmt.setCharacterStream(parameterIndex, reader);
 }

 @Override
 public void setNCharacterStream(int parameterIndex, Reader value) throws SQLException {
  this.prepStmt.setNCharacterStream(parameterIndex, value);
 }

 @Override
 public void setClob(int parameterIndex, Reader reader) throws SQLException {
  this.prepStmt.setClob(parameterIndex, reader);
  // TODO Add to tree set
 }

 @Override
 public void setBlob(int parameterIndex, InputStream inputStream) throws SQLException {
  this.prepStmt.setBlob(parameterIndex, inputStream);
 }

 @Override
 public void setNClob(int parameterIndex, Reader reader) throws SQLException {
  this.prepStmt.setNClob(parameterIndex, reader);
 }

}

Answer 7

有点黑客，但对我来说效果很好：

Integer id = 2;
String query = "SELECT * FROM table WHERE id = ?";
PreparedStatement statement = m_connection.prepareStatement( query );
statement.setObject( 1, value );
String statementText = statement.toString();
query = statementText.substring( statementText.indexOf( ": " ) + 2 );

Answer 8

我认为可以在数据库和您的应用程序之间放置代理，然后观察通信。我不知道你会用什么软件来做到这一点。