Featured White Papers

• Measuring Risk to Improve Java Software Quality
• Do You Really Get Classloaders?
• Pragmatic Continuous Delivery with Jenkins, Nexus, and LiveRebel
• Your Next Java Web App: Less XML, No Long Restarts, Fewer Hassles
• Coding with JRebel, Java Forever Changed
• 2012 Developer Productivity Report: Java Tools, Tech, Devs, and Data

Sponsored Links

• Graph databases are 1000x faster than relational.

  Learn More!

• Career opportunities at Harman- Careers That'll Rock Your Socks Off.

  Learn More!

• Developer-friendly Java PDF libraries by Qoppa Software

  Live Demos

• Stop blaming your app! Diagnose Java performance issues.

  Free tool >

• UrbanCode: Go From Continuous Integration to
  Continuous Delivery

  Learn More!

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

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

Page 4 of 4

// 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 
    //
    // Scenario 3: "{? = call calculate_living_costs()}" 
    // where the '?' denotes an output parameter that should
    // be interpreted as a java.sql.ResultSet. If you use this syntax
    // you will need to register the output parameter, as per scenario 
    // (1) and (2). For some drivers, you could use the same syntax as
    // scenario four below:
    //
    // Scenario 4: "{call calculate_living_costs()}"
    // The difference is essentially that the database procedure in 
    // the fourth scenario hides any return parameters.
    // From a JDBC standpoint, however, the code is similar.
    aStatement = conn.prepareCall("<Some_SQL_Procedure_call>");
    
    // Thankfully, no need to register a ResultSet output parameter
    // as the return value of the stored procedure is a CURSOR/ResultSet.
    //
    // Most calls to procedures of this type would handle only single 
    // ResultSets return values, though...
    //            
    // Call the procedure in the database
    ResultSet valueReceived = aStatement.executeQuery();
    
    // Handle the received ResultSet
    while(valueReceived.next())
    {
        // ...
    }
}
catch(Exception ex)
{
    // Log an error message
    System.err.println("Database communication error: " + ex);
}

Database support for the ResultSet CallableStatement scenario

How have various driver manufacturers approached the solution to the ResultSet CallableStatement scenario? Here are three examples that assume that a single ResultSet will return (multiple ResultSet scenarios will be covered in the next installment of this series):

// The scenario requires importing some oracle driver types
import oracle.jdbc.driver.*;

CallableStatement aStatement;
ResultSet valueReceived;

// Create a PL/SQL block to open the cursor
// This could be used to call a PL/SQL procedure as well.
aStatement = conn.prepareCall
   ("begin open ? for select * from gamePlayers; end;");

// Oracle uses the OracleTypes.CURSOR type integer to
// designate an output ResultSet
aStatement.registerOutParameter(1, OracleTypes.CURSOR);

// Execute the query
aStatement.execute();

// Oracle uses the non-standard method getCursor 
// to receive the output ResultSet
valueReceived = ((OracleCallableStatement)aStatement).getCursor(1);

// Handle results
while (valueReceived.next())
{
        // ...
}

// The two Transact-SQL databases use a similar technique
// to handle the ResultSet CallableStatement scenario: 
// 
// Only a single ResultSet may be returned in one call,
// unless lots of non-standard functionality should be used.
CallableStatement aStatement;
ResultSet valueReceived;

// Call a stored procedure returning a ResultSet 
aStatement = conn.prepareCall
    ("{call getDataOnAllDogs}");

//Execute the stored procedure and get a result set from it.
valueReceived = aStatement.executeQuery();

// Handle results
while (valueReceived.next())
{
        // ...
}

// The Informix way to handle the ResultSet 
// CallableStatement scenario conforms to the way of 
// Sybase and Microsoft above.

CallableStatement aStatement;
ResultSet valueReceived;

// Call a stored procedure returning a ResultSet 
aStatement = conn.prepareCall
    ("{call someStoredProcedure()}");

//Execute the stored procedure and get a result set from it.
valueReceived = aStatement.executeQuery();

// Handle results
while (valueReceived.next())
{
        // ...
}

Table 6. Various database products and their solutions to the ResultSet CallableStatement scenario

Although the last two examples in the table above might look easy enough to handle from the standpoint of Java application development, note that none of them have dealt with multiple ResultSets returning from a single procedure call.

Conclusion

I've described two of the three principal ways of calling a database to extract information, using the JDBC API: the standard ResultSet and standard Callable Statement scenarios (the third, which I haven't covered, is the class hierarchy in the java.sql package). However, for industrial-strength system performance, you can gain better performance and ease of maintenance by using a combination of these two scenarios. Developers making ample use of the ResultSet CallableStatement scenario can essentially build a database application API that is called from the Java server application, thus encapsulating the physical database layout.

The price you pay when employing this combined scenario is a lack of thorough calling standards; thus, in a small way, you are abandoning the JDBC's database independence.

The authors of this month's server-side Java computing articles will be holding a free online seminar on January 13 at 11:00 a.m. PST. Register to join at http://seminars.jguru.com

About the author

Read more about Enterprise Java in JavaWorld's Enterprise Java section.