Hi Eve, hi Wouter,
I would like to second what Marina said about the performance of the
query. Another issue is that the Java Persistence query language does
not support functions as LIKE arguments, so LIKE followed by CONCAT is
not supported. Only a string literal or a string valued input
parameter.is standardized.
What Marina suggests is a query like:
SELECT i FROM Iteamdetailsent i WHERE i.description= :param OR
i.itmid=:param OR i.itmname=:param
It takes a single input parameter used in three places in the query.
Regards Michael
> Eve, Wouter,
>
> This query will have a horrible performance in the database, and won't
> work if any of the columns are not character type.
>
> Any query with OR operation between 3 values will require all 3
> parameters to be set no matter what.
>
> Eve, are you searching always the same text? If this is the case, use
> your original query, and set the same value for all 3 parameters.
>
> HTH,
> -marina
>
> Wouter van Reeven wrote:
>> On Tue, May 08, 2007 at 12:23:34PM +0100, Eve Pokua wrote:
>>
>>> 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.
>>
>>
>> Ehrm 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.
>>
>>
>> According to the EJB3 specification, then LIKE operator is
>> implemented. See
>> pages 92 and 93 of the spec.
>>
>>
>> Wouter
>>