Server-side Java: Use JDBC for industrial-strength performance, Part 2

How a Java application communicates with a relational database is remarkably similar from case to case, since the application module that communicates with the database performs the same operations. In most cases, the communication module also performs all bulk extraction and insertion operations in the same order as other JDBC database drivers. Unless the application system is developed inside the database itself (using database-compiled stored procedures to contain all functionality), you must compose a Statement of several parallel SQL queries or statements in order to retrieve multiple ResultSets. Should the application system use database-compiled stored procedures, your options on how to acquire multiple ResultSet objects vary depending on the database’s capabilities and its JDBC driver. You can use all the patterns discussed in this article with JDBC versions 1.22 and 2.0.

Two obvious data mining scenarios exist when extracting multiple ResultSets from a database: they are the multiple SQL queries scenario and the CallableStatement multiple ResultSet scenario. These scenarios are both likely to work on any database that is a candidate for industrial-strength application deployment, since the JDBC API is developed to support them properly. As a minimum discussion grounds for this article, an industrial-strength database should be able to handle database-compiled stored procedures.

Multiple ResultSets at a glance

The theory behind handling multiple ResultSets is very simple. The database driver handles a set of ResultSets instead of the single ResultSet you may be used to. Thus, the logical (and simplified) structure returned by a database call of either the multiple SQL queries scenario or the CallableStatement multiple ResultSet scenario can be thought of as a java.util.List. The elements of that List are the ResultSets returned by the database as a response to the SQL query.

You can retrieve the current element in the results list by using the getResultSet() or getUpdateCount() method. There is no method in the JDBC that will tell you what type of result you are currently examining (for example, a ResultSet object or updateCount int primitive). Instead, the two getter methods return special results (null or -1) to indicate that the current result is of an unexpected type. Both getter methods return special results when the end of the results list has been reached. You can use the getMoreResults() method to move the current result reference forward in the list of database results. Note that the getMoreResults() method should close any ResultSet it previously opened — but I recommend testing the JDBC driver implementation before assuming that this is the case.

The code at a glance

The database table structure used by the JDBC calling code is simply a table named test that contains two columns (id:integer and fname:varchar). In addition to the table, a stored procedure is required. The stored procedure simply performs two select statements in the test table. The implementation of the stored procedure in a TransactSQL-oriented database (such as Sybase or MS SQL Server) is:

CREATE PROCEDURE reverseSelectionProcedure AS
BEGIN
        -- First select statement, producing a 1-column JDBC ResultSet
        select fName from testlogin.Test;
        -- Second select statement, producing a 2-column JDBC ResultSet
        select * from testlogin.Test;
END

The structure of all code samples in this article follow the same pattern: they inherit all functionality, except the main() method, from the MultipleResultsets class. The main() method is fully implemented in each subclass. This complete exclusion code is given in favor of the more elegant abstract factory pattern (see the Resources section below for more information) to improve readability.

The following is the method call sequence:

1. main() calls setupConnection(), which creates the Logger and returns a fully set-up JDBC connection to the database.
2. A PreparedStatement is created from appropriate SQL. Note that CallableStatement extends PreparedStatement.
3. processStatement() is called to print out some status data from the given PreparedStatement.
4. main() closes the Statement from step 2 and Connection from step 1.

The only helper class used is the Logger, which logs messages to a file or the standard output stream, depending on its construction. The only relevant method call of the Logger class is the log() method. Below is the code structure for all scenarios.

First, here is the code structure of the Logger class:

import java.io.*;
/**
 * Class that logs messages to a file or the
 * standard output stream.
 */
public class Logger
{
   /** Stream connected to the output file. */
   private PrintWriter out;
   /**
    * Constructor to invoke if all messages of
    * this Logger should be directed to the file
    * with the supplied filename.
    * <br>NOTE! The contents of the given file is
    * erased before any message is written to it.
    *
    * @param logFileName Name of the logfile.
    */
   public Logger(String logFileName)
   {
      try
      {
         // Create the log stream
         out = new PrintWriter(new FileWriter(logFileName), true);
      }
      catch(IOException ex)
      {
         System.out.println(" [Logger Constructor]: " + 
                   "Could not create the logFile (" + logFileName + ").");
         System.out.println(" [Logger Constructor]: " + 
                   "Reason is " + ex);
      }
   }
   /**
    * Constructor to invoke if all messages of this Logger should
    * be redirected to the standard output stream.
    */
   public Logger()
   { }
   /**
    * Prints the given message to the log stream, and
    * appends a newline character.
    *
    * @param msg Message to log.
    */
   public void log(String msg)
   {
      // Log the message
      if(out == null) System.out.println(msg);
      else out.println(msg);
   }
}

The common superclass of the examples used in this article is MultipleResultsets, as seen below. It provides two services: setting up the database connection (including loading the drivers) and processing the results of the entire PreparedStatement, which holds the SQL query to the database.

Here is the code structure of the MultipleResultsets class:

import java.sql.*;
public abstract class MultipleResultsets
{
  // Logger helper class
  public static Logger log;
  public static Connection setupConnection() throws Exception
  {
      // Load the database driver
      Class.forName(<databaseDriverClass>);
      // The Logger directs output to the file "log.txt"
      log = new Logger("log.txt");
      log.log("Time: " + new Timestamp(System.currentTimeMillis()));
      // Open a connection to the database
      return DriverManager.getConnection(<jdbcURL>, <login>, <password>);
  }
  public static void processStatement(PreparedStatement stmnt) 
  throws Exception
  {
      // Execute the statement. The boolean return parameter
      // denotes the kind of result obtained from execution.
      boolean returnedResultSet = stmnt.execute();
      int rsIndex = 0;
      while(true)
      {
          // Did we get a ResultSet?
          if(! returnedResultSet )
          {
               // This might be an update count 
               int updateCount = stmnt.getUpdateCount();
               if(updateCount == -1)
               {
                  System.out.println("nDone handling all results.");
                  break;
               }
               // This IS an update count
               log.log("Updated " + updateCount + " database rows.");
          }
          else
          {
               // This should be a ResultSet
               ResultSet rs = stmnt.getResultSet();
               ResultSetMetaData rsmd = rs.getMetaData();
               // Log ResultSet geometry here, so that we may 
               // see  the number of columns in the ResultSet
               log.log("Got ResultSet[" + (++rsIndex) + "]: " 
                                        + rsmd.getColumnCount() + " columns.");
               // Do not forget to close the ResultSet and 
               // release the database resources owned by it!
               // 
               // Since we use the getMoreResults() method to
               // obtain the next ResultSet or updateCount, the
               // explicit close is not required. However, I do
               // recommend closing explicitly at all times to
               // eliminate bad driver behavior/implementation.
               // 
               // Check, for instance, the JDBC/ODBC bridge driver here....
               rs.close();
          }
          // Get the next ResultSet returned from the Statement.
          // If there are no more Results, the getMoreResults method
          // will return false.
          
if(!stmnt.getMoreResults()) break;

      }
  }
}

The two classes SimpleSQLMultipleResultsets and StoredProcedureMultipleResultsets extend MultipleResults and provide a main method implementation that realizes the two usage scenarios described above. The SimpleSQLMultipleResultsets class uses an SQL query that returns two ResultSets from the database.

Here’s the skeleton code of the multiple SQL queries scenario:

import java.sql.*;
public abstract class SimpleSQLMultipleResultsets extends MultipleResultsets
{
  public static void main(String[] args) throws Exception
  {
      Connection conn = setupConnection();
      // Create a SQL query returning multiple ResultSets
      String sql = "select * from test; select fname from test";
      PreparedStatement stmnt = conn.prepareStatement(sql);
      processStatement(stmnt);
      // Close all database resources
      stmnt.close();
      conn.close();
  }
}

When run, the SimpleSQLMultipleResultsets produce the following result:

Time: 1999-12-10 12:27:27.784
Got ResultSet[1]: 2 columns.
Got ResultSet[2]: 1 columns.

When performing the same operation using the database-compiled stored procedure reverseSelectionProcedure, the logic is moved to the database and the Java application acts as a data sink, absorbing all results transmitted by the database. In such a case, you also need to use a CallableStatement object to encapsulate the call to the database stored procedure. No other code needs to be altered, since the CallableStatement interface extends PreparedStatement.

Following is the skeleton code for the CallableStatement multiple ResultSet scenario:

import java.sql.*;
public class StoredProcedureMultipleResultsets extends MultipleResultsets
{
  public static void main(String[] args) throws Exception
  {
      Connection conn = setupConnection();
      // Create a SQL query calling a stored procedure returning multiple ResultSets
      String sql = "{call reverseSelectionProcedure()}";
      CallableStatement stmnt = conn.prepareCall(sql);
      processStatement(stmnt);
      // Close all database resources
      stmnt.close();
      conn.close();
  }
}

When run, the StoredProcedureMultipleResultsets produce the following result:

Time: 1999-12-10 12:44:44.445
Got ResultSet[1]: 1 columns.
Got ResultSet[2]: 2 columns.

Some performance and JDBC driver hints

A good JDBC driver implementation is the key to obtaining good Java/database communication performance. Depending on how well the database and the JDBC driver uses caching and how useful that caching is for stored procedures, performance test results can become difficult to interpret. It is easier to use cache effectively for less difficult questions; I would therefore recommend against using stored procedures as simple as the reverseSelectionProcedure above. If your selection SQL statement string is as simple as the one executed by the reverseSelectionProcedure, you could often reach better performance with a PreparedStatement.

Be aware that some JDBC drivers need thorough coercing before returning multiple ResultSets to you. In some cases, the drivers silently ignore the second ResultSet of the statement. Therefore, you need to test a JDBC driver before using it in industrial-strength systems.

A simple example: When I tried out three different drivers for a database when extracting 10,000 lines that were roughly 80 characters in length apiece, I received running times ranging from 361 to 520 milliseconds in the multiple SQL queries scenario, and 500 milliseconds in the CallableStatement multiple ResultSet scenario when communicating with a database that had no cache. When running the queries a couple of times in sequence to allow the database/JDBC driver to build up its cache, the running times dropped to 10 to 401 milliseconds in the Multiple SQL queries scenario and 441 milliseconds in the CallableStatement multiple ResultSet scenario. The state and type of the drivers varied, and, in one case, my compiler complained about dead or unreachable code in the compiled driver classes.

Conclusion

Reducing network or interprocess calls to extract data from a database boosts speed in the application system. Using transactions or batch handling (provided in the JDBC 2.0 API) improves performance and provides isolation for the database user. As a complement, extracting multiple ResultSets using the multiple SQL queries scenario or the CallableStatement multiple ResultSet scenario could reduce database access time even more. Industrial-strength server-side applications should use this capability if possible.

The authors of this month’s server-side Java computing articles will be holding a free online seminar on February 10 at 10:00 a.m. Pacific Standard Time. Register to join at https://seminars.jguru.com.