I'm trying to use the LIKE statement with JPA. What I want to do is get all the items that have a name that matchs the keyword 'og' for example ('Dog', 'Big dog'...). If I write that :
query = em.createQuery("SELECT i FROM Item i WHERE i.name LIKE :keyword");
query.setParameter("keyword", keyword);
That will take the items that have the exact keword (in my example only 'og' will appear). I've tried to use the wildcards % but id doesn't work :
query = em.createQuery("SELECT i FROM Item i WHERE i.name LIKE '%:keyword%'");
query = em.createQuery("SELECT i FROM Item i WHERE i.name LIKE %:keyword%");
Does anybody knows how to use the LIKE statement with wildcards ? And by the way, is there a way to ignore the case of my keyword ('og' or 'OG' should give the same result).
Thanks,
Antonio Goncalves
二楼:
Hello Antonio,
Can you not use
query = em.createQuery("SELECT i FROM Item i WHERE UPPER(i.name) LIKE UPPER(:keyword)");
query.setParameter("keyword","%"keyword"%");
query.getResultList();
Best Regards,
Chris
三楼:
Hi Chris. It doesn't work with the UPPER keyword. Here is the error that I've got :
Caused by: Exception [TOPLINK-8025] (Oracle TopLink Essentials - 2006.4 (Build 060412)): oracle.toplink.essentials.exceptions.EJBQLException
Exception Description: Syntax error parsing the query [SELECT i FROM Item i WHERE UPPER(i.name) LIKE UPPER(:keyword) ORDER BY i.name], unexpected token [LIKE].
|#]
Any idea ?
Otherwise, the wildcard works fine :
query = em.createQuery("SELECT i FROM Item i WHERE i.name LIKE :keyword");
query.setParameter("keyword","%"keyword"%");
query.getResultList();
四楼:
Hello,
Sorry, looks like i was wrong on the UPPER(:keword) portion. The spec states:
string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]
"The pattern_value is a string literal or a string-valued input parameter"
So you will have to call toUpperCase() on the string you pass into the query ie:
query.setParameter("keyword","%"keyword.toUpperCase()"%");
Best Regards,
Chris
最终写法:
Thanks, it works fine like that
query = em.createQuery("SELECT i FROM Item i WHERE UPPER(i.name) LIKE :keyword ORDER BY i.name");
query.setParameter("keyword", "%" + keyword.toUpperCase() + "%");
JPA - LIKE Statement with wildcard Posted: 2006-9-15 上午6:04 ReplyHi,I'm trying to use the LIKE statement with JPA. What I want to do is get all the items that have a name that matchs the keyword 'og' for example ('Dog', 'Big dog'...). If I write that :q