Obtenir requête de java.SQL.PreparedStatement [dupliquer]
cette question a déjà une réponse ici:
dans mon code j'utilise java.sql.PreparedStatement
.
j'exécute ensuite la méthode setString()
pour remplir les caractères génériques de la déclaration préparée.
y a-t-il un moyen pour moi de récupérer (et d'Imprimer) la requête finale avant que la méthode executeQuery()
soit appelée et que la requête soit exécutée? Je veux juste ça pour le débogage.
8 réponses
ceci n'est défini nulle part dans le contrat de L'API JDBC, mais si vous êtes lucky , le pilote JDBC en question peut retourner le SQL complet en appelant simplement PreparedStatement#toString()
. C'est-à-dire:
System.out.println(preparedStatement);
d'après mon expérience, ceux qui le font sont au moins les PostgreSQL 8.X et MySQL 5.x les pilotes JDBC. Pour le cas où votre pilote JDBC ne le supporterait pas, votre meilleure option est d'utiliser une instruction wrapper qui logue toutes les méthodes setXxx()
et finalement popule une Chaîne SQL sur toString()
basée sur les informations enregistrées. Par exemple Log4jdbc ou P6Spy .
vous pouvez essayer d'appeler toString()
sur la déclaration préparée après avoir défini les valeurs de bind.
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());
cela fonctionne lorsque vous utilisez le pilote MySQL JDBC, mais je ne suis pas sûr qu'il le fera dans d'autres cas. Vous devrez peut-être garder une trace de toutes les reliures que vous faites et ensuite les imprimer.
sortie D'échantillon du code ci-dessus.
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'
pour ceux qui cherchent une solution pour Oracle, j'ai fait une méthode à partir du code de Log4Jdbc. Vous aurez besoin de fournir la requête et les paramètres passés à l'état de préparation puisque les extraire de lui est un peu d'une douleur:
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();
}
}
}
vous pouvez ajouter log4jdbc à votre projet. Cela ajoute la journalisation des commandes sql pendant qu'elles exécutent + beaucoup d'autres informations.
j'ai fait un contournement pour résoudre ce problème. Visitez le lien ci-dessous pour plus de détails http://code-outofbox.blogspot.com/2015/07/java-prepared-statement-print-values.html
Solution:
// 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);
}
}
si vous voulez seulement enregistrer la requête, ajoutez 'logger' et 'profileSQL' à l'url jdbc:
&logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true
alors vous obtiendrez la déclaration SQL ci-dessous:
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
le logger par défaut est:
com.mysql.jdbc.log.StandardLogger
Mysql liste de propriétés jdbc: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
un peu d'un piratage, mais il fonctionne très bien pour moi:
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 );
je suppose qu'il est possible de placer un proxy entre le DB et votre application puis d'observer la communication. Je ne sais pas quel logiciel vous utiliseriez pour faire ça.