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:
locktype = helper.getLockType(connection,accessIntent); LOCKTYPE_NOTAPPLICABLE = 0; LOCKTYPE_SELECT = 1; LOCKTYPE_SELECT_FOR_UPDATE = 2; LOCKTYPE_SELECT_FOR_UPDATE_WITH_RS = 3; LOCKTYPE_SELECT_FOR_UPDATE_WITH_RR = 4;
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.
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.
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.
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.
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.
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):
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.
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);
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); }
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); }