Featured White Papers

• Why choose an Open Source BPM?
• 10 FAQs on Business Process Management
• Best Practices for Getting Started with BPM
• Free Your Devs From The Pain Of Java Redeploys
• Increase Velocity 40% by Using JRebel
• What Developers Want: The End of Application Redeploys
• Developer Productivity Report: Java Tools, Tech, Devs, and Data
• Coding with JRebel: Java Forever Changed
• Utilizing Fast Testing to Transform Java Development
• 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

• APIs for Java Programmers: Manage & Convert DOC, XLS, PPT, PDF & More

• Instantly Preview Java Code Changes: JRebel.com

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 2

Follow these Java server-side data -mining patterns with multiple ResultSets for maximum performance

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

How a Java application communicates with a relational database is remarkably similar from case to case, since the application module that communicates with the database performs the same operations. In most cases, the communication module also performs all bulk extraction and insertion operations in the same order as other JDBC database drivers. Unless the application system is developed inside the database itself (using database-compiled stored procedures to contain all functionality), you must compose a Statement of several parallel SQL queries or statements in order to retrieve multiple ResultSets. Should the application system use database-compiled stored procedures, your options on how to acquire multiple ResultSet objects vary depending on the database's capabilities and its JDBC driver. You can use all the patterns discussed in this article with JDBC versions 1.22 and 2.0.

Two obvious data mining scenarios exist when extracting multiple ResultSets from a database: they are the multiple SQL queries scenario and the CallableStatement multiple ResultSet scenario. These scenarios are both likely to work on any database that is a candidate for industrial-strength application deployment, since the JDBC API is developed to support them properly. As a minimum discussion grounds for this article, an industrial-strength database should be able to handle database-compiled stored procedures.

Multiple ResultSets at a glance

The theory behind handling multiple ResultSets is very simple. The database driver handles a set of ResultSets instead of the single ResultSet you may be used to. Thus, the logical (and simplified) structure returned by a database call of either the multiple SQL queries scenario or the CallableStatement multiple ResultSet scenario can be thought of as a java.util.List. The elements of that List are the ResultSets returned by the database as a response to the SQL query.

Figure 1. Multiple ResultSets as a List

You can retrieve the current element in the results list by using the getResultSet() or getUpdateCount() method. There is no method in the JDBC that will tell you what type of result you are currently examining (for example, a ResultSet object or updateCount int primitive). Instead, the two getter methods return special results (null or -1) to indicate that the current result is of an unexpected type. Both getter methods return special results when the end of the results list has been reached. You can use the getMoreResults() method to move the current result reference forward in the list of database results. Note that the getMoreResults() method should close any ResultSet it previously opened -- but I recommend testing the JDBC driver implementation before assuming that this is the case.