Wrapping DBMS stored procedures as methods in session EJB components

Integrating Web application server and database management (DBMS) technologies is a common requirement for many new business applications. This article explores one facet of that integration effort: how to design and develop methods in session Enterprise JavaBeans (EJB) components that wrap or invoke existing DBMS stored procedures.

C. M. Saracco (saracco@us.ibm.com), Software Engineer, IBM

C. M. SaraccoCynthia M. Saracco is a senior software engineer at IBM Silicon Valley Lab. She has published two books and more than 40 papers on technologies related to database management, object-oriented programming, and Web technologies. In addition, she has lectured on these subjects throughout North America, South America, Europe, and the Middle East. You can e-mail her at saracco@us.ibm.com.



02 March 2001

Integrating Web application server and database management (DBMS) technologies is a common requirement for many new business applications. In this article, we'll explore one facet of that integration effort: how to design and develop methods in session Enterprise JavaBeans (EJB) components that wrap or invoke existing DBMS stored procedures. You should be familiar with the fundamentals of EJB technology, the Structured Query Language (SQL), and Java Database Connectivity (JDBC) to get the most from this article.

If you're working on Web-based applications that need to access or modify data in a DBMS, you may have gravitated toward an EJB-based design. You might find that you can cut your coding and maintenance work, as well as potentially improve data access performance, by having your session EJB components take advantage of DBMS stored procedures.

Companies have used stored procedures for years, largely because they help reduce network traffic and improve performance in distributed computing environments. Typically, these procedures contain important business logic that involves multiple database operations. Remote applications call these procedures, and the SQL statements they contain are executed on the DBMS server. Any results, of course, are passed back to the application when the procedure completes.

The business logic contained in these legacy stored procedures is often useful to Web applications. Rather than duplicate that logic in your EJB components, why not exploit it by wrapping these procedures as methods in your session beans? You'll avoid redundant code in your DBMS server and in your EJB components -- a productivity drain when you consider development, debugging, and maintenance overhead. And as a side benefit, performance might improve. Calling a stored procedure can reduce the number of SQL statements your EJB component would otherwise have to issue, thereby reducing communications overhead with the remote DBMS.

Getting started

Now that you understand why you might want to call stored procedures from your session beans, let's focus on how you can get started. First, you'll need access to an appropriate development environment, which should probably include a Java development tool with built-in EJB support, a Web application server, and a relational DBMS. My reference configuration included VisualAge for Java Enterprise Edition 3.0.2, WebSphere Application Server Advanced Edition 3.0.2.1, and DB2 V7.1; these were all installed on a single Windows NT system. For details on how to configure this environment to support the work outlined in this article, see "Leveraging DBMS Stored Procedures through Enterprise JavaBeans (in Resources) or consult the product manuals.

With the right software environment, you're ready to get to work. The coding pattern that we'll be exploring is best suited for stateless session EJB components, although it could also be applied to stateful session beans. However, because stateless session beans consume fewer system resources than stateful session beans and involve slightly less code, they're generally preferrable.

One of the first design issues you'll need to consider is how to map data between your stored procedure and your EJB component. Stored procedures can require multiple input, output, and input/output parameters as well as return one or more result sets (representing rows of data). Unless you want to use different coding patterns for different kinds of procedures, you'll need to write your EJB components so they can cope with all these possibilities.

Handling the input (or input/output) parameters is pretty easy: each one required by the stored procedure is mapped to an input parameter of your EJB component. But handling stored procedure output is trickier. You might have multiple output parameters and multiple result sets to pass back to the caller; these will have to be returned as a single serializable object to conform to EJB specifications. You could write your own class that's capable of packaging up that data into a single object and include all the necessary meta data along with it. (That meta data would describe the internal structure of your object so the client would know how to process it.) But that's a lot of work.

If you're using VisualAge for Java and WebSphere, there's a better option: use their Data Access Beans (DAB) library. This library contains classes that provide a layer of function on top of basic JDBC. You'll find the com.ibm.db.CallableStatement class particularly handy, as it enables you to create a serializable object that contains all the output returned by a stored procedure, including multiple result sets (if present) and associated meta data. And as an added bonus, the library is designed to support any JDBC-enabled data source, so it helps make your beans "DBMS neutral." With the DAB library, you can employ a single coding pattern to wrap any stored procedure in your session EJB component. And you can even use a single, generic coding pattern in your EJB clients to process any results returned by your wrapper methods.


Reviewing the development tasks

Let's explore the steps you should take to integrate your EJB components and DBMS stored procedures using a generic coding pattern:

  1. Determine which stored procedure is to be wrapped as an EJB method. If the procedure doesn't already exist, create it and debug it following the standard process for your DBMS.
  2. Determine which stateless session EJB component is to be used. If the EJB component doesn't already exist, create it and debug it following the standard process for your Java development environment.
  3. Extend the EJB component's remote interface to include a new method for wrapping the stored procedure.
  4. Extend the EJB component's implementation to include the logic of the new method that wraps the stored procedure. Connecting to the database, invoking the stored procedure, processing any results, and handling any exceptions are among the issues that will need to be addressed.
  5. Test your work by building a client application or servlet to call the EJB component wrapper method.

The first two items are fundamental programming tasks with which you're probably already familiar. Specific steps may vary a bit depending on the products you're using, but most offerings have tools to help you out. For example, if you're using VisualAge for Java and DB2, you can take advantage of the Stored Procedure Builder to complete step 1 and the EJB development feature to complete step 2. We won't focus on the first two steps for this article. But the remaining three items warrant a closer look. In this article, we'll discuss each of those steps in the context of a working example.


Exploring an application scenario

Let's assume we need to build an application that supports the marketing division of a firm that maintains a financial-oriented Web site. This site enables people to register as a client, track their investment portfolios, and post comments to electronic bulletin boards. We'll also assume that data supporting this site is stored in DB2 tables. The following code sample shows how these tables can be created.

SQL statements for creating sample tables in DB2

create table client (

  id              int                 not null primary key,

  name            varchar(30),

  email           varchar(30),

  phone           varchar(12),

  regdate         date,

  mktg            char,

  constraint      check1 check (mktg in ('y', 'Y', 'n', 'N'))

)



create table portfolio (

  id              int                 not null,

  clientID        int                 not null references client,

  ticker          varchar(10)         not null,

  cost            decimal (9,2),

  qty             int,

  date            date,

  primary key (id, clientID, ticker)

)



create table boards (

  msgno           varchar(15)         not null primary key,

  subject         varchar(40),

  date            date,

  clientID        int                 not null references client

)

The database also contains a stored procedure of particular interest. The procedure, CLIENTREPORT, provides a comprehensive profile of registered site users, including their investments and issues they've discussed on the bulletin boards. Client names and e-mail addresses are included with this report, so that a marketing staff member might contact the user with suggestions regarding additional products or services that may be of interest. It is this procedure that we want to wrap in a session EJB component.

Since this procedure could have been written in a variety of languages (including the Java programming language), we won't show its full contents here. The source code really isn't that important anyway, as you can't always assume you'll have access to it. But just to give you some idea of the stored procedure's contents, here are the three SELECT statements it includes:

SQL statements within CLIENTREPORT stored procedure

select name, e-mail from client where id = ?

select id, ticker, cost, qty, date from portfolio where clientid = ?

select msgno, subject, date from boards where clientid = ?

The question mark denotes that the statement will rely on input from the caller at run time; in this case, the caller must supply a valid data value representing the client ID of interest. As you may have guessed by looking at these statements, the stored procedure will require one input parameter (for the client ID), will return two output parameters (for the client's name and e-mail address), and will return two result sets (one containing data about the client's portfolios and one containing data about the client's bulletin board postings).


Modifying the EJB component's remote interface

Now let's start working on our EJB component code. Since we want to make our wrapper method available to EJB component clients, we'll need to extend our bean's remote interface. We'll be using a stateless session bean called Analysis and including a lookupClient method for our stored procedure wrapper. This method requires a single integer as input to represent the client ID for which we want a report; it returns a DAB CallableStatement object (found in the com.ibm.db.* package). Any exceptions returned by the procedure will be converted into RemoteExceptions (which is appropriate for EJB 1.0-compliant session beans).

The modified portion of the EJB component remote interface is shown in the following coding example.

EJB component remote interface

// Enterprise JavaBean Remote Interface for Analysis session bean

public interface Analysis extends javax.ejb.EJBObject {

// remote interface for our lookupClient method

 com.ibm.db.CallableStatement lookupClient(java.lang.Integer clientId)

     throws java.rmi.RemoteException;

. . .

}

Note that if you're using the VisualAge EJB component wizard, you don't need to code this manually. Instead, after coding the method in the bean's implementation class, you can invoke a menu item to promote the method to the bean's remote interface, and the necessary code will be added automatically.


Coding the stored procedure wrapper method

Now we're ready to focus on the bean implementation class itself; here's where we'll include the code to call our stored procedure and return all its output as a com.ibm.db.CallableStatement object. This code sample contains the full implementation of our lookupClient(...) method, which will invoke the CLIENTREPORT stored procedure. We'll review the logic of each code block (referenced in comments in the code) in subsequent sections, so you'll have a better idea of how to implement a similar method for your own stored procedures.


Connecting to the database

Let's look into portions of this code example in greater detail.

Before calling a stored procedure, you need to establish a connection to the DBMS. There are two ways to do this: use JDBC 1.0-style connections or use JDBC 2.0-style DataSources. The latter is generally preferred in WebSphere environments because it provides for connection pooling, which makes more efficient use of system resources. Our coding pattern uses DataSources for this reason.

In addition to deciding on the type of connection you want to establish, you should consider where to put the connection logic in your bean. You have multiple options:

  • Put it directly in the wrapper method
  • Put it in a private helper method
  • Put it in the ejbCreate() method (and put the corresponding logic for disconnecting in the ejbRemove() method)

The trade-offs of these approaches are beyond the scope of this paper. For simplicity, our sample places all connect/disconnect logic directly in the method.

Code Block 1 shows how to use DataSources for the connection when working with VisualAge for Java 3.0.2 and WebSphere 3.0.2.1. We create a hash table, populate it with values appropriate for our WebSphere environment, and establish an InitialContext. The next few lines of code make use of this initial context and Java Naming and Directory Interface (JNDI) services to obtain a reference to the desired DataSource, which we created previously in WebSphere using the Administrative Console. In this case, our DataSource was named LocalDB2Sample. Next, we use this DataSource to obtain a connection, passing it an appropriate database user ID and password. After obtaining this connection from the pool, we can feed this information to our DAB DatabaseConnection object to set up its required connection specification. Finally, we set autoCommitMode to false, because the EJB component is responsible for handling our transaction management services.

For test purposes, it's convenient to be able to run EJB components that use DataSources in the VisualAge for Java WebSphere Test Environment. For instructions on how to do so for release 3.0.2 of the product, see "Creating DataSources in the VisualAge for Java WebSphere Test Environment" by David Zimmerman (in Resources).


Calling the stored procedure

With our connection established, we can now focus on calling the stored procedure. As shown in Code Block 2 of the wrapper method coding example, we begin by creating a DAB StatementMetaData object, which will hold a specification for our stored procedure. Next, we define the SQL statement to be executed. Here, we'll be calling the CLIENTREPORT procedure, which requires one input parameter (for the client's ID) and two output parameters (for the client's name and e-mail address). Next, we add parameters to our specification. For each of the procedure's parameters, we specify a parameter name, its data type, and its parameter mode.

Code Block 3 creates the DAB CallableStatement object, which we'll execute shortly. CallableStatements represent SQL that can be used to execute a stored procedure. After creating the object, we set its meta data to that specified in Code Block 2. Then we associate a DatabaseConnection (created in Code Block 1) with this CallableStatement.

The next task is simple: we need to execute the CallableStatement object, which will cause the DBMS to run the stored procedure. Before doing so, however, we must set the input parameter of our procedure to the value passed into our method by the EJB client application. This logic is shown in Code Block 4.


Retrieving the stored procedure's output and returning to the caller

In Code Block 5 of the wrapper method coding example, we retrieve the two output parameters returned by the stored procedure. As you'll recall, these represent the name and e-mail address of our Web site client. However, we don't need to explicitly retrieve the result sets returned by the stored procedure. (These result sets contain data about our client's portfolios and bulletin board postings.) You might wonder why this is so.

Some DBMSs require that you retrieve all needed values from the result sets returned by a stored procedure before you obtain any output parameter values. Because of this requirement, the CallableStatement bean does not obtain any output parameters from the database until specifically requested via a getParameter() method since the user controls when values from a result set are retrieved. By default all rows in a result set are automatically retrieved and stored in the cache after the stored procedure is executed. But the output parameters must always be explicitly retrieved and stored in the cache.

With the output parameters retrieved, we then return the DAB CallableStatement to the EJB component's caller. This object now contains all output returned by the procedure (including result sets) as well as appropriate meta data to help the caller parse the object correctly. We'll see how to do that when we review the sample client application that calls our session bean wrapper method.

If you're familiar with JDBC, you may be wondering why we didn't explicitly issue a commit statement in this code block. Indeed, if we had used JDBC 1.0-style connections, we would need to (or else our work would be rolled back when we close the database connection in the "finally" block). However, using DataSources and accepting WebSphere's default transaction attributes for EJB components (TX_REQUIRED) causes WebSphere to automatically provide transaction management services for our work. Thus, an explicit commit statement is inappropriate.


Handling exceptions and closing open resources

Of course, it's possible that something may go wrong when your session bean executes. So you'll need to provide for exception handling. Code Block 6 includes a simple exception handler appropriate for EJB 1.0-compliant beans. It simply catches any exception encountered, includes an appropriate error message, and throws the exception as a new RemoteException back to the caller.

In addition, this code block contains a "finally" block to ensure that any resources opened by the method will be closed. Here, we release any resources associated with the CallableStatement object. Next, we remove any DAB reference to the connection we had used for our work. And lastly, we ensure that the WebSphere connection is closed.


Building a client application

With the EJB wrapper method built, it's time to focus on a client application. As with our EJB component, we'll first present the full code sample for the client application. Then we'll review individual code blocks in greater detail.

The client application shown here -- ClientAnalysis -- communicates with the EJB component using RMI/IIOP. Its work is simple: create the session bean, invoke its lookupClient(...) method, process the DAB CallableStatement object that's returned from the method, and remove the bean. The application is written as a generic client for processing CallableStatements; that is, we don't assume any prior knowledge about the CallableStatement's internal structure. Instead, we rely strictly on the meta data contained within it to parse the object and work with its relevant components, such as the output parameters and result sets returned by the procedure. This approach illustrates a generic coding pattern you can use in any application to handle CallableStatements. As such, it complements the generic coding pattern we used for wrapping a stored procedure (of any type) within a stateless session EJB component.


Creating the EJB component and calling its wrapper method

Code Block 1 of our client application begins the main(...) method. It specifies a client ID of interest and invokes a private helper method to acquire the session EJB component we'll be using. After the bean is created, its lookupClient(...) method is called. This is the method that wraps the CLIENTREPORT stored procedure and returns a DAB CallableStatement.

The private helper method -- createEJB() -- warrants a closer look. We chose to separate the EJB component creation work into a separate method because this code may need to change slightly depending on the Web application server in use. In particular, the way in which the JNDI InitialContext is obtained can differ, as certain properties associated with this context will vary.

This createEJB() method creates a hash table and populates it with appropriate values for our software environment. Next, a new InitialContext object is created, and this is used to obtain a remote reference to the EJB component home using JNDI services. This remote reference is explicitly narrowed before it's returned from the JNDI context -- a coding requirement when using RMI over IIOP. Once the EJB component home is obtained, we create a stateless session bean and return it to the main method of our client application.


Processing the returned object

Code Block 2 of the client application processes the DAB CallableStatement object returned by the EJB component. We begin by locating the root meta data object associated with the CallableStatement. Because CallableStatements can contain multiple result sets, multiple StatementMetaData objects can be chained together and included in the CallableStatement. The root of the chain will always contain the meta data describing our SQL statement and associated parameters, so that's where we want to start. This enables us to obtain the number of parameters included in our CallableStatement. The count returned will include all IN, INOUT, and OUT parameters of the procedure. Using a loop, we process all parameters and print relevant information about each, including the parameter's name, corresponding Java class, and mode (a number indicating IN, INOUT, or OUT mode).

Next, we can check for result sets and process them. First, we determine the number of result sets included in the CallableStatement object. Using a loop, we get each result set, which is represented as a DAB SelectResult object. Then we use another private helper method -- processRS(...) -- to process the result set. The processRS(...) method determines the number of rows and columns contained in the SelectResult passed to it. Assuming some rows are present, it uses nested loops to print information about all columns in all rows. This information includes the column's name as well as its value.

At this point, the client application's work is nearly complete. Code Block 3 removes the session bean, prints a line indicating it's done, and terminates. Of course, any exceptions encountered are handled in the code appearing after block 3. In this case, we simply print a stack trace.


Summary

Hopefully, you now understand how your session EJB components can leverage business logic encapsulated in legacy DBMS stored procedures. Potential benefits for doing so include reduced network traffic between the EJB server and the DBMS, improved productivity, and a reduction in overall software maintenance costs. If you follow the coding patterns outlined in this article, you'll be able to wrap any type of stored procedure as a method in your stateless session beans, regardless of the parameters or result sets associated with the procedure. Furthermore, you'll be able to use a generic coding pattern for calling any such EJB component and processing the object it returns, without having to be aware of the object's internal structure beforehand.


Acknowledgment

The author would like to thank Becky Nin for her help with this article.

Resources

  • Bontempo, Charles J. and Cynthia Maro Saracco. Database Management: Principles and Products, Prentice Hall, 1995, ISBN 0-13-380189-6. Discusses fundamentals of database management systems, and profiles capabilities available in various commercial products.
  • Date, C. J. An Introduction to Database Systems, Seventh Edition, Addison-Wesley, 1999, ISBN 0-20138-590-2. Discusses fundamentals of database management systems and provides detailed information on the relational data model.
  • DB2 product manuals
  • Current version of "Enterprise JavaBeans Specification" available for download.
  • Current version of "JDBC API Specification" available for download.
  • Monson-Haefal, Richard. Enterprise JavaBeans, O'Reilly and Associates, 1999, ISBN 1-56592-605-6. Describes fundamentals of EJB programming and provides numerous coding examples.
  • Saracco, Cynthia Maro. Universal Database Management: A Guide to Object/Relational Technology, Morgan Kaufmann Publishers, Inc., 1998, ISBN 1-55860-519-3. Describes object-oriented extensions that relational DBMS vendors have integrated, or are likely to integrate, into their products. Provides numerous SQL examples.
  • Ueno, Ken and Tom Alcott, Jeff Carlson, Andrew Dunshea, Hajo Kitzhofer, Yuko Hayakawa, Frank Mogus, Colin D. Wordsworth. WebSphere V3 Performance Tuning Guide, IBM Redbook, March 2000. Provides performance tuning tips for WebSphere users. Visit www.ibm.com/redbooks and search for SG24-5657.
  • WebSphere product manuals in online form (shipped with the product) as well as product information available from Web application servers Web site.
  • White, Seth and Maydene Fisher, Rick Cattell, Graham Hamilton, Mark Hapner. JDBC API Tutorial And Reference, Second Edition, Addison-Wesley, 1999, ISBN 0-201-43328-1. Describes JDBC 2.0 and provides numerous coding examples.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=13762
ArticleTitle=Wrapping DBMS stored procedures as methods in session EJB components
publish-date=03022001