Error in one-to-many relationship

[kammau]

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

[aloubyansky]

Do you have accessors in ActionEJB for memberId?
If not, then remove cmp-field element from ejb-jar.xml and jbosscmp-jdbc.xml for memberId. It's CMR field, not CMP.

[kammau]

If I remove the cmp field element in ejb-jar.xml and jbosscmp-jdbc.xml, then I cannot have the method findByMemberIdAndStatusId in ActionEJB.
I tried this in both JBoss3.0.0 and JBoss3.0.3, But all do not work.

[aloubyansky]

Try this one

SELECT OBJECT(a) FROM Action AS a WHERE a.member.memberId = ?1 AND a.statusId = ?2

Note: not a.memberId, but a.member.memberId. Action doesn't have a cmp field, but has a cmr member. And member has a cmp field memberId.