Hi Wouter,
I think your query works. The actual value of the input parameter might
include the wild card, e.g. q.setParameter("param", "hello%").
Regards Michael
> Hi everyone,
>
>
> Yes, the query may be slow. It not always is depending on the table structure
> and the setup of the databse schema. But it's safe to assume that this query is
> slow :-(
>
> I suggested a similar query like Marina did in a previous email but that was
> ignored by everyone :-) Please note that the query will only return exact
> matches. I wonder how to use params in combination with a LIKE statement that
> returns inexact matches. Something like this
>
> SELECT i FROM Iteamdetailsent i WHERE i.description LIKE :param OR
> i.itmid LIKE :param OR i.itmname LIKE :param
>
> I guess this query will only return exact matches too since no wild characters
> are used. Any idea?
>
>
> Thanks, Wouter
>
> On Tue, May 08, 2007 at 09:27:50PM +0200, Michael Bouschen wrote:
>
>> 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
>>>>
>>>>