Introduction

The initial release of WebSphere© Studio Application Developer (hereafter called Application Developer) Version 5.0 contained a then-new feature called the Table and Data Source Creator, which was designed to make the development of Container Managed Persistence (CMP) beans that much easier by no longer requiring that developers be concerned with the exact manner in which the persistence mechanism operates. Feedback from this feature, and from the October 2000 article, Testing Container Managed Persistence EJBs with the Table and Data Source Creator in WebSphere Studio Application Developer 5.0, suggests that this feature has been extremely useful for many users. Others, however, felt left out.

Building J2EE applications that access relational databases can be challenging for experienced developers, and is especially so for beginners. Most J2EE applications of any scope require coding Java^TM persistence at some point, even if the application uses CMP, which delegates a lot of the responsibility for managing the persistence layer to the application server's EJB container. While it is true that CMP simplifies the management and coding of database interactions, it still requires that databases, tables, and schema be created and configured beforehand in order to execute the application that uses CMP. This database set-up stage is typically time consuming and complex, and it usually requires database administration expertise, as well as privileged access to the database.

Introduced in WebSphere Studio Application Developer Version 5.0, the Table and Data Source Creator simplified the set-up of the test backend database and the creation and configuration of test data sources. The Table and Data Source Creator, which operates automatically with a single menu click, is helpful for getting right to testing an application without having to do all this necessary setup yourself. In this way, the Table and Data Source Creator handles the persistence issues, and frees you from needing to know much about how the CMP is persisted.

The specific persistence mechanism originally used for the Table and Data Source Creator was Cloudscape^TM Version 5.0, primarily because it is lightweight and pure Java. Although many readers embraced Cloudscape, many others voiced their desire to perform the same functions using DB2© or Oracle©, since these were the database applications already running in their environments. As both the developer of the original Table and Data Source Creator and the author of the above article, I am pleased to say that the "new and improved" Table and Data Source Creator available in Application Developer Version 5.1 has been broadened to support DB2 Version 8.1 and Oracle 9i (with a single menu click, plus an additional dialog box), as well as Cloudscape. We hope this enhancement makes testing CMPs on production or production-level databases easier for shops using DB2 or Oracle.

About this tutorial

This article presents a hands-on look at the new features of the Table and Data Source Creator, and steps you through the process of creating a simple CMP, persisting it, and then running it from Cloudscape, DB2 and Oracle. Introducing DB2 and Oracle does present some unavoidable complexities that Cloudscape does not have, but these complexities are managable provided you have simple JDBC access to the database (i.e. user ID, password, DB2 8.1 and/or Oracle 9i database, etc.). If you prefer while following along, the EAR module referenced in this article is available in a downloadable file.

In this tutorial, you will develop a simple CMP, called HelloWorldCMP, persist it, and then test it on three popular database systems. The emphasis of the tutorial will be on the tools, rather than on producing a real world sample application. If you don't have access to DB2 or Oracle, you still have Cloudscape support available "out of the box" so you will be able to follow along easily.Also, although the steps in this article refer to Application Developer on Linux© (Red Hat© Version 8.0), the same steps will apply to the Windows© version as well. The only discernable differences should be in the GUI elements and the directory paths, which will differ somewhat on Linux versus Windows.

When this tutorial is complete, the J2EE hierarchy for the CMP and the maps you create will look like this:

The Table and Data Source Creator for Application Developer Version 5.1 supports the thin, or type-four, pure Java JDBC drivers. The support for Cloudscape remains the same; there is nothing the user needs to provide. For DB2 and Oracle, supporting the type-four JDBC drivers means that you can build your tables on a database on any machine, local or remote; e.g. on a UNIX© box of some sort. You don't need to have the development environment on the same machine as the database server (unless you want to). Therefore, for example, you can develop your CMPs on Red Hat Linux but host the DB2 V8.1 database server on an AIX© box. This flexibility means you can mix and match, and frees you from performing database administrative functions you don't want -- or aren't authorized -- to perform.

This tutorial is made up of the following development and testing tasks:

1. Create an EJB 2.0 project and an EJB 2.0 CMP
2. Generate top-down EJB-to-RDB mappings
3. Create a test server instance and configuration
4. Create tables and data sources
5. Develop and test a CMP with even less effort

Back to top

1. Create an EJB 2.0 project and an EJB 2.0 CMP

1. In Application Developer, create a new EJB 2.0 project by selecting File => New => Project => EJB => EJB Project.
2. Enter the following values, as shown in Figure 2:
   • Project name: HelloWorld
   • EAR project: accept the default name, DefaultEAR.
3. Accept the remaining defaults and click Finish.
   Figure 2. Creating an EJB 2.0 project
   
   
4. From the J2EE perspective, create a new CMP called HelloWorldCMP by selecting File => New => Enterprise Bean from the list box, and then select the HelloWorld project.
5. In the "Create a 2.0 Enterprise Bean" dialog (Figure 3), check the CMP 2.0 Bean radio button. Name the bean HelloWorldCMP, and the package a name of your choosing. Click Next.
   Figure 3. Supplying the EJB and EAR module project names
   
   

6. Optionally check the Local Client View radio button in the "Enterprise Bean Details" page of the Create an Enterprise Bean wizard (Figure 6) to generate both remote and local interfaces, then add the following CMP fields, as shown in Figures 4 and 5:

   Field name	Type
   firstName	String
   lastName	String
   message	String
   age	int (key)

   
   Figure 4. Supplying CMP attributes
   
   
   Figure 5. Supplying the key field for the CMP
   
   
7. When finished, the wizard should look similar to Figure 6, below:
   Figure 6. Configuring the CMP
   
   

Back to top

2. Generate top-down EJB-to-RDB mappings

Typically, you would generate a top-down map for only the database(s) you wish to test on. For the purposes of this tutorial, to demonstrate the new support for DB2 and Oracle, we will create three top-down maps, one for each database. That is, we will use all three backends (Cloudscape V5.0, DB2 Version 8.1, and Oracle 9i) to demonstrate the multiple backend support in Application Developer Version 5.1. (You are free to create just the ones you are interested in, and in any order.)

1. In Application Developer, select the HelloWorldCMP EJB project. Right-click and select Generate => EJB to RDB Mapping (Figure 7).

   
   Figure 7. Selecting the EJB to RDB Mapping action
   
   
2. Select the Create a new backend folder radio button (Figure 8) and select Cloudscape, V5.0 (which is the default).
3. Leave the default values for the database and schema names, SAMPLE and NULLID, respectively. You may accept the default to generate the DDL, or deselect it if you wish; it is not required for the table creator. Click Finish.
4. Repeat steps a through c to generate an EJB-to-RDB mapping for DB2, Universal Database, V8.1 and for Oracle9i,but be sure the database and schema names you plan to use are specified, as shown in Figure 9.
5. When finished, you will have three top-down mappings defined (Figure 8).
   Figure 8. Two of three top down mappings defined, ready to create a third
   
   
   Figure 9. Creating a DB2 V8.1 top down mapping
   
   

Back to top

3. Create a test server instance and configuration

The Table and Data Source Creator is one of the Server Tools,specifically for WebSphere Application Server V5 unit test servers. Therefore, you need to have a WebSphere Application Server V5 unit test server instance created before you can use the tool. You will see later how the server can be automatically created for you as part of the overall set of server tasks, but the following steps show how you can create the server yourself.

1. In Application Developer, create a new WebSphere Application Server V5 test server configuration and instance by selecting File => New => Other => Server => Server and Configuration. Name this server and configuration HelloWorldServer. Accept the default Servers folder which will create a new server project called Servers. Accept the other defaults and click Finish. See Figure 10.
   Figure 10. Creating a test environment server instance and configuration
   
   
2. From the J2EE view, select the HelloWorldServer server, then right click to Add and Remove Projects....
3. Add the Default EAR module to the server; this will configure the project's module on this server.
   Figure 11. Adding the DefaultEar project to the server configuration
   
   
4. Select the HelloWorldCMP enterprise bean from the J2EE Hierarchy view in the J2EE perspective, as you did to generate the mapping (see Figure 7), and double click to open up the Deployment Descriptor editor (Figure 12). Scroll down to the bottom of the Overview tabbed page, where you will find that a backend ID has been generated for each of the three maps, as well as a default JNDI name.
5. You will set the Current backend ID to the backend that you wish to create tables and data sources for. You will also need to edit the JNDI name to keep the names unique if you intend (as we will here) to run all three data sources at once. For this example, we will use the default name jdbc/Default when creating Cloudscape tables and data sources, jdbc/Default2 for DB2, and jdbc/Default3 for Oracle (Figure 12). With unique names, all three data sources will be able to coexist on the same server at the same time.
   Figure 12. Setting the current backend ID and JNDI name
   
   

Back to top

4. Create tables and data sources

The steps below will create the tables and data sources for each backend in sequence: Cloudscape, DB2, Oracle.

Cloudscape

1. In the Deployment Descriptor editor (Figure 13), make sure that the following values are properly set:

   • Current Backend ID: CLOUDSCAPE_V50_1
   • JNDI name: jdbc/Default
   
   Figure 13. Cloudscape has been set as the current backend ID
   
   
2. Make sure the server is not running. Select the HelloWorldCMP server from any perspective or view, then select Create tables and data sources from the context popup menu.
3. Cloudscape table and data source creation requires no further input. Watch the progress of the operation until the tables and data sources are successfully created (Figure 14).
   Figure 14. Automatically successfully completed table and data source creation for CLoudscape v5.0
   
   

DB2 V8.1

1. In the Deployment Descriptor editor, set the following values:
   • Current Backend ID: DB2UDBNT_V8_1
   • JNDI name: jdbc/Default2.
2. Select the HelloWorldCMP server as before, and from the context menu select Create tables and data sources.
3. Unlike Cloudscape, there are connection properties that must be set for DB2 and Oracle; they only need to be set once. All fields (except password) are persisted between workbench invocations, and all fields (including password) are persisted in memory for the duration of workbench life. This means you don't need to re-enter these values to recreate the tables, only press the enter key.
   
   For DB2, you must initally supply the server, port, user, password and alias (you can accept the default DB2_USER if you like) and the full path to both the db2jcc.jar file (containing the actual type-four JDBC driver) and a license JAR file, as listed in Figure 15. You must specify both JAR files.
   
   If you have an installed DB2 V8.1 database instance on the same machine as Application Devloper, this path should be automatically calculated for you by the tool. However, for remote instances (i.e. when DB2 is installed on any physical machine other than the one on which Application Developer is installed), you will need to have copies of these two JAR files on the development machine that contains Application Developer, and you need to specify the paths to both to continue.
   
   You can get copies of these files from an installed copy of DB2 V8.1 (typically installed in the SQLLIB/java directory). Figure 15 illustrates access to a DB2 backend on a different machine than the development environment using the type 4 JDBC drivers. In this case, copies of both the db2jcc.jar and db2jcc_license_cisuz.jar files are assumed to have been copied to the development machine.
   Figure 15. Supplying connection settings for a DB2 database
   
   
4. Click OK. If the settings are acceptable and the database is available, the tables and data sources will be created automatically. You should get a result dialog that looks identical to Figure 14, except that the Vendor and Backend ID will reflect DB2.

Oracle 9i

1. To generate tables and data sources for Oracle 9i, set the following values in the Deployment Descriptor editor:
   • Current Backend ID: ORACLE_V9_1
   • JNDI name: jdbc/Default3.
2. Select the HelloWorldCMP server as before, and from the context menu select Create tables and data sources.
3. Just like with DB2, a dialog box will be presented for Oracle to gather the connection settings. You must supply a valid user ID, password and hostname. As with DB2 support, the actual database system can reside on the development machine or on another (remote) machine. If an installed copy of Oracle 9i exists on the development machine, the path to the classes12.jar file should be calculated for you. If Oracle is on another machine, you must have a copy of the classes12.jar file (which you can get from the Oracle Web site or from an Oracle installation) on the development machine, and then must manually provide the full path to it. In Figure 16, the database happens to be on another host and a copy of the JAR file is on the development machine. The user ID and password used is scott/tiger, which is a test ID typically created by Oracle for sample databases.
   Figure 16. Supplying connection settings for an Oracle database
   
   
4. Click OK.

And there you have it: the ability to automatically generate and configure tables and data sources on the server withonly a couple of clicks!

At this point, we have generated three sets of tables and data sources for three different database vendors. All you need to do now is to generate your deploy code as you normally would, and then run this server and test and debug the CMP. The Resources below go into a fair detail of how to use the EJB test client to test your CMPs, so there's no need to repeat it here. Just know that one of the more complicated, least understood and most error-prone stages of actually deploying and using a CMP is pretty much taken care for you automatically by the Table and Data Source Creator.

Back to top

5. Develop and test a CMP with even less effort

If you are new to Application Developer, J2EE, EJB or CMP, or would just prefer not to perform Steps 1 through 4 for whatever reason, then do we have a feature for you!

In Application Developer Version 5.1, the already highly simplified "Run on Server" (and "Debug on Server") feature has been made even more simplistic, particularly with regard to developing and testing CMPs. It is now possible for you to concentrate on the business logic in your EJB -- even if it is CMP -- while Application Developer takes care of:

• deploying your code,
• creating and configuring a server instance,
• creating tables and data sources,
• starting the server with the deployed application and, finally,
• bringing you right to your EJB in the Universal Test Client.

And all with just a couple of clicks! This is made possible by a new notion of server tasks, or actions, to be completed before a server instance is actually launched or even created. Application Developer now supports automatically creating and configuring a new server, deploying EJB code and creating tables and data sources for current backend IDs.

To use "Run on Server" to run a CMP with persistence ona test server, here are the minimum steps:

1. Perform Steps 1 and 2 above; that is, create your CMP using the Create EJB wizard.
2. Select the EJB from any view (J2EE perspective or the Server perspective will do), right-click and select Run on Server from the context menu (Figure 17).
   Figure 17. Performing a "run on Server" on the CMP
   
   

3. Select the Create a new server (if this is the first time) and click Next.

   
   Figure 18. Create a new server
   
   
4. On the next page, change the port if necessary (typically you don't need to).
5. ClickNext to display the two main server tasks (Figure 19). Make sure both boxes are checked, then click Finish. That's it.
   Figure 19. Selecting server tasks to be performed
   
   

When the dialog comes up to inform you that Cloudcape tables and data sources were successfully created, press Enter or select OK and watch as your bean is deployed into the new server, which is then started, and the Universal Test Client is launched on your bean. You should be able to continue your involvement right at the Universal Test Client to test the various methods and persistence methods in your code, with the vast majority of the "plumbing" that got you to this point performed behind the scenes. Isn't that the way it should be?

Back to top

Conclusion

This tutorial illustrated the "new and improved" Table and Data Source Creator in WebSphere Studio Application Developer 5.1 that, while continuing to support Cloudscape, now supports DB2 V8.1 and Oracle 9i by way of the type-four, pure Java, JDBC drivers.By helping you to create and configure the database tables (whether remote or local) and the associated data source configuration on the server (JDBC providers, data sources, authorization,aliases, etc.) for DB2 and Oracle, the Table and Data Source Creator further streamlines CMP development, automatically performing tasks generally considered to be time consuming and complex.

Even with no need for, or access to, DB2 or Oracle, by using Cloudscape you still are freed from the tasks of database management and configuring servers in the early stages of development. In this way, you can run and test CMPs "out of the box" quickly and as easily as you can session beans, and without an external database system.

We also introduced the new Run on Server feature that completely automates the steps from intial CMP construction to running and testing on WebSphere Application Server. This is a great time-saver, as well as a helpful entry point for users who are new to CMP development and testing.

Back to top

Download

Name	Size	Download method
HelloWorldCMP.zip	0.1 MB	FTP|HTTP

Information about download methods

Resources

• Developing and Testing a Complete "Hello World" J2EE Application with WebSphere Studio Application Developer -- Part 1, Sheldon Wosnick, IBM WebSphere Developer Technical Journal, October 2001
  
  
• Testing Container Managed Persistence EJBs with the Table and Data Source Creator in WebSphere Studio Application Developer 5.0, Sheldon Wosnick, IBM WebSphere Developer Technical Journal, October 2002
  
  
• Developing and Testing a Complete "Hello World" J2EE Application with WebSphere Studio Application Developer Version 5.0, Elson Yuen, Sheldon Wosnick, IBM WebSphere Developer Technical Journal, June 2003
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• 1. Create an EJB 2.0 project and an EJB 2.0 CMP
• 2. Generate top-down EJB-to-RDB mappings
• 3. Create a test server instance and configuration
• 4. Create tables and data sources
• 5. Develop and test a CMP with even less effort
• Conclusion
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into WebSphere on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers