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