MathJax

SyntaxHighlighter

Highlight

Custom CSS

Friday, October 20, 2006

JDBC PreparedStatement Logging for Java

The JDBC PreparedStatement interface doesn't provide a reliable way to log your SQL queries. While you can always log the statement that's being prepared, more often than not, you need more information than INSERT INTO SOMETABLE VALUES(?, ?, ?, ?, ?). You need to be able to see how data is being sent to the database, and PreparedStatement, on its own, gives you no reliable way to do that.

An article by IBM shows you have to implement a PreparedStatement interface that wraps around a real statement so that you can capture parameters for logging. However, when I tried the example code, it didn't compile under Java 5.0 (presumably because of the new features for PreparedStatement - though better query logging was mysteriously missing as a feature).

You may download my modifications to LoggableStatement below.

Example

From IBM's article:

String sql = "select foo, bar from foobar where foo < ? "
    + "and bar = ?";
long fooValue = 99;
String barValue = "christmas";

Connection conn = dataSource.getConnection();
PreparedStatement pstmt;

if(logEnabled) // use a switch to toggle logging.
  pstmt = new LoggableStatement(conn,sql);
else
  pstmt = conn.prepareStatement(sql);

pstmt.setLong(1,fooValue);
pstmt.setString(2,barValue);

if(logEnabled)
  System.out.println("Executing query: "+
      ((LoggableStatement)pstmt).getQueryString());

ResultSet rs = pstmt.executeQuery();

Update

The link to loggable statement is gone, I know. Wait, why are you still using straight JDBC?
Post a Comment