|
Hello,
As most of you have already
noticed, EJB-QL lacks UPPER() and LOWER() functions. A very common operation in
SQL is to compare the string column of a row with a string that is passed into
the query from a client. Usually one can cast both strings to lower case with
LOWER() and java.lang.String.toLowerCase() to perform case-insensitive
comparisons. It's much faster to do it at the database level, however the
current limitations of EJB-QL are forcing me to do things like the
following:
Collection filteredEntities = new
ArrayList();
for (Iterator i = entityHome.findAllEntities();
i.hasNext(); )
{
EntityLocal currentEntity =
(EntityLocal) i.next();
if
(currentEntity.getFirstName().toLowerCase().equals(firstName.toLowerCase()))
filteredEntities.add(currentEntity);
}
In effect, I am performing an operation at the
Java-level what *should* otherwise be performed at the SQL level. I am sure
that doing it this way is slower than allowing it to perform the operation at
the database level. Are you all implementing similar functionality this way? Now
I know that application servers such as BEA WebLogic do a good job of caching
CMP 2.0 entity beans, and perhaps executing the above code multiple times won't
hit the database constantly as long as the underlying entity beans aren't
modified within a certain period of time. I am extremely surprised that the
functions LOWER() and UPPER() were not implemented in the EJB-QL enhancements of
the new EJB 2.1 specification. I have also been doing sorting operations at the
Java-level instead of the database level due to the lack of the ORDER BY clause
in EJB-QL. However, I will move this logic to the EJB-QL side as soon as the EJB
2.1 specification is implemented in my application server.
If it's not too late, I cry out to the Sun
engineers: PLEASE ADD UPPER() AND LOWER() TO EJB-QL in EJB 2.1.
Best regards,
Ryan LeCompte
|
- Re: Lack of case-insensitive comparisons in EJB-QL Ryan LeCompte
- Re: Lack of case-insensitive comparisons in EJB... Lars-Fredrik Smedberg
