Newsletter sign-up
View all newsletters

Enterprise Java Newsletter
Stay up to date on the latest tutorials and Java community news posted on JavaWorld

Sponsored Links

Optimize with a SATA RAID Storage Solution
Range of capacities as low as $1250 per TB. Ideal if you currently rely on servers/disks/JBODs

JavaWorld.com > Java Enterprise Edition >

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

Java server-side data mining patterns made powerful with JDBC

By Lennart Jorelid, JavaWorld.com, 01/21/00

  • Print
  • Feedback
.

Page 3 of 4

Scenario Database Capabilities Description
1 Stored 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.
3 Stored 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.
4 Stored 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.

Scenario Database 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.

Some JDBC drivers have solved the problem by treating a ResultSet object as an object of type java.lang.Object. Thus, the outgoing ResultSet may be handled as an object that is cast into a java.sql.ResultSet before actually being used to extract data. This particular way of handling ResultSets suggests that adding the method getResultSet(int index) in java.sql.CallableStatement and the constant java.sql.Types.RESULTSET could improve the JDBC API.

As an alternative, the ResultSet could be extracted with the Object getObject(int index) method, and later cast to a ResultSet. Check your driver documentation to see if this is permitted and possible.

JDBC skeleton code for Scenario 2

Essentially, the same problems that pertain to our first scenario also pertain to the second. Thus, in order to receive a ResultSet from a CallableStatement using a database driver that cannot handle CURSOR-type return values, driver-specific method calls (and, optionally, type specifications) are required: 

// 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.
    // 
    // 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(2, MyDriverClass.RESULTSET_TYPE);
    aStatement.registerOutParameter(1, Types.<errorStatusType>);
                
    // 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.
    //
    // *Messy*
    //
    ResultSet valueReceived = ((<SpecificDriverClass>) aStatement).getResultSet(2);
    int errorStatus = aStatement.get<errorStatusType>(1);
    
    // Handle the status code of the call
    if(errorStatus == <completelyMad>)
    {
        // Bail out
        throw new SQLException("Database call broken with status: " + errorStatus);
    }
    
    // Handle the received ResultSet
    while(valueReceived.next())
    {
        // ...
    }
}
catch(Exception ex)
{
    // Log an error message
    System.err.println("Database communication error: " + ex);
}


JDBC skeleton code for Scenarios 3 and 4

The third scenario is convenient, and relies fully on JDBC-standard method calls. However, few modern relational databases can handle the syntax given in it ({? = call calculate_living_costs()}). It still has an advantage over scenarios 1 and 2 because the Java code is database independent. Thus, for portability reasons scenario 3 (described below) is so far the best usage scenario. Scenario 4 is handled in the same way as scenario 3 for our purposes; note that one sometimes has to register the output ResultSet as a parameter. Check the driver documentation.

  • Print
  • Feedback