11 million installations can't be wrong

MySQL Journal

Subscribe to MySQL Journal: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get MySQL Journal: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

MySQL Journal Authors: Greg Schulz, Cloud Best Practices Network, Jayaram Krishnaswamy, Elizabeth White, Jnan Dash

Related Topics: Java EE Journal, MySQL Journal

J2EE Journal: Article

Considering MySQL? Read On... (Part 2)

A powerful combination for mission-critical applications

  • Read part one of this 2-part article

    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

    A J2EE application consists of many subsystems or modules. The archetypical application consists of the following subsystems: UtilProject, DomainProject, AgentProject, TestProject, and SEMAppWeb. These subsystems, as well as dependencies between them, are depicted in Figure 1. The UtilProject contains common utility classes. The DomainProject contains the container-managed persistence (CMP) EntityBeans as well as a Session Facade Stateless Session Bean (SLSB). The AgentProject contains a SLSB that enqueues messages on to a queue for asynchronous processing, and a Message-Driven Bean (MDB) to process the messages. The TestProject aggregates all the tests from other projects. The SEMAppWeb project is the default web project.

    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.
    The next section starts with a high-level overview of the Enterprise JavaBeans and specifically explores the issues that arise when using them with MySQL.

    Enterprise JavaBeans (EJBs)

    Enterprise JavaBeans are the core component model for J2EE. There are three types of EJBs: Session Beans, Entity Beans, and Message-Driven Beans. Entity Beans are specifically designed to interact with the database. There are two types of Entity Beans: Entity Beans with bean managed persistence (BMP) and Entity Beans with container managed persistence (CMP). When using BMP, the programmer writes all the JDBC code and has control over that code. When using CMP, the programmer declaratively specifies persistence in eXtensible Markup Language (XML). The EJB container is responsible for providing the persistence by generating the necessary JDBC code. The archetypical J2EE blueprint architecture uses Entity Beans with CMP.

    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

    Date and time types are used in many different ways in an application. A specific example where date and time are used is to maintain audit information such as create date and update date. The create date records the date and time when the row is inserted. The update date records the date and time when the row is modified. There are a couple of different ways to approach and solve this puzzle. One approach is to maintain and update all the information programmatically in code. The advantage of this approach is that the programmer has complete control over the code. The disadvantage of this approach is that code has to be written, tested, debugged, and maintained.

    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.
    Carefully choosing one policy and implementing it throughout the complete project is recommended.

    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

    JTA is an application programming interface (API) used to coordinate distributed transactions between different resources. For example, using JTA, a JMS message can be sent or received and data can be committed to the database via JDBC within a single global transaction. In this example, one resource is the JMS server and another resource is the RDBMS. The distributed transaction spans the JMS server as well as the RDBMS. In order to support distributed transactions, resources such as database servers or JMS servers need to support industry standard X/Open XA protocol. The drivers that access the resources need to support XA as well.

    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

    The following steps describe 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.
    The JDBC datasource is not XA enabled. Neither MySQL nor MySQL's JDBC driver support XA. Executing the AgentFacadeBean's enqueuePerson() method results in TransactionRolledbackException complaining that the "JDBC driver does not support XA , hence cannot be a participant in two-phase commit. To force this participation, set the EnableTwoPhaseCommit property on the corresponding JDBCTxDataSource property, to true."

    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.
    Transactions and Redelivery
    In the sample application, the ProcessPersonBean Message-Driven Bean (MDB) listens to the queue and processes messages off the queue. The ProcessPersonBean is configured with Container Managed Transaction Demarcation (CMTD) with the transaction attribute of Required. The onMessage() method executes within a container transaction. Calling the setRollbackOnly() method of the MessageDrivenContext rolls back the transaction. WebLogic Server also rolls back the transaction in the event of a runtime exception thrown from the onMessage(). Throwing runtime exceptions from the onMessage() method is considered a programming error according to the JMS specification.

    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

    To verify that everything is configured properly and the application is deployed, run the MasterTest class. The MasterTest aggregates all the JUnit tests. Copy sample-build.properties to build.properties and change the property values to match the environment. Run the MasterTest by invoking ant as follows: ant invokeMasterTest.


    This article discussed issues such as primary key generation and deferrable constraints that arise when using Entity Beans with container-managed persistence and MySQL. I described changes to the WebLogic Domain Configuration to support the Java Transaction API. Knowing and understanding the impact of using MySQL with various J2EE technologies such as EJBs, JMS, JDBC, and JTA is a must for successful project implementation. As illustrated, MySQL, BEA WebLogic Workshop, and BEA WebLogic Server form a powerful combination to architect, design, and deploy mission critical applications. Acknowledgments I want to thank Steve Ditlinger, Roshni Malani, and Sarah Woo for reviewing this article and providing invaluable feedback. References
  • Malani, Prakash. "Considering MySQL? Read On..., part I". BEA WebLogic Developer's Journal, Vol. 3, issue 4
  • To discuss the article and ask questions start here: http://groups.yahoo.com/group/bartssandbox. Free membership is required.
  • Main MySQL Web site: www.mysql.com
  • Starting point for MySQL documentation: www.mysql.com/documentation/index.html
  • www.oreillynet.com/lpt/wlg/3946
  • For all things EJBGen related: www.beust.com/cedric/ejbgen
  • Detailed information about JMS transactions and redelivery options: www.javaworld.com/javaworld/jw-03-2002/jw-0315-jms_p.html
  • DuBois, Paul. The Definitive Guide to Using, Programming, and Administering MySQL 4 Databases, Second Edition. (www.bookpool.com/.x/d4jha9om4m/sm/0735712123)
  • Oracle's deferrable constraint support: (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22integ.htm#4666). Free membership to Oracle Technical Network (OTN) may be required.
  • Checkout J2EE Patterns here: (http://java.sun.com/blueprints/patterns/index.html). For Session Façade Pattern used in the sample application refer to http://java.sun.com/blueprints/corej2eepatterns/Patterns/SessionFacade.html and http://java.sun.com/blueprints/patterns/SessionFacade.html.
  • For Value Object Pattern used in the sample application refer to http://java.sun.com/blueprints/corej2eepatterns/Patterns/TransferObject.html and http://java.sun.com/blueprints/patterns/TransferObject.html.
  • JUnit, including downloading the software: www.junit.org/index.html
  • More Stories By Prakash Malani

    Prakash Malani has extensive experience in architecting, designing, and developing object-oriented software and has done software development in many application domains such as entertainment, retail, medicine, communications, and interactive television.He practices and mentors leading technologies such as J2EE, UML, and XML. Prakash has published various articles in industry- leading publications.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.