This article explores using MySQL as the database engine where the application is developed using BEA WebLogic Workshop 8.1 and deployed to BEA WebLogic Server 8.1. Using an archetypical J2EE architecture, I evaluate the impact of using MySQL from various aspects such as choosing the correct version of MySQL, setting-up the server, and making development adjustments. The information presented here not only enhances the readers' understanding of the tools and technologies utilized, but also saves countless hours. Even readers who employ different database technologies will find the information and material useful.

Last month (WLDJ, Vol. 3, issue 4), I discussed how to select the "right" version of MySQL and described various changes to the WebLogic Domain Configuration to support key J2EE technologies such as Java DataBase Connectivity (JDBC) and Java Message Service (JMS). This month, I'll describe the changes, adjustments, and modifications to support Enterprise JavaBeans (EJBs), the core component model for J2EE as well as Java Transaction API (JTA). Just as in Part I, the development tool of choice is WebLogic Workshop 8.1 and the application is deployed to WebLogic Server 8.1.

Subsystem Architecture

Before proceeding further you need to complete the following steps:

• Create a BEA WebLogic Workshop application (e.g. SEMApp).
• Choose SEMDomain as the server.
• Add junit.jar into the library.
• Import a Java project within SEMApp named UtilProject.
• Import an EJB project within SEMApp named DomainProject.
• Import an EJB project within SEMApp named AgentProject.
• Import a Java project within SEMApp named TestProject.
• In accordance with the project dependencies, change the build order to the following: UtilProject, DomainProject, AgentProject, SEMAppWeb, and TestProject.
• Build all the projects.
• Start WebLogic Server.

Enterprise JavaBeans (EJBs)

The following sections discuss issues that arise when using MySQL and Entity Beans with CMP. One issue is automatic primary key generation. The other issue is deferrable constraints. The archetypical sample application consists of many CMP entity beans to illustrate these issues. Figure 2 shows a class diagram of the CMP entity beans and the relationships between them.

Automatic Primary Key Generation
BEA WebLogic Server supports two methods for automatic primary key generation. The first method, known as the NamedSequenceTable strategy, uses a sequence table. This approach is generic and works with most databases, including MySQL. As an example, for the Person table, create a Person_Seq table for primary key generation. The Person_Seq table has one column named Sequence. Insert a row into Person_Seq where the sequence starts, typically zero. Refer to sem.sql in the provided source code example. Insert the following EJBGen tag for automatic primary key generation for each entity.

*
* @ejbgen:automatic-key-generation
* cache-size="10"
* name="Person_Seq"
* type="NamedSequenceTable"
*

Refer to the source code for PersonBean.ejb in the example source code (the source code is online at www.sys-con.com/weblogic/sourcec.cfm).

The second method takes advantage of native DBMS support. For example, Oracle has sequences that can be utilized for automatic primary key generation whereas Microsoft SQL Server has identity columns for automatic primary key generation. BEa WebLogic Server supports the native DBMS feature for Oracle and Microsoft SQL Server databases only.

Using a single, automatic, primary key generation technique throughout the project is recommended. Since the strategy of NamedSequenceTable is officially supported, that strategy is recommended. Further information about automatic primary key generation support is available at http://e-docs.bea.com/wls/docs81/ejb/entity.html#1155399.

Deferrable Constraints
Databases such as Oracle and DB2 support a feature known as deferrable constraints. When using a deferrable constraint, the constraint, such as a foreign key, is not checked right away during an insert or update. The constraint is checked later at the commit time. This capability gives the WebLogic Server CMP engine considerable leeway in ordering SQL statements for maximum efficiency and performance. However, MySQL does not support deferrable constraints. Therefore, the default CMP behavior will cause foreign key violations as the constraints are always checked right away. For example, the relationship between Person and PersonStatus entities demonstrates the problem. A potential solution is to turn off the ordering of database operations. Insert the following EJBGen tag to turn off the ordering of database operations:

* @ejbgen:entity
* ...
* order-database-operations="false"

However, this tag has no effect whatsoever. By default, batching of statements is enabled. If batching of statements is enabled, then ordering of database operations is automatically enabled. Therefore, turn off both the batching of operations and ordering of database operations. Insert the following EJBGen tag to turn off ordering of database operations and batching of statements.

* @ejbgen:entity
* ...
* order-database-operations="false"
* enable-batch-operations="false"

Refer to the source code for PersonBean.ejb in the code example. Specifying order-database-operations and enable-batch-operations to "false" is recommended for all the entity beans in the project.

Choosing Date and Time Column Types

Another approach is to leverage database-specific features that will automatically insert or update the information. The advantage of this approach is that the programmer does not have to write, test, debug, and maintain the code. The disadvantage of this approach is that the programmer is at the mercy of the facilities available in the database. One way to automatically insert and update dates is to leverage database triggers. Unfortunately, the current version of MySQL does not support these, although a future major version will support them. However, MySQL can still be leveraged to automatically insert and update date and time types. Consider the date and time column types available in MySQL: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. For the auditing purposes, as date and time are both important only DATETIME and TIMESTAMP make sense.

Consider the DDL for the BUYER table. Both the CREATE_DATE and UPDATE_DATE columns are DATETIME column types. The programmer is responsible for maintaining CREATE_DATE and UPDATE_DATE. One recommended place to set the create date is in the ejbCreate() method. Refer to the BuyerBean.ejb included in the example. A recommended place to set the update date is in the session facade update method. Refer to the updateBuyer() method in DomainFacadeBean.ejb in the example.

Consider the DDL for the PERSON table. Both the CREATE_DATE and UPDATE_DATE columns are TIMESTAMP column types. Updating a row in the PERSON table automatically updates the CREATE_DATE column whereas the UPDATE_DATE column remains unchanged. This result is unexpected! There are at least two potential ways to get the expected result. One is to change the order of the CREATE_DATE and UPDATE_DATE columns as illustrated by the ADDRESS table. The other is to change the column type of CREATE_DATE to DATETIME and set the CREATE_DATE column programmatically as illustrated by the PRODUCT table.

Consider the DDL for the SELLER table. Both the CREATE_DATE and UPDATE_DATE columns are TIMESTAMP column types. However, compared with the PERSON table, the order of CREATE_DATE and UPDATE_DATE is reversed as listed in the DDL. The column UPDATE_DATE is listed and created before the CREATE_DATE column. Updating a row in the SELLER table automatically updates the UPDATE_DATE column, leaving the CREATE_DATE column intact. This is the expected result.

Consider the DDL for the CREDIT_CARD table. The CREATE_DATE column type is DATETIME whereas the UPDATE_DATE column type is TIMESTAMP. The CREATE_DATE column is set programmatically. MySQL updates the UPDATE_DATE column automatically when the row is updated in the table. This is the expected result. One recommended place to set the create date is in the ejbCreate() method. Refer to the code for CreditCardBean.ejb included in the source code example.

Remember to keep the following rules in mind about the TIMESTAMP column type:

• The column value is automatically set to the current date and time, if NULL is inserted into any TIMESTAMP column.
• The column value is automatically set to the current date and time upon create or update for only the first TIMESTAMP in the row column if no value is inserted into that column.
• Inserting an explicit date and time value explicitly defeats timestamping.

Other Recommendations
One recommendation is to always use object types rather than primitive types. For example, use java.lang.Integer instead of int. The default value for java.lang.Integer is null where as the default value for int is zero. If there is a nullable foreign key constraint, then the constraint is violated when using the default value of zero because no such matching row exists in the foreign table.

Many properties can be set to enable outputting of more information to assist in debugging and monitoring. weblogic.ejb20.cmp.rdbms.codegen.verbose is one such property. Setting the property to true displays the JDBC statements as well as binding of parameters to the statements.

JTA Domain Configuration Changes

Consider for example the AgentFacadeBean's enqueuePerson() method. The method not only writes to the database, but also enqueues a message onto a queue. Both the JMS as well as the RDBMS resources need to be XA enabled.

If the JMS resource is not XA enabled, then obtaining the connection factory using a resource reference results in an error. The error message informs you that "two-phase commit is not available". For the SEMDomain, set the property on the connection factory to enable XA.

Enabling XA on the JMS Connection Factory

1. Make sure BEA WebLogic Server is running.
2. Launch the WebLogic Server Console.
3. Log into the Console.
4. Select Services / JMS / Connection Factories.
5. Select semJMSConnectionFactory.
6. Select Transactions tab.
7. Enable XA Connection Factory Enabled check box.

BEA WebLogic Server allows distributed transactions even if at the most one of the resources does not support XA. It has the capability to emulate XA for the resource that does not support XA. To emulate XA and allow MySQL to participate in two-phase commits, modify the datasource configuration that accesses MySQL to emulate XA.

Emulating XA
The following steps describe changing the datasource configuration to emulate XA.

1. Make sure BEA WebLogic Server is running.
2. Launch the WebLogic Server Console.
3. Log into the Console.
4. Select Services / JDBC / Data Sources.
5. Select a JDBC Data Source (e.g. semJDBCDataSource).
6. Display Advanced Options by clicking on Show.
7. Select Emulate Two-Phase Commit for non-XA driver checkbox.

If the transaction is rolled back, WebLogic Server redelivers the message. The default redelivery count value is -1, meaning there is no limit to the number of times the message is redelivered. No limit causes BEA WebLogic Server to continuously reprocess the message over and over again as long as the transaction is rolled back. The WebLogic Server server can be thrown into an infinite loop. To stop WebLogic Server from endlessly processing the same message over and over again, the redelivery count value can be modified. For the SEMDomain, configure the redelivery count value to 0 or higher. The redelivery count value is modified differently based on whether the destination is a regular (i.e., nondistributed) or a distributed destination. One way to modify the redelivery count for a nondistributed destination, is to modify the redelivery count on the destination itself. One way to modify the redelivery count for a distributed destination is to modify the redelivery count on the JMS template corresponding to the distributed destination.

Setting the Redelivery Count Value
The following steps describe changing the redelivery count value for the PersonQueue.

1. Make sure WebLogic Server is running.
2. Launch the WebLogic Server Console.
3. Log into the Console.
4. Select Services / JMS / Templates.
5. Select PersonQueue.
6. Select Configuration tab.
7. Select Redelivery sub tab.
8. Change the value of Redelivery Limit to 3.

Running and Verifying

Conclusion