EJB QL is a powerful new tool that promises to improve performance, flexibility, and portability of entity beans in container-managed persistence, but it has some design flaws and omissions.
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.