| |
First floor: |
|
|
| Hi, 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' shove give the same result ). Thanks, Antonio Goncalves |
|
| |
| |
Second floor: |
|
|
| 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 |
|
|
| |
| |
Third floor: |
|
|
| 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 essenessen- 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 (); |
|
|
| |
| |
Fourth floor: |
|
|
| 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 |
|
|
| |
| |
Final Statement: |
|
|
| 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 () + "% "); Antonio |
|
|
My incorrect syntax: SQL. append ("and S. idcard like %? % "); Paraml. Add (stuff. getidcard (). Trim ());
Correct:
SQL. append ("and S. idcard like? ");
Paraml. Add ("%" + stuff. getidcard (). Trim () + "% ");
From: http://forums.oracle.com/forums/thread.jspa? Threadid = 423742
