wake-up-neo.com

Abfrage von Java.sql.PreparedStatement abrufen

In meinem Code verwende ich Java.sql.PreparedStatement.

Anschließend führe ich die setString() -Methode aus, um die Platzhalter der vorbereiteten Anweisung auszufüllen.

Kann ich die letzte Abfrage abrufen (und ausdrucken), bevor die Methode executeQuery() aufgerufen und die Abfrage ausgeführt wird? Ich möchte dies nur zu Debugging-Zwecken.

javajdbcprepared-statement
152
llm

Dies ist nirgends im JDBC-API-Vertrag festgelegt, aber wenn Sie Glück haben , gibt der betreffende JDBC-Treiber möglicherweise die vollständige SQL zurück, indem Sie einfach PreparedStatement#toString(). Das heißt.

System.out.println(preparedStatement);

Nach meiner Erfahrung sind dies mindestens die PostgreSQL 8.x- und MySQL 5.x-JDBC-Treiber. Für den Fall, dass Ihr JDBC-Treiber dies nicht unterstützt, verwenden Sie am besten einen Anweisungswrapper, der alle setXxx() -Methoden protokolliert und schließlich eine SQL-Zeichenfolge auf toString() basierend auf den protokollierten Informationen auffüllt . Zum Beispiel Log4jdbc oder P6Spy .

150
BalusC

Sie können versuchen, toString() für die vorbereitete Anweisung aufzurufen, nachdem Sie die Bindungswerte festgelegt haben.

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

Dies funktioniert, wenn Sie den JDBC-MySQL-Treiber verwenden, aber ich bin nicht sicher, ob dies in anderen Fällen der Fall ist. Möglicherweise müssen Sie alle von Ihnen vorgenommenen Bindungen im Auge behalten und diese dann ausdrucken.

Beispielausgabe des obigen Codes.

Before : [email protected]: SELECT * FROM test WHERE blah1=** NOT SPECIFIED ** and blah2=** NOT SPECIFIED **
After : [email protected]: SELECT * FROM test WHERE blah1='Hello' and blah2='World'
27
Rich Adams

Für diejenigen unter Ihnen, die nach einer Lösung für Oracle suchen, habe ich eine Methode aus dem Code von Log4Jdbc erstellt. Sie müssen die Abfrage und die an prepareStatement übergebenen Parameter bereitstellen, da das Abrufen von ihnen etwas mühsam ist:

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();
        }
    }
}
22
qwertzguy

Sie können Ihrem Projekt log4jdbc hinzufügen. Dies fügt die Protokollierung von SQL-Befehlen hinzu, während sie viele andere Informationen ausführen.

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

12
sproketboy

Wenn Sie nur die Abfrage protokollieren möchten, fügen Sie der jdbc-URL 'logger' und 'profileSQL' hinzu:

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

Dann erhalten Sie die folgende SQL-Anweisung:

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

Der Standard-Logger ist:

com.mysql.jdbc.log.StandardLogger

Liste der Mysql-JDBC-Eigenschaften: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

6
imxylz

Ich habe eine Problemumgehung vorgenommen, um dieses Problem zu lösen. Besuchen Sie den folgenden Link für weitere Informationen http://code-outofbox.blogspot.com/2015/07/Java-prepared-statement-print-values.html

Lösung:

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

}
5
Ali Helmy

Ein bisschen hacken, aber es funktioniert gut für mich:

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 );
3
starryknight64

Ich würde annehmen, dass es möglich ist, einen Proxy zwischen der DB und Ihrer App zu platzieren und dann die Kommunikation zu beobachten. Ich bin nicht mit der Software vertraut, die Sie dazu verwenden würden.

2
WoodenKitty