The use of the OBJECT( ) operator is cumbersome and provides little or no value to the bean developer. It’s trivial for EJB vendors to determine when an abstract schema type is the return value, so the OBJECT( ) operator provides little real value during query translation. In addition, the OBJECT( ) operator is applied haphazardly. It’s required when the return type is an abstract schema identifier, but not when a path expression of the SELECT clause ends in a CMR field. Both return an EJB object reference, so the use of OBJECT( ) in one scenario and not the other is illogical and confusing.

When questioned about this, Sun replied that several vendors had requested the use of the OBJECT( ) operator because it will be included in the next major release of the SQL programming language. EJB QL was designed to be similar to SQL because SQL is the query language that is most familiar to developers, but this doesn’t mean it should include functions and operations that have no real meaning in Enterprise JavaBeans.

EJB QL doesn’t provide native support for the java.util.Date class. The java.util.Date class should be supported as a natural type in EJB QL. It should be possible, for example, to do comparisons with Date CMP fields and literal and input parameters using comparison operators (=, >, >=, <, <=, <>). It should also be possible to introduce common date functions so that comparisons can be done at different levels, such as comparing the day of the week (DOW( )) or month (MONTH( )), etc. In addition, date literals should be supported. For example, a literal like “2004-04-02” for April 2nd, 2004 should be acceptable as a literal. Of course, supporting Date types and literals in EJB QL is not trivial and problems with interpretation of dates and locales would need to be considered, but the failure to address Date as a supported type is significant.

While the aggregate functions and functional expressions provided by EJB QL are valuable to developers, many other functions should also be added. For example, CAST( ) (useful for comparing different types) and date functions, such as DOW( ), MONTH( ), etc., could be added. The UPPER( ) and LOWER( ) functional expressions should also be added—they make it possible to do caseless comparisons in the LIKE clause.

In EJB 2.0 and 2.1, EJB QL statements can only declare a single SELECT expression. In other words, it’s not possible to SELECT multiple items. The following query is illegal:

SELECT addr.city, addr.state
FROM Address AS addr

Today, you can only select either the city or the state, but not both.

In SQL, the GROUP BY and HAVING clauses are commonly used to apply stricter organization to a query and narrowing the results for aggregate functions. The GROUP BY clause is usually used in combination with aggregate functions, because it allows you to cluster data by category. For example, the following query provides a count for all the cities in each state:

SELECT addr.state, COUNT(addr.city)
FROM Address AS addr
GROUP BY addr.state

The HAVING clause is used with a GROUP BY clause and acts as a filter, restricting the final output. The HAVING clause employs aggregate functional expressions using only the identifiers used in the SELECT clause. For example, the following query uses the GROUP BY and HAVING clauses to select and count only the states with more than 200 cities:

SELECT addr.state, COUNT(addr.city)
FROM Address AS addr
GROUP BY addr.state
HAVING COUNT(addr.city) > 200

Subqueries can be useful; they are common to SQL and some other query languages. A subquery is a SELECT statement inside of another SELECT statement, usually in the WHERE, SELECT, or HAVING clause. For example, the following subquery finds the average amount paid for a reservation, a value that is subsequently used to find all reservations where the amount paid is greater than the average.

SELECT  OBJECT(res)
FROM Reservations AS res
WHERE res.amountPaid >= 
     ( SELECT AVG(r.amountPaid) FROM Cruise AS c, IN( c.reservations ) AS r   
       WHERE c = ?1   )

Dynamic queries are supported by most vendors, but not the specification. In EJB 2.0 and 2.1, all EJB QL statements are statically compiled at deployment time. In other words, you can’t make up a query on the fly and submit it to the EJB container system. This restriction makes it difficult to create reports and do analysis because you always have to know the queries before the beans are deployed. Most vendors already support dynamic queries—it’s a mystery why EJB QL doesn’t.