EJB QL

EJB QL is the standard query language for defining the behavior of custom find and select methods. The EJB QL syntax is portable across databases, database schemas, and EJB containers because it is based on the abstract persistence schema defined for the entity beans and not the underlying data store. This allows you to specify the behavior of the query methods in an abstract, portable way. For each query method available to the entity bean, you must define a matching EJB QL statement that determines its runtime behavior.

WebLogic provides a number of extensions to the standard EJB QL. Some of these extensions also will be available in a future release of the EJB specification.

11.4.1 Using WebLogic Extensions to EJB QL

When using traditional EJB QL, you specify the EJB QL statement for each query method in the ejb-jar.xml descriptor file. However, if you intend to use any of the WebLogic extensions to EJB QL syntax, you also should define a weblogic-ql element in the weblogic-cmp-rdbms-jar.xml descriptor file. For instance, if you need to use the ORDERBY clause in your EJB QL statement, you must specify the EJB QL statement in the weblogic-cmp-rdbms-jar.xml file:

 EmployeeEJB
 ...
 
 
 findByLastName
 
 java.lang.String
 
 
 
 
 select object(e) from EmployeeEJB e where 
 e.lastName = ?1 orderby e.firstName
 ]]>
 
 10
 True
 True
 

In this case, the findByLastName( ) method returns a list of Employee EJBs with matching last names, and in alphabetical order by their first names. The ORDERBY clause actually defers all sorting to the underlying DBMS; this means the order in which the results are returned depends on the actual database. As a result, the EJB no longer will be completely portable the results of the find will be dependent on the DBMS to which it is deployed. Like the SQL ORDER BY clause, you can sort on multiple CMP fields, each in either ascending or descending order. Notice how we've configured additional properties for the query method:

• The max-elements element specifies the maximum number of EJB instances that will be returned by the find method. It resembles the setMaxRows( ) method on the JDBC Statement interface.
• The include-updates element lets you specify whether any updates made during the current transaction ought to be manifested in the results returned by the query. If you enable this option, the container flushes all changes for the cached transactions to the database before executing the find query. In WebLogic 8.1, this element is set to true by default, in order to maintain compliance with the J2EE 1.3 standard. In WebLogic 7.0, this option is set to false by default. Setting it to false yields the best performance. In this case, updates made by cached transactions are not reflected in the results of the query because the changes have not been written to the database yet.
• The sql-select-distinct[2] tag ensures that the database query generated from the EJB QL statement will include a DISTINCT qualifier automatically and thus return unique rows. If you enable this option, the find methods for the entity bean automatically will filter out any duplicate EJB instances. Once again, the EJB container defers the task of filtering out duplicated results to the underlying database.

  [2] This element is deprecated in WebLogic 8.1. Instead, you should use the DISTINCT qualifier in the query itself.

Similarly, if the query method relies on other WebLogic extensions to the EJB QL syntax (such as aggregate functions or subqueries), you also should define them in the weblogic-cmp-rdbms-jar.xml descriptor that is, until a future release of the EJB specification incorporates them into the standard.

11.4.2 Returning a ResultSet

In WebLogic, you can implement select methods that return the values of multiple CMP fields, in the form of a JDBC ResultSet. Just like SQL, WebLogic's QL extends the standard syntax by letting you specify a comma-separated list of CMP fields in the SELECT query. For instance, you could implement a select method that returns the first and last names of all employees in a department:

select e.firstName, e.lastName from DepartmentEJB d, in(d.employees) e 
where d.id=?1

In this case, the corresponding select method would return a collection in the form of a java.sql.ResultSet:

public abstract class DepartmentBean implements javax.ejb.EntityBean {
 ...
 public abstract java.sql.ResultSet ejbSelectEmployeeNames( ) 
 throws FinderException;
 ...
}

However, if a select method returns a ResultSet, the corresponding query may only return values of CMP fields or aggregates of CMP fields. It cannot return an EJB instance or a collection of EJB instances. For instance, the ejbSelectAll( ) method defined earlier for the Department EJB cannot return a ResultSet:

 select object(d) from DepartmentEJB d


//disallowed: select method must return either Department, or a collection-type
public abstract java.sql.ResultSet ejbSelectAll( ) throws FinderException;

The select method ejbSelectAvgSalaries( ), which returns the average salaries for all departments in company XYZ, could return a ResultSet:

select d.name, avg(e.salary) from DepartmentEJB d, in(d.employees) e 
group by d.name orderby 2 desc

The ORDERBY 2 clause implies that the results should be sorted on the second field in the SELECT list. In other words, the list is returned in descending order of average department salaries.

11.4.3 Executing Dynamic Queries

The EJB specification forces all find and select methods to be declared statically for an entity bean in its XML deployment descriptors. WebLogic lets you construct EJB QL statements dynamically in your application code, and then execute these queries at runtime without having to redeploy the EJB JAR. You can enable dynamic EJB QL queries for an entity bean using the enable-dynamic-queries element in the weblogic-ejb-jar.xml descriptor file.

 EmployeeEJB
 
 ...
 True
 
 EmployeeHome

This element ensures that the generated container class corresponding to the EJB's home interface also implements the weblogic.ejb.QueryLocalHome interface (or the QueryHome interface, if it is a remote entity bean).

Once you've compiled the EJB classes and redeployed the EJB JAR, you then can use the weblogic.ejb.Query interface to execute EJB QL statements. The following code fragment shows how you can dynamically create and then execute a custom find query:

 Context ctx = new InitialContext( );
 EmployeeHome home = (EmployeeHome) ctx.lookup("EmployeeHome");

 //get all Employee(s) whose lastname is "Mountjoy"
 QueryLocalHome qh = (QueryLocalHome) home;
 Query query = qh.createQuery( );
 query.setMaxElements(10);
 Collection results = query.find(
 "select object(e) from EmployeeEJB e where e.lastName='Mountjoy'");

 Employee e = null;
 for (Iterator i=results.iterator( ); i.hasNext( ); ) {
 e = (Employee) i.next( );
 //process results any way you like
 }

For a remote entity bean, you would look up the JNDI name for the remote bean, and use the weblogic.ejb.QueryHome interface instead. The Query interface allows you to assign properties that customize the behavior of the query:

• The setMaxElements( ) method sets the maximum number of results returned by the EJB query. By default, the EJB query will return all results that are matched.
• The setTransaction( ) method lets you specify the transaction setting for the EJB query. You can specify one of the following transaction settings for an EJB query: TX_REQUIRED, TX_REQUIRES_NEW, and TX_MANDATORY. These constants have the same meaning as the transaction attributes that can be applied to the methods of an EJB that supports container-managed transactions. For instance, the TX_REQUIRES_NEW constant implies that the EJB query will be executed in its own transaction context.
• The setIncludeUpdates( ) method lets you specify whether the EJB container should flush the changes of all cached transactions to the database before executing the query. By default, the changes of any cached transactions are not reflected in the results of the query.
• The setResultTypeRemote( ) method lets you specify whether the EJB query returns remote EJB instances.

The find( ) method suffers from the same limitations as any find EJB query. So, if you need to be able to create and execute a select method dynamically, you should use the execute( ) method. The execute( ) method returns a JDBC ResultSet, so you cannot execute an EJB query that returns just an EJB instance, or a collection of EJB instances. The following example shows how to dynamically execute an EJB query that returns the average salaries for all departments in company XYZ:

 Context ctx = new InitialContext( );
 EmployeeHome home = (EmployeeHome) ctx.lookup("EmployeeHome");

 //get average salaries for all departments
 QueryLocalHome qh = (QueryLocalHome) home;
 Query query = qh.createQuery( );
 query.setMaxElements(10);
 ResultSet results = query.execute("select d.name, avg(e.salary)
 from DepartmentEJB d, in(d.employees) e group by d.name orderby 2 desc");

 while (rs.next( )) {
 System.out.println("Name: " + rs.getString(1));
 System.out.println("Avg. Salary: " + rs.getDouble(2));
 }

As with select methods, you can access the abstract persistence schema for other entity beans in the EJB JAR, and also return multiple CMP fields in the SELECT clause.

You cannot dynamically execute a query that accepts input parameters. WebLogic doesn't provide a mechanism for supplying input values to parameters expected by a query.

Some purists may not like the idea of using EJB queries that return JDBC result sets because one of the goals of automatic EJB persistence is to minimize JDBC code in your applications. Nevertheless, it is important that you can build EJB queries where you can specify multiple CMP fields in your SELECT clause. In that case, the ResultSet is a convenient container for the collection of results returned by the EJB query.

11.4.4 WebLogic's EJB QL Extensions

Now we will examine various EJB queries that illustrate WebLogic's extensions to the EJB QL syntax. While discussing EJB QL, we'll continue to use examples from the Department-Employee scenario.

WebLogic lets you apply aggregate functions over a CMP field. Aggregate functions work like their SQL counterparts these functions are evaluated over the entire range of EJB instances that match the WHERE clause. The following query returns the maximum and minimum salaries for any employee in a specific department:

select max(e.salary), min(e.salary) from DepartmentEJB d, in(d.employees) e 
where d.id=?1

Aggregate functions may apply only to targets in the SELECT clause, and cannot appear in the WHERE clause. Other aggregate functions that can be used include SUM, COUNT, and AVG. The next query returns the average salaries for all departments in the company:

select d.name, avg(e.salary) from DepartmentEJB d, in(d.employees) e 
group by d.name orderby 2 desc

We've already seen how WebLogic's ORDERBY clause closely mimics the SQL ORDER BY clause. In this case, the results are returned in descending order of average salaries. The GROUP BY clause is another WebLogic enhancement. The previous example implies that the average should be computed over all employees with matching department names.

As with SQL, WebLogic also allows you to implement complex query logic by embedding subqueries in the WHERE clause. The following query shows how you can use subqueries to return all employees in a given department:

select object(e) from EmployeeEJB e where e.department in (select object(d) 
from DepartmentEJB d where d.id=?1)

Note that the subquery returns a collection of Department EJB instances (in this case, the collection will have only one element). Remember that a subquery may return EJB instances only if the EJBs returned don't use a compound primary key. Now, here's a query that returns all employees in a department who earn more than the average salary:

select object(e) from Department d, in(d.employees) e where d.id=?1 and
e.salary > (select avg(f.salary) from EmployeeEJB f)

In this case, the subquery returns the result of an aggregate function to the outer query. It also is called an uncorrelated query because the subquery can be evaluated independently of the outer query. WebLogic also supports correlated queries, in which the results from the outer query are involved in the evaluation of the subquery. Here is an example that returns the names of all departments that have at least one employee in them:

select d.name from DepartmentEJB d where exists 
(select e.id from EmployeeEJB e where e.department.id = d.id)

Subqueries allow you to build interesting and complex EJB queries however, they also incur an overhead of additional processing. We easily could have written the previous query using the IS EMPTY clause:

select d.name from DepartmentEJB d where d.employees is not empty

For a more detailed understanding of EJB QL syntax and related issues, please refer to Enterprise JavaBeans, Third edition, by Richard Monson-Haefel (O'Reilly).

11.4.5 Reporting EJB-QL Compilation Errors

In WebLogic 8.1, error messages during EJB QL compilation visually indicate which parts of the query are erroneous. When an error is reported, the EJB QL compiler brackets the location of the problem with these symbols: =>> <<=. The following output shows how the EJB QL compiler indicates the nature of the problem:

ERROR: Error from appc: Error while reading 'META-INF/weblogic-cmp-rdbms-jar.xml'. 
The error was: 
Query: 
EJB Name: DepartmentEJB 
Method Name: findAvgDeptSalaries 
Parameter Types: ( ) 
Input EJB Query: SELECT f.name, avg(e.salary) FROM DepartmentEJB d, in(d.employees) e 
GROUP BY d.name ORDER BY 2 DESC 
SELECT =>> f.name <<=, avg(e.salary) FROM 
DepartmentEJB d, in(d.employees) e GROUP BY d.name ORDER BY 2 DESC
Invalid Identifier in EJB QL expression: 
Problem, the path expression/Identifier 'f.name' starts with an identifier: 'f'. The 
identifier 'f', which can be either a range variable identifier or a collection 
member identifier, is required to be declared in the FROM clause of its query or in
the FROM clause of a parent query. 
'f' is not defined in the FROM clause of either its query or in any parent query. 
Action, rewrite the query paying attention to the usage of 'f.name'.

In addition, the EJB QL compiler will report multiple errors during compilation, if they exist. In previous WebLogic releases, you would have to fix the first error in the query, and then recompile the EJB QL query to discover any subsequent problems.