case-insensitive search in EJB QL

Question

Hello,

This looks really simple and I can't believe I haven't found a solution myself. I have a bean named PersonBean, which has a name. Now I want to write a finder method that takes a string and looks for people with that string in their name, case-insensitively. Here is my current EJB QL:

SELECT OBJECT(p)
FROM Person p
WHERE (p.name LIKE ?1)

I have 2 problems with this:

1. I want the search to be case-insensitive (the above is case-sensitive) and I can't find something like lower() or upper() in EJB QL. What can I do to have that behavior? UPDATE: I'm using J2EE version 1.4 and glassfish version 2.1, if that matters.
2. I have to pass an ugly string to the method, i.e findByString("%john%"). Is there a way to write the EJB QL so that I can pass something like findByString("john")?

Answer 1

EJB-QL as used in EJB 2.x entity beans (not to be confused with JPQL as used in JPA and EJB3 entity beans) is extremely limited. Altough there were some additions to the language in EJB 2.1 there are to my beliefs no upper/lower functions.

I don't know what container your running in but JBoss has made some extensions to EJB-QL (called JBossQL) and features a UCASE function.

Answer 2

Regarding your second question, there is a CONCAT function in EJB QL 2.1, so I think the following should work:

WHERE (p.name LIKE CONCAT('%', CONCAT(?1, '%')))

Edit: The above does not work because the ql grammer only allows literal strings and input parameters in a LIKE expression, which is really limiting. It should be possible to achive the same effect by using the LOCATE function like this:

WHERE LOCATE(p.name, ?1) <> 0