Methods of encapsulating stored procedures as EJB components

Integrated WEB application servers and database management (DBMS) technologies are common requirements for many new business applications. In this article, we'll discuss one aspect of this integration: how to design and develop methods to encapsulate or invoke existing DBMS stored procedures in a session Enterprise JavaBeans (EJB) component. You should be familiar with the basics of EJB technology, Structured Query Language (SQL), and Java Database Connectivity (JDBC) in order to fully understand this article.

If you are working on a WEB application development that requires access or modification of data in Dmbs, you may already be transferring to an EJB based design. You may find that by enabling the session EJB component to take advantage of the DBMS stored procedures, you can reduce coding and maintenance efforts and potentially improve data access performance.

Some companies have been using stored procedures (stored procedure) for years, largely because they can help reduce network traffic and improve performance in a distributed computing environment. Typically, these processes contain important business logic that involves multiple database operations. The remote application calls these procedures to execute the SQL statements they contain on the DMBS server. Of course, at the end of the process, all results are returned to the application.

These old stored procedures are usually useful for Web applications. Instead of replicating these logic in the EJB component, why not encapsulate these processes as methods in the session bean? This avoids redundant code in the DBMS server and EJB components--redundant code loses development efficiency when considering development, debugging, and maintenance overhead. This can also bring benefits to performance. Invoking a stored procedure reduces the amount of SQL statements that the EJB component would have to emit, thereby reducing the communication overhead with remote DBMS.

Entry

Now that you understand why you're calling a stored procedure from a session bean, let's look at how to get started. First, you need to use the appropriate development environment, which should include a Java development tool with built-in EJB support, a WEB application server, and a relational DBMS. My reference configuration includes the VisualAge for Java Enterprise version 3.0.2,websphere application Server Advanced Edition 3.0.2.1, and DB2 V7.1, all installed on a Windows NT system. For more information about how to configure the environment to support the work described in this article, see "Leveraging DBMS Stored procedures through Enterprise JavaBeans" (in resources) or reference product manuals.

With the right software environment, you can start. Although the encoding pattern we are discussing may be appropriate for stateless sessions (stateless session) beans, it can also use stateful sessions (stateful session) bean components. However, because stateless session beans consume less system resources than stateful session beans and have less code involved, it is generally recommended that stateless session beans be used.

The first design issue to consider is how to map data between stored procedures and EJB components. Stored procedures may require multiple input, output, and input/output parameters and return one or more result sets (representing data rows). Unless you want to use different encoding patterns for different types of processes, you need to write an EJB component to handle all of these possibilities.

Processing input (or input/output) parameters is simple: Map each parameter required by the stored procedure to the input parameters of the EJB component. However, processing the output of a stored procedure is tricky. There may be multiple output parameters and multiple result sets to return to the calling program, which needs to be returned as a serializable object to conform to the EJB specification. You can write your own class so that it can package the data into an object and include all the necessary metadata in that object. (The metadata describes the internal structure of the object so that the client knows how to handle it.) But it takes a lot of work.

If you are using VisualAge for Java and WebSphere, there is a better option: use their data access Bean (DAB) library. The library contains classes that provide a function layer that is above the basic JDBC. You may find the Com.ibm.db.CallableStatement class particularly handy because it allows you to create a serializable object that contains all the output returned from the stored procedure, including multiple result sets (if any), and related metadata. Another benefit is that the library is designed to support any data source that supports JDBC, so it can make the bean "independent of the DBMS." With the DAB library, you can encapsulate any stored procedure in the session EJB component in one encoding mode. You can even use a generic encoding pattern in the EJB client to handle any results returned from the wrapper method.

Review development tasks

Let's discuss the steps to integrate EJB components and DBMS stored procedures using the common coding pattern:

Determine which stored procedure to encapsulate as an EJB method. If the procedure does not exist, follow the DBMS standard procedure to create and debug.

Determine which stateless session EJB component to use. If the EJB does not exist, follow the standard procedures for the Java development environment to create and debug.

Extend the remote interface of the EJB component to include new methods for encapsulating stored procedures.

Extend the implementation of the EJB component to include the logic of the new method that encapsulates the stored procedure. Connecting to a database, calling a stored procedure, processing all of the result sets, and handling all exceptions is a problem to be resolved later.

The EJB component wrapper method is invoked to test the work done by building a client application or Servlet.

The first two items are basic programming tasks that you may already be familiar with. Individual steps may vary slightly depending on the product being used, but most products have tools to help. For example, if you are using VisualAge for Java and DB2, you can use the stored Procedure builder to complete step 1 and take advantage of the EJB development feature to complete step 2. This article will not focus on the first two steps. However, the remaining three steps need to be explained in detail. This article discusses each of these steps in the context of a real example.