 | Level: Intermediate C. M. Saracco (saracco@us.ibm.com), Software Engineer, IBM
01 Oct 2000 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:
- 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.
- 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.
- Extend the EJB component's remote interface to include a new method for
wrapping the stored procedure.
- 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.
- 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.
Acknowledgement
I'd 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.
-
Data Access Beans Javadoc. Describes the APIs of the Data
Access Beans discussed in this article. Available via the VisualAge
for Java online documentation. With the product running, select
Help -> Reference -> IBM APIs -> Data Access Beans -> Package com.ibm.db.
- 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.
- Picon, Joaquin and Patrizia Genchi, Maneesh Sahu, Martin Weiss, Alain
Dessureault. Enterprise JavaBeans Development Using VisualAge for Java, IBM Redbook, May 1999. Provides step-by-step instructions
for developing various kinds of EJB components using VisualAge for Java. Visit www.ibm.com/redbooks and search for SG24-5429.
- 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.
About the author | 
| |
Cynthia 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 thorughout North America, South America, Europe, and the Middle East. She can be reached at saracco@us.ibm.com. |
Rate this page
| |
