3 Replies Latest reply on Oct 21, 2002 2:22 PM by alexis bergue

    1 to Many relationship

    alexis bergue Newbie

      Hi all,

      My question may be stupid but I am unable to create a working 1 to Many relationship with JBoss 3.0.3. My DB is MySQL 3.23.51-Max using InnoDB tables.


      Here are my 2 tables:

      CREATE TABLE user (
      userID int(11) NOT NULL default '0',
      email char(64) NOT NULL default '',
      password char(64) NOT NULL default '',
      PRIMARY KEY (userID),
      UNIQUE KEY email_idx (email)
      ) TYPE=InnoDB;

      CREATE TABLE galleryimage (
      imageID int(11) NOT NULL default '0',
      userID int(11) NOT NULL default '0',
      file char(64) NOT NULL default '',
      PRIMARY KEY (imageID),
      KEY userID_idx (userID)
      ) TYPE=InnoDB;


      My 2 entity beans (without queries & resource-ref) and my relationship in ejb-jar.xml:


      <ejb-name>User</ejb-name>
      nix.prj.eovia.ejb.UserHome
      nix.prj.eovia.ejb.User
      <local-home>nix.prj.eovia.ejb.UserLocalHome</local-home>
      nix.prj.eovia.ejb.UserLocal
      <ejb-class>nix.prj.eovia.ejb.UserBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>nix.prj.eovia.ejb.UserPK</prim-key-class>
      False

      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>user</abstract-schema-name>
      <cmp-field>
      <field-name>userID</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>email</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>password</field-name>
      </cmp-field>



      <ejb-name>GalleryImage</ejb-name>
      nix.prj.eovia.ejb.GalleryImageHome
      nix.prj.eovia.ejb.GalleryImage
      <local-home>nix.prj.eovia.ejb.GalleryImageLocalHome</local-home>
      nix.prj.eovia.ejb.GalleryImageLocal
      <ejb-class>nix.prj.eovia.ejb.GalleryImageBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>nix.prj.eovia.ejb.GalleryImagePK</prim-key-class>
      False

      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>galleryimage</abstract-schema-name>
      <cmp-field>
      <field-name>imageID</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>userID</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>file</field-name>
      </cmp-field>



      <ejb-relation>
      <ejb-relation-name>User-GalleryImage</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      User-has-many-GalleryImages
      </ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>User</ejb-name>
      </relationship-role-source>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      GalleryImage-has-a-User
      </ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>GalleryImage</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>user</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>



      The same items in jbosscmp-jdbc.xml:


      <ejb-name>User</ejb-name>
      <table-name>user</table-name>



      <ejb-name>GalleryImage</ejb-name>
      <table-name>galleryimage</table-name>



      <ejb-relation>
      <ejb-relation-name>User-GalleryImage</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      User-has-many-GalleryImages
      </ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>userID</field-name>
      <column-name>userID</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      GalleryImage-has-a-User
      </ejb-relationship-role-name>
      </ejb-relationship-role>
      </ejb-relation>



      When I run query using the foreign key like:
      SELECT OBJECT(gi) FROM galleryimage gi WHERE gi.user.file=?1
      My relationship is working fine.

      But when I try to insert a row into galleryimage via GalleryImage Bean, JBoss executes the following query to do the job:
      10:46:03,595 DEBUG [GalleryImage] Executing SQL: INSERT INTO galleryimage (imageID, userID, file, userID) VALUES (?, ?, ?, ?)

      And JDBC driver obviously returns the following error:
      java.sql.SQLException: General error: Column 'userID' specified twice
      at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
      at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
      at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
      at org.gjt.mm.mysql.PreparedStatement.executeUpdate(Unknown Source)
      at org.gjt.mm.mysql.PreparedStatement.executeUpdate(Unknown Source)
      at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeUpdate(LocalPreparedStatement.java:308)


      If I remove userID field from key-fields into jbosscmp-jbd.xml relationship or from cmp-field into ejb-jar.xml entity bean, JBoss is unable to do the relationship.

      So my question is very simple: how can I avoid the previous error and thus have a relationship working perfectly?


      This issue makes me crazy and freezes my work since 3 days ago. I am sure that I do a very simple mistake. Probably in jbosscmp-jdbc.xml. But which one? I have read the DTD and made several tests but no success. Some help will be so cool.

      Thanks,
      Alexis

        • 1. Re: 1 to Many relationship
          Tung Dang Novice

          1)
          CREATE TABLE galleryimage (
          imageID int(11) NOT NULL default '0',
          userID int(11) NOT NULL default '0',
          file char(64) NOT NULL default '',
          PRIMARY KEY (imageID),
          KEY userID_idx (userID)
          ) TYPE=InnoDB;

          Jboss does not allow a not-null foreign key, so just change it to:

          userID int(11) default '0'

          2)

          <ejb-name>GalleryImage</ejb-name>
          ...

          <cmp-field>
          <field-name>userID</field-name>
          </cmp-field>


          ...


          You must not explicitly map a foreign key, JBoss does it for you.

          3)
          There are something wrong in your relationship mapping in ejb-jar.xml and in jboss-jdbc.xml. Take a look in Quick Start for futher information.

          Hope it helps.

          • 2. Re: 1 to Many relationship
            alexis bergue Newbie

            Hi tdang,

            Thanks for your answer.

            My relationship works better now and I can insert rows into my table. But there is still a last issue. I have moved userID at the end of the table and removed NOT NULL parameter:

            CREATE TABLE galleryimage (
            imageID int(11) NOT NULL default '0',
            file char(64) NOT NULL default '',
            userID int(11) default NULL,
            PRIMARY KEY (imageID),
            KEY userID_idx (userID)
            ) TYPE=InnoDB;


            JBoss add a row with the following query:
            INSERT INTO galleryimage (imageID, file, userID) VALUES (?, ?, ?)


            But JBoss always put NULL into userID column. How can JBoss put the correct foreign key value?

            Thanks,
            Alexis

            • 3. Re: 1 to Many relationship
              alexis bergue Newbie

              Hi tdang,

              Thanks for your answer.

              My relationship works better now and I can insert rows into my table. But there is still a last issue. I have moved userID at the end of the table and removed NOT NULL parameter:

              CREATE TABLE galleryimage (
              imageID int(11) NOT NULL default '0',
              file char(64) NOT NULL default '',
              userID int(11) default NULL,
              PRIMARY KEY (imageID),
              KEY userID_idx (userID)
              ) TYPE=InnoDB;


              JBoss add a row with the following query:
              INSERT INTO galleryimage (imageID, file, userID) VALUES (?, ?, ?)


              But JBoss always put NULL into userID column. How can JBoss put the correct foreign key value?

              Thanks,
              Alexis