8 Replies Latest reply on Sep 17, 2004 9:25 AM by malipanchu

    OFFSET & LIMIT does not make it into the generated sql

    greiezer
    greiezer Jul 2, 2004 3:58 PM

      Hi,

      Im using the following JBoss-QL query in JBoss 3.2.3 for a ejbSelect method:

      SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p
      WHERE c = ?1
      AND p.state IS NOT NULL
      AND p.state = 'passiv'
      ORDER BY p.name
      OFFSET ?2
      LIMIT ?3

      But as yoy can see in the following the generated sql does not contain the OFFSET & LIMIT options.

      21:02:35,221 DEBUG [Category#ejbSelectActiveProducts] SQL:
      SELECT t0_p.productId, t0_p.type, t0_p.manufacturer, t0_p.productLink, t0_p.name, t0_p.description, t0_p.listPrice, t0_p.marge, t0_p.porto, t0_p.state, t0_p.registered, t0_p.modified, t0_p.weight, t0_p.keyWords, t0_p.category_fk
      FROM t_category t1_c INNER JOIN t_product t0_p ON t1_c.categoryId=t0_p.category_fk
      WHERE (t1_c.categoryId=?)
      AND t0_p.state IS NOT NULL
      AND t0_p.state = 'passiv'
      ORDER BY t0_p.name ASC

      I have also downloaded jboss-3.2.5, but the generated sql is here too missing the OFFSET & LIMIT options.
      Then I experimented with the EJBQLToSQL92Compiler setting in (jboss-3.2.5/server/default/conf/standardjbosscmp-jdbc.xml) but that does not help either.
      What else could I try?
      Has anybody managed to get this working?

      Regards

      Andreas

      ------------------------------------------------

      Code snippets from the class:

      * @jboss.query
      * description="This generates jboss-ql and overwrites the original query from the ejbSelectMethod"
      * signature="java.util.Collection ejbSelectActiveProducts( my.domain.catalog.interfaces.CategoryLocal category, int start, int count )"
      * query="SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p WHERE c = ?1
      * AND p.state IS NOT NULL
      * AND p.state = 'passiv'
      * ORDER BY p.name OFFSET ?2 LIMIT ?3"
      * strategy="on-load"
      * page-size="128"
      *
      * @jboss.persistence
      * create-table="${jboss.create.table}"
      * remove-table="${jboss.remove.table}"
      */
      public abstract class CategoryEJB implements EntityBean
      {
      ...



      /**
      * @ejb.select
      * query="SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p WHERE c = ?1
      * AND p.state IS NOT NULL
      * AND p.state = 'passiv'"
      */
      public abstract Collection ejbSelectActiveProducts( CategoryLocal category, int start, int count ) throws FinderException;


      ------------------------------------------------
      <jbosscmp-jdbc>

      ...
      <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>

      ....
      </jbosscmp-jdbc>



      • 4892 Views
      • Tags:
        • 1. Re: OFFSET & LIMIT does not make it into the generated sql
          triathlon98
          triathlon98 Jul 2, 2004 4:58 PM (in response to greiezer)

          Don't look at the SQL. It works (I use it), it just isn't handled at SQL level.

          Joachim

            • Actions
          • 2. Re: OFFSET & LIMIT does not make it into the generated sql
            greiezer
            greiezer Jul 3, 2004 11:51 AM (in response to greiezer)

            Thanks for the reply Joachim.

            OK, I have 107 products which are related to a category.

            CategoryLocal cat = CategoryUtil.getLocalHome( ).findByPrimaryKey( categoryId );
Collection products = cat.getActiveProducts( offset, limit )


            At this point I would expect that 'products' has size=limit, but the actual size is 107.

            So to actually actually access the wanted data in the first run, one must use a for loop. 
            for ( int i = 0, imax = offset + limit; ( i < imax ) && it.hasNext( ); i++ )
{
 _log.debug(" --> for : i : " + i );
 Object obj = it.next( );
 if ( i >= offset )
 {
 _log.debug(" --> lst.add( getData( obj, type ) ) : " + obj + ", " + type );
 lst.add( getData( obj, type ) );
 }
}


            Is this also the case for you, or can you just iterate over a 'limit'-sizes collection?

            Andreas

              • Actions
            • 3. Re: OFFSET & LIMIT does not make it into the generated sql
              triathlon98
              triathlon98 Jul 4, 2004 5:58 AM (in response to greiezer)

              Andreas,


              When I use limit, it effectively only return the number of records according to the limit (or less if there are less records).

              From your code, I would assume you are using a session facade. So, you would need to post the session facade code.

              In fact, the call you published only has two parameters, and the finder has three, so maybe something goes wrong there.

              Kind regards,
              Joachim

                • Actions
              • 4. Re: OFFSET & LIMIT does not make it into the generated sql
                greiezer
                greiezer Jul 4, 2004 7:42 AM (in response to greiezer)

                Hi Joachim,

                Sorry, didn't want do upset you :-)
                It was and is also my intention to post a working solution to the forum.
                I am not able to figure it out my self and you wrote in your reply that you have it working for you.
                I would very much appreciate if you could find some time to look at the code and debugging output below.

                Regards

                Andreas
                I am using the following code:

                ---------------------------------------------------------
 CategoryEJB
---------------------------------------------------------

 * @jboss.query
 * description="This generates jboss-ql and overwrites the original query from the ejbSelectMethod"
 * signature="java.util.Collection ejbSelectActiveProducts( my.domain.catalog.interfaces.CategoryLocal category, int start, int count )"
 * query="SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p WHERE c = ?1
 * AND p.state IS NOT NULL
 * AND p.state = 'passiv'
 * ORDER BY p.name OFFSET ?2 LIMIT ?3"
 * strategy="on-load"
 * page-size="6"
 *
 * @jboss.persistence
 * create-table="${jboss.create.table}"
 * remove-table="${jboss.remove.table}"
 */
public abstract class CategoryEJB implements EntityBean
...

 /**
 * @ejb.interface-method
 */
 public Collection getActiveProducts( int start, int count )
 throws javax.ejb.FinderException {
 CategoryLocal category = (CategoryLocal) _ctx.getEJBLocalObject();
 return ejbSelectActiveProducts( category, start, count );
 }

...

 //==========================================
 // ejbSelect methods
 //==========================================

 /**
 * @ejb.select
 * query="SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p WHERE c = ?1
 * AND p.state IS NOT NULL
 * AND p.state = 'passiv'"
 */
 public abstract Collection ejbSelectActiveProducts( CategoryLocal category, int start, int count ) throws FinderException;

---------------------------------------------------------
 ejb-jar.xml
---------------------------------------------------------

 <entity >
 <ejb-name>Category</ejb-name>
 <local-home>my.domain.catalog.interfaces.CategoryLocalHome</local-home>
 <local>my.domain.catalog.interfaces.CategoryLocal</local>
 <query>
 <description><![CDATA[]]></description>
 <query-method>
 <method-name>ejbSelectActiveProducts</method-name>
 <method-params>
 <method-param>my.domain.catalog.interfaces.CategoryLocal</method-param>
 <method-param>int</method-param>
 <method-param>int</method-param>
 </method-params>
 </query-method>
 <ejb-ql><![CDATA[SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p WHERE c = ?1 AND p.state IS NOT NULL AND p.state = 'passiv']]></ejb-ql>
 </query>
 </entity>

---------------------------------------------------------
 jbosscmp-jdbc.xml
---------------------------------------------------------

 <entity>
 <ejb-name>Category</ejb-name>
 <create-table>true</create-table>
 <remove-table>false</remove-table>
 <table-name>T_CATEGORY</table-name>
 <query>
 <description><![CDATA[This generates jboss-ql and overwrites the original query from the ejbSelectMethod]]></description>
 <query-method>
 <method-name>ejbSelectActiveProducts</method-name>
 <method-params>
 <method-param>my.domain.catalog.interfaces.CategoryLocal</method-param>
 <method-param>int</method-param>
 <method-param>int</method-param>
 </method-params>
 </query-method>
 <jboss-ql><![CDATA[SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p WHERE c = ?1 AND p.state IS NOT NULL AND p.state = 'passiv' ORDER BY p.name OFFSET ?2 LIMIT ?3]]></jboss-ql>
 <read-ahead>
 <strategy>on-load</strategy>
 <page-size>6</page-size>
 </read-ahead>
 </query>
 </entity>

---------------------------------------------------------
 PPWebShopEJB, (SLSB session facade)
---------------------------------------------------------

public abstract class PPWebShopEJB implements SessionBean {

 /**
 * @throws FinderException if the category not found
 * @ejb.interface-method
 */
 public Page getProducts( String categoryId, int offset, int limit )
 throws FinderException
 {
 try
 {
 CategoryLocal cat = CategoryUtil.getLocalHome( ).findByPrimaryKey( categoryId );
 return toPage( cat.getActiveProducts( offset, limit ), offset, limit, ProductValue.class );
 }
 catch ( NamingException n )
 {
 throw new EJBException( n );
 }
 }

 private Page toPage( Collection col, int start, int count, Class type )
 {
 _log.debug(" toPage( col, start, count, type )" );
 _log.debug(" --> col : " + col );
 _log.debug(" --> start : " + start );
 _log.debug(" --> count : " + count );
 _log.debug(" --> type : " + type );
 _log.debug("");

 int size = col.size( );
 _log.debug(" --> size() : " + size );

 if ( size == 0 ) { return Page.EMPTY_PAGE; }

 ArrayList lst = new ArrayList( );
 Iterator it = col.iterator( );

 for ( int i = 0, imax = start + count; ( i < imax ) && it.hasNext( ); i++ )
 {
 _log.info(" --> for : i : " + i );

 Object obj = it.next( );
 if ( i >= start )
 {
 lst.add( getData( obj, type ) );
 _log.debug(" --> lst.add( getData( obj, type ) ) : " + obj + ", " + type );
 }
 }

 _log.info(" toPage() - done" );

 return new Page( lst, col.size() ,start, ( start + count ) < size );
 }


 private Object getData( Object obj, Class type )
 {
 if ( type == CategoryValue.class )
 {
 return ( ( CategoryLocal ) obj ).getCategoryValue( );
 }
 else if ( type == ProductValue.class )
 {
 return ( ( ProductLocal ) obj ).getProductValue( );
 }
 else if ( type == WebOrderValue.class )
 {
 return ( ( WebOrderLocal ) obj ).getOrderValue( );
 }
 else if ( type == OrderItemValue.class )
 {
 return ( ( OrderItemLocal ) obj ).getOrderItemValue( );
 }
 else if ( type == CreditCardValue.class )
 {
 return ( ( CreditCardLocal ) obj ).getCreditCardValue( );
 }
 else if ( type == AddressValue.class )
 {
 return ( ( AddressLocal ) obj ).getAddressValue( );
 }
 else
 {
 throw new EJBException( "Invalid data type: " + type );
 }
 }


---------------------------------------------------------
 JBoss 3.2.5 deployment log
---------------------------------------------------------

12:45:16,131 DEBUG [Category#ejbSelectActiveProducts] JBossQL: SELECT OBJECT(p) FROM Category AS c, IN (c.products) AS p WHERE c = ?1 AND p.state IS NOT NULL AND p.state = 'passiv' ORDER BY p.name OFFSET ?2 LIMIT ?3
12:45:16,291 DEBUG [Category#ejbSelectActiveProducts] SQL: SELECT t0_p.productId, t0_p.type, t0_p.manufacturer, t0_p.productLink, t0_p.name, t0_p.description, t0_p.listPrice, t0_p.marge, t0_p.porto, t0_p.state, t0_p.registered, t0_p.modified, t0_p.weight, t0_p.keyWords, t0_p.category_fk FROM t_category t1_c INNER JOIN t_product t0_p ON t1_c.categoryId=t0_p.category_fk WHERE (t1_c.categoryId=?) AND t0_p.state IS NOT NULL AND t0_p.state = 'passiv' ORDER BY t0_p.name ASC


---------------------------------------------------------
 Debug messages for an invocation of "ejbSelectActiveProducts"

 Category contains 107 products
 Offset = start = 12
 Limit = count = 6
---------------------------------------------------------

12:52:46,420 DEBUG [CategoryAction] fetching the page ...
12:52:46,422 DEBUG [PPWebShopEJB] getProducts( categoryId, start, count ) : 5602f366d583ec970152506ebac53a71, 12, 6
12:52:46,423 DEBUG [Category#findByPrimaryKey] Executing SQL: SELECT t0_Category.categoryId FROM t_category t0_Category WHERE t0_Category.categoryId=?
12:52:46,426 DEBUG [Category] Executing SQL: SELECT name, description, Category_subCategories, parentCategory, image FROM t_category WHERE (categoryId=?)
12:52:46,429 DEBUG [Category#ejbSelectActiveProducts] Executing SQL: SELECT t0_p.productId, t0_p.type, t0_p.manufacturer, t0_p.productLink, t0_p.name, t0_p.description, t0_p.listPrice, t0_p.marge, t0_p.porto, t0_p.state, t0_p.registered, t0_p.modified, t0_p.weight, t0_p.keyWords, t0_p.category_fk FROM t_category t1_c INNER JOIN t_product t0_p ON t1_c.categoryId=t0_p.category_fk WHERE (t1_c.categoryId=?) AND t0_p.state IS NOT NULL AND t0_p.state = 'passiv' ORDER BY t0_p.name ASC
12:52:46,564 INFO [PPWebShopEJB] toPage( col, start, count, type )
12:52:46,566 INFO [PPWebShopEJB] --> col : [ProductLocal:57304178d583ec9700af35563cc44d9c, + 106 more of them]
12:52:46,574 INFO [PPWebShopEJB] --> start : 12
12:52:46,575 INFO [PPWebShopEJB] --> count : 6
12:52:46,576 INFO [PPWebShopEJB] --> type : class my.domain.catalog.model.ProductValue
12:52:46,577 INFO [PPWebShopEJB]
12:52:46,577 INFO [PPWebShopEJB] --> size() : 107
12:52:46,579 INFO [PPWebShopEJB] --> for : i : 0
12:52:46,579 INFO [PPWebShopEJB] --> for : i : 1
12:52:46,582 INFO [PPWebShopEJB] --> for : i : 2
12:52:46,583 INFO [PPWebShopEJB] --> for : i : 3
12:52:46,584 INFO [PPWebShopEJB] --> for : i : 4
12:52:46,584 INFO [PPWebShopEJB] --> for : i : 5
12:52:46,585 INFO [PPWebShopEJB] --> for : i : 6
12:52:46,586 INFO [PPWebShopEJB] --> for : i : 7
12:52:46,586 INFO [PPWebShopEJB] --> for : i : 8
12:52:46,587 INFO [PPWebShopEJB] --> for : i : 9
12:52:46,587 INFO [PPWebShopEJB] --> for : i : 10
12:52:46,588 INFO [PPWebShopEJB] --> for : i : 11
12:52:46,588 INFO [PPWebShopEJB] --> for : i : 12
12:52:46,589 INFO [PPWebShopEJB] --> lst.add( getData( obj, type ) ) : ProductLocal:5730421bd583ec9701497f3c4703764f, class my.domain.catalog.model.ProductValue
12:52:46,592 DEBUG [Product] Executing SQL: SELECT productId, type, manufacturer, productLink, name, description, listPrice, marge, porto, state, registered, modified, weight, keyWords, category_fk FROM t_product WHERE (productId=?) OR (productId=?) OR (productId=?) OR (productId=?) OR (productId=?) OR (productId=?)
12:52:46,607 INFO [PPWebShopEJB] --> for : i : 13
12:52:46,608 INFO [PPWebShopEJB] --> lst.add( getData( obj, type ) ) : ProductLocal:57304f1cd583ec97008808ddc1dc19f6, class my.domain.catalog.model.ProductValue
12:52:46,612 INFO [PPWebShopEJB] --> for : i : 14
12:52:46,613 INFO [PPWebShopEJB] --> lst.add( getData( obj, type ) ) : ProductLocal:57304bb7d583ec97005a7dd335f6bb1a, class my.domain.catalog.model.ProductValue
12:52:46,617 INFO [PPWebShopEJB] --> for : i : 15
12:52:46,618 INFO [PPWebShopEJB] --> lst.add( getData( obj, type ) ) : ProductLocal:573049cfd583ec9701802efb20284d2d, class my.domain.catalog.model.ProductValue
12:52:46,621 INFO [PPWebShopEJB] --> for : i : 16
12:52:46,622 INFO [PPWebShopEJB] --> lst.add( getData( obj, type ) ) : ProductLocal:57304e90d583ec970192e760a5b22168, class my.domain.catalog.model.ProductValue
12:52:46,626 INFO [PPWebShopEJB] --> for : i : 17
12:52:46,627 INFO [PPWebShopEJB] --> lst.add( getData( obj, type ) ) : ProductLocal:57305611d583ec97017072bf72b3361a, class my.domain.catalog.model.ProductValue
12:52:46,631 INFO [PPWebShopEJB] toPage() - done
12:52:46,634 DEBUG [CategoryAction] ... page fetched and stored in productsMap


                  • Actions
                • 5. Re: OFFSET & LIMIT does not make it into the generated sql
                  triathlon98
                  triathlon98 Jul 4, 2004 9:12 AM (in response to greiezer)

                  Andreas,

                  I don't see anything wrong.

                  You could try to enable trace logging and see whether this given any extra clues.

                  If that doesn't make you any wiser, then try to build a small testcase and submit that in a bugreport on sourceforge.

                  Joachim

                    • Actions
                  • 6. Re: OFFSET & LIMIT does not make it into the generated sql
                    greiezer
                    greiezer Jul 4, 2004 10:25 AM (in response to greiezer)

                    Joachim,

                    Resetting the ql-compiler to its default value did the trick!

                    <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler</ql-compiler>
<!--
<ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>
-->


                    I am a lucky man now:-)
                    Thank you for your help

                    Andreas

                      • Actions
                    • 7. Re: OFFSET & LIMIT does not make it into the generated sql
                      aloubyansky
                      aloubyansky Jul 5, 2004 3:00 AM (in response to greiezer)

                      Fixed. Thanks.

                      Index: EJBQLToSQL92Compiler.java
===================================================================
RCS file: /cvsroot/jboss/jboss/src/main/org/jboss/ejb/plugins/cmp/jdbc/EJBQLToSQL92Compiler.java,v
retrieving revision 1.6.2.8
diff -r1.6.2.8 EJBQLToSQL92Compiler.java
261a262,265
> else if(childNode instanceof ASTLimitOffset)
> {
> childNode.jjtAccept(this, null);
> }


                        • Actions
                      • 8. Re: OFFSET & LIMIT does not make it into the generated sql
                        malipanchu
                        malipanchu Sep 17, 2004 9:25 AM (in response to greiezer)

                        I am using JBOSS 3.2.3. (with MYSQL)

                        I am not able to get the finder query with LIMIT with 3.2.3.

                        The following is the query used:

                        SELECT OBJECT(o) FROM CUSTOMER o LIMIT ?1

                        I have not modified any configuration.

                        Am I missing anything? Any suggestions?

                          • Actions