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(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 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.

Related

Категории