Reputation: 8778
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:
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.findByString("%john%")
. Is there a way to write the EJB QL so that I can pass something like findByString("john")
?Upvotes: 2
Views: 2051
Reputation: 34014
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
Upvotes: 1
Reputation: 2174
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.
Upvotes: 2