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
> >>
--
People: "If she weighs the same as a Duck, she's made of wood!"
Sir Bedevere: "And therefore...?"