Object/Relational Mapping @ WEBSPHERE JOURNAL

Object/Relational Mapping

It's one of the greatest challenges in enterprise application development: object/relational mapping. Business information lives in relational databases, and applications are made up of objects. There is no shortage of products that attempt a systematic mapping between tables and objects, all with limited success.

EJBs and WebSphere add some new twists to O/R mapping, and WebSphere Studio Application Developer provides some new solutions, which we will explore here.

Persistence of Vision

If you work on an EJB project, you'll run into this situation very soon, if you haven't already: a relational database exists, and you must access it using EJBs. One reaction to this task is panic, frantically writing SQL statements to be invoked from a session bean, or even worse, directly from the client. If this approach works at all, it will produce an application that cannot be expanded or maintained as database schemas and processing requirements change (almost constantly). There are better strategies to use, such as employing DAOs (Data Access Objects) to access the database tables, and shuttling data back and forth via POJOs (Plain Old Java Objects).

Whether container-managed persistence (CMP), bean-managed persistence (BMP), or another mapping strategy is used, the key to successful maintenance is autogenerating the beans when the underlying database schema changes. In BMP, most of the complexity is in the code and the deployment descriptor file is relatively simple. For CMP, the complexity is in the deployment descriptors, which can become a nightmare as they are edited to match changes in the database schema. This means we need to be able to autogenerate deployment descriptors as well as code.

CMP provides many advantages, especially on WebSphere Application Server. For example, the container can optimize queries as read-only so only the load method will be executed on the bean instead of both the load and the store, as would be the case with BMP. This can lead to a substantial performance increase. Perhaps the biggest advantage to CMP is that you can use a standard, built-in object-oriented query language (EJB QL) with CMP, but that's a topic for another article.

Against the Grain?
When the EJB standard was new, J2EE pundits tended to recommend a "large-grained" approach. This meant that if a system consisted of many persistent JavaBeans, only a few would be EJBs. Usually stateless session beans would suffice to make JDBC calls. If entity beans were used at all, they would be responsible (via BMP) for persisting the simpler JavaBeans. This could become a maintenance nightmare. In a "fine-grained" approach, all relational tables are mapped to entity beans. This is more flexible and can be much more easily maintained. It can also have better performance and scalability. Note, however, that the ability to autogenerate code and deployment descriptor files is the key to success with fine-grained EJB assets.

In my opinion, all persistence and most transactions in a J2EE project should be handled via entity beans. There is considerable debate in the EJB/J2EE community about where entity beans belong, but there can be no doubt that one of the main purposes of entity beans is O/R mapping. Ideally, entity beans will make the persistence layer transparent to the rest of an application. Bean-managed persistence can be a reasonable solution, provided the BMP beans can be generated and regenerated automatically when the database changes. Making changes to BMP entities by hand can be a long, tedious, and error-prone process. Fortunately, there are commercially available tools to automate BMP creation, such as Borland's JBuilder Enterprise Edition. Of course, when it comes to creating and updating relationships between BMP entity beans, developers are largely on their own, often using data-access objects to perform joins between EJBs.

In a J2EE server that supports the EJB 2.0 specification, such as WebSphere 5.0 (coming soon), container-managed persistence for EJBs and the relationships between them is standardized and fully supported. If the database you're using doesn't require special kinds of access for the sake of maintaining isolation levels (locking) or some other feature, EJB 2.0 CMP is a good option. Generating and updating the deployment descriptors needed for 2.0 CMP can be a challenge (especially if done in a text editor), but for WebSphere 4.0 developers, CMP can involve additional complexity. Since CMP for relationships between entity beans is not in the EJB 1.1 specification, WebSphere implements this feature via methods inserted into each entity bean in the relationship, along with relatively complex deployment descriptors.

Up and Down with WebSphere Studio Application Developer

EJB developers almost invariably find themselves using two basic approaches to build an application: bottom-up and top-down. Most projects use a combination of both, but beginning from the bottom is the most useful since most enterprise projects involve connecting to at least one existing database.

The bottom-up approach starts with an existing database schema. Developers then use a tool such as WebSphere Studio Application Developer (WSAD) to generate EJBs and relationships from the tables in the schema. Each table must declare a primary key (compound keys are usually okay). By default, relationships are generated where foreign keys exist. The entire system can then be built on an entity-bean foundation that can be generated and regenerated automatically as the schema changes. Depending on where you are in the development cycle, the database schema may or may not migrate frequently. If the schema is volatile, or the project is entering into a maintenance mode, this is an ideal approach.

If the object model already exists, but the database doesn't (brand new project), it's also possible to build the system from the top down, using a tool such as WSAD to generate the database schema from the object model and map it back to the entity beans in the model. WSAD generates a set of tables to support the CMP entities inside the EJB project. In these tables, each column corresponds to a CMP field of the enterprise bean, and the generated mapping maps the field to the column. Relationship associations are mapped to foreign-key relationships. By default, EJB inheritance hierarchies are mapped to a single table; that is, the base and all derived enterprise beans are mapped to the same database table. Additional options exist that support generating joined tables for the enterprise beans.

WSAD 4.0 is based on the open-source Eclipse IDE, and as such, supports a number of different perspectives for a project, and each perspective supports multiple views. Some of the perspectives available are Data (databases), Java, and J2EE. We can develop EJBs best in the J2EE perspective, and our first task is to create a new enterprise application project.

From the perspective menu on the menu bar, select the J2EE perspective, then left-click on Enterprise Applications in the J2EE view (see Figure 1). Now select New> Enterprise Application Project. You must supply a name for the project. If you click finish, then the default application client, EJB, and Web projects will automatically be created for this enterprise application project (see Figure 2). If you don't want these created, you can change the project names or deselect them here and create them later as needed. Click finish and you have a new enterprise application project. We will use this project to autogenerate entity beans from a schema.

To generate entity beans from a schema via bottom-up mapping, follow these steps:

1. In the J2EE perspective, select the EJB project from which you want to generate the bottom-up mapping. This must be a newly created, empty project with no EJBs in it.
2. Right-click on EJB project, then select Generate > EJB to RDB Mapping (see Figure 3).
3. In the Create new EJB/RDB Mapping dialog box, select Bottom up (see Figure 4) and then click Next.
4. At this point, you will immediately be confronted with a form you must fill out to connect to the database from which you want to generate EJBs (see Figure 5). To connect to an existing database schema, you'll need to provide the following information:

Connection name: This can be any name.
Database: The name of the database that contains the schema, such as bilbobaggins.
User ID, password: The user name and password needed to gain access to the database.
Database vendor type: The vendor type, such as Oracle8i 8.1.7.
JDBC driver: The driver you want to use, such as Oracle Thin JDBC Driver.
Host: The server where the database resides, such as theshire.frodo.net.
Port number: The port used to access the database server, such as 1521.
Class location: The ZIP or JAR file that contains the JDBC drivers for your database, such as c:\Oracle\classes12.zip.

5. Click Next. This step may take a minute to complete if the database is relatively large.
6. Select the tables from the database you want to import. You're probably only interested in one schema. You can select one table, all tables, or a combination of tables, and WSAD will generate EJBs accordingly.
7. The following rules apply to database tables: be careful that a single table or a subset of tables has no broken foreign-key relationships. Every table you want to convert to an EJB must specify a primary key. Columns must be convertible directly to Java types. Foreign keys must be specified as such in the table.
8. Define a package and prefix for the generated EJB classes, then click Finish.

To generate a database schema from an EJB project, simply right-click on the project and select Generate > Schema DDL. The resulting file will be stored as Table.ddl in the META-INF directory for the project.

When you create a new application client, EJB, or Web project you must specify the name of the enterprise application project in which it will be included. If you export an enterprise application project from WSADS, a corresponding .ear file will be generated, filled with .jar files for each of the EJB and Web projects belonging to the enterprise application project. This file is directly deployable to an instance of WAS 4.0.

Deployment Descriptors
Whether you deploy to a test server within WSAD, or to an external server, you will need a flawless set of deployment descriptor files. These include the following:

Application Project

application.xml defines the components in the .ear file.
ibm-application-ext.xml
.modulemaps

EJB Project

ejb-jar.xml
ibm-ejb-jar-ext.xml
ibm-ejb-jar-bnd.xml
Map.mapxmi
Schema

The application.xml and the ejb-jar.xml files are standard, as described in the EJB 1.1 specification. The other files may need a little explanation. The EJB specification allows for vendor-specific descriptor files in addition to the standard J2EE deployment descriptors. They enable the use of vendor-specific EJB features such as the inheritance and association features IBM provides. The ibm-application-ext.xml and the ibm-ejb-jar-ext.xml files define the IBM-specific features used in this project. The .modulemaps file defines the contents of the application .ear file. The schema is a directory containing the definitions for each container-managed attribute generated, and the Map.mapxmi file provides the mapping between the schema and the EJBs.

Almost more valuable than generating the entity beans themselves, WSAD also automatically generates almost all of the deployment descriptor files necessary for your application to use CMP on WAS. It is far from trivial to create these correctly by hand, so this can save a lot of time, especially when it comes to maintenance.

Before our entities can be deployed, we must generate the stubs and ties for them. To do this, right-click the EJB project and select Generate > deploy and RMIC code. This will generate the stubs, ties, and other classes needed for the application to run in WebSphere. The generated code will automatically be verified at this point. To validate without generating code, lef-click on the EJB project and select Run Validation.

Ties That Bind

The one file that isn't automatically created when you generate EJBs from the schema is ibm-ejb-jar-bnd.xml. This is the file that tells the server/container where the database (data source) resides. Instead, you must create it by right-clicking on the EJB project and selecting Open With > EJB Extension Editor (see Figure 6). Once the extension editor is visible, select the tab marked Bindings. Now click on the EJB project. If no information is visible, type in the JNDI name of the data source defined in the server instance you are using, along with the user name and password (see Figure 7). Finally, click on the file menu and select Save>EJB Extensions Editor.

If no data source is defined in the server instance on which you will be installing this EJB project (as part of an application project), you will need to define it as follows: create a JNDI name for the data source. This can be any name you want, but every EJB project that uses the data source will need to include the JNDI name and login information. In WAS, data sources are tied to defined database drivers, so one driver can have many data sources. One way to specify the server data source is to edit the server-cfg.xml file as shown in the following:

<resourceProviders xmi:type= "resources:JDBCDriver"
xmi:id="JDBCDriver_3"
name="oracle.jdbc.driver.Ora cleDriver" description=""
implementationClassName="ora
cle.jdbc.pool.OracleCon- nectionPool"
urlPrefix="">
<factories
xmi:type="resources:Data-
Source"xmi:id="Data- Source_2"
name="RingDataSource" jndiName="jdbc/RingData-
Source"description=""cate
gory=""
jtaEnabled="false" databaseName="ring"
minimumPoolSize="1"
maximumPoolSize="10"
connectionTimeout="180"
idleTimeout="1800"
orphanTimeout="1800"
statementCacheSize="100"
defaultUser="GOLLUM"
defaultPassword="{xor}
Liw7Oik="disableAutoCon-
nectionClean-up="false">
<propertySet xmi:id="null_ps"/>
</factories>
</resourceProviders>

The other way to specify the data source from WSAD is to open the server configuration corresponding to the server instance you want to use, select the Data Sources tab, and add the data source under an existing driver or create a driver, then add the data source. Select the file menu, then select save when you are finished (see Figure 8).

An example of entity beans and deployment descriptors that might be generated from a database sche-ma using WSAD 4.02 can be found at www.sys-con.com/websphere/source.cfm. This example also in-cludes the database schema gene-rated from the entity beans. Which came first? You decide!

Conclusion
J2EE development tools and the EJB specification have improved to the point where it is practical and beneficial to use entity beans as fine-grained, persistent components. Automatic generation and re-generation of entity beans from a relational database, and autogeneration of database schemas from existing entity beans are the keys to success in this approach.