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

Java server-side data mining patterns made powerful with JDBC

Modern application systems tend to rely heavily on server-side data storage support using one or more databases. The best way to contact a database from a server-side Java application is by using the class hierarchy in the java.sql package. However, while most JDBC classes and interfaces are well designed, my experience is that corporations building data-mining systems in Java often use simpler, rather than more powerful, JDBC usage patterns. By augmenting the JDBC API as shown in this article, you'll become aware of the possibilities it provides. The usage patterns I describe here can be implemented with JDBC version 1.22 and 2.0.

Current obvious usage scenarios

Two obvious data-mining scenarios, the standard ResultSet and CallableStatement scenarios, exist for creating server-side applications that talk to a database. You can likely use these scenarios on any database for industrial-strength application deployment, since the JDBC API is developed to support them properly. For the specific purposes of this article, let's assume that we are working with an industrial-strength database that can handle database-compiled stored procedures.

The table below describes our two scenarios.

Usage ScenarioDescriptionDiagram of Scenario
Standard ResultSet scenarioCreate a Statement (or PreparedStatement) from the open database Connection. Pass the SQL query to the Statement and receive a ResultSet with the logical view from the database.
Standard ResultSet Scenario
Standard CallableStatement scenarioCreate a CallableStatement from the open database Connection. Pass an SQL procedure call (executing a database stored procedure) to the CallableStatement and receive a primitive or string from the database.
Standard CallableStatement Scenario

Table 1. Two obvious data-mining scenarios

Below is the skeleton code for the standard ResultSet scenario:

// Database connection supposedly // already made. Connection conn; Statement aStatement = null; ResultSet aResultSet = null;

try { // Create the database statement. aStatement = conn.createStatement(); // Ship the question to the database aResultSet = aStatement.executeQuery("<some_SQL_query>");

// Handle the result while(aResultSet.next()) { // ... } } catch(Exception ex) { // Log an error message System.err.println("Database communication error: " + ex); }

And here is the skeleton code for the standard CallableStatement scenario:

// Database connection supposedly // already made. Connection conn; CallableStatement aStatement = null;

try { // Create the database callable statement. // For example, assume that the "<Some_SQL_Procedure_call>" // is something like "{call calculate_interest_rate(?)}" // where the '?' denotes an output parameter. aStatement = conn.prepareCall("<Some_SQL_Procedure_call>"); // Register a primitive output parameter, in this // example case of SQL type INTEGER. All type constants // can be found in the java.sql.Types class. aStatement.registerOutParameter(1, Types.INTEGER); // Call the procedure in the database aStatement.executeUpdate(); // Handle the output int valueReceived = aStatement.getInt(1); } catch(Exception ex) { // Log an error message System.err.println("Database communication error: " + ex); }

The CallableStatement interface provides a rich variety of getXXX methods. As a result, it can receive parameters of various types from the database stored procedure call. The registered parameters must be declared as OUT parameters in the database stored procedure if getXXX is to receive them.

Limitations of current obvious usage scenarios

It seems that speed and stability are the issues most important to database-application engineers when they begin developing server-side Java applications. Although the two standard scenarios described above are quite stable, a hungry developer seeking cutting-edge performance may find them lacking. Depending on the query type and results expected, the two scenarios may be quick enough for all purposes, or they may be terribly lacking in speed.

QuestionAnswer
What type of JDBC query would make the two scenarios above lose performance?An SQL query (returning multiple rows and columns) that wouldbe called multiple times with sufficiently different search conditions to make the database SQL compilation cumbersome -- in other words, the archetype for creating a stored procedure which would return a ResultSet.
Why would I gain speed by creating a database stored procedure instead of sending an SQL query to the database engine?

Although you may not be sure how the database driver class implements its method calls, it is safe to assume that the result is handled more quickly as it is produced closer to the database engine. In general, system bottlenecks are introduced anywhere a network or interprocess connection is established.

Thus, the quickest way to assemble complex two-dimensional ResultSets (i.e., those containing data from several tables, and perhaps calculated values as well) is to create a database stored procedure to call.

Table 2. Performance issues affecting the scenarios in Table 1

Thus, the quickest way to assemble complex, two-dimensional ResultSets (containing data from several tables, and perhaps calculated values as well), is to create a database stored procedure to call.

From a design standpoint, system encapsulation increases if important database functionality is hidden behind a procedure name. By sharing advantages similar to object-oriented encapsulation, system flexibility increases, assuming that you can restructure database data without affecting the Java server application. In other words, decoupling the database physical view from the logical view as seen by the application leads to a faster and more maintainable system.

You could improve server-side database communication performance by using a hybrid of these two scenarios, as described in the table below:

Usage ScenarioDescriptionDiagram of Scenario
ResultSet CallableStatement scenarioCreate a CallableStatement from the open database Connection. Pass an SQL procedure call (executing a database stored procedure) to the CallableStatement and receive a ResultSet from the database.
ResultSet CallableStatement Scenario

Table 3. The hybrid ResultSet CallableStatement scenario

Although the ResultSet CallableStatement scenario seems simple enough to realize in Java code, the differences between databases and JDBC database drivers are now growing painfully visible to the developer. Despite the fact that JDBC is supposed to be mostly platform- and database-independent, the specifics of each database are visible not only when defining a stored procedure, but also when trying to return a ResultSet from one. Some database engines cannot return values from stored procedures; values that should be returned to the Java application must instead be declared as output parameters. Other databases may return only some kinds of types from a stored procedure call (generally INTEGER values to indicate the error code status of a procedure call). There are still other database types that might be able to return a full JDBC ResultSet equivalent (in some cases, this would correspond to a database CURSOR type), either as a result from a stored procedure call or as an output parameter. Depending on the capabilities of the stored procedure execution engine being called, different calling methods may be used, as described in the table below:

ScenarioDatabase CapabilitiesDescription
1Stored procedures cannot return values, but may return output parameters.Input and output parameters alike must be set/registered in the CallableStatement prior to executing the query.
2

Stored procedures can return values, but only primitives (such as INTEGER error codes).

Stored procedures may return output parameters of any kind.

Unless the desired return value of a stored procedure is of the specific type(s) that may be returned, all parameters must be set/registered in the CallableStatement prior to executing the query.
3Stored procedures may return values of any type.ResultSets may be returned directly or as output parameters from a stored procedure call. All input parameters must be set before executing the query.
4Stored procedures may return (multiple) implicit ResultSets without using output parameters or return values.(Multiple) ResultSets may be returned implicitly from the stored procedure call. No output parameters are required.

Table 4. Different calling methods for various database capabilities

Below is the database pseudocode to match the above scenarios. A detailed description of each scenario follows in the next section.

ScenarioDatabase pseudocode
1

CREATE PROCEDURE <procedureName> (theResult CURSOR OUTPUT, <otherParameters>) AS

/* Gather data from all over database */ /* .... */

END PROCEDURE

2

CREATE PROCEDURE <procedureName> (theResult CURSOR OUTPUT, <otherParameters>) AS

/* Gather data from all over database */ /* .... */ RETURN <statusCode> END PROCEDURE

3

CREATE PROCEDURE <procedureName> (<inputParameters>)RETURNING CURSOR AS

/* Gather data from all over database */ /* .... */ RETURN <aCursor> END PROCEDURE

4

CREATE PROCEDURE <procedureName> (<inputParameters>) AS

/* Gather data from all over database */ /* .... */ SELECT .... /* Creates an output ResultSet */ END PROCEDURE

Table 5. Database pseudocode for the various scenarios in Table 4

The JDBC calling structure (and the corresponding Java server application implementation) is actually quite dependent upon the database engine's capabilities. Should you want multiple ResultSet objects returned from a stored procedure, the situation gets more complex. In the next issue of JavaWorld, I will suggest a general Java application design that should lessen the database's impact on the application when receiving multiple ResultSets from a call to a stored procedure. At the moment, however, consider developing a Java application that talks to a database using our four different capability scenarios. For the time being, you need only concern yourself with output ResultSet objects.

JDBC skeleton code for Scenario 1

The code sample below is an example of a JDBC implementation of our first scenario.

// Database connection supposedly // already made. Connection conn; CallableStatement aStatement = null;

try { // Create the database callable statement. // For example, assume that the "<Some_SQL_Procedure_call>" // is something like "{call calculate_living_costs(?)}" // where the '?' denotes an output parameter that should // be interpreted as a java.sql.ResultSet aStatement = conn.prepareCall("<Some_SQL_Procedure_call>"); // Register a primitive output parameter // as a .... WHAT? // // Remember, no static final parameter that would indicate a ResultSet // or database CURSOR type exists in the java.sql.Types class. // The only standard option is to use the ResultSet.getObject method // to extract the CURSOR, then hope that it can be cast into a // java.sql.ResultSet. // // Thus, you have to consult the database JDBC driver documentation // to see how an output CURSOR/ResultSet should be registered. // // In most cases, the type info parameter sent in to the // registerOutParameter method is a type native to the // driver. The result: Non-standard type behavior... // // *Messy* // aStatement.registerOutParameter(1, MyJDBCDriverTypes.RESULTSET_TYPE); // Call the procedure in the database aStatement.execute(); // Handle the output // // Unfortunately, there is no method getXXX that returns a // java.sql.ResultSet object in the java.sql.CallableStatement // interface. // // Thus, you have to consult the database JDBC driver documentation // to see what method you would call to interpret the outgoing // database CURSOR as a java.sql.ResultSet. // // Hopefully, you could extract a java Object, which could be // cast into a ResultSet. // ResultSet valueReceived = ((<SpecificDriverClass>) aStatement).getResultSet(1);

// Handle the received ResultSet while(valueReceived.next()) { // ... } } catch(Exception ex) { // Log an error message System.err.println("Database communication error: " + ex); }

The most obvious problem with this approach is that the code lacks database independence. This is because the two calls that register the outgoing ResultSet parameter and extract the data itself use JDBC driver-specific code, since the java.sql.Types and java.sql.CallableStatement lack direct support for handling ResultSets as output parameters.

1 2 Page 1
Page 1 of 2