3 Replies Latest reply on Oct 4, 2002 2:51 AM by Alexey Loubyansky

    Error in one-to-many relationship

    Kammau Newbie

      Hi All,
      I am in trouble with one-to-many relationship in CMP.
      I have two tables
      Table 1: member
      Columns:
      member_id
      member_name
      member_address

      Table 2: action
      Columns
      action_id
      action_name
      status_id
      member_id (FOREIGN KEY (member_id) REFERENCES member(member_id))

      In the ejb-jar.xml
      ...

      <ejb-name>MemberEJB</ejb-name>
      <local-home>MemberLocalHome</local-home>
      MemberLocal
      <ejb-class>MemberEJB</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.String</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>Member</abstract-schema-name>
      <cmp-field>
      <field-name>memberId</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>memberName</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>memberAddress</field-name>
      </cmp-field>
      <primkey-field>memberId</primkey-field>


      <ejb-name>ActionEJB</ejb-name>
      <local-home>ActionLocalHome</local-home>
      ActionLocal
      <ejb-class>ActionEJB</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.String</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>Action</abstract-schema-name>
      <cmp-field>
      <field-name>actionId</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>actionName</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>stausId</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>memberId</field-name>
      </cmp-field>
      <primkey-field>actionId</primkey-field>

      ...
      <ejb-relation>
      <ejb-relation-name>Member-Action</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Member-Has-Actions</ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>MemberEJB</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>actions</cmr-field-name>
      <cmr-field-type>java.util.Set</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Actions-belong-to-Member</ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>ActionEJB</ejb-name>
      </relationship-role-source>
      </ejb-relationship-role>
      </ejb-relation>
      ...

      In jbosscmp-jdbc.xml
      ...

      <ejb-name>MemberEJB</ejb-name>
      <table-name>member</table-name>
      <cmp-field>
      <field-name>memberId</field-name>
      <column-name>member_id</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>memberName</field-name>
      <column-name>member_name</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>memberAddress</field-name>
      <column-name>member_address</column-name>
      </cmp-field>



      <ejb-name>ActionEJB</ejb-name>
      <table-name>action</table-name>
      <cmp-field>
      <field-name>actionId</field-name>
      <column-name>action_id</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>actionName</field-name>
      <column-name>action_name</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>statusId</field-name>
      <column-name>status_id</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>memberId</field-name>
      <column-name>member_id</column-name>
      </cmp-field>

      ...
      <ejb-relation>
      <ejb-relation-name>Member-Action</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Member-Has-Actions</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>memberId</field-name>
      <column-name>member_id</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>TestRelation-Has-ActionHistory</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>
      ...

      If I create an action, error will occur. The error is like this
      "Column member_id is specified more than once in INSERT OR UPDATE statement."
      Then I comment the cmp field mapping of memberId in ActionEJB in ejb-jar.xml and jbosscmp-jdbc.xml,
      I can create an action successfully.

      However, I now need a find by method in ActionEJB. The method is findByMemberIdAndStatusId,
      in the ejb-jar.xml

      <query-method>
      <method-name>findByMemberIdAndStatusId</method-name>
      <method-params>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <ejb-ql>
      <![CDATA[SELECT OBJECT(a) FROM Action AS a WHERE a.memberId = ?1 AND a.statusId = ?2]]>
      </ejb-ql>


      Since I have commented the memberId field of ActionEJB in ejb-jar.xml, I cannot deploy the using above xml
      because memberId is not a valid field.

      How can I continue? I need your help.
      Thank you in advance.

      Kammau