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

Follow these Java server-side data -mining patterns with multiple ResultSets for maximum performance

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.

Two obvious data-mining scenarios
Usage scenarioDescriptionDiagram of Scenario
Multiple SQL queries scenarioCreate a Statement (or PreparedStatement) from the open database connection. Pass multiple SQL queries to the statement and receive a ResultSet with the logical view from the database.
Standard ResultSet Scenario
CallableStatement multiple ResultSet scenarioCreate a CallableStatement from the open database connection. Pass an SQL procedure call (executing a database StoredProcedure) to the CallableStatement and receive multiple ResultSets.
Stored Procedure Scenario

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.

Figure 1. Multiple ResultSets as a List

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.

1 2 3 4 Page 1
Page 1 of 4