Hi!
UPPER(...) and LOWER(...) functions are not always the best case. If you have much data you want to be able to hit an index doing searches. I know that some DBMS can handle functional indexes but that is far from all. I usually think of the column on which I need to do say e.g. case insensitive search on, then I add a column NAMEUPPER for NAME and populate it using a trigger. This will satisfy what you are after and will be faster (even if you can do a functional index).
Thanx
Lars-Fredrik Smedberg
-----Original Message-----
From: Ryan LeCompte
To: [EMAIL PROTECTED]
Sent: 2002-10-28 06:37
Subject: Lack of case-insensitive comparisons in EJB-QL
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
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
http://www.louisiana.edu/~rml7669 <http://www.louisiana.edu/~rml7669>
