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