Re: get the query created by PreparedStatement

Eric,

I have always wanted the same thing for both debugging as well as logging.
The best I have been able to come up with is a wrapper class to
PreparedStatement.  This works well with the exception that some of the
methods of PreparedStatement are depreciated and you will always get a
compiler warning about them (but you have to implement them because your
implementing the PreparedStatement interface).

All you need do is put the code in a package of your choosing and implement
the static StringUtil function that simply counts the '?'s in the query.  A
simple statement.toString() should give you useful output.

Hope this helps.



package com.thuna.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Ref;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Array;
import java.sql.ResultSetMetaData;
import java.util.Calendar;
import java.sql.SQLWarning;
import java.sql.Connection;
import java.sql.SQLException;
import com.thuna.misc.StringUtil;

/**
  * a wrapper class for prepared statments to allow them to be logged
  */
public class PreparedStatementLogable
        implements PreparedStatement
{
    PreparedStatement stmt;
    String sql;
    String[] parm;

    public PreparedStatementLogable(Connection con, String sql)
            throws SQLException
    {
        this.stmt = con.prepareStatement(sql);
        this.sql = sql;
        parm = new String[StringUtil.countOccurances(sql, "?")];
    }

    public String toString() {
        StringBuffer buff = new StringBuffer(sql);
        for (int i=0; i<parm.length; i++) {
            buff.append("\n").append(parm[i]);
        }
        return buff.toString();
    }

/****************************************************************************
****/ /* implement PreparedStatement
/****************************************************************************
****/ public ResultSet executeQuery() throws SQLException {
        return stmt.executeQuery();
    }

    public int executeUpdate() throws SQLException {
        return stmt.executeUpdate();
    }

    public void setNull(int parameterIndex, int sqlType) throws SQLException
 { parm[parameterIndex-1] = null;
        stmt.setNull(parameterIndex, sqlType);
    }

    public void setBoolean(int parameterIndex, boolean x) throws SQLException
{
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setBoolean(parameterIndex, x);
    }

    public void setByte(int parameterIndex, byte x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setByte(parameterIndex, x);
    }

    public void setShort(int parameterIndex, short x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setShort(parameterIndex, x);
    }

    public void setInt(int parameterIndex, int x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setInt(parameterIndex, x);
    }

    public void setLong(int parameterIndex, long x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setLong(parameterIndex, x);
    }

    public void setFloat(int parameterIndex, float x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setFloat(parameterIndex, x);
    }

    public void setDouble(int parameterIndex, double x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setDouble(parameterIndex, x);
    }

    public void setBigDecimal(int parameterIndex, BigDecimal x) throws
SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setBigDecimal(parameterIndex, x);
    }

    public void setString(int parameterIndex, String x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setString(parameterIndex, x);
    }

    public void setBytes(int parameterIndex, byte[] x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setBytes(parameterIndex, x);
    }

    public void setDate(int parameterIndex, java.sql.Date x) throws
SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setDate(parameterIndex, x);
    }

    public void setTime(int parameterIndex, Time x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setTime(parameterIndex, x);
    }

    public void setTimestamp(int parameterIndex, Timestamp x) throws
SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setTimestamp(parameterIndex, x);
    }

    public void setAsciiStream(int parameterIndex, InputStream x, int length)
throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setAsciiStream(parameterIndex, x, length);
    }

    public void setUnicodeStream(int parameterIndex, InputStream x, int
length) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setUnicodeStream(parameterIndex, x, length);
    }

    public void setBinaryStream(int parameterIndex, InputStream x, int
length) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setBinaryStream(parameterIndex, x, length);
    }

    public void clearParameters() throws SQLException {
        parm = new String[parm.length];
        stmt.clearParameters();
    }

    public void setObject(int parameterIndex, Object x, int targetSqlType,
int scale) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setObject(parameterIndex, x, targetSqlType, scale);
    }

    public void setObject(int parameterIndex, Object x, int targetSqlType)
throws SQLException {
        parm[parameterIndex-1] = "" + x;
        stmt.setObject(parameterIndex, x, targetSqlType);
    }

    public void setObject(int parameterIndex, Object x) throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setObject(parameterIndex, x);
    }

    public boolean execute() throws SQLException {
        return stmt.execute();
    }

    public void addBatch() throws SQLException {
        stmt.addBatch();
    }

    public void setCharacterStream(int parameterIndex, Reader reader, int
length) throws SQLException {
        parm[parameterIndex-1] = reader.toString();
        stmt.setCharacterStream(parameterIndex, reader, length);
    }

    public void setRef(int i, Ref x) throws SQLException {
        parm[i-1] = String.valueOf(x);
        stmt.setRef(i, x);
    }

    public void setBlob(int i, Blob x) throws SQLException {
        parm[i-1] = String.valueOf(x);
        stmt.setBlob(i, x);
    }

    public void setClob(int i, Clob x) throws SQLException {
        parm[i-1] = String.valueOf(x);
        stmt.setClob(i, x);
    }

    public void setArray(int i, Array x) throws SQLException {
        parm[i-1] = String.valueOf(x);
        stmt.setArray(i, x);
    }

    public ResultSetMetaData getMetaData() throws SQLException {
        return stmt.getMetaData();
    }

    public void setDate(int parameterIndex, Date x, Calendar cal) throws
SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setDate(parameterIndex, x, cal);
    }

    public void setTime(int parameterIndex, Time x, Calendar cal) throws
SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setTime(parameterIndex, x, cal);
    }

    public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal)
throws SQLException {
        parm[parameterIndex-1] = String.valueOf(x);
        stmt.setTimestamp(parameterIndex, x, cal);
    }

    public void setNull(int paramIndex, int sqlType, String typeName) throws
SQLException {
        parm[paramIndex-1] = null;
        stmt.setNull(paramIndex, sqlType, typeName);
    }

    public ResultSet executeQuery(String sql) throws SQLException {
        return stmt.executeQuery(sql);
    }

    public int executeUpdate(String sql) throws SQLException {
        return stmt.executeUpdate(sql);
    }

    public void close() throws SQLException {
        stmt.close();
    }

    public int getMaxFieldSize() throws SQLException {
        return stmt.getMaxFieldSize();
    }

    public void setMaxFieldSize(int max) throws SQLException {
        stmt.setMaxFieldSize(max);
    }

    public int getMaxRows() throws SQLException {
        return stmt.getMaxRows();
    }

    public void setMaxRows(int max) throws SQLException {
        stmt.setMaxRows(max);
    }

    public void setEscapeProcessing(boolean enable) throws SQLException {
        stmt.setEscapeProcessing(enable);
    }

    public int getQueryTimeout() throws SQLException {
        return stmt.getQueryTimeout();
    }

    public void setQueryTimeout(int seconds) throws SQLException {
        stmt.setQueryTimeout(seconds);
    }

    public void cancel() throws SQLException {
        stmt.cancel();
    }

    public SQLWarning getWarnings() throws SQLException {
        return stmt.getWarnings();
    }

    public void clearWarnings() throws SQLException {
        stmt.clearWarnings();
    }

    public void setCursorName(String name) throws SQLException {
        stmt.setCursorName(name);
    }

    public boolean execute(String sql) throws SQLException {
        return stmt.execute(sql);
    }

    public ResultSet getResultSet() throws SQLException {
        return stmt.getResultSet();
    }

    public int getUpdateCount() throws SQLException {
        return stmt.getUpdateCount();
    }

    public boolean getMoreResults() throws SQLException {
        return stmt.getMoreResults();
    }

    public void setFetchDirection(int direction) throws SQLException {
        stmt.setFetchDirection(direction);
    }

    public int getFetchDirection() throws SQLException {
        return stmt.getFetchDirection();
    }

    public void setFetchSize(int rows) throws SQLException {
        stmt.setFetchSize(rows);
    }

    public int getFetchSize() throws SQLException {
        return stmt.getFetchSize();
    }

    public int getResultSetConcurrency() throws SQLException {
        return stmt.getResultSetConcurrency();
    }

    public int getResultSetType() throws SQLException {
        return stmt.getResultSetType();
    }

    public void addBatch(String sql) throws SQLException {
        stmt.addBatch(sql);
    }

    public void clearBatch() throws SQLException {
        stmt.clearBatch();
    }

    public int[] executeBatch() throws SQLException {
        return stmt.executeBatch();
    }

    public Connection getConnection() throws SQLException {
        return stmt.getConnection();
    }
}

Here is a copy of the code

On Sunday 18 March 2001 18:26, you wrote:
> Hi,
>
> Is there an easy way to get the actual query sent by a preparedstatement
> without contstructing it yourself?
>
> I have a long case statment with many setXXX and the whole of my query is
> basicly just
>
> INSERT INTO TABLE(?,?,?,?,?,?,?,?,?)
>
> along with those setXXX statments.
>
> I would like to be able to print out to a log what the resulting SQL ends
> up being. But I don't see any way to do that. Isn't there some part of the
> JDBC API that will give you the last query executed or something like that?
>
>
> Thanks,
>
> Eric
>
>
> Frazier Consulting
> http://www.kwinternet.com/eric
> (250) 655 - 9513
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

========================================
Garry Thuna
Tactical Executive Systems
garry.thuna@tacticalExecutive.com