6 Replies Latest reply on Sep 21, 2003 7:58 AM by peejay

    EJB-QL LIKE

    Philipp Röthl Newbie

      Hi,

      I've just tried the following finder methode:
      * @ejb.finder
      * signature="java.util.Collection findByName(java.lang.String name)"
      * query="SELECT DISTINCT OBJECT(o) FROM Country o WHERE o.name LIKE '?1'"
      * description="name is not indexed."

      It should return all countries that match a given name (using the LIKE operator) -> It does not work :-(
      I found an article in this form that says that EJB QL does not allow paramters in a LIKE statement.

      Does anybody know a workaround for this problem. There has to be something as this problem occurs often...

      Thanks,
      phi

        • 1. Re: EJB-QL LIKE
          Juha Lindfors Master

          JBossQL supports parameters in LIKE clauses.

          • 2. Re: EJB-QL LIKE
            Dietmar Scheidl Newbie

            Hi,

            this is no problem. The EJB 2.0 Spec does not allow 'LIKE ?1'. You can use JBossQL to create your desired finder. Have a look at:

            http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=

            • 3. Re: EJB-QL LIKE
              mverbist Newbie

              I found a way to do this for the beginning of a word.
              But you have to pass an additional parameter.

              The EJBQL is
              SELECT OBJECT(l) FROM Label l WHERE SUBSTRING(l.name, 1, ?2) = ?1

              where the first parameter is the like-string
              and the second parameter is the length of the like-string

              A warning however, for the moment I'm having trouble with this on Oracle. All worked fine on MySQL though.

              An other way would perhaps be to implement yur own finder with native SQL, but I'm not sure what the EJB2.0 CMP specs say about this. I think it is not allowed.
              Anyone on this?

              • 4. Re: EJB-QL LIKE
                palmrich Newbie


                i'm solving this problem
                adding xDoclet tag @jboss.query

                -----------------------------------
                * @ejb.finder
                * signature="java.util.Collection findByName(java.lang.String name)"
                * query="SELECT DISTINCT OBJECT(o) FROM Country o WHERE o.name LIKE '?1'"
                -------------------------------------

                ----->

                Change upper @ejb.finder to following
                ( moving LINK syntax to @jboss.query from @ejb.finder ) :

                --------------------- start --------------------------->
                /**
                * @ejb.finder
                * signature="java.util.Collection findByName java.lang.String name)"
                * query="SELECT DISTINCT OBJECT(o) FROM Country o WHERE o.name "
                *
                *
                * @jboss.query
                * signature="java.util.Collection findByName java.lang.String name)"
                * query="SELECT DISTINCT OBJECT(o) FROM Country o WHERE o.name LIKE ?1 "
                *
                */
                <--------------------- end ----------------------------------

                @jboss.query support other sql keywords like this:

                * @jboss.query
                * signature="java.util.Collection findByName java.lang.String name)"
                * query="SELECT DISTINCT OBJECT(o) FROM Country o WHERE o.name LIKE ?1 ORDER BY o.name DESC "
                *

                Have a good time...

                • 5. Re: EJB-QL LIKE
                  Per Steffensen Newbie

                  Hi

                  Im having the same problem, that I cannot use parameters in LIKE constraints.

                  Is see that the solution is using JBossQL.

                  Im not using xDoclet.

                  How do I use JBossQL, when Im not using xDoclet?

                  Thanx

                  • 6. Re: EJB-QL LIKE
                    peejay Newbie

                    You can use LOCATE(?1, o.name) > 0 instead of the like operator