table joins using CMP and EJB QL

[mlapolla]

Hi,

I've seen some messages on this topic but no answers that work for me. I am using JBuilder 7 and am deploying on both Weblogic and JBoss/Tomcat and I was wonder how to do a table join. I am using CMP and am trying not to use BMP.

Specifically, I've set up my entity bean relations correctly and now I need to create a finder method or something equiv. where I can invoke the following queries:

SELECT e.EntityID, ParentEntityID, CompanyName, FirstName, LastName, Address1, Address2, City, State, PostalCode
Active, Authenticated, AccountNumber
FROM Entity e, SubscriberEntity se
WHERE e.EntityID=se.EntityID and se.SubscriberID=3

and

select p.phonenumber, p.importancelevel, p.description, s.email, f.FeatureID, f.FeatureName, c.Active
from lineprofiles p, subscriber s, CurrentState c, Features f
where p.subscriberid=s.subscriberid and p.subscriberid=3 AND p.PhoneNumber=c.PhoneNumber AND c.FeatureID=f.FeatureID

They are not related queries but just two examples. I am trying to stay within the CMP framework. Is this possible? Can I mix and match my BMP and CMP entity beans and get good results?

I am having trouble with the joins. How do I do that? I seem to only be able to reference one table and I cannot seem to reference a specific field in my queries at all. I can only do queries of the form:

select object(o) form OTable As o where o.something = ?1

Any help would be just great.

Thank you very much.

[mlapolla]

BTW, I found much of what I needed in Professional EJB published by WROX but not everything. Does someone know a better reference. I looked up the EJB QL reference on line but a better explanation would be good. More like the WROX book.

Thanks.

[mlapolla]

The spec is good but now that I have a gotten the EJB QL to work, how do I recover the data.

I am using JBuilder7 and now my EJB QL looks like this:

select object(a) from Account a, SubscriberAccount sa where a.accountid = sa.accountid and sa.subscriberid = ?1

All well and good. But when I get my RemoteInterface back, it does not include the results from the sa table. They getSubscriber() method has no remote interface and when I put one on it, I get an error stating that a client is not allowed to directly access the data form a CMP RDBMS persistence manager.

So, how do I get this Cartesian product back to the client?

Do I have to process it on the server and send it back as a value object? Or a collection of value objects? That seems bogus.

Thanks.