EJB QL Lab - Stock Purchase Reports

INTRODUCTION/OBJECTIVES

In this lab you will work with the data stock purchase transaction generated by the Stock Purchase Web application you have created in MDB Lab Part II - Stock Purchase MDB Project hands-on lab. The stock purchase transaction data resides in STOCK_PRCH_TXN table. Representing a stock purchase transaction for customers of WEB Bank Online, each row in STOCK_PRCH_TXN table is related to a row in CUSTOMER table thus forming a one-to-many relationship.

In this lab you will be working with a pre-coded StockPurchaseReports Session EJB to create a report displaying a list of customers that own a given stock. First you will create a finder method for Customer CMP EJB and then you will implement this finder method with EJB QL (Query Language).

What is EJB QL?

EJB QL is a new EJB 2.0 feature. A subset of SQL92, EJB QL is a database independent query language which EJB 2.0 containers translate to the query language of the persistent datastore (for example IBM DB2). One of the key benefit of using EJB QL is that it makes J2EE applications database vendor independent.

This Customer CMP EJB finder method will traverse CMR relationship between the Customer object and the Stock_prch_txn object to find the Customer objects that have purchased stocks with a given stock symbol.

What is CMR?

In EJB, an association that specifies relationships between enterprise beans and is represented by a CMR field in the deployment descriptor. These relationships are maintained by the EJB container.

Next you will implement getStockOwners method in StockPurchaseReports Session EJB. In the getStockOwners method you will call the finder method of Customer CMP EJB to return a Collection of Customer object that match the stock ownership criterion. Then you will iterate though the Collection to format a report. You will be testing the Session EJB using the Universal Test Client. Finally you will be making a small correction to the EJB QL finder query to ensure that no duplicate Customer objects are returned in the Customer EJB finder method.

Note: This workshop requires that the following products are installed:

WebSphere Studio Application Developer Early Availability Version 5.0
DB2 UDB 7.2 FP1

Note: This hands-lab depends on MDB Lab Part II - Stock Purchase MDB Project lab. If you have not completed this lab you will need to import the solution EAR WBStockPurchaseSolution.EAR with EAR project name of WBStockPurchase.

Section 1 - Setup

___ 1. Import WBStockPurchaseReportsEJB.jar

a. Switch a J2EE Navigator View in the J2EE Perspective.

b. From main menu bar select File>Import...

c. From Select page select EJB JAR file and press the Next> button.

d. On Enterprise Application Import page:

press the Browse... button to navigate to WS50STEW>hands-on>J2EE Part II EJBQL directory and select WBStockPurchaseReportsEJB.jar;

for EJB project make sure to select Existing radio button;

for Project name select WBStockPurchaseEJB;

make sure to select Overwrite existing resources without warning check box.

e. Press the Finish button.

f. Expand WBStockPurchase EJB project.

You should see StockPurchaseReports Session EJB.

___ 2. Add EJB JNDI References to Access Customer CMP EJB

The StockPurchaseReports Session EJB contains the business logic for the stock purchase report generation. It retrieves data from the customer CMP EJB, formats it and returns it to the clients that call it. To access the Customer and Stock_prch_txn CMP EJBs, the StockPurchaseReports Session EJB must have JNDI references to them.

a. Double click on EJB Modules>WBStockPurchaseEJB project to open EJB Deployment Descriptor editor.

b. Click on the References tab.

c. Select StockPurchaseReports Session EJB.

d. Press the Add... button.

e. On Reference page select EJB Local Reference radio button

and press the Next> button.

f. On the EJB Local References page, enter Name of ejb/Customer.

g. Press the Browse... button to the right of Link entry field.

h. In Reference Link dialog select Enterprise bean in current EJB project radio button and then from the Link drop down menu select Customer EJB

and press the OK button.

i. On EJB Local Reference page press the Finish button.

j. On References page, press the Add... button again.

k. On Reference page select EJB Local Reference radio button and then press the Next> button.

l. Enter Name of ejb/Stock_prch_txn

m. Press the Browse... button to the right of Link entry field.

n. In the Reference Link dialog select Enterprise bean in current EJB project radio button and then from the Link drop down menu select Stock_prch_txn EJB and Press the OK button.

o. On the EJB Local Reference page Press the Finish button.

p. Press CTRL-S to save changes and then close the EJB Deployment Descriptor editor.

___ 3. Generate Deploy Code

Since you have added references from the StockPurchaseReports Session EJB to the various CMP EJBs that are used in this Session EJB, you will need to generate the deploy code to reflect these references.

a. Select EJB Modules>WBStockPurchaseEJB EJB project.

b. Click right mouse button and from the pop up menu select Generate>Deploy Code and RMIC Code...

c. On the Generate Deploy and RMIC Code dialog press the Select all button and press the Finish button.

___ 4. Update STOCK_PRCH_TXN table

Recall that in the MDB Lab Part II - Stock Purchase MDB Project hands-on lab, during the testing phase, you have created four stock purchase transactions which were recorded in STOCK_PRCH_TXN table. Before we begin this lab we will need to delete the rows you have created in that lab and create more rows in STOCK_PRCH_TXN table. This is so that our report generating queries produce a more interesting output and also so that your queries match the screen shots in the lab instructions.

a. Run WS50STEW>hands-on>J2EE Part II EJBQL>UpdateStockPrchTxn.bat

Section 2 - Examine the Schema and Generated Container Managed Relationships.

The StockPurchaseReports Session EJB will be directly accessing only Customer CMP EJB which maps to CUSTOMER table. The data in the STOCK_PRCH_TXN table will be accessed indirectly via Customer CMP EJB through CMR (container Managed Relationship) between CUSTOMER and STOCK_PRCH_TXN. In this section we will look at both tables and examine the generated CMR. The CMR was generated by the bottom-up mapping when the Customer and Stock_prch_txn CMP EJBs were generate from the AIS database.

___ 1. Examine the CMP Relationship of CUSTOMER and STOCK_PRCH_TXN Tables

a. From J2EE Hierarchy view select EJB Modules>WBStockPurchaseEJB EJB module, press right mouse button and from the pop up menu select Open With>Mapping Editor>DB2UDBNT_V72_1

b. In Overview pane (second from the top) expand Customer tree node.

c. In Overview pane (second from the top) expand Stock_prch_txn tree node.

d. Note that CUSTOMER table has the key of CUST_ID and that STOCK_PRCH_TXN table has a foreign key referencing CUST_ID. This is how the bottom-up EJB creation wizard created the one-to-many CMR relationship between Customer and Stock_prch_txn CMP EJBs.

Also briefly examine the various columns in both tables and see how they were mapped to the fields in CMP EJBs.

e. Close the Mapping Editor. If you are prompted to, On Save Resource message box, make sure to press the No button.

___ 2. Examine CMR between the Customer and the Stock_prch_txn CMP EJBs

a. From J2EE Hierarchy view select EJB Modules>WBStockPurchaseEJB , press right mouse button and from the pop up menu select Open With>Deployment Descriptor Editor

b. Press the the Overview notebook tab -

c. On the Overview page scroll down to Relationships 2.0 section and select Stock_prch_txn_To_Customer

d. Press the Edit... located to the right of the Relationships 2.0 section.

e. On the Relationship page press the Next> button.

f. You should now see the Relationship Roles page.

Note the following:

The bottom-up CMP EJB wizard created the correct CMR mapping based on the foreign key in STOCK_PRCH_TXN table. A row in the CUSTOMER table has a one-to-many relationship with rows in STOCK_PRCH_TXN table. This is because a customer may have generated several stock purchase transactions which are recorded in STOCK_PRCH_TXN table.

The Navigate check buttons control the direction of "navigation" between Customer and Stock_prch_txn objects. We will use this capability in EJB QL queries.

The CMR field referencing variables (fk_cust_id1 and stock_prch_txn) are used in EJB QL queries. In Step 3, we will be using stock_prch_txn reference to navigate from CUSTOMER to STOCK_PRCH_TXN table.

When using EJB QL queries our return type for a collection Stock_prch_txn CMP EJB objects will be java.util.Collection.

Note: Editing and adding CMR relationships makes sense only in top-down approach. The CMR relationship establish using the above wizard will be reflected in the generated database schema.

g. Press the Cancel button.

h. Press the Source notebook tab and scroll to the end of the listing to locate the <ejb-relations> for Stock_prch_txn_To_Customer

Note the how highlighted items map to the Relationship page from step f.

Section 3 - Create Customer CMP EJB Finder Method

In this section you will add a finder method to Customer CMP EJB home interface. This finder method, named java.util.Collection findCustomersByStockPurchase( String symbol), will return a Collection of Customer local home interface objects that represent customers who purchased a given stock. You will define the logic of the findCustomersByStockPurchase via an EJB QL query. This query will use the one-to-many relationship between Customer and Stock_prch_txn object to navigate to Stock_prch_txn object so that it can to find the customers in STOCK_PRCH_TXN table who purchased stock with a given stock symbol (the parameter of the select method).

___ 1. Define findCustomersByStockPurchase Finder Method and EJB QL Query for Customer CMP EJB

a. In the Deployment Descriptor Editor press the Beans tab and click on Customer CMP EJB.

b. Scroll down to Queries section and press the Add... button.

c. On Enterprise Java Bean 2.0 Query page select

New radio button for Method,

find method for Method type,

Local for Type,

findCustomersByStockPurchase for Name

d. Press the Add... button to the right of Parameters.

e. In the Add Method Parameter window enter a Name of symbol and a Type of java.lang.String

and press the OK button.

f. Back on Enterprise Java Bean 2.0 Query page, select java.util.Collection for Return type.

The Enterprise Java Bean 2.0 Query page should look similar to this:

g. Press the Next> button.

h. On the EJBQL page enter Query statement of:

SELECT OBJECT(c) FROM Customer c, IN(c.stock_prch_txn) s WHERE s.symbol = ?1

and press the Finish button.

i. Let's examine the elements of the EJB QL finder query.

Note that the EJB QL syntax is very similar to SQL syntax. In fact it is based on the SQL-92 specification. The main difference is that while SQL operates on tables and columns EJB QL works with CMP EJBs, CMP fields and CMR relationships.

The EJB QL query consists of three clauses: SELECT, FROM and an optional WHERE.

The FROM clause declares the schema navigation variable, in our case variable c.

The SELECT clause uses the navigation variable, introduced in the FROM clause, to define the return type of the query - in our case the Customer object. The SELECT statement also defines a range variable s in terms of the relationship stock_prch_txn between the Customer and Stock_prch_txn objects. See Section 2.2.f to see where this variable was defined.

Finally the WHERE close defines the conditional aspects of the query. In our case we want to restrict the Customer objects to those that have the symbol variable equal to the first parameter of the finder method (stock symbol). Note that ?1 refers to the first parameter of the finder method.

j. Press the CTRL-S to save the changes and then close EJB Deployment Descriptor editor.

___ 2. Generate Deploy Code

a. Select EJB Modules>WBStockPurchaseEJB EJB project.

b. Click the right mouse button and from the pop up menu select Generate>Deploy Code and RMIC Code...

c. On the Generate Deploy and RMIC Code dialog press the Select all button and press the Finish button.

___ 3. Examine the Generated Finder Method

a. Open EJB Modules>WBStockPurchaseEJB>Customer .

b. Double click on CustomerLocalHome to open Java Editor for CustomerLocalHome.java.

c. You should the generated findCustomerByStorckPuchase finder method.

d. Close the Java Editor.

Section 4 - Code and Test String getStockOwners( String symbol) Method

In this section you will code String getStockOwners( String symbol) method to StockPurchaseReports Session EJB. Here is the high level overview of the logic:

Invoke the finder method java.util.Collection findCustomersByStockPurchase( String symbol) that you have defined in Section 3, to return a Collection of Customer CPM EJBs that purchased stock with given a stock symbol.
Iterate through the Collection of Customer objects and format a report string.

___ 1. Add Code to StockPurchaseReportsBean.java

a. From J2EE Hierarchy view double click on EJB Modules>WBStockPurchaseEJB>StockPurchaseReports>StockPurchaseReportsBean to open StockPurchaseReportsBean.java.

b. In Outline View click on getStockOwners( String) method.

c. Replace the code in getStockOwners() method with the following:
// Format report header
StringBuffer sbOwners = new StringBuffer("\n\nThe following customers own " + symbol + " stock\n\n");
sbOwners = formatLine(sbOwners, 9, title[0]);
sbOwners = formatLine(sbOwners, 18, title[1]);
sbOwners = formatLine(sbOwners, 18, title[2]);
sbOwners.append("\n"); 

// Use finder method to get collection of customers that purchased given stock
Collection customers = null;
try {
  customers = customerHome.findCustomersByStockPurchase(symbol);
}
catch (FinderException e) {
  System.err.println("Problem finding Customer EJBs");
  e.printStackTrace();
}

// Iterate thorough the Collection and format report lines
Iterator iterator = customers.iterator();
while (iterator.hasNext()) {
  CustomerLocal customer = (CustomerLocal) iterator.next();
  sbOwners = formatLine(sbOwners, 9, ((CustomerKey) customer.getPrimaryKey()).getCust_id().toString());
  sbOwners = formatLine(sbOwners, 18, customer.getCust_fn());
  sbOwners = formatLine(sbOwners, 18, customer.getCust_ln());
  sbOwners.append("\n");
}
System.out.println(sbOwners);
return sbOwners.toString();
Examine the code. Notice the finder method returning a Collection of Customer objects. Also note that for the sake of efficiency, the customerHome object used in the getStockOwners() method is instantiated in the ejbCreate().

Finally, note the return type of the getStockOwners() method. It is a String object. Perhaps a better design would have been to return a serializable Java Bean object that contains the data with assessor methods for the rows and columns.

d. Press the CTRL-S to save and close StockPurchaseReportsBean.java file.

___ 2. Test getStockOwners( String symbol) Method

To test getStockOwners method we will be using Universal Test Client (UTC).

a. From J2EE Hierarchy view select EJB Modules>WBStockPurchaseEJB>StockPurchaseReports click on the right mouse button and from the pop-up menu select Run on Server.

b. You may see the Server Selection wizard. If you do, select WebSphere V5 Unit Test from Configure an existing server (or from Use a server that is already configure), check Do not show this dialog next time (Set this server as the preferred server) check box and press the Finish button.

c. On the Select a server client window select Test EJB remote interface and press the Finish button.

d. In the Web Browser pane expand fully EJB References link

e. Click on StockPurchaseReports create() link to create an instance of StockPurchaseReports Session EJB.

f. In the Parameters pane click the Invoke button.

g. In the Parameters pane click the Work with Object button.

h. Expand EJB References>StockPurchaseReports>StockPurchaseReports 1 link.

i. Click on the String getStockOwners(String) method.

j. In the Parameters pane expand java.lang.String under Parameter:

k. For java.lang.string enter Bank of Montreal stock symbol Value of BMO and press the Invoke button.

l. You should now see the following report in the Console view:

m. To verify that indeed three customer own Bank of Montreal's stock, run WS50STEW>hands-on>J2EE Part II EJBQL>ShowStockPrchTxn.bat

Note that there are three BMO stock purchase transactions and that the CUST_ID value for these transactions are 3, 4 and 6.

___ 3. Test getStockOwners( String symbol) Method Using IBM as Input

a. For java.lang.string enter IBM's stock symbol Value of IBM and press the Invoke button.

b. You should now see the following report in the Console view:

c. Notice that there is something wrong with this report!

There are duplicate records in the report. This is because the EJB QL query behind java.util.Collection findCustomersByStockPurchase( String symbol) method simply returns instances of Customer object for every row in STOCK_PRCH_TXN table where symbol equals to "IBM". Examine the rows in STOCK_PRCH_TXN table where symbol equals "IBM"; you will see that there are sixteen such rows and that is exactly the number of rows in the generated report.

d. Switch to the Server Perspective and in the Server view click right mouse button on WebSphere V5 Unit Test server and from the pop-up menu select Stop.

Section 5 - Fix the Duplicate Row Problem

___ 1. Alter EJB QL Query associated with findCustomersByStockPurchase() method

a. Switch to the J2EE Perspective, J2EE Hierarchy View.

b. Double click on EJB Modules>WBStockPurchaseEJB project to open EJB Deployment Descriptor editor.

c. In deployment Descriptor Editor press Beans tab and click on Customer CMP EJB.

d. Scroll down to the Queries section, select Query SELECT... and press the Edit... button.

e. On Edit Finder 2.0 page press the Next> button.

f. On the EJBQL page, in Query statement MLE, edit the EJB QL query by adding the DISTINCT keyword after the SELECT statement.

The DISTINCT keyword will fix our duplicate report entry problem by ensuring that all Customer objects returned in the java.util.Collection findCustomersByStockPurchase( String symbol) method are unique.

g. Press the Finish button.

h. Press CTRL-S to save and close EJB Deployment Descriptor editor.

___ 2. Generate Deploy Code

a. Select EJB Modules>WBStockPurchaseEJB EJB project.

b. Click right mouse button and from the pop up menu select Generate>Deploy Code and RMIC Code...

c. On Generate Deploy and RMIC Code dialog press the Select all button and press the Finish button.

___ 3. Test getStockOwners( String symbol) Method

a. From J2EE Hierarchy view select EJB Modules>WBStockPurchaseEJB>StockPurchaseReports click on the right mouse button and from the pop-up menu select Run on Server.

b. You may see the Server Selection wizard. If you do, select WebSphere V5 Unit Test from Configure an existing server (or from Use a server that is already configure), check Set this server as the preferred server check box and press the Finish button.

c. On the Select a server client window select Test EJB remote interface and press the Finish button.

d. In the Web Browser pane expand fully EJB References link

e. Click on StockPurchaseReports create() link to create an instance of StockPurchaseReports Session EJB.

f. In the Parameters pane click the Invoke button.

g. In the Parameters pane click the Work with Object button.

h. Expand EJB References>StockPurchaseReports>StockPurchaseReports 1 link.

i. Click on the String getStockOwners(String) method.

j. In the Parameters pane expand java.lang.String under Parameter:

k. For java.lang.string enter Bank of Montreal stock symbol Value of BMO and press the Invoke button.

l. You should now see the following report in the Console view:

Note that the duplicate entries are now gone!

j. Switch to the Server Perspective and in the Server view click right mouse button on WebSphere V5 Unit Test server and from the pop-up menu select Stop to stop the server.

Congratulations, you've completed the EJB QL Lab - Stock Purchase Reports..