11 Replies Latest reply on May 24, 2002 3:58 PM by Olaf Bergner

    CMR and Foreign Keys

    William Houck Newbie

      I've seen many similar questions in this forum but I haven't been successful applying them to my own project. Please help.

      Is it possible to use CMR to model a foreign key relationship when the tables in question already exist? That is to say, the container will not be allowed to create the tables or add columns. The SQL script used to create the tables (before adding an ejb layer) will assign a foreign key reference to one column/table.

      My project works fine when I allow JBoss to create the tables. However, when I point to my existing tables via jbosscmp-jdbc.xml I receive the following run time exception when accessing a given row:

      Column not found: Unknown column 'shipAddress' in 'field list'

      As an aside, the jboss created tables seem to be bi-directional in that foreign key columns are created in both tables. In my pre-existing tables I assign a foreign key reference to one column/table, to accomplish a uni-directional relationship between two tables.

      My Basic Table Info:
      2 tables (order and address) with a one to one unidirectional relationship. order has an address. address belongs to order. As you can see below, a foreign key column is created in the address table that
      refers to the primary key column of the order table.

      The attached zip file contains my very small project which I'm running against JBoss 3.0.0RC1 and MySQL. If anyone prefers that I post portions of it here rather than
      download it I will be happy to do so.


      create table wrox_order(order_ID bigint not null,
      customer_name varchar(22),
      primary key (order_ID))
      type=innodb;


      create table wrox_address(order_ID bigint not null,
      address_ID bigint not null,
      street varchar(17),
      city varchar(17),
      state varchar(17),
      zip varchar(17),
      foreign key (order_ID)
      REFERENCES wrox_order(order_ID),
      primary key (address_ID),
      unique (order_ID, address_ID))
      type=innodb;

        • 1. Re: CMR and Foreign Keys
          William Houck Newbie

          Sorry, here is the attached project I mentioned above.

          • 2. Re: CMR and Foreign Keys
            Dain Sundstrom Master

            You won't be able to make a mapping for that table structure because it uses a not-null foreign key field. I don't know why wrox would suggest this type of mapping as it is very dependent on the server implementation of CMP 2.

            You will have to be flexible, and remove the not null constraint until this feature is implemented.

            Also you getting the errors because your table mapping in jbosscmp-jdbc.xml file is wrong (I haven't looked at it, but it's wrong).

            • 3. Re: CMR and Foreign Keys
              William Houck Newbie

              Thanks so much for responding, Dain. The create statements are my own, not wrox's (wouldn't want to make them look bad). I used their name because the attached code was oringinally theirs (I've modified it slightly).

              I did read in this forum that the foreign key field cannot be null as of yet. I'm happy to modify it accordingly but MySQL threw an error when I removed the "not null" statement. Can you suggest a statement that MySQL will accept?

              Regarding jbosscmp-jdbc.xml, I agree. I'm sure I've got it wrong. I've read through this forum quite a bit but just don't seem to understand the solution (I've also purchased your documentation). Any help you can give me with this file would really be great. Sorry to be asking, I know you've done quite a bit of that in this forum.

              • 4. Re: CMR and Foreign Keys
                William Houck Newbie

                Still no luck with jbosscmp-jdbc.xml.

                After re-reading Dain's "for-pay" docs and examples I've modified my code slightly but with no impact on the problem. However I think the key-field elements in this version do a better job of modeling the REFERANCES clause of my SQL foreign key declaration.

                I noticed in the examples that foreign key columns are not mapped as cmp fields (this seems strange since primary key columns are). As a result, I've removed the cmp entries and java code that mapped my foreign key field. Again, I haven't impacted the problem with these changes so any help is greatly appreciated.

                I was unable to attach the whole project so here is an excerpt from jbosscmp-jdbc.xml:

                <ejb-relation>
                <ejb-relation-name>Order-Address</ejb-relation-name>
                <foreign-key-mapping>
                <ejb-relationship-role>
                <ejb-relationship-role-name>
                order-has-a-address
                </ejb-relationship-role-name>
                <key-fields/>
                </ejb-relationship-role>
                <ejb-relationship-role>
                <ejb-relationship-role-name>
                address-belongsto-order
                </ejb-relationship-role-name>
                <key-fields>
                <key-field>
                <!--
                This is a ref. to the primary key attribute in OrderEJB2
                -->
                <field-name>orderID</field-name>

                <!--
                This is a ref. to the order_ID clomun of the wrox_address table. This column was assigned a foreign key reference to wrox_order.order_ID
                -->
                <column-name>order_ID</column-name>

                </key-field>
                </key-fields>
                </ejb-relationship-role>
                </foreign-key-mapping>
                </ejb-relation>


                However, I did come up with a create statement that both allows a null foreign key and is acceptable to MySQL. It seems the foreign key field must be set to unique when null is allowed:

                create table wrox_address(order_ID bigint,
                address_ID bigint not null,
                street varchar(17),
                city varchar(17),
                state varchar(17),
                zip varchar(17),
                primary key (address_ID),
                foreign key (order_ID)
                REFERENCES wrox_order(order_ID),
                unique (order_ID))
                type=innodb;

                • 5. Re: CMR and Foreign Keys
                  Dain Sundstrom Master

                  You just need to drop the not null from the order_ID column. Try the following:

                  create table wrox_address(
                   order_ID bigint,
                   address_ID bigint not null,
                   street varchar(17),
                   city varchar(17),
                   state varchar(17),
                   zip varchar(17),
                   foreign key (order_ID)REFERENCES wrox_order(order_ID),
                   primary key (address_ID)
                  ) type=innodb;
                  


                  • 6. Re: CMR and Foreign Keys
                    William Houck Newbie

                    Dain, thanks for responding and welcome back. Although the MySQL create statement is no longer an issue I've had no impact on the real problem. Again, no matter how I modify jbosscmp-jdbc.xml I always get "Column not found: Unknown column 'shipAddress' in 'field list'
                    " at run time. All my previous posts are still relevant. I can really use some help with this.

                    • 7. Re: CMR and Foreign Keys
                      Sebastien ASTIE Newbie

                      Will,
                      I have exactly the same problem, i have a 1-* relationship between two entities.
                      What I don't understand is that the same code runs fine on Weblogic 6.1, but it doesn't with Jboss 3.0 RC3.
                      I always get the Column not found Exception.
                      I keep on changing the jbosscmp-jdbc.xml but nothing, it is really frustrating.

                      • 8. Re: CMR and Foreign Keys
                        William Houck Newbie

                        I was able to make a little headway by removing the foreign-key-mapping tag (it's no longer required) from jbosscmp-jdbc.xml thereby allowing my ejb-relationship-role tags to be directly under ejb-relation. This tag appears to suppress everything within. I think it's the reason my constant fiddling with the key-field tags had no impact on my problem.

                        My new runtime error states that JBoss expected two columns with the same name when my table only has one. Any ideas?

                        Will

                        • 9. Re: CMR and Foreign Keys
                          Olaf Bergner Newbie

                          Hello,

                          I am merely a beginner, but I think your problem is that you got the mapping in jbosscmp-jdbc.xml reversed. I am not familiar with the semantics involved, but I think they require that you map the primary key of the entity bean represented by the current role in the relationship (the order bean in your case) to the relevant field of the bean represented by the opposite role (the address bean).

                          So I suggest you try the following:

                          <foreign-key-mapping>
                          <ejb-relationship-role>
                          <ejb-relationship-role-name>order-has-a-address</ejb-relationship-role-name>
                          <key-fields> <!-- <foreign-key-fields> -->
                          <key-field>
                          <field-name>orderID</field-name>
                          <column-name>order_ID</column-name>
                          </key-field>
                          <key-fields/>
                          </ejb-relationship-role>
                          <ejb-relationship-role>
                          <ejb-relationship-role-name>address-belongsto-order</ejb-relationship-role-name>
                          </ejb-relationship-role>
                          </foreign-key-mapping>

                          Don't forget to delete the field orderID from the list of cmp-fields in AddressEJB since it is now a cmr-field. Adjust ejb-jar.xml if necessary, though I doubt this should prove so.

                          I think what causes the confusion is that ejb-jar.xml describes the beans' view of the relationship (pointing from order to address), whereas jbosscmp-jdbc.xml endorses the database's view of the same relationship, in your case pointing from address to order. It's reversed.

                          Please let me know if this helps.

                          Regards,
                          Olaf

                          • 10. Re: CMR and Foreign Keys
                            Sebastien ASTIE Newbie

                            Will,

                            I modified my jbosscmp-jdbc.xml wut i still have the same error:

                            Column not found: Unknown column 'department' in 'field list';

                            I seems we are a lot experiencing the same problem, the RC3 treats CMR fields as CMP fields, so you get this exception saying that the column (corresponding to you CMR field name) doesn't exist.

                            Again, I'm running the same code, same ejb-jar.xml on Weblogic 6.1 without any problem.

                            Regards

                            • 11. Re: CMR and Foreign Keys
                              Olaf Bergner Newbie

                              Hello Will,

                              there is an error in my example. Make the first <foreign-key-mapping> a <foreign-key-mapping/> and drop the </foreign-key-mapping> at the end.

                              Regards,
                              Olaf