Developing a CMP EJB that uses stored procedure

Why and when to perform this task

Any data logic method that uses a stored procedure must be defined as an abstract method. For example, an Account bean has an abstract method of getNumberOfTransactions(), which gets the number of transactions within a date range as follows:

public abstract int getNumberOfTransactions( String transactionType, Date fromDate, Date toDate) throws InvalidTransactionTypeException;

This method is pushed down to use stored procedure call.

Refer to Example: Coding beanUserDefinedPushDownMethodsImpl and a stored procedure CMP EJB to use the stored procedure in a CMP EJB.

Application design consideration

When you develop a CMP EJB that uses stored procedure, there are several things to consider. Among them are:

The following guidelines are recommended when you develop such applications:

  1. Using stored procedure in a CMP EJB versus a Session bean

    Current WebSphere best practices encourage business logic written as methods on session beans that form a layer between the servlet, or top-level application, and any entity beans. This business method is typically defined as logic that manipulates various data to achieve a specific task in an application. A business method is not concerned with the details of how the data is obtained or persisted.

    An example of a business method is:

    calculateMonthlyCharges(Account anAccount, PolicyRule monthlyChargeRules)
    where Account is a CMP EJB and PolicyRule is another object in the application. This business method can be implemented in Java, but you might choose to implement this as a stored procedure.

    Another likely method in a banking system is to determine the average monthly balance in an Account; most banks provide free checking if your average balance is over a certain amount. You can put the logic for calculating average balance in a session bean just as you can with a business method; However, it may make better sense from an Object Oriented design point of view to put this logic on the Account CMP EJB itself in a data logic method; for example:

    averageBalance(Date fromDate, Date toDate)
    Also, if you modeled the averageBalance() method on the Account object, it makes sense that you can implement averageBalance() on the Account CMP EJB. The logic to calculate average balance uses only data in this Account entity or its related entities, and the result applies only to this entity. The logic is likely to be of use to other parts of the application that use Account beans. These guidelines are useful for logic written as a data logic method versus a business method. Such a data logic method can be implemented in Java, but again, you might choose to use a stored procedure.

    If a data logic method touches or depends on only one Entity bean or its related entities, then the best practice is to put the logic in the CMP EJB; otherwise, put it in the Session Bean.

  2. Stored procedure and CMP persistent fields

    The best practice for CMP EJB development is not to use the stored procedure to update the backend data, nor to end the bean's transaction in a data logic method. When you use a stored procedure to update the backend data, we recommend that the application end the bean's transaction before touching any CMP EJB persistent fields.

    In the case where a bean is a lifetimeInCacheUsage bean, we recommend using the PMCacheInvalidationRequest API to re-sychronize the bean's persistent data.

    If the stored procedure must reference the latest CMP persistent data, including any updates not yet pushed to the backend, you can utilize the Application Server Toolkit (ASTk) to ensure that the CMP EJB field data is updated in the database prior to invoking a stored procedure. This is indicated in the Application Server Toolkit (ASTk) to ensure that the CMP EJB field data is updated in the database prior to invoking a stored procedure. To do this, in the ASTk set preflush to TRUE when you indicate the push down method to access the stored procedure.

    If the stored procedure is an SQL stored procedure or a Java stored procedure for RDB, do not close the connection given to you by WebSphere inside the beanUserDefinedPushDownMethodsImpl class. If your beanDefinedPushDownMethodsImpl method returns a record with a ResultSet and you close the connection, WebSphere Application Server runtime throws a "ResultSet has already closed" exception.

  3. Synchronize CMP EJB persistent fields with the UserDefinedPushDownMethodsImpl class

    If some persistent fields are changed for a CMP EJB after the UserDefinedPushDownMethodsImpl class has been implemented, you can update the UserDefinedPushDownMethodsImpl class to reflect the changes. For example, changing the persistent field type, the order of the persistent fields, or adding/removing one of the persistent fields, you must synchronize the changes in this implementation class. Otherwise, your CMP EJB will not be accessed correctly. If the signature of the method being pushed down is not changed, then you can compile the updated version of beanUserDefinedPushDownMethodsImpl and put it back to the deployed ear file. Otherwise, you must rerun the cmpDeploy and ejbDeploy tools. Running the cmpDeploy tool overrides the beanUserDefinedPushDownMethodsImpl class. Be sure to make a copy of it before you run the tool.

  4. Stored Procedure and transaction

    If a local transaction is committed or rolled back inside a stored procedure, this might or might not impact the top level transaction. Therefore, a stored procedure should not end the caller's transaction. Otherwise, when a method ends, it might think it commits the top level transaction plus the stored procedure work. This is the same business practice as when any stored procedure is involved inside an application or a Session Bean.

  5. Stored procedure and exception

    Each CRUD pushdown method in the UserDefinedPushDownMethodsImpl throws the javax.resource.ResourceException. You must use the createResourceException method from the WSPushDownHelper to chain the stored procedure exception or the return code in the UserDefinedPushDownMethodsImpl class.

    If a data logic method in the UserDefinedPushDownMethodsImpl class throws an exception, you can define and create an instance of any Exception inside the method. Client code calling the data logic method can catch the user-defined Exception.

  6. Various stored procedure return values

    The return value from a stored procedure varies from one stored procedure to another. The return record from the UserDefinedPushDownMethodsImpl must be recognized by WebSphere. Therefore, you must process the return value correctly in the UserDefinedPushDownMethodsImpl class.

    Here are recommendations and guidelines to handle various return values (is assumed that the helper in the following scenarios refers to WSRelationalPushDownHelper):

    1. In an ejb finder, the return value is a ResultSet and it has the same order and format as described in the UserDefinedPushDownMethodsImpl. Let WebSphere process the return ResultSet for you. Here is the example
      ResultSet rs = callableStatement.getResultSet();
      returnValue = helper.createCCIResultSet(rs, connection)
      Ensure that the return ResultSet has the same order and format as described in the method comment in the beanUserDefinedPushDownMethodsImpl; otherwise, the exception might occur while WebSphere processes the resultSet.
    2. In an EJB finder, the return value is not a ResultSet, but it has all the CMP EJB persistent fields through the CallableStatement output parameters. Process the return value and convert it into an IndexedRecord (described in the UserDefinedPushDownMethodsImpl class). For single finder or for multi-finders, the returned IndexedRecord must be "record of records."

      Create an IndexedRecord to set your return value into this record, based on the documented format. For example,

      callableStatement.execute();
      IndexedRecord rec = helper.createIndexedRecord();
      // assume that there are three output parameters: one integer and two String fields.
      rec.add(new Integer(callableStatement.getInt(1)));
      rec.add(callableStatement.getString(2));
      rec.add(callableStatement.getString(3));
      return helper.createCCIRecord(rec);

    3. In an EJB finder, the return value is a ResultSet, but it has a different number of fields than in the current CMP EJB. Process the return value as described in number 2. Create an IndexedRecord to set the ResultSet values into this record. For example, the ResultSet returns 6 fields and only the 1, 4, 6 are used by this CMP EJB.
      CallableStatement.execute();
      ResultSet rs = callableStatement.getResultSet();
      
      if (rs.next()) {
      item = helper.createCCIIndexedRecord();
      item.add(new Integer(rs.getInt(1)));  
      item.add(rs.getString(4));
      item.add(rs.getString(6));
      returnValue.add(item);
      }
    4. In an EJB finder, the returned ResultSet has multiple rows in the same layout as described in the UserDefinedPushDownMethodsImpl class. (See step number 6a.)
    5. In an EJB finder, the return ResultSet has multiple rows that are not the same layout as described in the UserDefinedPushDownMethodsImpl. In this case, build a composite IndexedRecord. This process is similar to the process described in step number 6b; however, returnValue acts as the top level IndexedRecord. For each row of the result set, you must create an IndexedRecord to store the data, then add to the returnValue IndexedRecord. For example,
      ResultSet rs = callableStatement.getResultSet();
      
      IndexedRecord item = null; // This is a record for each row
      
      while (rs.next()) 
      {
      
      item = helper.createCCIIndexedRecord();
      
      // get the resultSet data into the item here
      
      // after all the data are moved,
      
      returnValue.add(item);
      
      }
      
    6. In a data logic method, the return value is an object or a value that matches the method. Return this value to the caller.


Related concepts
Stored procedure support for CMP EJB
Related tasks
Deploying a CMP EJB that uses stored procedure
Related reference
Example: Coding beanUserDefinedPushDownMethodsImpl and a stored procedure CMP EJB
Related topics
WSPushDownHelper
WSRelationalPushDownHelper
WSPushDownHelperFactory



Searchable topic ID:   cmpdevelop
Last updated: Jun 21, 2007 8:07:48 PM CDT    WebSphere Business Integration Server Foundation, Version 5.0.2
http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp?topic=/com.ibm.wasee.doc/info/ee/ae/tdat_cmpdevelop.html

Library | Support | Terms of Use | Feedback