1 Reply Latest reply on Jul 17, 2006 6:03 PM by Frank Langelage

    Using LIMIT in EJBQL

    Glenn Puckett Newbie

      I have JBoss v4.0.3SP1 installed along with MySQL v5.0.22.

      I am using Eclipse 3.1.2 with MyEclipse 4.1.1 for my development. I am using XDoclet to generate the code for the EJBs.

      In my application I have my CMP EJBs configured for v2.X.

      In one of my EJBs I have the following finder:



       * @ejb.finder
       * query="SELECT OBJECT(t) FROM TaxMaster t WHERE t.taxNbr >= ?1 ORDER BY t.taxNbr"
       * signature="java.util.Collection findListByTaxNbr(java.lang.String partialKey)"
       * description="find the list of entries that start with a specific character string"
      


      When I run this the DAOImp module fails with
      javax.servlet.ServletException: Unexpected Error
      java.lang.OutOfMemoryError: Java heap space
      ; CausedByException is:
       Unexpected Error
      java.lang.OutOfMemoryError: Java heap space
      
      


      Obviously JBoss is trying to read every entry in the table that satisfies the query and load it into memory. This won't work at all.

      According to the JBoss documentation I can include a LIMIT parm on the QL call. So I changed the QL to:

       * @ejb.finder
       * query="SELECT OBJECT(t) FROM TaxMaster t WHERE t.taxNbr >= ?1 ORDER BY t.taxNbr ASC LIMIT ?2"
       * signature="java.util.Collection findListByTaxNbr(java.lang.String partialKey, int tLimit)"
       * description="find the list of entries that start with a specific character string"
      


      Now when I attempt to deploy the application I get the following failure:

      10:56:42,111 INFO [BaseLocalProxyFactory] Bound EJB LocalHome 'TaxMaster' to jndi 'TaxMasterLocal'
      10:56:43,736 INFO [STDOUT] org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "LIMIT" at line 1, column 79.
      Was expecting one of:
       <EOF>
       "," ...
      


      So, how do I implement the LIMIT option? I don't mind to upgrade to a more current version if that will fix the problem. But I'd rather not go to that touble just yet if it won't help.

      I have researched this somewhat and can't find anything in the forums that indicate that JBoss has a problem with LIMIT. So I am hoping I am just doing something wrong.

      Thanks for your interest and feedback.

      Glenn Puckett
      MetaMAP, Inc
      Lexington, KY