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

1 2 3 4 Page 3
Page 3 of 4

Here is the code structure of the MultipleResultsets class:

import java.sql.*;

public abstract class MultipleResultsets { // Logger helper class public static Logger log;

public static Connection setupConnection() throws Exception { // Load the database driver Class.forName(<databaseDriverClass>);

// The Logger directs output to the file "log.txt" log = new Logger("log.txt"); log.log("Time: " + new Timestamp(System.currentTimeMillis()));

// Open a connection to the database return DriverManager.getConnection(<jdbcURL>, <login>, <password>); }

public static void processStatement(PreparedStatement stmnt) throws Exception { // Execute the statement. The boolean return parameter // denotes the kind of result obtained from execution. boolean returnedResultSet = stmnt.execute(); int rsIndex = 0;

while(true) { // Did we get a ResultSet? if(! returnedResultSet ) { // This might be an update count int updateCount = stmnt.getUpdateCount();

if(updateCount == -1) { System.out.println("\nDone handling all results."); break; }

// This IS an update count log.log("Updated " + updateCount + " database rows."); } else { // This should be a ResultSet ResultSet rs = stmnt.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData();

// Log ResultSet geometry here, so that we may // see the number of columns in the ResultSet log.log("Got ResultSet[" + (++rsIndex) + "]: " + rsmd.getColumnCount() + " columns.");

// Do not forget to close the ResultSet and // release the database resources owned by it! // // Since we use the getMoreResults() method to // obtain the next ResultSet or updateCount, the // explicit close is not required. However, I do // recommend closing explicitly at all times to // eliminate bad driver behavior/implementation. // // Check, for instance, the JDBC/ODBC bridge driver here.... rs.close(); }

// Get the next ResultSet returned from the Statement. // If there are no more Results, the getMoreResults method // will return false.

if(!stmnt.getMoreResults()) break;

} } }

The two classes SimpleSQLMultipleResultsets and StoredProcedureMultipleResultsets extend MultipleResults and provide a main method implementation that realizes the two usage scenarios described above. The SimpleSQLMultipleResultsets class uses an SQL query that returns two ResultSets from the database.

Here's the skeleton code of the multiple SQL queries scenario:

import java.sql.*;

public abstract class SimpleSQLMultipleResultsets extends MultipleResultsets { public static void main(String[] args) throws Exception { Connection conn = setupConnection();

// Create a SQL query returning multiple ResultSets String sql = "select * from test; select fname from test";

PreparedStatement stmnt = conn.prepareStatement(sql);

processStatement(stmnt);

// Close all database resources stmnt.close(); conn.close(); } }

When run, the SimpleSQLMultipleResultsets produce the following result:

Time: 1999-12-10 12:27:27.784
Got ResultSet[1]: 2 columns.
Got ResultSet[2]: 1 columns.

When performing the same operation using the database-compiled stored procedure reverseSelectionProcedure, the logic is moved to the database and the Java application acts as a data sink, absorbing all results transmitted by the database. In such a case, you also need to use a CallableStatement object to encapsulate the call to the database stored procedure. No other code needs to be altered, since the CallableStatement interface extends PreparedStatement.

1 2 3 4 Page 3
Page 3 of 4