Help with ejbSelect and EJB-QL

[hunterhillegas]

I need ordered output from a CMR accessor so at Dain's suggestion I am planning on adding and ejbSelect method and overriding it to accomplish this... That's when I got a little lost...

I have a bean called Product and a bean called ProductLineItem. There is a one to many unidirectional relationship between them, and a method in Product called getLineItems() that returns the line items as a collection. The problem is that there is no way to specify ordering for the return of that collection... It goes by the primary key which is not what I need...

So... It seems that writing an ejbSelect method and overriding it with my own SQL might do the trick...

But I have questions... I've not used ejbSelect methods yet...

Would I want it in the Product or ProductLineItem class? Is the latter even an option since the relationship is unidirectional?

What kind of EQB-QL statement do I use to navigate the CMR relationship?

Anyway, any help is appreciated!

Thanks,
Hunter

[dsundstrom]

You put the ejbSelect in whatever bean is going to access it. ejbSelect method don't have any context to the current bean type. You would have code like this in the product bean:

public abstract Set ejbSelectLineItemsForProduct(Product p);

public Set getMyStuff() {
return ejbSelectLineItemsForProduct(ctx.getObject);
}

and ejb-ql like this:

SELECT OBJECT(l)
FROM Product p, IN(p.lineItems) l
WHERE p = ?1

You will still have to override the EJB-QL with declared SQL because I haven't added support for an order clause to the EJB-QL parser.

[hunterhillegas]

This looks reasonable. Thanks.

What kind of declared SQL would I use to traverse the CMR?

In the example in the docs it looks as if you are using the generated foreign key names... Is that what I should be doing?

[hunterhillegas]

Okay, I'm totally confused with the declared SQL across the CMR relationship.

Help! This is what I have:


Get Line Items in Alpha Order
<query-method>
<method-name>ejbSelectLineItemsForProduct</method-name>
<method-params>
<method-param>com.guerrillabroadcasting.groundswell.ejb.entity.ProductLocal</method-param>
</method-params>
</query-method>
<declared-sql>

<ejb-name>ProductLineItem</ejb-name>

????
????
ProductLineItem.description
</declared-sql>

[hunterhillegas]

Anyone ever used the declared SQL functionality that could help me out?

[alu1344]

There are two different querys. One is the EJB-QL in ejb-jar (which should be almost empty in your case) and the other has a similar sintax and is defined in the jbosscmp-jdbc file.

My case uses findMethods (I still haven't needed ejbSelect methods, but daily I try to remember that they are there :))

ejb-jar:

...

<query-method>
<method-name>findByComercialIdAndFacturaCobro</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
<method-param>java.lang.Short</method-param>
</method-params>
</query-method>
<!-- This query is overriden in jbosscmp-jdbc -->
<ejb-ql>
</ejb-ql>


...


jbosscmp-jdbc:

<!-- At similar point -->


<query-method>
<method-name>findByComercialIdAndFacturaCobro</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
<method-param>java.lang.Short</method-param>
</method-params>
</query-method>
<declared-sql>
, FACTURA

CLIENTE.ID = FACTURA.CLI_ID AND
CLIENTE.ID={0} AND FACTURA.COBRO={1}

CLIENTE.NOMBRE
</declared-sql>


The examples provided by Dain are great. If in doubt, check the jbosscmp-jdbc.dtd

[hunterhillegas]

Hmmmm... I do have Dain's docs... Still, the syntax for ejbSelects in jbosscmp-jdbc.xml is different from the finder syntax...

I'm still not quite there... Anyone else?