At my company, I am in our production support team and I can say that it is VERY helpful to log PreparedStatements with the parameters replaced. This makes it very easy to debug problems with SQL in code. I have attached my wrapper PreparedStatement class. Hopefully this helps.
Kevin LaVergne
At 07:47 AM 9/4/2003, Shapira, Yoav wrote:
Howdy, I personally am not a big fan of logging these types of objects: I much prefer logging the beans I create from result sets (or DAO objects, or whatever we call them this week). However, one possibility for you would be to create a ResultSetRenderer (implements http://jakarta.apache.org/log4j/docs/api/org/apache/log4j/or/ObjectRende rer.html) or a PreparedStatementRenderer...
Yoav Shapira Millennium ChemInformatics
>-----Original Message----- >From: Larry Young [mailto:[EMAIL PROTECTED] >Sent: Wednesday, September 03, 2003 8:30 PM >To: [EMAIL PROTECTED] >Subject: logging JDBC objects > >Hello, > > Has anyone looked at logging JDBC objects, like PreparedStatement >and ResultSet classes? They don't have any built-in ways of displaying >their contents, so I'm considering creating a wrapper class to do this for >me, but I thought I'd check the list to see if anyone else has already >solved this problem before I proceed. > >--- regards --- >Larry > > >-------------------------- >Larry Young >The Dalmatian Group >www.dalmatian.com > > > >--------------------------------------------------------------------- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED]
This e-mail, including any attachments, is a confidential business communication, and may contain information that is confidential, proprietary and/or privileged. This e-mail is intended only for the individual(s) to whom it is addressed, and may not be saved, copied, printed, disclosed or used by anyone else. If you are not the(an) intended recipient, please immediately delete this e-mail from your computer system and notify the sender. Thank you.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
package com.parago.common.sql;
import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.Method;
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.ParameterMetaData;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Collection;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.TreeMap;
import org.apache.log4j.Logger;
/**
* An implementation of <code>java.sql.PreparedStatement</code> that has the ability
to log its SQL with the bind variables replaced.
*/
public class PreparedStatement implements java.sql.PreparedStatement
{
private static final String DELIM = "?";
private Logger logger = null;
private java.sql.PreparedStatement innerStatement = null;
private String sql = null;
private Map parameters = new TreeMap();
private boolean logOnExecute = true;
public PreparedStatement(String sqlString, java.sql.PreparedStatement stmt)
{
this(sqlString, stmt, PreparedStatement.class, true);
}
public PreparedStatement(String sqlString, java.sql.PreparedStatement stmt,
boolean aLogOnExecute)
{
this(sqlString, stmt, PreparedStatement.class, aLogOnExecute);
}
public PreparedStatement(String sqlString, java.sql.PreparedStatement stmt, Class
clazz)
{
this(sqlString, stmt, clazz, true);
}
public PreparedStatement(String sqlString, java.sql.PreparedStatement stmt, Class
clazz, boolean aLogOnExecute)
{
logger = Logger.getLogger(clazz);
if (logger.isDebugEnabled())
{
logger.debug("Creating PreparedStatement from \"" + sqlString + "\".");
}
sql = sqlString;
innerStatement = stmt;
logOnExecute = aLogOnExecute;
}
public String getSql()
{
return sql;
}
public Collection getParameters()
{
return parameters.values();
}
public void logStatement()
{
if (logger.isDebugEnabled())
{
logger.debug(getFormattedStatement());
}
}
public String getFormattedStatement()
{
if (parameters.isEmpty())
{
return sql;
}
int count = 1;
StringTokenizer st = new StringTokenizer(sql, DELIM, true);
StringBuffer sb = new StringBuffer();
String token = null;
while (st.hasMoreTokens())
{
token = st.nextToken();
if (token.equals(DELIM))
{
sb.append(parameters.get(new Integer(count)));
count++;
}
else
{
sb.append(token);
}
}
return sb.toString();
}
public void setBigDecimal(int i, BigDecimal decimal) throws SQLException
{
innerStatement.setBigDecimal(i, decimal);
parameters.put(new Integer(i), decimal);
}
public void setRef(int i, Ref ref) throws SQLException
{
innerStatement.setRef(i, ref);
parameters.put(new Integer(i), ref);
}
public void setInt(int i, int i1) throws SQLException
{
innerStatement.setInt(i, i1);
parameters.put(new Integer(i), new Integer(i1));
}
public void setDate(int i, Date date) throws SQLException
{
innerStatement.setDate(i, date);
parameters.put(new Integer(i), date);
}
public void setObject(int i, Object obj) throws SQLException
{
innerStatement.setObject(i, obj);
parameters.put(new Integer(i), obj);
}
public void setTime(int i, Time time) throws SQLException
{
innerStatement.setTime(i, time);
parameters.put(new Integer(i), time);
}
public void setNull(int i, int i1) throws SQLException
{
innerStatement.setNull(i, i1);
parameters.put(new Integer(i), new Integer(i1));
}
public void setBoolean(int i, boolean bool) throws SQLException
{
innerStatement.setBoolean(i, bool);
parameters.put(new Integer(i), new Boolean(bool));
}
public void setBlob(int i, Blob blob) throws SQLException
{
innerStatement.setBlob(i, blob);
parameters.put(new Integer(i), blob);
}
public void setByte(int i, byte b) throws SQLException
{
innerStatement.setByte(i, b);
parameters.put(new Integer(i), new Byte(b));
}
public void setTimestamp(int i, Timestamp timestamp) throws SQLException
{
innerStatement.setTimestamp(i, timestamp);
parameters.put(new Integer(i), timestamp);
}
public void setDate(int i, Date date, Calendar calendar) throws SQLException
{
innerStatement.setDate(i, date, calendar);
}
public void setArray(int i, Array array) throws SQLException
{
innerStatement.setArray(i, array);
parameters.put(new Integer(i), array);
}
public void setShort(int i, short si) throws SQLException
{
innerStatement.setShort(i, si);
parameters.put(new Integer(i), new Short(si));
}
public void setNull(int i, int i1, String str) throws SQLException
{
innerStatement.setNull(i, i1, str);
}
public void setString(int i, String str) throws SQLException
{
innerStatement.setString(i, str);
// StringBuffer does not like a null string
// Since this one is used for logging, an empty
// string should be fine.
if (str == null)
{
str = "";
}
StringBuffer sb = new StringBuffer(str);
sb.insert(0, '\'');
sb.append('\'');
parameters.put(new Integer(i), sb.toString());
}
public void setLong(int i, long l) throws SQLException
{
innerStatement.setLong(i, l);
parameters.put(new Integer(i), new Long(l));
}
public void setClob(int i, Clob clob) throws SQLException
{
innerStatement.setClob(i, clob);
parameters.put(new Integer(i), clob);
}
public void setFloat(int i, float f) throws SQLException
{
innerStatement.setFloat(i, f);
parameters.put(new Integer(i), new Float(f));
}
public void setDouble(int i, double d) throws SQLException
{
innerStatement.setDouble(i, d);
parameters.put(new Integer(i), new Double(d));
}
public void setBytes(int i, byte[] param) throws SQLException
{
innerStatement.setBytes(i, param);
parameters.put(new Integer(i), param);
}
public void setCharacterStream(int i, Reader reader, int i1) throws SQLException
{
innerStatement.setCharacterStream(i, reader, i1);
}
public void setUnicodeStream(int i, InputStream stream, int i1) throws SQLException
{
innerStatement.setUnicodeStream(i, stream, i1);
}
public void setAsciiStream(int i, InputStream stream, int i1) throws SQLException
{
innerStatement.setAsciiStream(i, stream, i1);
}
public void setBinaryStream(int i, InputStream stream, int i1) throws SQLException
{
innerStatement.setBinaryStream(i, stream, i1);
}
public void setObject(int i, Object obj, int i1) throws SQLException
{
innerStatement.setObject(i, obj, i1);
}
public void setTimestamp(int i, Timestamp timestamp, Calendar calendar) throws
SQLException
{
innerStatement.setTimestamp(i, timestamp, calendar);
}
public void setTime(int i, Time time, Calendar calendar) throws SQLException
{
innerStatement.setTime(i, time, calendar);
}
public void setObject(int i, Object obj, int i1, int i2) throws SQLException
{
innerStatement.setObject(i, obj, i1, i2);
}
public void clearParameters() throws SQLException
{
innerStatement.clearParameters();
parameters.clear();
}
public boolean execute() throws SQLException
{
if (logOnExecute)
{
logStatement();
}
return innerStatement.execute();
}
public int executeUpdate() throws SQLException
{
if (logOnExecute)
{
logStatement();
}
return innerStatement.executeUpdate();
}
public ResultSetMetaData getMetaData() throws SQLException
{
return innerStatement.getMetaData();
}
public ResultSet executeQuery() throws SQLException
{
if (logOnExecute)
{
logStatement();
}
return innerStatement.executeQuery();
}
public void addBatch() throws SQLException
{
if (logOnExecute)
{
logStatement();
}
innerStatement.addBatch();
parameters.clear();
}
public ResultSet executeQuery(String str) throws SQLException
{
return innerStatement.executeQuery(str);
}
public int executeUpdate(String str) throws SQLException
{
return innerStatement.executeUpdate(str);
}
public void close() throws SQLException
{
innerStatement.close();
parameters.clear();
}
public int getMaxFieldSize() throws SQLException
{
return innerStatement.getMaxFieldSize();
}
public void setMaxFieldSize(int i) throws SQLException
{
innerStatement.setMaxFieldSize(i);
}
public int getMaxRows() throws SQLException
{
return innerStatement.getMaxRows();
}
public void setMaxRows(int i) throws SQLException
{
innerStatement.setMaxRows(i);
}
public void setEscapeProcessing(boolean bool) throws SQLException
{
innerStatement.setEscapeProcessing(bool);
}
public int getQueryTimeout() throws SQLException
{
return innerStatement.getQueryTimeout();
}
public void setQueryTimeout(int i) throws SQLException
{
innerStatement.setQueryTimeout(i);
}
public void cancel() throws SQLException
{
innerStatement.cancel();
}
public SQLWarning getWarnings() throws SQLException
{
return innerStatement.getWarnings();
}
public void clearWarnings() throws SQLException
{
innerStatement.clearWarnings();
}
public void setCursorName(String str) throws SQLException
{
innerStatement.setCursorName(str);
}
public boolean execute(String str) throws SQLException
{
return innerStatement.execute(str);
}
public ResultSet getResultSet() throws SQLException
{
return innerStatement.getResultSet();
}
public int getUpdateCount() throws SQLException
{
return innerStatement.getUpdateCount();
}
public boolean getMoreResults() throws SQLException
{
return innerStatement.getMoreResults();
}
public void setFetchDirection(int i) throws SQLException
{
innerStatement.setFetchDirection(i);
}
public int getFetchDirection() throws SQLException
{
return innerStatement.getFetchDirection();
}
public void setFetchSize(int i) throws SQLException
{
innerStatement.setFetchSize(i);
}
public int getFetchSize() throws SQLException
{
return innerStatement.getFetchSize();
}
public int getResultSetConcurrency() throws SQLException
{
return innerStatement.getResultSetConcurrency();
}
public int getResultSetType() throws SQLException
{
return innerStatement.getResultSetType();
}
public void addBatch(String str) throws SQLException
{
innerStatement.addBatch(str);
}
public void clearBatch() throws SQLException
{
innerStatement.clearBatch();
parameters.clear();
}
public int[] executeBatch() throws SQLException
{
return innerStatement.executeBatch();
}
public Connection getConnection() throws SQLException
{
return innerStatement.getConnection();
}
/**
* @see java.sql.PreparedStatement#getParameterMetaData()
* @since JDK 1.4
*/
public ParameterMetaData getParameterMetaData() throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.PreparedStatement#setURL(int, URL)
* @since JDK 1.4
*/
public void setURL(int parameterIndex, URL x) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#execute(String, int)
* @since JDK 1.4
*/
public boolean execute(String sql, int autoGeneratedKeys) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#execute(String, int[])
* @since JDK 1.4
*/
public boolean execute(String sql, int[] columnIndexes) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#execute(String, String[])
* @since JDK 1.4
*/
public boolean execute(String sql, String[] columnNames) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#executeUpdate(String, int)
* @since JDK 1.4
*/
public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#executeUpdate(String, int[])
* @since JDK 1.4
*/
public int executeUpdate(String sql, int[] columnIndexes) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#executeUpdate(String, String[])
* @since JDK 1.4
*/
public int executeUpdate(String sql, String[] columnNames) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#getGeneratedKeys()
* @since JDK 1.4
*/
public ResultSet getGeneratedKeys() throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#getMoreResults(int)
* @since JDK 1.4
*/
public boolean getMoreResults(int current) throws SQLException
{
throw new UnsupportedOperationException();
}
/**
* @see java.sql.Statement#getResultSetHoldability()
* @since JDK 1.4
*/
public int getResultSetHoldability() throws SQLException
{
throw new UnsupportedOperationException();
}
}
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
