Обсуждение: get the query created by PreparedStatement
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
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
Quoting Garry Thuna <garry.thuna-pgsql-jdbc@tacticalExecutive.com>:
> Eric,
>
> I have always wanted the same thing for both debugging as well as
> logging.
How about the toString() method ;-)
Ok, may not be standard JDBC, but:
PreparedStatement ps = ....;
// later
org.postgresql.jdbc2.PreparedStatement ps2 =
(org.postgresql.jdbc2.PreparedStatement) ps;
System.out.println(ps2.toString());
Peter
--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Hi, This does work, both with the postgress and mysql drivers. But I do need to make SURE it will work no matter the driver, so it sounds like Garry's code would be the best bet for that. Thoughts? Thanks, Eric >How about the toString() method ;-) > >Ok, may not be standard JDBC, but: > >PreparedStatement ps = ....; > >// later >org.postgresql.jdbc2.PreparedStatement ps2 = > (org.postgresql.jdbc2.PreparedStatement) ps; >System.out.println(ps2.toString()); > >Peter > >-- >Peter Mount peter@retep.org.uk >PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ >RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/ > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513
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