2 Replies Latest reply on Sep 24, 2002 10:35 PM by Mark LaPolla

    table joins using CMP and EJB QL

    Mark LaPolla Newbie

      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.

        • 1. Re: table joins using CMP and EJB QL
          Mark LaPolla Newbie

          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.

          • 2. Re: table joins using CMP and EJB QL
            Mark LaPolla Newbie

            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.