6 Replies Latest reply on May 23, 2004 5:45 PM by ram_iyer

    LIKE keyword in ejb ql with an input parameter CANT MAKE IT

    brian1 Newbie

      I am trying to use the LIKE keyword in a finder with a wildcard, using an input parameter, I want the ejb ql to look like this:
      SELECT OBJECT(i) FROM Person i WHERE i.lastName LIKE ?1%

      I have used LIKE '?1%', (obviously doesn't work, looks for last names that start with ?1. so I tried LIKE CONCAT(?1,'%'). The descriptor won't parse, compains about expecting a string literal after the LIKE keyword. I have tried several other configurations as well, and nothing I do works. Either the descriptor won't parse, or the ejb ql doesn't do what I want. I am fairly sure this is not a new issue, but i cannot find the solution. I have read the spec, and it says I should be able to do LIKE CONCAT(?1,'%'). I also rigorously inspected a deployment descriptor from a different app that is running in a different app server, and it is written JUST LIKE THAT. Imagine my frustration. I can only assume I am making a noob error, but if you could, would you please let me know what it is. Your help is appreciated.

        • 1. Re: LIKE keyword in ejb ql with an input parameter CANT MAKE
          sesques Novice

          Hi,

          I use myself

           * @ejb:finder
           * signature = "Collection findByLibelle(java.lang.String libelle)"
           * query = "SELECT OBJECT(o) from PhML_Produit as o WHERE o.prdLibelle LIKE '%?1%'"
          


          and it works fine.


          • 2. Re: LIKE keyword in ejb ql with an input parameter CANT MAKE
            brian1 Newbie

             

            @ejb.finder
             * description = "Find a PersonBean whose last name starts with the supplied argument"
             * query = "SELECT OBJECT(i) FROM Person as i WHERE i.active = 1 AND i.lastName LIKE '?1%'"
             * signature = "java.util.Collection findByLastName(java.lang.String lastNameStart)"

            is exactl what I just built with, and it doesn't find anything. well, it will find people whose last names start with "?1"... what am I missing?

            • 3. Re: LIKE keyword in ejb ql with an input parameter CANT MAKE
              sesques Novice

              Sorry for the last response. In fact my code does not work, it return nothing like yours.
              Regarding the EJB spec, nothing in the spec says that the LIKE keyword in EJB-QL statement must work with input parameters. The only example is with literal strings. So the LIKE statement in EJB-QL is useless, in conclusion.

              You must use a @jboss.query for that, but passing the % in the parameter.


              @ejb.finder
               * query = "SELECT OBJECT(i) FROM Person as i WHERE i.active = 1 AND i.lastName LIKE '?1%'"
               * signature = "java.util.Collection findByLastName(java.lang.String lastNameStart)"
              @jboss.query
               * query = "SELECT OBJECT(i) FROM Person as i WHERE i.active = 1 AND i.lastName LIKE ?1"
               * signature = "java.util.Collection findByLastName(java.lang.String lastNameStart)"
              


              Then you can call it like this:
              Person.findByLastName(yourpattern+"%")
              




              • 4. Re: LIKE keyword in ejb ql with an input parameter CANT MAKE
                brian1 Newbie

                Horay Horay Horay !!! that works beautifully. I no wish I should have looked closer at the jboss specific way to override finders.... I am new to JBoss and EJB in general. I suppose the spec does not specifically give an example of using input params in finders with LIKE... I am off to ask some questions on sun's boards now, as it seems a bit odd. the BNF for EJB QL says you can use a string expression with like, and CONCAT should be a String Expression... at least that's how I read it. Anyway, thank you. If there is anything I can do, let me know, as the ability to search in this way is very important in my app. Thanks again.

                • 5. Re: LIKE keyword in ejb ql with an input parameter CANT MAKE
                  jimboinlondon Newbie

                  Blimey. I was trying to do this all this aft. and you chaps have solved my
                  problem. Thanks.

                  James

                  • 6. Re: LIKE keyword in ejb ql with an input parameter CANT MAKE
                    ram_iyer Newbie

                    thanks to sesques! it works!!
                    i have had a couple of frustrating days trying to solve the 'like ?1%' problem.