7 Replies Latest reply on Apr 22, 2002 11:35 PM by dsundstrom

    FAQ: Relationships in JBoss3.0.0 beta2

    pazu

      For all the unaware out there: jbosscmp-jdbc.xml syntax has changed a bit in beta2 regarding relationships. Here's the new DTD, with comments on each element.

      <!ELEMENT ejb-relation (ejb-relationship-name, read-only?, (foreign-key-mapping | relation-table-mapping), (ejb-relationship-role, ejb-relationship-role)?)>


      <!ELEMENT ejb-relationship-name (#PCDATA)>

      Each ejb-relation should have a <ejb-relationship-name> element containing the relationship name. This should match the <ejb-relationship-name> declared in ejb-jar.xml.


      <!ELEMENT read-only (#PCDATA)>

      <read-only> can be true or false. If true, this relationship is considered readonly and any attempt to modify the relationship collection will raise an exception.


      Next, you should define the mapping method for the relationship: it can be <foreign-key-mapping/> or <relation-table-mapping>.

      <!ELEMENT foreign-key-mapping EMPTY>

      Use <foreign-key-mapping/> if you want to use foreign keys to map this relationship. Please note that this is an empty element.


      <!ELEMENT relation-table-mapping (table-name?, datasource?, create-table?, remove-table?, row-locking?, pk-constraint?)>

      Use <relation-table-mapping> if you want to use a relation table to map this relationship. All child elements are optional, and are identical to the entity level elements with the same name.


      <!ELEMENT ejb-relationship-role (ejb-relationship-role-name, fk-constraint?, key-fields?, read-ahead?)>

      You should define both relationship roles now. Here are comments for the child elements


      <!ELEMENT ejb-relationship-role-name (#PCDATA)>

      This is the name for this role. This should match the <ejb-relationship-role> element in ejb-jar.xml


      <!ELEMENT fk-constraint (#PCDATA)>

      This can be true or false. If true, JBossCMP will create a foreign key constraint when creating this relationship in the database. This only makes sense if <create-table> for this entity or for the relation table is set to true.


      <!ELEMENT key-fields (key-field*)>
      <!ELEMENT key-field (field-name, ((column-name, (jdbc-type, sql-type)?) | (property*)))>

      Each <key-field> elements represents a primary key field for this relationship role. <field-name> is the entity field name, <column-name> is the database column where this field should be persisted. <jdbc-type> and <sql-type> are this field's JDBC and SQL types, respectivelly, and are optional -- but if you specify one you should specify both. is for DVC mapping. I'll not cover DVC in this post.

      And that's it! Declare <key-fields/> for both <ejb-relationship-role>s and you're set.


      <ELEMENT read-ahead (#PCDATA)>
      The last element on the <ejb-relationship-role> is <read-ahead>, which can be true or false. Set to true to enable read ahead for this role.


      Now, an example. Suppose you have a Country bean and a City bean. They have a 1-n relationship. Primary key for Country is countryId and primary key for City is cityId. Here's how you configure this relationship:
      [pre]
      <ejb-relation>
      <ejb-relationship-name>Country-City</ejb-relationship-name>
      <read-only>false</read-only> <!-- could be omitted -->
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>country-has-cities</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>countryId</field-name>
      <column-name>country_id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>city-isin-country</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>
      [/pre]


      Another example. Suppose now you have a Worker bean (primary key: workerId) that has a n-n relationship with the City bean. (the worker can operate on many cities). Here's the relationship:
      [pre]
      <ejb-relation>
      <ejb-relationship-name>Worker-City</ejb-relationship-name>
      <relation-table-mapping>
      <table-name>worker_city</table-name>
      <create-table>true</create-table>
      <pk-constraint>true</pk-constraint>
      </relation-table-mapping>
      <ejb-relationship-role>
      <ejb-relationship-role-name>worker-worksin-city</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>workerId</field-name>
      <column-name>worker_id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>city-houses-workers</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>cityId</field-name>
      <column-name>city_id</column-name>
      </key-fields>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>
      [/pre]

      That's it. Feel free to post comments, provide more examples and correct my mistakes (I'm sure there are lots of mistakes. It was a pain to type everything in the browser).

        • 1. Re: FAQ: Relationships in JBoss3.0.0 beta2

          Hi,

          can you write an example of creating a new City-Bean, if there is a NOT NULL Constraint in the CITY Table of DB.

          Thanks,
          Gio

          • 2. Re: FAQ: Relationships in JBoss3.0.0 beta2
            pazu

            You mean, if there is a NOT NULL constraint for the country_id field in the city table? As far as I know, this is not currently possible.

            As relationships can't be set in ejbCreate(), you need the foreign key field to be able to accept NULL values. If there is a way around this limitation, I'm not aware of it.

            • 3. Re: FAQ: Relationships in JBoss3.0.0 beta2

              Hi,

              well, I think it could be written as bug or future request.

              There a lot of Cases where foreign key field can NOT be NULL.
              I just asked our DB-Designer to remove NOT NULL restrictions - he was not very happy with it. :-(

              Can Dain help us? :-)

              Gio

              • 4. Re: FAQ: Relationships in JBoss3.0.0 beta2
                dsundstrom

                It's on the todo list. It should be in JBoss 3.1.

                -dain

                • 5. Re: FAQ: Relationships in JBoss3.0.0 beta2
                  cgod

                  To cheer your "DB-Designer" up a little you could perhaps use "INITIALLY DEFERRED" and "DEFERRABLE" we've used these SQL keywords with Oracle and Postgresql.

                  Oracle:
                  http://www-db.stanford.edu/~ullman/fcdb/oracle/or-faq.html#deferred

                  • 6. Re: FAQ: Relationships in JBoss3.0.0 beta2
                    jhvaranda

                    Hello Pazu,

                    I didn't understand how to use this tag:

                    <!ELEMENT key-fields (key-field*)>
                    <!ELEMENT key-field (field-name, ((column-name, (jdbc-type, sql-type)?) | (property*)))>

                    Each <key-field> elements represents a primary key field for this relationship role.


                    How can I tell Jboss what column it can use to store the fk value for this relationship?

                    <field-name> is the entity field name, <column-name> is the database column where this field should be persisted.
                    <jdbc-type> and <sql-type> are this field's JDBC and SQL types, respectivelly, and are optional -- but if you specify one you should specify both. is for DVC mapping. I'll not cover DVC in this post.


                    Field-name is already mapped into the cmp-mapping section, so why do I need to this mapping? It would be enough to inform the field-name, isn't that?

                    Now, an example. Suppose you have a Country bean and a City bean. They have a 1-n relationship. Primary key for Country is countryId and primary key for City is cityId. Here's how you configure this relationship:
                    In your example how can I define the fk_column name, in city relational table, to be used by jboss? Suppose that I already have defined that with my dba and I would not like jboss to create this. Your example:
                    <ejb-relation> <ejb-relationship-name>Country-City</ejb-relationship-name> <read-only>false</read-only> <!-- could be omitted --> <foreign-key-mapping/> <ejb-relationship-role> <ejb-relationship-role-name>country-has-cities</ejb-relationship-role- name> <key-fields> <key-field> <field-name>countryId</field-name> <column-name>country_id</column-name> </key-field> </key-fields> </ejb-relationship-role> <ejb-relationship-role> <ejb-relationship-role-name>city-isin-country</ejb-relationship-role-n ame> <key-fields/> </ejb-relationship-role></ejb-relation>

                    It does not make much sense for me. You are mapping a field that you already mapped somewhere. We need to inform what Jboss doesn´t know until now: The fk_column in city table. Does Jboss support this feature?

                    Thanks in advance,
                    JH

                    • 7. Re: FAQ: Relationships in JBoss3.0.0 beta2
                      dsundstrom

                      > To cheer your "DB-Designer" up a little you could
                      > perhaps use "INITIALLY DEFERRED" and "DEFERRABLE"
                      > we've used these SQL keywords with Oracle and
                      > Postgresql.
                      >
                      > Oracle:
                      > http://www-db.stanford.edu/~ullman/fcdb/oracle/or-faq.
                      > tml#deferred

                      This is cool. I didn't know that even existed. Should I add this to the default template for foreign key constraints? Which one should I use, or should I use both?