Overpower the PreparedStatement

Save time debugging your database queries with DebuggableStatement

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

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.