11 Replies Latest reply on Nov 16, 2004 5:27 PM by benzin2

    CMP many-to-many relationship with 3 beans

    archevis

      Let's say we have two ordinary entities, Employee and Company, related to each other through a third entity called Employment. The DB tables could be stripped down to the following:

      Employee:
      - emp_id

      Company:
      - com_id

      Employment:
      - emp_id
      - com_id
      - start_date

      Foreign keys should be obvious... My point here is that the associative bean Employment itself contains data of interest, and so relationship table mapping will not do. The goal is to be able to traverse from a Company through Employments to Employees, and vice versa, using container managed relationships.

      I reckon for the Employment bean we would need a custom primary key class EmploymentPK, containing reference to the CompanyLocal and EmployeeLocal of interest.

      Assuming I have a ejb-jar.xml containing...

      <ejb-relation>
      <ejb-relation-name>company-employments</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>company</ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>Company</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>employments</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>employments</ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>Employment</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>company</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>
      <ejb-relation>
      <ejb-relation-name>employee-employments</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>employee</ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>Employee</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>employments</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>employments</ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>Employment</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>employee</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>

      ... what should the jbosscmp-jdbc.xml contain?

      Here is my suggestion so far:

      <ejb-relation>
      <ejb-relation-name>company-employments</ejb-relation-name>
      <foreign-key-mapping />
      <ejb-relationship-role>
      <ejb-relationship-role-name>company</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>companyID</field-name>
      <column-name>com_id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>employments</ejb-relationship-role-name>
      <key-fields />
      </ejb-relationship-role>
      </ejb-relation>
      <ejb-relation>
      <ejb-relation-name>employee-employments</ejb-relation-name>
      <foreign-key-mapping />
      <ejb-relationship-role>
      <ejb-relationship-role-name>employee</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>employeeID</field-name>
      <column-name>emp_id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>employments</ejb-relationship-role-name>
      <key-fields />
      </ejb-relationship-role>
      </ejb-relation>

      Anyone care to comment on this?

        • 1. Re: CMP many-to-many relationship with 3 beans

          In Mastering Enterprise JavaBeans 2nd Edition, (Ed Roman et al), the authors recommend "faking" the M:N relationship by representing the intermediate table (Employment) as a first order entity bean. This effectively reduces the M:N relationship to two 1:N relationships.

          This does cause a bit of a class explosion, but makes understanding and visualizing the relationships a lot easier.

          • 2. Re: CMP many-to-many relationship with 3 beans
            archevis

            Roman's example of "fake M:N" is exactly what I'm getting at. However, I'm struggling with getting it all to work with JBoss. If anyone could provide a more or less working example I would be ever grateful :)

            • 3. Re: CMP many-to-many relationship with 3 beans

              Using your context, the jboss-cmp-jdbc file would have an entry like:

              <ejb-relation> <ejb-relation-name>company-employments</ejb-relation-name>
              <ejb-relationship-role>
              <ejb-relationship-role-name>
              employments
              </ejb-relationship-role-name>
              <fk-constraint>false</fk-constraint>
              <key-fields/>
              </ejb-relationship-role>
              <ejb-relationship-role>
              <ejb-relationship-role-name>
              company
              </ejb-relationship-role-name>
              <key-fields>
              <key-field>
              <field-name>companyPK</field-name>
              <column-name>COMPANY_ID</column-name>
              </key-field>
              </key-fields>
              </ejb-relationship-role>
              </ejb-relation>

              (I'm not sure what your primary key field and columns are called, but you get the idea.)

              I use XDoclet to generate all this. If you do, let me know and I'll post the XDoclet tags I'd used on the CMR methods.

              Jack

              • 4. Re: CMP many-to-many relationship with 3 beans
                archevis

                Thank you for your reply. However, when I try to run a test JBoss generates a rather odd SQL statement:

                SELECT FROM employment WHERE (company_pk=1)

                and of course MySQL responds with an error...

                Obviously JBoss figured out which table to search within, so I must be close. Do I need to explicitly declare the Employment foreign keys (companyPK and employeePK) as CMP fields anywhere? Either in EmploymentBean or EmploymentPK? I have messed around with the idea, but it doesn't seem to work either. For the moment I'm following Roman's pattern, with EmploymentPK containing references to a CompanyLocal and an EmployeeLocal.

                • 5. Re: CMP many-to-many relationship with 3 beans
                  normal

                  I have the same problem.
                  can anyone tell me how to wirte xdoclet tag for generating primarky key?

                  • 6. Re: CMP many-to-many relationship with 3 beans
                    aparaapara

                    Here is an example that works for me. I have two entities called Profile and Component. A profile can have many components, and a component can be owned by many profiles, so it's an N-N relationship. However, I need to keep attribute called order, thus I have another Entity called ProfileComponent which acts as my "join" component.

                    The join component has it's own primary key, which is not the composite of the other two keys.

                    Here is a relationship portion from ejb-jar.xml:

                    <ejb-relation>
                    <ejb-relation-name>Profile-ProfileComponent</ejb-relation-name>
                    <ejb-relationship-role>
                    <ejb-relationship-role-name>profile-has-profilecomponents</ejb-relationship-role-name>
                    One
                    <relationship-role-source>
                    <ejb-name>Profile</ejb-name>
                    </relationship-role-source>
                    </ejb-relationship-role>
                    <ejb-relationship-role>
                    <ejb-relationship-role-name>profilecomponent-belongs-to-profile</ejb-relationship-role-name>
                    Many
                    <relationship-role-source>
                    <ejb-name>ProfileComponent</ejb-name>
                    </relationship-role-source>
                    <cmr-field>
                    <cmr-field-name>profile</cmr-field-name>
                    </cmr-field>
                    </ejb-relationship-role>
                    </ejb-relation>

                    <ejb-relation>
                    <ejb-relation-name>Component-ProfileComponent</ejb-relation-name>
                    <ejb-relationship-role>
                    <ejb-relationship-role-name>component-has-profilecomponents</ejb-relationship-role-name>
                    One
                    <relationship-role-source>
                    <ejb-name>Component</ejb-name>
                    </relationship-role-source>
                    </ejb-relationship-role>
                    <ejb-relationship-role>
                    <ejb-relationship-role-name>profilecomponent-belongs-to-component</ejb-relationship-role-name>
                    Many
                    <relationship-role-source>
                    <ejb-name>ProfileComponent</ejb-name>
                    </relationship-role-source>
                    <cmr-field>
                    <cmr-field-name>component</cmr-field-name>
                    </cmr-field>
                    </ejb-relationship-role>
                    </ejb-relation>


                    Here is the part from jbosscmp-jdbc.xml

                    <ejb-relation>
                    <ejb-relation-name>Profile-ProfileComponent</ejb-relation-name>
                    <foreign-key-mapping/>

                    <ejb-relationship-role>
                    <ejb-relationship-role-name>profile-has-profilecomponents</ejb-relationship-role-name>
                    <key-fields>
                    <key-field>
                    <field-name>id</field-name>
                    <column-name>profileid</column-name>
                    </key-field>
                    </key-fields>
                    </ejb-relationship-role>

                    <ejb-relationship-role>
                    <ejb-relationship-role-name>profilecomponent-belongs-to-profile</ejb-relationship-role-name>
                    </ejb-relationship-role>
                    </ejb-relation>

                    <ejb-relation>
                    <ejb-relation-name>Component-ProfileComponent</ejb-relation-name>
                    <foreign-key-mapping/>

                    <ejb-relationship-role>
                    <ejb-relationship-role-name>component-has-profilecomponents</ejb-relationship-role-name>
                    <key-fields>
                    <key-field>
                    <field-name>id</field-name>
                    <column-name>componentid</column-name>
                    </key-field>
                    </key-fields>
                    </ejb-relationship-role>

                    <ejb-relationship-role>
                    <ejb-relationship-role-name>profilecomponent-belongs-to-component</ejb-relationship-role-name>
                    </ejb-relationship-role>
                    </ejb-relation>


                    The tables look like this:

                    Profile
                    ---------

                    ....


                    Component
                    ------------

                    ....


                    ProfileComponent
                    ------------------

                    [componentId]
                    [profileId]
                    order

                    Hope this helps.

                    -AP_

                    • 7. Re: CMP many-to-many relationship with 3 beans
                      benzin2

                      I have the same problem but with with some differences, these are:

                      1. I would like to use the two foreign relation keys as a composite primary key for the "fake" entity bean (so that I can have additional colums/feilds), and do not have to change the database design to have a new primary key for the relation table.

                      2. I would lite to keep the database design so that the primary key here as well consist of the composite foreign keys.

                      Is this possible??
                      Please help!!

                      • 8. Re: CMP many-to-many relationship with 3 beans
                        aloubyansky

                        Yes.

                        • 9. Re: CMP many-to-many relationship with 3 beans
                          benzin2

                          Could you please give me an example of how to do this.
                          To add to the problem i am using auto-increment for the primary key for each of the two entities that are related.

                          Could you show me how it should look in jboss.xml, jbosscmp-jdbc.xml, ejb-jar.xml and the how the source code should look.

                          Or perhaps there is an example somewhere that I can download?

                          • 10. Re: CMP many-to-many relationship with 3 beans
                            aloubyansky

                            Just map foreign keys to intermediate bean's primary key columns. There won't be direct many-to-many relationship between the two beans. Instead, there will be two 1-to-many relationships.

                            • 11. Re: CMP many-to-many relationship with 3 beans
                              benzin2

                              Thank you for your response!
                              I have tried to implement what you say but not been successful.
                              I have searched for examples on the net and found some but they do not seam to work.

                              Could you please give me some relevant examples of xml and source code (ejbCreate and ejbPostCreate) or perhaps let me know where I can download it.
                              Or if you would like to email it to me my address is
                              pierre.ljungberg@swipnet.se

                              Please !! I really need help, have tried everything I can think of.
                              By the way I'm using jboss 3.2.3 and mysql 4.0.22 if it makes a difference.
                              BR
                              /Pierre