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
.

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 Scenario Description Diagram of Scenario
Standard ResultSet scenario Create 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 scenario Create 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.

Question Answer
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:

  • Print
  • Feedback