6 Replies Latest reply on Oct 2, 2002 1:53 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) from OTable As o where o.something = ?1

      Any help would be just great. I am also having trouble doing queries of the form

      select t.field1, field2 from Table as t

      As I stated about, I can only do

      select object(t)

      Any help will be appreciated.

      Thank you very much.

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

          I love this. I am replying to myself.

          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
            Dain Sundstrom Master

            I like the spec it; it's very well writen. Other then that, the O'Reilly ejb book is good.

            • 3. 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.

              • 4. Re: table joins using CMP and EJB QL
                Dain Sundstrom Master

                I'm sorry, I really don't understand what you are tring to do. Maybe someone else in this forum can help you.

                • 5. Re: table joins using CMP and EJB QL
                  Jochen Cordes Novice

                  Hi !

                  With EJB-QL you can only return either single fields/objects or collections of these. I think you're trying to send back a union of Account and SubscriberAccount (how are they related n-1 ?). You would have to encapsulate the data in a value-object (as you suspected), combining the fields of Account and SubscriberAccount (which really only makes sense if subscriberid is not unique). Did you declare to return Remote-Interfaces on your query (or what do you mean by "when I get my RemoteInterface back", default is LocalInterfaces) ?

                  BTW: Is not a cartesian product ;-).

                  Bye,

                  Jochen.

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

                    The book calls them cartesian joins.

                    Thanks for the info.

                    Right. I used a value object and then had to populate it and returned a collection. Painful but doable.

                    This is what, I assume, a facade is for. To aggregate joins and other related objects.

                    Now, I have another question. If I want to create a PK, in oracle, I can use a sequence to create the next PK for me. How do I map an e bean or in some other way access that sequence so that I can have a unique PK each time.

                    I could use a table an map an e bean to it but that's not as nice as using Oracle sequences.

                    Thanks again.