EJB relations an PK fields

[apurrichus]

Hi, I am Using Container Managed Persistence with JBOSS, and I have a problem when i try to insert a record on an ejb that its primary key makes reference to another EJB, i've mapped the field as CMP, but when i set the ejb-relation the sql is corrupt....

I have the next database Model:

IFX_PRICING
------------------------
 ID NOT NULL VARCHAR2(15) ---> pk
 CUSTOMER NUMBER(8)
 TOTAL NOT NULL NUMBER(25,13)
 CREATION_DATE NOT NULL DATE

IFX_PRICINGSERVICE
 ---------------------------------
 PRICING NOT NULL VARCHAR2(15) -->PK FK from IFX_PRICING
 SERVICE NOT NULL NUMBER(3) -->PK
 TOTAL NOT NULL NUMBER(25,13)

Then I map the CMP Entity beans, i set the access Methods for PricingBean like:
// Access methods for relationship fields
public abstract Collection getPricingServices();
public abstract Collection getPricingServiceFeatures();


and I define my relations as:

<ejb-relation>
 <ejb-relation-name>Pricing-PricingService</ejb-relation-name>
 <ejb-relationship-role>
 <multiplicity>One</multiplicity>
 <relationship-role-source>
 <ejb-name>Pricing</ejb-name>
 </relationship-role-source>
 <cmr-field>
 <cmr-field-name>pricingServices</cmr-field-name>
 <cmr-field-type>java.util.Collection</cmr-field-type>
 </cmr-field>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <multiplicity>Many</multiplicity>
 <relationship-role-source>
 <ejb-name>PricingService</ejb-name>
 </relationship-role-source>
 <cmr-field>
 <cmr-field-name>pricing</cmr-field-name>
 </cmr-field>
 </ejb-relationship-role>
 </ejb-relation>

First, If I don't define the <cmr-field-name>pricing</cmr-field-name> i get :

Could not create entity:java.sql.SQLException: ORA-00904: "PRICING_PRICINGSERVICES": invalid identifier

Printing the sql script:
[org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PricingService] Insert Entity SQL: INSERT INTO IFX_PRICINGSERVICE (PRICING, SERVICE, TOTAL, Pricing_pricingServices) VALUES (?, ?, ?, ?)

I don't understand why if I only want to define a unidirectional relation...

Second, If I set the field on my deployment descriptor (bidirectional), when I try to insert a record on my PRICINGSERVICE EJB, i get :

Could not create entity:java.sql.SQLException: ORA-00957: duplicate column name

Printing the sql script:
[org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.PricingService] Insert Entity SQL: INSERT INTO IFX_PRICINGSERVICE (PRICING, SERVICE, TOTAL, Pricing_pricingServices) VALUES (?, ?, ?, ?)

I guess is for the pk, that is cmp and is defined as cmr too, when i define my relationship, If i omit the ejb-relation I can insert a new record, but I need to get all details of my pricingservices associated to my Pricing.... (I don't want to do it with a select method)

someone answer me that i've just discovered why experts recomends not to use composite primary key with ejb relations, but form me this is weird... because it would be limiting the design..

[aloubyansky]

You need to map foreign key fields to primary key columns in the jbosscmp-jdbc.xml

[apurrichus]

I already have the relationship mapped on jbosscmp-jdbc.xml, but the problem continues

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC '-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN' 'http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd'>
<jbosscmp-jdbc>
 <defaults>
 <datasource>java:/jdbc/IntraOraDS</datasource>
 <datasource-mapping>Oracle9i</datasource-mapping>
 <create-table>false</create-table>
 <remove-table>false</remove-table>
 <read-only>false</read-only>
 <read-time-out>30000</read-time-out>
 <row-locking>false</row-locking>
 <pk-constraint>true</pk-constraint>
 <fk-constraint>false</fk-constraint>
 </defaults>
 <enterprise-beans>
 <entity>
 <ejb-name>Pricing</ejb-name>
 <table-name>IFX_PRICING</table-name>
 <cmp-field>
 <field-name>id</field-name>
 <column-name>ID</column-name>
 </cmp-field>
 <cmp-field>
 <field-name>customer</field-name>
 <column-name>CUSTOMER</column-name>
 </cmp-field>
 <cmp-field>
 <field-name>total</field-name>
 <column-name>TOTAL</column-name>
 </cmp-field>
 <cmp-field>
 <field-name>creationDate</field-name>
 <column-name>CREATION_DATE</column-name>
 </cmp-field>
 </entity>
 <entity>
 <ejb-name>PricingService</ejb-name>
 <table-name>IFX_PRICINGSERVICE</table-name>
 <cmp-field>
 <field-name>pricingId</field-name>
 <column-name>PRICING</column-name>
 </cmp-field>
 <cmp-field>
 <field-name>serviceId</field-name>
 <column-name>SERVICE</column-name>
 </cmp-field>
 <cmp-field>
 <field-name>total</field-name>
 <column-name>TOTAL</column-name>
 </cmp-field>
 </entity>
 </enterprise-beans>

 <relationships>
 <ejb-relation>
 <ejb-relation-name>Pricing-PricingService</ejb-relation-name>
 <foreign-key-mapping/>
 <ejb-relationship-role>
 <ejb-relationship-role-name>Pricing-has-PrincingServices</ejb-relationship-role-name>
 <key-fields>
 <key-field>
 <field-name>id</field-name>
 <column-name>pricing</column-name>
 </key-field>
 </key-fields>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <ejb-relationship-role-name>PricingService-belongs-Pricing</ejb-relationship-role-name>
 <key-fields/>
 </ejb-relationship-role>
 </ejb-relation>
 </relationships>
</jbosscmp-jdbc>

[apurrichus]

NOBODY CAN HELP???

[sbrbot]

Bidirectional relation between Pricing and PricingService (one-to-many);

part of ejb-jar.xml:

 <ejb-relation>
 <ejb-relation-name>Pricing-has-PricingServices</ejb-relation-name>
 <ejb-relationship-role>
 <description>pricing</description>
 <ejb-relationship-role-name>PricingRelationshipRole</ejb-relationship-role-name>
 <multiplicity>One</multiplicity>
 <relationship-role-source>
 <description>pricing</description>
 <ejb-name>Pricing</ejb-name>
 </relationship-role-source>
 <cmr-field>
 <description>pricingService</description>
 <cmr-field-name>pricingServices</cmr-field-name>
 <cmr-field-type>java.util.Collection</cmr-field-type>
 </cmr-field>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <description>pricingService</description>
 <ejb-relationship-role-name>PricingServiceRelationshipRole</ejb-relationship-role-name>
 <multiplicity>Many</multiplicity>
 <relationship-role-source>
 <description>pricingService</description>
 <ejb-name>PricingService</ejb-name>
 </relationship-role-source>
 <cmr-field>
 <description>pricing</description>
 <cmr-field-name>pricing</cmr-field-name>
 </cmr-field>
 </ejb-relationship-role>
 </ejb-relation>

part of jbosscmp_jdbc.xml:

 <ejb-relation>
 <ejb-relation-name>Pricing-has-PricingServices</ejb-relation-name>
 <ejb-relationship-role>
 <ejb-relationship-role-name>PricingRelationshipRole</ejb-relationship-role-name>
 <key-fields>
 <key-field>
 <field-name>id</field-name>
 <column-name>PRICING</column-name>
 </key-field>
 </key-fields>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <ejb-relationship-role-name>PricingServiceRelationshipRole</ejb-relationship-role-name>
 </ejb-relationship-role>
 </ejb-relation>

Tke into account that all NOT NULL database fields have to be parameters od ejbCreate() method.

[mamato]

I'm trying to do the same with xDoclet but as now I've not been able to find a solution. I've also tryed middlegen but it gaves a warning "JBoss 3.0 doesn't support fields that are part of CMR to be CMP at the same time." is it true ? How do I work around ?

[apurrichus]

Thanks a lot Stjepan, until now i review this post (i though it was lost)... Today I prove your xml conf files and it works like I expected, i had omitted the relation, because i was unable to map it... so, some questions more about jbosscmp-jdbc.xml:

why you omit the map (<foreign-key-mapping/>) how does it works?
why <key-fields/> are not used on PricingServiceRelationshipRole ?
is true, that is better avoid this type of relations (composite pk) and only use one field primary keys?
can you recommend me some guide for jboss? all that i had read was limited...

Thanks again...

someone knows how to map it with xdoclet as mamato says?