Foreign Key within Composite Primary Key and CMR

[cwolf2]

Hi,


I'm having a problem with CMR and composite primary keys, and however hard I try I just can't get it to work. I'd be grateful if someone could help me, or put me out of my misery by telling me it isn't possible.

What I'm trying to do is to setup a one-to-many foreign key mapping where the foreign key is part of a composite primary key - in my case between tables:

ord (primary key = orderid) and

ordline (primary key = orderid, orderlinenumber)

where ordline.orderid is a foreign key to table ord.

When I try to deploy (under both 3.2.1 and 3.2.2RC4) I get a duplicate column name error as JBOSS tries to create ordline.orderid twice.

If you have got this working could you let me see the relevant sections of your jbosscmp-jdbc.xml and ejb-jar.xml files, or let me know changes necessary to my deployment files (relevant sections of which are shown below).


Thanks,
Chris.

P.S. I can easily e-mail all the source files upon request.


ejb-jar.xml (abridged):
-----------------------

<ejb-jar>
<enterprise-beans>


<ejb-name>Order</ejb-name>

<cmp-field>
<field-name>orderID</field-name>
</cmp-field>

<cmp-field>
<field-name>customerName</field-name>
</cmp-field>

<cmp-field>
<field-name>orderDate</field-name>
</cmp-field>

<primkey-field>orderID</primkey-field>




<ejb-name>OrderLine</ejb-name>
<prim-key-class>chrisw.order.OrderLinePK</prim-key-class>

<cmp-field>
<field-name>orderLinePK</field-name>
</cmp-field>

<cmp-field>
<field-name>quantity</field-name>
</cmp-field>

<primkey-field>orderLinePK</primkey-field>


</enterprise-beans>




<ejb-relation>
<ejb-relation-name>Order-OrderLine</ejb-relation-name>

<ejb-relationship-role>
<ejb-relationship-role-name>order-orderlines</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>Order</ejb-name>
</relationship-role-source>

<cmr-field>
<cmr-field-name>orderLines</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role>
<ejb-relationship-role-name>orderline-order</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>OrderLine</ejb-name>
</relationship-role-source>

<cmr-field>
<cmr-field-name>order</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>



</ejb-jar>



jbosscmp-jdbc.xml (abridged):
-----------------------------

<jbosscmp-jdbc>

<enterprise-beans>


<ejb-name>Order</ejb-name>
<table-name>ord</table-name>

<cmp-field>
<field-name>orderID</field-name>
<column-name>orderid</column-name>
</cmp-field>

<cmp-field>
<field-name>customerName</field-name>
<column-name>customername</column-name>
</cmp-field>

<cmp-field>
<field-name>orderDate</field-name>
<column-name>orderdate</column-name>
</cmp-field>



<ejb-name>OrderLine</ejb-name>
<table-name>ordline</table-name>

<cmp-field>
<field-name>orderLinePK</field-name>

<property-name>orderID</property-name>
<column-name>orderid</column-name>


<property-name>orderLineNumber</property-name>
<column-name>orderlinenumber</column-name>

</cmp-field>

<cmp-field>
<field-name>quantity</field-name>
<column-name>quantity</column-name>
</cmp-field>



</enterprise-beans>



<ejb-relation>
<ejb-relation-name>Order-OrderLine</ejb-relation-name>
<foreign-key-mapping/>

<ejb-relationship-role>
<ejb-relationship-role-name>order-orderlines</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>orderID</field-name>
<column-name>orderid</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>

<ejb-relationship-role>
<ejb-relationship-role-name>orderline-order</ejb-relationship-role-name>
<key-fields/>
</ejb-relationship-role>
</ejb-relation>



<dependent-value-classes>
<dependent-value-class>
Order Line multi-part primary key
chrisw.order.OrderLinePK

<property-name>orderID</property-name>
<column-name>orderid</column-name>


<property-name>orderLineNumber</property-name>
<column-name>orderlinenumber</column-name>

</dependent-value-class>
</dependent-value-classes>

</jbosscmp-jdbc>

[cbuckley]

You do not need your primary key tag on the OrderLine in the ejb-jar.xml. Your primary key class need to have references to the two fields that act as your primary key, I would suggest getting this to work first, than move on to your relationship.

[cwolf2]

Hi,


Firstly can I thank you for your help here.

I have tried removing "<primkey-field>orderLinePK</primkey-field>" from the ejb-jar.xml file, but I still get the same error "ORA-00957: duplicate column name".

Could you elaborate on what additional references my primary key class needs to the two fields that act as my primary key? There are already references within jbosscmp-jdbc.xml in the dependent-value-class section and the cmp-field section for field orderLinePK.

The composite primary key of OrderLine seems to work fine when deployed without CMR. The problem occurs when I try to use CMR to create a relationship to Order using a foregin key which is part of OrderLine's composite primary key.


Thanks again,
Chris.



P.S. My beans deploy "properly" when I change the key-fields section to:

key-field>
<field-name>orderID</field-name>
<column-name>orderid2</column-name>
</key-field>

HOWEVER, I end up with a table which has two order id columns: orderid and orderid2.

[shivank]

Hi,
I am facing same problem.
Is any one of you able solve this problem ?
I will be grateful to you If you can help me solve this.


with regards
Shiva

[cwolf2]

Shiva,


First of all apologies for taking so long to reply to you - I've been out with a stomach bug for the last few days.

With Chris Buckley's kind help I have managed to change my *.xml files so that my beans will deploy properly. My plan is that once I have tested the beans (etc.) I will publish the working version for anyone who may be interested. This should be within a couple more days, however, if helpful to you I am more than happy to e-mail what I am working with at the moment (i.e. before I have finished testing).


Best regards,
Chris

[vijayakumarramakrishnan]

Hiii...
Ive got the same problem.. can u plz help me...
Vijay

[shivank]

Hi Chris,
Hope you fixed the stomach bug and your system working cool.

I 'll be very happy if you can e-mail me those *.xml files. My e-mail i.d is [e-mail].

Thanks a lot

with regards
Shiva

[ahaikio]

I'd like to get some help on this issue aswell. Please contact [e-mail]

[cwolf2]

Hi,

I have (somewhat later than expected) finished testing after making amendments suggested by Chris Buckley.

I have not been able to find a problem while testing (on 3.2.2). I have attached the source code in case anyone is interested in looking at it.


Chris.

[shy613]

Hi Chris,

I 'll be very happy if you can e-mail me those *.xml files. My e-mail i.d is [e-mail].

Thanks a lot

with regards
shy613

[devilbot]

Hi Chris,

Would you please mail me those related xml files? Many thanks to your help.

my email address: [e-mail]

Best regards,
Austin

[mikea-xoba]

if i understand your discussion, i believe i may be having a similar problem which i'm trying to tackle at the moment, which is basically the question:

"can a CMR field be part of a CMP primary key?"

and if so, how does one do it? i'm also wondering if xdoclet would do it properly, if it's possible at all.

[aloubyansky]

"can a CMR field be part of a CMP primary key?"

How do you imagine that?

If you mean whether you can use the same columns for CMP fields and for foreign key fields the CMR is mapped to, then the answer is yes, you can.

[mikea-xoba]

i think i see the point ---

CMP's must be set in the entity bean's ejbCreate method, which returns the primary key. and CMR's must be set in the beans ejbPostCreate method, which is called after creation. thus, the CMR fields formally can *not* be part of the primary key (correct me if i'm wrong).

but what you're saying is that a CMP primary key field and a CMR field as a foreign key can both share the same column in a database table. i tried that and it seems to work. to make things appear to be in 'normal form', i also hide that 'extra' CMP field (the one shared with the CMR field) by not exposing it in any ejb interface; its only set in ejbCreate(). to maintain primary key integrity with caching and everything else, i guess i should not expose the set...() CMR method in any interface either.

is this correct?

thanks again,
mike

[aloubyansky]

that's correct. Except for exposing CMR setter (?) what do you mean?