Overpower the PreparedStatement - Save time debugging your database queries with DebuggableStatement

By Bob Byron and Troy Thompson, JavaWorld.com, 01/25/02

from: http://www.javaworld.com/javaworld/jw-01-2002/jw-0125-overpower.html?page=1

Shortly after you gain database coding experience, you discover the benefits of the java.sql package's PreparedStatement, which delivers a performance boost by reusing database calls, thus more efficiently interacting with the database. Armed with that knowledge, you happily employ PreparedStatement, only to discover that your first debugging session has become more difficult. You know your PreparedStatement's SQL command as well as the statement's parameter values ... or do you? Enter the DebuggableStatement. In this article, we demonstrate how a DebuggableStatement can provide the answers necessary to avoid a potential debugging nightmare.

Note: To download this article's source code, see Resources.

The prepared statement

How an application interacts with the database represents an often-overlooked aspect of application development. Indeed, many programmers find the simplest way to get the data, then leave the database-tuning to the database administrator. However, application-side Java techniques can boost your database's performance, thus improving your application's efficiency. As mentioned above, Java's PreparedStatement interface frequently offers the best way to accomplish that task.
First, consider some background by asking, "Why use a PreparedStatement?" Indeed, you can accomplish your SQL tasks with the Statement class. However, Statement causes problems. Your SQL statements can fall into two categories: static or dynamic. With a static query, the string doesn't change; you create it once, then call it over and over again. In contrast, with a dynamic query, you create a new SQL command by concatenating strings and variables, then instantiating a new Statement object and executing that command. Either way, each time you execute a new command, the database must compile the SQL code. Such compilation can include costly syntax checking, name validation, and pseudo code generation. If you never worry about your database's performance, read no further and live your life in bliss.
If, however, you do worry about your database's performance, you may consider PreparedStatement. A PreparedStatement is the Java encapsulation of a parameterized query in which the SQL statement compiles a single time, but can execute many times. To change the query conditions, you employ placeholders within the statement to indicate bind variables. After such bind variables (parameters to the query) are set, the query executes, (note the question mark indicating a bind variable in the following example's query):

try {
  Connection con = DriverManager.getConnection(url);
  String select = "SELECT * FROM customer_tbl WHERE customer_id = ?";
  PreparedStatement ps = con.prepareStatement(select);
  ResultSet rs = null;
  for(int i=0;i<10;i++) {
//  PreparedStatement Example
    ps.setString(1,""+i);
    rs = ps.executeQuery();
  }
} catch (SQLException sqle) {sqle.printStackTrace();}

In the example, the code executes 10 queries, but, rather than compiling and validating each new SQL string built with a different customer ID, then executing the query, the database sees one query with a bind variable. The database uses the query over and over again, thus avoiding costly compilation and potentially delivering a tremendous performance boost.

Page 2 of 4
So, you're convinced PreparedStatement class is the way to go. You recode your JDBC (Java Database Connectivity) calls using prepared statements. Unfortunately, one of your beautifully prepared SQL statements goes awry by returning an SQLException.

When things go wrong

Now what do you do? When you constructed your SQL string, you could have sent the statement to a log file, and then, perhaps, run the SQL in a database tool. However, that won't work with a prepared statement because you don't have the values in the statement, only the markers. Moreover, when you search the Javadocs, you can't find a way to retrieve the derived statement.
The reason for the problem: The connection object returns the PreparedStatement, which is an interface, not a class. Therefore, the JDK defines the terms for interacting with the PreparedStatement, while the database vendor determines its implementation. The PreparedStatement doesn't include methods that retrieve objects from the driver.
Considering all that, what are your options?

• You could run the debug tool in your favorite IDE, write down the variable values, insert the statement into the database tool, and change the ? to the values.
• You could send the statement and values to the log file as they are set, then reconstruct the statement in the database tool.
• You could capture the string as it goes to the database.

None of these is an appealing option. Each takes and requires more error-prone human intervention. Further, capturing the string using a network tool can prove difficult and might not return discernable results. Yes, you could use a DBMS (database management systems) manager, but then you'd have to find a tool compatible with your database, not to mention the extra cost and setup time.

The solution

Wouldn't it be nice if your PreparedStatement could tell you the SQL string? The solution is found within Java's powerful object-oriented features. We need a proxy that captures the values, then forwards them to the prepared statement. Moreover, the proxy must give more information than we would otherwise receive. The solution: The DebuggableStatement, part of the comprehensive debuggable package, can greatly benefit your debugging efforts.
You probably wonder how implementing the debuggable package affects your code. debuggable requires just three lines of code. First, add a statement importing the debuggable package; second, create your PreparedStatement using the StatementFactory; third, if you want to see the statement, write out the PreparedStatement to the screen or a log file. It doesn't get much easier.
And here's the cool part: whereas the StatementFactory returns a PreparedStatement, the DebuggableStatement is a PreparedStatement. DebuggableStatement implements the PreparedStatement interface. When you call the StatementFactory, you get either a DebuggableStatement proxy or the vendor's PreparedStatement implementation. The debug states you set in the factory determine the class you receive. When you turn debugging on, you receive a DebuggableStatement. Otherwise, you receive the same PreparedStatement the Connection returns. Indeed, when you turn debugging off, you suffer no performance hit with PreparedStatement. For its part, DebuggableStatement creates little overhead.

Use the package

The example below demonstrates how to use the package in its simplest form. The query string sql retrieves all names and rates of employees who earn above a specified amount. (Note: we left the original prepareStatement() call in the code as a comment for your reference.) Notice the new call to the StatementFactory requires the use of the same objects. The connection object and SQL string (con and sql respectively) pass as parameters when creating the PreparedStatement. In this example, one additional parameter activates the debug feature. The methods you call and parameters you pass do not change in any fashion. The final statement that prints out ps gives you your payoff -- your derived SQL statement:

Connection con = DriverManager.getConnection(url);
DebugLevel debug = DebugLevel.ON;
String sql = "SELECT name,rate FROM EmployeeTable WHERE rate > ?";
//Use a factory instead of Connection to get a PreparedStatement.
//PreparedStatement ps = con.prepareStatement(sql);
  PreparedStatement ps = StatementFactory.getStatement(con,sql,debug);
  ps.setInt(1,25);
//If ps is a DebuggableStatement, you see the statement,
//otherwise, an object identifier is displayed
  System.out.println(" debuggable statement= " + ps.toString());

Anatomy of the code

The code's design proves fairly simple. Indeed, in many cases you don't need much code to get what you need from Java. Here are the players:

• StatementFactory: Returns a standard PreparedStatement. However, based on the debug level you pass, the object might be a DebuggableStatement instance.
• DebuggableStatement: A proxy class that saves the SQL statement, all parameters for subsequent display, and the PreparedStatement instance returned by the driver. You should always refer to this class's instances as PreparedStatement, not as DebuggableStatement.
• SqlFormatter: A base class for the vendor formatters. Its key method, format(Object o), returns a formatted, readable string whenever a saved object displays. The method uses instanceof to compare against known classes such as Date, Blob, or Clob.
• VendorSqlFormatter: A formatter tailored to the Vendor's specific SQL format, which refers to the vendor's name. Each VendorSqlFormatter helps overcome differences in vendors' databases by formatting the SQL string appropriate to the specific vendor's syntax. A VendorSqlFormatter extends the SqlFormatter. When you overload the format(Object o) listed in the previous bullet point, you can make an object print however you wish, as detailed later in this article.

Look closely at how the DebuggableStatement works, beginning with the constructor:

private PreparedStatement ps;  // Vendor-generated preparedStatement.
private String sql;            //Original statement going to database.
private String filteredSql;    //Statement filtered for rogue '?' that are not bind variables.
private DebugObject[] variables; //Array of bind variables
private SqlFormatter formatter;  //Format for dates
private long startTime;        //Time that statement began execution
private long executeTime;      //Time elapsed while executing statement
private DebugLevel debugLevel; //Level of debug
protected DebuggableStatement(Connection con, String sqlStatement, SqlFormatter formatter, DebugLevel debugLevel) throws SQLException{
//  Set values for member variables
    this.ps = con.prepareStatement(sqlStatement);
    this.sql = sqlStatement;
    this.debugLevel = debugLevel;
    this.formatter = formatter;
    [... filter out '?' in statement that are not bind variables]
//  Find out how many variables are present in statement.
    int count = 0;
    int index = -1;
    while ((index = filteredSql.indexOf("?",index+1)) != -1){
      count++;
    }
//  Show how many bind variables found
    if (debugLevel == DebugLevel.VERBOSE)
      System.out.println("count= " + count);
//  Create array for bind variables
    variables = new DebugObject[count];
  }

In the code above:

• The connection obtains the PreparedStatement from the vendor's driver, just as if you weren't using the debuggable package. Save the other passed parameters just as they are.
• Filter and save the statement in filteredSql, replacing question marks not considered bind variables with a #7 Unicode character (the bell character) so as not to confuse them. The question marks are restored into the string returned by the toString() method so you will see the statement accurately.
• The number of question marks remaining in the filteredSql indicate the number of bind variables contained in the SQL statement. The String's indexOf() method returns the character position. By successively looping through the question marks' positions, you can count the total number. That is, indexOf() finds a question mark, then goes to the next, and so on until indexOf() returns a -1, indicating no more question marks.
• With the bind-variables count in hand, you can allocate a DebugObjects array. In addition to holding the bind value, that debug object also has a Boolean indicating a set bind value. The Boolean indicator ensures you know when a bind value has been intentionally set to null.

Next, let's examine two frequently called methods for a prepared statement -- setInt() and setString():

public void setInt(int parameterIndex, int x) throws SQLException {
  saveObject(parameterIndex, new Integer(x));
  ps.setInt(parameterIndex,x);
}
public void setString(int parameterIndex, String x) throws SQLException{
  saveObject(parameterIndex, x);
  ps.setString(parameterIndex, x);
}
private void saveObject(int parameterIndex, Object o)
  throws ParameterIndexOutOfBoundsException {
  if(parameterIndex > variables.length)
    throw new ParameterIndexOutOfBoundsException(errMsg);
  variables[parameterIndex-1] = o;
}

The pass-through setters above call the saveObject() method to save the value, then call the PreparedStatement's associated method as normal. The above class's single purpose is to save the value, which allows you to later return the values via the toString() method. The saveObject() method checks the bounds to ensure the index has not exceeded the bind variable count.

Format the SQL for a specific database

Although there's an SQL standard, vendor implementations still differ, especially in the Date, Time, and Timestamp formats. To accommodate such differences, the SqlFormatter base class implements expanded formats. Overloading the class's format(Object o) method, called whenever a bind value will be displayed, lets you take charge of formatting. You look for the specific object using instanceof. For example, if you want the dates formatted for an Oracle database, create an OracleSqlFormatter, which extends SqlFormatter, and add a formatDate() method to accommodate your desired format:

public String format(Object o) throws SQLException{
  if (o == null)               return "NULL";
  if (o instanceof Blob)      return format((Blob)o);
  if (o instanceof Clob)       return format((Clob)o);
  if (o instanceof Array)      return format((Array)o);
  if (o instanceof Ref)        return format((Ref)o);
  if (o instanceof String)     return format((String)o);
  return o.toString();
}
public String formatDate(java.sql.Date date){
  return "TO_DATE('" + new java.sql.Timestamp(date.getTime()) +
    "','YYYY-MM-DD HH24:MI:SS.#')";
}

If you frequently use another database, you can write a custom Formatter for it by overriding the methods. If the specific format is unimportant to you, then the default formatter presents a general format that lets you see the executed query.

Page 4 of 4

Wrap up

The debuggable package gives greater insight into your PreparedStatement object, and lets you verify the derived commands sent to the database. By guaranteeing the values you've sent, you can use a database tool to verify your expectations. Further, you can tailor the statements to your database. Moreover, the package helps you pinpoint performance issues by recording the execution times. The bottom line: use the PreparedStatement class for efficiency, but use debuggable to overcome the resulting debugging issues. Happy debugging!

About the author

Bob Byron, a Sun Certified Java 2 Programmer, is an enterprise systems consultant with RAD Systems, Inc., and has been developing Java Applications since 1998. He serves as the vice president of the Dallas Java MUG and leads a local Java study group. Troy Thompson is a Java architect for the Freeman Companies. Troy, also a Sun Certified Java 2 Programmer, taught Java for three years, and now works on J2EE (Java 2 Platform, Enterprise Edition) Web applications. Find out more about Troy at The Java Underground.