7 Replies Latest reply on May 15, 2004 8:43 AM by ltcmelo

    How to implement LIKE with input parameters in EJB QL 2.0 (o

    ltcmelo

      Hi,
      is there a way to implement LIKE with input parameters in JBoss EJB QL?
      Something like...

      SELECT OBJECT(a) FROM MySchema a WHERE a.name = '% ? %'

      Thanks,
      ltcmelo

        • 1. Re: How to implement LIKE with input parameters in EJB QL 2.

          You can use JBossQL to declare a finder and use LIKE with input parameters:

          SELECT OBJECT(a) FROM MySchema a WHERE a.name LIKE ?1

          See the tag in jbosscmp-jdbc.dtd

          • 2. Re: How to implement LIKE with input parameters in EJB QL 2.
            ltcmelo

            I still haven't gotten it working.

            Here's what i have in jbosscmp-jdbc.xml

             <query>
             <query-method>
             <method-name>findByName</method-name>
             <method-params>
             <method-param>java.lang.String</method-param>
             </method-params>
             </query-method>
             <jboss-ql><![CDATA[SELECT OBJECT(b) FROM mySchemaPartyHouse AS b WHERE b.name LIKE ?1]]></jboss-ql>
            
             </query>
            


            for the exact same bean that i have this description in ejb-jar.xml
             <query>
             <query-method>
             <method-name>findByName</method-name>
             <method-params>
             <method-param>java.lang.String</method-param>
             </method-params>
             </query-method>
             <ejb-ql><![CDATA[SELECT OBJECT(b) FROM mySchemaPartyHouse AS b WHERE b.name = ?1]]></ejb-ql>
             </query>
            


            As I undertand, the query in JBossQL was supposed to be overriding the one in EJBQL, but this is not happening (or maybe, the jbossql query is not working as it should).

            Does anyone have an idea?

            Thanks,
            ltcmelo

            • 3. Re: How to implement LIKE with input parameters in EJB QL 2.
              ltcmelo

              Just complementing...
              I also tried with '%?1%' after LIKE and did not work.

              • 4. Re: How to implement LIKE with input parameters in EJB QL 2.

                 

                "ltcmelo" wrote:
                Just complementing...
                I also tried with '%?1%' after LIKE and did not work.


                How does it "not work" ?!


                • 5. Re: How to implement LIKE with input parameters in EJB QL 2.
                  ltcmelo

                   


                  How does it "not work" ?!


                  Because jboss undertands that as the constant string '?1' and not the input parameter 1.
                  For example, i created a field in mydb named 'abc?1def' and the finder returned this name, because, as i said, is looking for the string ?1.

                  So, i don't really know what's going on. I tried to use CONCAT(string, string), but will only work for the end or the beggining of the word (the way i could with '%something%').

                  So, what else could be the problem?

                  Thanks,
                  ltcmelo

                  • 6. Re: How to implement LIKE with input parameters in EJB QL 2.

                    This works for me:

                     home.create("C-124");
                     home.create("C-444");
                     home.create("C-123123123");
                     home.create("C");
                     home.create("CC");
                    
                     Collection c = home.findByName("C%");
                    
                     assertTrue(c.size() == 5);
                    


                    ejb-jar:
                    <query>
                     <query-method>
                     <method-name>findByName</method-name>
                     <method-params>
                     <method-param>java.lang.String</method-param>
                     </method-params>
                     </query-method>
                     <ejb-ql/>
                    </query>
                    


                    jbosscmp-jdbc.xml
                    <query>
                     <query-method>
                     <method-name>findByName</method-name>
                     <method-params>
                     <method-param>java.lang.String</method-param>
                     </method-params>
                     </query-method>
                     <jboss-ql>
                     SELECT OBJECT(a)
                     FROM MySchema AS a
                     WHERE a.name LIKE ?1
                     </jboss-ql>
                    </query>
                    



                    • 7. Re: How to implement LIKE with input parameters in EJB QL 2.
                      ltcmelo

                      Oh!
                      Thank you juha, without your code i'd never seen my mistake!
                      I'm even a little embarassed.
                      Actually, i was calling the method wrongly, i'm supposed to insert the % in the java code for the method, rigth???
                      That's what i got now.

                      public ArrayList getAllPartyHousesByName(String name){
                       // ...
                       name = "%" + name + "%";
                       try {
                       partyHousesColl = partyHouseLocalHome.findByName(name);
                       // ...
                      }
                      


                      Thank you again,
                      ltcmelo