I saw some suggestions as below:
One possible solution is replace LIKE operator with LOCATE() function.
LOCATE function return the current position of substring into field.
Example
.... WHERE LOCATE(?1, e.Description )>0
in
http://forum.java.sun.com/thread.jspa?threadID=199459&messageID=1890804
and the following in the Jee 5 tutorial the dukesbank:
, @NamedQuery(name = "Customer.FindByLastName", query = "SELECT a FROM
Customer a WHERE a.lastName LIKE :lastName")
in the Customer entity.
So the above suggests that the LIKE is implemented in EJB3. That means, I
might have to change the query for the server to accept the LIKE operator
correctly:
@NamedQuery(name = "GetItemsbydescption2",query = "SELECT i FROM
Iteamdetailsent i WHERE i.description LIKE
CONCAT('%',CONCAT(:description,'%') OR i.itmid LIKE
CONCAT('%',CONCAT(:itmid,'%') OR i.itmname LIKE
CONCAT('%',CONCAT(:itmname,'%')))"),
but how?
eve
>From: "Eve Pokua" <gorgeous65_at_msn.com>
>Reply-To: ejb_at_glassfish.dev.java.net
>To: persistence_at_glassfish.dev.java.net, ejb_at_glassfish.dev.java.net
>Subject: Re: @NamedQuery for finding details from more than 1 column
>Date: Tue, 08 May 2007 12:23:34 +0100
>
>Hello everyone,
>
>Wouter,
>
>I have tested your suggestions as below:
>
>Query from entity class:
>
>@NamedQuery(name = "GetItemsbydescption2",query = "SELECT i FROM
>Iteamdetailsent i WHERE i.description LIKE
>CONCAT('%',CONCAT(:description,'%') OR i.itmid LIKE
>CONCAT('%',CONCAT(:itmid,'%') OR i.itmname LIKE
>CONCAT('%',CONCAT(:itmname,'%')))"),
>
>
>and method for client to access in implementation stateful class:
>
>public List<Iteamdetailhlp> Despcodename2(String description){
> List<Iteamdetailsent> iteamdetailsent=null;
> //Iteamdetailsent iteamdetailsents=null;
> if (description!=null){
> try{
>
> iteamdetailsent=(List<Iteamdetailsent>)
>em.createNamedQuery("GetItemsbydescption2").getResultList();
> //iteamdetailsent=(List<Iteamdetailsent>)
>em.createNamedQuery("Getitemswithname").setParameter("itmname",
>itmname).getResultList();
> }catch(Exception ex){
> System.err.println("Can not get items details by Description entered.
>Name entered not recogniced");}}
>
>
> return copyToDetails(iteamdetailsent);
> }//end of Despcodename2
>
>
>I get the following error:
>
>Syntax error parsing the query [SELECT i FROM Iteamdetailsent i WHERE
>i.description LIKE CONCAT('%',CONCAT(:description,'%') OR i.itmid LIKE
>CONCAT('%',CONCAT(:itmid,'%') OR i.itmname LIKE
>CONCAT('%',CONCAT(:itmname,'%')))], unexpected token [LIKE].
>
>
>
>So far, i have found that the LIKE operator is not implemented in EJB2.
>
>
>But since I am using EJB3, maybe there might be an answer somewhere. I will
>keep on looking to see if this can be resolved.
>
>Other wise, I will try the other suggestions.
>
>Thanks
>
>eve
>
>
>
>
>
>
>
>
>>From: "Eve Pokua" <gorgeous65_at_msn.com>
>>Reply-To: persistence_at_glassfish.dev.java.net
>>To: persistence_at_glassfish.dev.java.net, ejb_at_glassfish.dev.java.net
>>Subject: Re: @NamedQuery for finding details from more than 1 column
>>Date: Tue, 08 May 2007 11:41:18 +0100
>>
>>Wouter,
>>
>>Your suggestions sounds good and the only way to find out if it will work,
>>is to put it into
>>practise. So I will have a go and update everybody on this.
>>
>>Also, at the moment, my method for this query is stated below:
>>
>>//retrieving items by description/ item code or item name
>> public List<Iteamdetailhlp> Despcodename(String description, String
>>itmname, String itmid){
>> List<Iteamdetailsent> iteamdetailsent=null;
>>
>> if (description!=null){
>> try{
>>
>> iteamdetailsent=(List<Iteamdetailsent>)
>>em.createNamedQuery("GetItemsbydescption").setParameter("description",
>>description).getResultList();
>>
>> }catch(Exception ex){
>> System.err.println("Can not get items details by Description entered.
>>Name entered not recogniced");
>>
>> }//end of catch
>> }
>>
>> else if (itmname!=null){
>> try{
>> iteamdetailsent=(List<Iteamdetailsent>)
>>em.createNamedQuery("Getitemswithname").setParameter("itmname",
>>itmname).getResultList();
>>
>> }catch(Exception ex){
>> System.err.println("Can not get items details by Description entered.
>>Name entered not recogniced");
>>
>> }//end of catch
>> }
>> else if(itmid!=null){
>> try{
>>
>>iteamdetailsent=(List<Iteamdetailsent>)em.find(Iteamdetailsent.class,
>>itmid);}
>> }catch(Exception ex){
>> System.err.println("Can not get items details by Description entered.
>>Name entered not recogniced");
>>
>> }//end of catch
>> }
>> return copyToDetails(iteamdetailsent);
>> }//end of Despcodename
>>
>>
>>To use your suggestion, I have to change the above method as below:
>>
>>//retrieving items by description/ item code or item name
>> public List<Iteamdetailhlp> Despcodename(String CONCAT){
>> List<Iteamdetailsent> iteamdetailsent=null;
>>
>> if (description!=null){
>> try{
>>
>> iteamdetailsent=(List<Iteamdetailsent>)
>>em.createNamedQuery("GetItemsbydescption").getResultList();
>>
>> }catch(Exception ex){
>> System.err.println("Can not get items details by Description entered.
>>Description entered not recogniced");
>>
>> }//end of catch
>> }
>>
>>
>> return copyToDetails(iteamdetailsent);
>> }//end of Despcodename
>>
>>
>>I will try and test this. I will soon update everyone on this.
>>
>>Thanks
>>
>>eve
>>
>>
>>
>>
>>
>>>From: Wouter van Reeven <wouter_at_van.reeven.nl>
>>>Reply-To: persistence_at_glassfish.dev.java.net
>>>To: persistence_at_glassfish.dev.java.net
>>>Subject: Re: @NamedQuery for finding details from more than 1 column
>>>Date: Tue, 8 May 2007 11:36:00 +0200
>>>
>>>A thought occured to me. It may be possible to rewrite the query to
>>>always work
>>>after all. Your query now looks like this:
>>>
>>>@NamedQuery(name = "GetItemsbydescption",query = "SELECT i FROM
>>>Iteamdetailsent i WHERE i.description= :description OR i.itmid=:itmid OR
>>>i.itmname=:itmname")
>>>
>>>The problem is that you do not always set all three parameters. If you
>>>only
>>>sppecify, e.g., the decription, the query translates to something like
>>>this:
>>>
>>>SELECT i FROM Iteamdetailsent i WHERE i.description="a description" OR
>>>i.itmid= OR i.itmname="
>>>
>>>which is invalid. I am not sure if this is possible, but maybe a query
>>>like this
>>>will do the trick:
>>>
>>>@NamedQuery(name = "GetItemsbydescption",query = "SELECT i FROM
>>>Iteamdetailsent i WHERE i.description LIKE
>>>CONCAT('%',CONCAT(:description,'%')
>>>OR i.itmid LIKE CONCAT('%",CONCAT(:itmid,'%') OR i.itmname LIKE
>>>CONCAT('%',CONCAT(:itmname,'%'"))
>>>
>>>I am sure this works in "normal" SQL but I am not sure if this works in
>>>EQL. the
>>>part I am not sure about is if the named parameters are picked up
>>>correctly.
>>>Basically this makes sure that if no parameters are entered, a "default"
>>>value
>>>of '%%' is used. The statement i.description LIKE '%%' always returns
>>>true if
>>>the description column contains any data.
>>>
>>>I think this construction is rather ugly though.
>>>
>>>On Tue, May 08, 2007 at 09:58:39AM +0100, Eve Pokua wrote:
>>> > In the client side, I intend to only supply one textfield and a button
>>> > which when entry is supplied by the client, will search the database.
>>>If
>>> > it does not find item details by itmid, it will search the next
>>>column:
>>> > description, if it does not, then the next column: itmname.
>>>
>>>So why not use a query like this:
>>>
>>>@NamedQuery(name = "GetItemsbydescption",query = "SELECT i FROM
>>>Iteamdetailsent i WHERE i.description= :item OR i.itmid=:item OR
>>>i.itmname=:item")
>>>
>>>This will return a result if any of the three columns matches. Please
>>>note that
>>>only EXACT matches will be returned. If you need to have a substring
>>>match
>>>please use LIKE instead of = (equals).
>>>
>>> > I think, if this don't work, I will separate all of them and use the
>>>drop
>>> > down menu.
>>> > So if user enters 'description', it calls the description method, and
>>>if
>>> > they enters itemid/code, it calls
>>> > the itemid method, and if by itemname, it calls the itemname method.
>>>
>>>
>>>HTH, Wouter
>>>
>>>--
>>>
>>>People: "If she weighs the same as a Duck, she's made of wood!"
>>>Sir Bedevere: "And therefore...?"
>>
>>_________________________________________________________________
>>Could you be the guest MSN Movies presenter? Click Here to Audition
>>http://www.lightscameraaudition.co.uk
>
>_________________________________________________________________
>Could you be the guest MSN Movies presenter? Click Here to Audition
>http://www.lightscameraaudition.co.uk
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ejb-unsubscribe_at_glassfish.dev.java.net
>For additional commands, e-mail: ejb-help_at_glassfish.dev.java.net
>
_________________________________________________________________
Could you be the guest MSN Movies presenter? Click Here to Audition
http://www.lightscameraaudition.co.uk