One of the toughest challenges of any software development architecture is reconciling the object-oriented paradigm with that of the relational database.

If this isn't done properly, the object layer will become too closely tied to the database schema and any change to the database schema will cause a large amount of rework in the object layer. CMP (Container-Managed Persistence), along with CMR (Container-Managed Relationships), EJB QL, and WebLogic QL, provides a mechanism for effectively managing this problem.

For most of us, the power of CMP is not the attempt to seamlessly adapt the code to entirely new database schemes, but a way to manage independent and inevitable evolution of the database and object layer. In an enterprise architecture, it is unlikely that a single database will serve only one client application. As the enterprise requirements evolve, the database scheme will need to change. If we effectively utilize the database abstractions provided by CMP, we can minimize the costs associated with these changes and increase flexibility throughout the enterprise.

CMP requires the programmer to write much less code. Time that was spent managing persistence can now be spent implementing business logic. Although persistence management tools have been around for a while, CMP allows the container to manage the persistence, which opens the door for optimizations not available to the application programmer or other automated persistence mechanisms.

With EJB 1.1, CMP was only suitable for the simplest persistence problems and most advanced systems could not effectively utilize it. Projects built entirely with CMP used an enormous number of fine-grained entity beans that communicated with each other through costly remote calls. Querying data that spanned multiple tables required writing standard SQL statements. We ended up with very slow systems that were still tightly coupled to the database. With EJB 2.0, new features like local interfaces, CMR, ejbSelect, and EJB QL went a long way toward solving most of these problems. Yet EJB QL is still very new, and unable to entirely fulfill the requirements of most systems. BEA filled this gap by extending EJB QL with WebLogic QL. In this article, I'll show you how to implement ejbSelects and Finder methods utilizing EJB QL and WebLogic QL.

What Are EJB QL and WebLogic QL?
EJB QL is a SQL-like query language used to write queries for ejbSelect and Finder methods. SQL key words like SELECT, FROM, and WHERE are used, but the queries are executed against an object graph of CMP entity beans, not the database. Prior to EJB QL, application server vendors had to provide their own query language. WebLogic had WLQL. EJB QL is new, and will take time to evolve. It is missing key features like subselects, aggregate functions, and group by and order by functionality. As EJB QL evolves, it will surely add most of these features, but for now they're available by using WebLogic QL, BEA's extension to EJB QL.

Setting Up the Example
The example application consists of invoices that can have one or more line items. We will create two entity beans, the Invoice Bean and the Line Item Bean, and one stateless session bean, the Manager Bean. CMR is used to link the Invoice Bean to the Line Item Bean. The entity beans use local interfaces to minimize overhead. This is extremely important when using fine-grained entity beans, which were considered an anti-pattern in EJB 1.1. That is no longer the case if local interfaces are utilized. CMP and CMR are defined in the deployment descriptors. (For information on setting CMP and CMR see http://edocs.bea.com/wls/docs70/ejb/cmp.html.)

In constructing the example I tried to keep it simple, but still follow best practices. That's why I included the Manager Bean. Its primary purpose was to control the transactions, and provide a remote interface for my test client. The entity bean method's transaction attributes were all set to "Mandatory", while the session bean method's transaction attributes were all set to "Required". This ensured that each logical unit of work would execute within one transaction, allowing for more efficient execution, but even more importantly it ensured that operations that have to be executed within the same transaction are.

We will use two database tables - the Invoice Table and the LineItem Table - to persist our beans (see Table 1). One final note: all of the examples were developed and tested using WebLogic 7.0.

Simple Finder Method
Let's start by adding a simple finder method to the InvoiceLocalHome, which will return a Collection of all the Invoice Beans. First we'll add the method to the InvoiceLocalHome interface. Notice the return type is Collection. This designates that all of the Invoice Beans matching the criteria of the query will be passed back. If the return type was InvoiceBean, only the first matching InvoiceBean would be returned.

Collection findAll() throws FinderException;

Listing 1 shows the XML to be inserted into the ejb-jar.xml deployment descriptor. The method name must match the method in the InvoiceLocalHome interface "findAll". Now we'll define the parameters. Even if your query does not require any parameters you must include the <method-params> tag.

Since this is a finder method we would like our return type to be a Collection of Invoice Beans. The key word OBJECT signifies that an entity bean will be returned. In the FROM clause we alias InvoiceBean with "i". This alias not only saves time typing, it can also be used to manage multiple instances of the same bean in one query. When the findAll method of the InvoiceLocalHome interface is called, a Collection containing local interfaces to all of the invoices will be returned. In reality, this would be impractical, but it is a good first example.

Finder Method with WebLogic QL
Now let's add a more advanced finder method. This finder will return invoices that do not contain a specified product. For example, show me all the invoices that do not contain a line item with the product "Popcorn." First, we'll add the method to the InvoiceLocalHome interface:

Collection findByDidNotOrder(java.lang.String item)
throws FinderException;

Always fully qualify the parameter types of the query parameter in code and in the deployment descriptor. This query cannot be expressed in EJB QL, but it can be in WebLogic QL:

SELECT OBJECT(i) FROM InvoiceBean AS i WHERE i.invoiceNumber NOT IN (SELECT
i2.invoiceNumber FROM InvoiceBean AS i2 WHERE i2.lineItems.name = ?1

Let's take a closer look at the WebLogic QL query. The first part is the same as the findAll query. The WHERE clause is where it gets interesting. The query utilizes a subselect, which is not available in EJB QL. The subselect utilizes another instance of the Invoice Bean. Notice that the relationship between the line items and the invoices does not have to be defined, as it has already been defined in the EJB-JAR.XML file utilizing CMR. Now the query is defined in terms of our objects and their relationships, not by the structure of the relational database.

The "name" is an attribute of the Line Item Bean, not a field in the database. The "?1" represents the first parameter being passed into the query, in this case the "item". If two parameters were passed, the second parameter would be referenced by "?2". Be careful when writing EJB QL, as a missing space after the "=" can cause errors during compilation. WebLogic QL cannot be placed directly into the EJB-JAR.XML deployment descriptor, but a placeholder must be created in the EJB-JAR.XML (see Listing 2). Notice that the <ejb-ql> tag is empty.

The real query needs to be placed in the weblogic-cmp-rdbms.xml deployment descriptor, as shown in Listing 3. This will override the query of the same name "findByDidNotOrder" inside the EJB-JAR.XML file.

Simple ejbSelect
ejbSelect methods execute an EJB QL or WebLogic QL query. They can return a single CMP field, an EntityBean or a Collection or Set of either one. BEA has extended the capability to include SQL ResultSet, a powerful feature that allows us to use an ejbSelect in the same way we would use a standard SQL call through JDBC.

There is a special type of ejbSelect statements, in entity. In entity ejbSelects use the bean instance to filter the return data. I prefer to use "non-in" entity ejbSelect methods when implementing home methods, and "in" entity ejbSelect methods when implementing functionality defined in the bean's interface. Although this is not technically required, conceptually it makes a great deal of sense.

ejbSelect Supporting a Home Method
Let's write a simple ejbSelect that returns a list of all the invoices and their totals. First we should define the method in the InvoiceBean class:

public abstract java.sql.ResultSet ejbSelectAllInvoices()
throws javax.ejb.FinderException;

This query will be executed against all of the invoices, as the method name does not end with "inEntity". Notice the return type of ResultSet. This method will return a ResultSet, just as if we had executed a direct JDBC query. Again we have the placeholder in the EJB-JAR.XML deployment descriptor. Now let's take a look at the weblogic-cmp-rdbms.xml deployment descriptor, which is shown in Listing 4. Notice the query returns two values, one a CMP column invoice number, and the other an aggregate of the line items' prices, or the total amount of the invoice. The query also uses the "GROUP BY" clause to perform aggregate functions.

Now let's expose ejbSelectAllInvoices through the InvoiceLocalHome interface. Remember, this is different from a finder because we are returning select data elements, not bean references. In the InvoiceLocalHome we will add the getInvoiceList method.

Collection getInvoiceList();

Next, in Listing 5, we'll add a method to the InvoiceBean to handle the getInvoiceList call.

In order to handle a custom home method we must add ejbHome to the beginning of the method name. Next, we call the ejbSelectAllInvoices method. It's not a good idea to return the ResultSet to the client.

ejbSelect with Weblogic QL
Let's use ejbSelect to total an invoice. First we'll declare the ejbSelect method inside the InvoiceBean class:

public abstract Double ejbSelectTotalInEntity();

The InEntity part of the method name tells the container to only consider data associated with this invoice instance. Otherwise this method would total all of the invoices in the database.

Next, declare the query in the weblogic-rdbms-jar.xml deployment descriptor (see Listing 6). Remember to always declare the placeholder query in the ejb-jar.xml file.

There are a few interesting things about this query. First, it returns an aggregate of a field. Second, the functionality could easily have been attained by iterating through the listItems Collection and summing up all of the prices. But that would cause the listItem beans to be loaded, and we would still have the overhead of iterating through the Collection. Although we're using local interfaces, there is still some cost involved. Instead, we had the database do the work and return only the answer. This way we reduce the load of the app server, and eliminate unneeded code.

Conclusion
This is just an introduction to EJB QL and WebLogic QL. There's a lot more out there, including WebLogic's ability to execute dynamic queries. I know a lot of developers won't use CMP, and I was one of them with EJB 1.1, but with EJB 2.0 it's definitely worth a second look. It eliminates an extraordinary amount of code and simplifies the entire development process. Once you define your entity beans, and their relationships with CMR, the rest is easy. It's definitely worth trying. The only way to tell if CMP will work for your application is by load testing the application to prove it can handle the performance requirements.