Weblogic Authors: Yeshim Deniz, Elizabeth White, Michael Meiner, Michael Bushong, Avi Rosenthal

Related Topics: Weblogic

Weblogic: 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.

    @ThingsExpo Stories
    To Really Work for Enterprises, MultiCloud Adoption Requires Far Better and Inclusive Cloud Monitoring and Cost Management … But How? Overwhelmingly, even as enterprises have adopted cloud computing and are expanding to multi-cloud computing, IT leaders remain concerned about how to monitor, manage and control costs across hybrid and multi-cloud deployments. It’s clear that traditional IT monitoring and management approaches, designed after all for on-premises data centers, are falling short in ...
    Organizations planning enterprise data center consolidation and modernization projects are faced with a challenging, costly reality. Requirements to deploy modern, cloud-native applications simultaneously with traditional client/server applications are almost impossible to achieve with hardware-centric enterprise infrastructure. Compute and network infrastructure are fast moving down a software-defined path, but storage has been a laggard. Until now.
    DXWorldEXPO LLC announced today that the upcoming DXWorldEXPO | CloudEXPO New York event will feature 10 companies from Poland to participate at the "Poland Digital Transformation Pavilion" on November 12-13, 2018.
    Digital Transformation is much more than a buzzword. The radical shift to digital mechanisms for almost every process is evident across all industries and verticals. This is often especially true in financial services, where the legacy environment is many times unable to keep up with the rapidly shifting demands of the consumer. The constant pressure to provide complete, omnichannel delivery of customer-facing solutions to meet both regulatory and customer demands is putting enormous pressure on...
    The best way to leverage your CloudEXPO | DXWorldEXPO presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering CloudEXPO | DXWorldEXPO will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at CloudEXPO. Product announcements during our show provide your company with the most reach through our targeted audienc...
    In an era of historic innovation fueled by unprecedented access to data and technology, the low cost and risk of entering new markets has leveled the playing field for business. Today, any ambitious innovator can easily introduce a new application or product that can reinvent business models and transform the client experience. In their Day 2 Keynote at 19th Cloud Expo, Mercer Rowe, IBM Vice President of Strategic Alliances, and Raejeanne Skillern, Intel Vice President of Data Center Group and ...
    More and more brands have jumped on the IoT bandwagon. We have an excess of wearables – activity trackers, smartwatches, smart glasses and sneakers, and more that track seemingly endless datapoints. However, most consumers have no idea what “IoT” means. Creating more wearables that track data shouldn't be the aim of brands; delivering meaningful, tangible relevance to their users should be. We're in a period in which the IoT pendulum is still swinging. Initially, it swung toward "smart for smart...
    @DevOpsSummit at Cloud Expo, taking place November 12-13 in New York City, NY, is co-located with 22nd international CloudEXPO | first international DXWorldEXPO and will feature technical sessions from a rock star conference faculty and the leading industry players in the world.
    DXWorldEXPO LLC announced today that All in Mobile, a mobile app development company from Poland, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. All In Mobile is a mobile app development company from Poland. Since 2014, they maintain passion for developing mobile applications for enterprises and startups worldwide.
    In his keynote at 19th Cloud Expo, Sheng Liang, co-founder and CEO of Rancher Labs, discussed the technological advances and new business opportunities created by the rapid adoption of containers. With the success of Amazon Web Services (AWS) and various open source technologies used to build private clouds, cloud computing has become an essential component of IT strategy. However, users continue to face challenges in implementing clouds, as older technologies evolve and newer ones like Docker c...
    DXWorldEXPO LLC announced today that ICC-USA, a computer systems integrator and server manufacturing company focused on developing products and product appliances, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City. ICC is a computer systems integrator and server manufacturing company focused on developing products and product appliances to meet a wide range of ...
    Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, discussed how from store operations and ...
    We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
    Headquartered in Plainsboro, NJ, Synametrics Technologies has provided IT professionals and computer systems developers since 1997. Based on the success of their initial product offerings (WinSQL and DeltaCopy), the company continues to create and hone innovative products that help its customers get more from their computer applications, databases and infrastructure. To date, over one million users around the world have chosen Synametrics solutions to help power their accelerated business or per...
    Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
    Founded in 2000, Chetu Inc. is a global provider of customized software development solutions and IT staff augmentation services for software technology providers. By providing clients with unparalleled niche technology expertise and industry experience, Chetu has become the premiere long-term, back-end software development partner for start-ups, SMBs, and Fortune 500 companies. Chetu is headquartered in Plantation, Florida, with thirteen offices throughout the U.S. and abroad.
    Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: Driving Business Strategies with Data Science," is responsible for setting the strategy and defining the Big Data service offerings and capabilities for EMC Global Services Big Data Practice. As the CTO for the Big Data Practice, he is responsible for working with organizations to help them identify where and how to start their big data journeys. He's written several white papers, is an avid blogge...
    Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: Driving Business Strategies with Data Science," is responsible for setting the strategy and defining the Big Data service offerings and capabilities for EMC Global Services Big Data Practice. As the CTO for the Big Data Practice, he is responsible for working with organizations to help them identify where and how to start their big data journeys. He's written several white papers, is an avid blogge...
    Charles Araujo is an industry analyst, internationally recognized authority on the Digital Enterprise and author of The Quantum Age of IT: Why Everything You Know About IT is About to Change. As Principal Analyst with Intellyx, he writes, speaks and advises organizations on how to navigate through this time of disruption. He is also the founder of The Institute for Digital Transformation and a sought after keynote speaker. He has been a regular contributor to both InformationWeek and CIO Insight...
    "IBM is really all in on blockchain. We take a look at sort of the history of blockchain ledger technologies. It started out with bitcoin, Ethereum, and IBM evaluated these particular blockchain technologies and found they were anonymous and permissionless and that many companies were looking for permissioned blockchain," stated René Bostic, Technical VP of the IBM Cloud Unit in North America, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventi...