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