Error in one-to-many relationship
kammau Oct 3, 2002 10:15 AMHi 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