Re: Case-insensitive LIKE comparison in JPQL

Michael Bouschen Wed, 19 Dec 2007 01:16:04 -0800

Hi Cedric,

according to the JPQL spec the pattern value following the keyword LIKEmust be a string literal or a string-valued input parameter. So I assumethe expression lower(:username) is the problem the OpenJPA querycompiler is complaining about. As a workaround you could try "lowering"the value for the username input parameter yourself before passing it tothe setParameter call. Then the JPQL would look like:

 SELECT u FROM User u WHERE lower(u.username) LIKE :username


Regards Michael

I'm trying to do a relatively simple comparison of an named parameter to a
column in a database using LIKE.  The following JPQL code works, but the
LIKE comparison is case-sensitive:

SELECT u FROM User u WHERE u.username like :username

What I'm looking for is something along the lines of Hibernate's ILIKE, so
that if I have a named parameter "Joe", it will match a column in the
database with a value of "joe", "JOE", or "Joe".

If this were like plain-old SQL, I would imagine I could simply wrap both
u.username and :username in the LOWER() function:

SELECT u FROM User u WHERE lower(u.username) like lower(:username)

But OpenJPA doesn't seem to like that:

<openjpa-1.0.0-r5715:6560 nonfatal user error>
org.apache.openjpa.persistence.ArgumentException: Encountered "lower ( u .
username ) like lower" at character 28, but expected: ["(", ")", "+", "-",
".", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY",
"AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT",
"CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC",
"DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING",
"IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE",
"LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT",
"OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT",
"SUBSTRING", "SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE",
<BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>,
<STRING_LITERAL>].
        at
org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9285)
        at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9162)
        at 
org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1858)
        at 
org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1836)
        at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1702)
        at
org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1664)
        at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1511)
        at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:90)
        at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:62)
        at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:1659)
        ... 31 more

Any suggestions?



--
[EMAIL PROTECTED] Engineering GmbH  Tel.: +49/(0)30/235 520-33
Buelowstr. 66                Fax.: +49/(0)30/217 520-12

10783 Berlin mailto:[EMAIL PROTECTED]

Geschaeftsfuehrung: Anna-Kristin Proefrock
Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52

Re: Case-insensitive LIKE comparison in JPQL

Reply via email to