10 Replies Latest reply on Aug 21, 2003 6:57 PM by samart

    how to perform SELF JOIN using CMP

    taiwuco Newbie

      Hi,

      I have a Many-to-Many relationship of the table account and itself. The join table was created by CMP. I tried this join in a CMP findBy as:

      SELECT OBJECT(ap) FROM Account ap, Account ac WHERE ap.parentAccounts = ac.childAccounts AND ap.accountID = ?1

      and got the following error:

      - nested throwable: (org.jboss.deployment.DeploymentException: Error compiling ejbql; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "=" at line 2, column 83.
      Was expecting:
      "IS" ...
      ))
      at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:409)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:665)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:507)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:470)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy4.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:405)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:515)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:202)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:191)

      What is the problem? Can I do SELF JOIN with CMP? If I don't use JOIN, can anyone show me how to get an organizational structure from a table without doing JOIN? Thanks.

      Brian

        • 1. Re: how to perform SELF JOIN using CMP
          Dietmar Scheidl Newbie

          Hi,

          if I got you right, you are trying to get all parentAccounts of a childAccount so the query should look like this one:

          SELECT DISTINCT OBJECT(ap) FROM Account ac, IN (ac.parentAccounts) ap WHERE ac.accountID = ?1

          You did not write what you intend to do so I hope the above query does help.

          Dietmar

          • 2. Re: how to perform SELF JOIN using CMP
            dema Newbie

            If using only relations will be enough then here are my relations in TreeBean:

            CREATE TREE (
            ID NUMBER,
            PARENT_ID NUMBER
            )

            Actually this was generated by Middlegen for XDoclet.


            /**
            * This is a bi-directional one-to-many relationship CMR method
            *
            * * @ejb.interface-method view-type="local"
            *
            * @ejb.relation
            * name="TREE-cmp20-TREE-cmp20"
            * role-name="TREE-cmp20-1-has-TREE-cmp20-2"
            *
            * @jboss.relation-mapping style="foreign-key"
            * @ejb.value-object
            * aggregate="kz.alb.pcat.domain.interfaces.TreeValue"
            * aggregate-name="ChildNode"
            * members="kz.alb.pcat.domain.interfaces.TreeLocal"
            * members-name="ChildNode"
            * type="Collection"
            * relation="external"
            */
            public abstract java.util.Collection getChildTrees();

            ----------------------------------------
            /**
            * This is a bi-directional one-to-many relationship CMR method
            *
            * @return the related kz.alb.pcat.domain.interfaces.TreeLocal.
            *
            * @ejb.interface-method view-type="local"
            *
            * @ejb.relation
            * name="TREE-cmp20-TREE-cmp20"
            * role-name="TREE-cmp20-2-has-TREE-cmp20-1"
            * cascade-delete="yes"
            *
            * @jboss.relation-mapping style="foreign-key"
            *
            * @jboss.relation
            * fk-constraint="true"
            * fk-column="PARENT_ID"
            * related-pk-field="id"
            *
            */
            public abstract kz.alb.pcat.domain.interfaces.TreeLocal getParentTree();


            • 3. Re: how to perform SELF JOIN using CMP
              taiwuco Newbie

              Dietmar, thank you for your comments. However, my question is not just to get the direct parents of a child node. I want to get
              1. the parents, grand parents, grand grand parents.
              and
              2. the children, grand children, grand grand children.
              That is, to get tree structures in both directions.

              Brian

              • 4. Re: how to perform SELF JOIN using CMP
                taiwuco Newbie

                dema,
                Thanks. I can do bi-directional table-based 1:M relationship betwen a table and itself. From your message, I found Middlegen and I will try to see if I can use the tool to save me more time. Thanks.

                The problem is not the relationship but the query:
                How to do self join query.

                If there are 1,000,000 members forming a tree-like organization such as in a multi-level marketing membership, or in a bill of material relationship. I want to get all levels of the tree below the node. Howver, the other problem may come as the self join is very expensive on join 1 million members. Is there an alternatve way?

                Brian

                • 5. Re: how to perform SELF JOIN using CMP
                  Dietmar Scheidl Newbie

                  Let us asume you have an entity bean which has a CMR between parent and children which is bidirectional. Now you have two methods, one to get the parent (e.g. getParentAccount()), and one to get the collection containing the children (getChildAccounts()).

                  Now just write a simple recursive method which generates the desired collection:

                  public java.util.Collection getAllChildAccounts(Account startAccount)
                  {
                  // Generate a collection to put the found accounts into
                  java.util.Collection col = new java.util.LinkedList();

                  // add startAccount
                  col.add(startAccount);
                  addChildAccounts(col,startAccount);
                  return col;
                  }


                  public void addChildAccounts(java.util.Collection col, Account startAccount)
                  {
                  java.util.Iterator it = startAccount.getChildAccounts().iterator();
                  Account tmpAccount = null;
                  while( it.hasNext() )
                  {
                  tmpAccount = (Account)it.next();
                  col.add(tmpAccount);
                  addChildAccounts(col,tmpAccount);
                  }
                  }

                  You could then do the same for the parent accounts. Hope I could help,
                  Dietmar

                  • 6. Re: how to perform SELF JOIN using CMP
                    taiwuco Newbie

                    (Re: Dietmar)
                    Thanks. I think your codes will work. However, let's assume I have 300,000 member accounts. In many cases, it will generate a lot of SQL queries if I want to find the account tree from the top levels of the membership hierarchy. Will the recursive queries be expensive? That is, I have to buy more equipment to provide more computing power.

                    • 7. Re: how to perform SELF JOIN using CMP
                      Dietmar Scheidl Newbie

                      You are right that the above code will produce many SQL Statements but it is the most portable way.

                      I have only seen one better chance but that is depending on your DB. In a former project we used recursive SQL Statements to do such a SELECT but then you are tied to a feature of a DB which may not be portable. I have never tried to use that with EJBs and CMP.

                      An example of recursive SQL on DB2 can be found here:
                      http://www7b.boulder.ibm.com/dmdd/library/techarticle/0203venigalla/0203venigalla.html

                      • 8. Re: how to perform SELF JOIN using CMP
                        taiwuco Newbie

                        Thanks. At least I know I can't avoid using recursive quries to solve the problem unless I use a specific DBMS such as DB2. Unfortunately, I don't have DB2.

                        Best Regards

                        • 9. Re: how to perform SELF JOIN using CMP
                          santoshp Newbie

                          I just visited this forum. If this is not a closed issue, I here is my thought.

                          I would like to do it using a SQL query:

                          SELECT parent_id , child_id , level
                          FROM account
                          START WITH parent_id = 1111
                          CONNECT BY PRIOR child_id = parent_id

                          -- this query returns all the children...grand children...grand grand chidren..... until the last one in hierarchy for parent=1111.

                          Similariliy to get all the parents, this is the query:

                          SELECT parent_id , child_id , level
                          FROM account
                          START WITH child_id = 9999
                          CONNECT BY PRIOR parent_id = child_id
                          ----------
                          To have a nice view of the hiearchy use these queries respectively:

                          SELECT LPAD(' ',2*(LEVEL-1)) || parent_id , child_id
                          FROM account
                          START WITH parent_id = 1111
                          CONNECT BY PRIOR child_id = parent_id

                          and

                          SELECT LPAD(' ',2*(LEVEL-1)) || parent_id , child_id
                          FROM account
                          START WITH child_id = 9999
                          CONNECT BY PRIOR parent_id = child_id



                          • 10. Re: how to perform SELF JOIN using CMP
                            samart Newbie

                            just load all 1 M beans, ordered, into a list, then use a recurisive function to build the node tree, starting at any node. create a stack and push and pop to walk and build the node tree. i've done this to create an xml based catalog that had to be arbitrarily deep.