4 Replies Latest reply on Nov 27, 2002 6:56 AM by Harald

    cmr + self referencing table

    Mattia Newbie

      Hi all,
      I have a simple tree structure stored in a DB table as follow:

      id int(5)
      parent int(5) references id
      name varchar(255)
      isLeaf int(1)

      and this is the relationship as defined in jbosscmp-jdbc.xml

      <ejb-relation>
      <ejb-relation-name>Children</ejb-relation-name>
      <foreign-key-mapping />
      <ejb-relationship-role>
      <ejb-relationship-role-name>TreeElementRelationshipRole</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>id</field-name>
      <column-name>parent</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>TreeElementRelationshipRole1</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>
      <ejb-relation>
      <ejb-relation-name>Parent</ejb-relation-name>
      <foreign-key-mapping />
      <ejb-relationship-role>
      <ejb-relationship-role-name>TreeElementRelationshipRole</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>id</field-name>
      <column-name>id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>TreeElementRelationshipRole1</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>id</field-name>
      <column-name>parent</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>


      So I have a CMP Entity Bean (TreeElement.java) with 3 cmp fields (id,name,isLeaf) and 2 cmr fields (parent,children). So the EJB has 1 parent (one-to-one) and many children (one-to-many).

      And now comes the problem:
      when I create a new TreeElement this is the query launched by JBoss:
      exec sp_executesql N'INSERT INTO tree (id, type, name, id, parent, parent) VALUES (@P1, @P2, @P3, @P4, @P5, @P6)', N'@P1 int ,@P2 int ,@P3 nvarchar(4000) ,@P4 int ,@P5 int ,@P6 int ', 44, NULL, N'ElementTest', NULL, NULL, NULL"

      and the obvious exception: "Column name 'id' appears more than once in the result column list".
      How can I manage those kind of recursive relationship?
      Finder querys suffer the same problem (duplicate 'parent' and 'id' selection) but at least it is not an error.

      Is it my own error or a bug/missing feature in JBoss?
      wouldn't be possible to check for duplicate column names when the query is created?

      TIA,
      mattia

        • 1. Re: cmr + self referencing table
          Harald Newbie

          Hi there, I am not sure why you would like to do something like this. But in any case, your jbosscmp-jdbc
          look a bit strange to me. Have you tried something like this?

          <ejb-relation>
          <ejb-relation-name>Parent</ejb-relation-name>
          <foreign-key-mapping />
          <ejb-relationship-role>
          <ejb-relationship-role-name>TreeElementRelationshipRole</ejb-relationship-role-name>
          <key-fields/>
          </ejb-relationship-role>
          <ejb-relationship-role>
          <ejb-relationship-role-name>TreeElementRelationshipRole1</ejb-relationship-role-name>
          <key-fields>
          <key-field>
          <field-name>isLeaf</field-name>
          <column-name>child</column-name>
          </key-field>
          </key-fields>
          </ejb-relationship-role>
          </ejb-relation>

          The <column-name> should correspond to your CMR filed

          • 2. Re: cmr + self referencing table
            Mattia Newbie

            well I'm just trying to represent a simple tree structure (eg: a forum, a document category structure...).

            > The <column-name> should correspond to your CMR filed

            AFAIK <field-name> should correspond to a PK in my object and <column-name> to database column name.

            I'm thinking of another solution:
            leave only 1 relation (tha Children relationship) and create a custom finder findParentByChild(int child) with
            <ejb-ql>SELECT OBJECT(t) FROM Tree (t) IN t.children (c) WHERE c.id = ?1</ejb-ql>

            that means find the object of which ID is child.
            hmmm... I'll test it and let you know of the results :)

            thanks
            --
            mattia


            • 3. Re: cmr + self referencing table
              Mattia Newbie

              it works!

              the correct EJBQL is
              <ejb-ql>SELECT OBJECT(t) FROM Tree t IN (t.children) c WHERE c.id = ?1</ejb-ql>

              and instead of a Elem.getParent() I have to invoke ElemHome.findParentByChild(Elem.getId())

              It seems correct to me:
              - avoid duplicate column
              - think of this relation as a descend-only relation in the tree (an Element has many children and knowing a child I can find its parent)

              would a bidirectional relationship be useful?

              bye
              --
              mattia



              • 4. Re: cmr + self referencing table
                Harald Newbie

                Well, it depend on how many iterations you need to do down the list and how often. In the bidirectional case you would not need the itiration.
                As to the CMR and the colum you are right of course, I was thinking of the case of unknown publik keys.