6 Replies Latest reply on Aug 24, 2004 12:56 PM by apurrichus

    EJB relations an PK fields

    apurrichus Newbie

      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..

        • 1. Re: EJB relations an PK fields
          Alexey Loubyansky Master

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

          • 2. Re: EJB relations an PK fields
            apurrichus Newbie

            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>
            


            • 3. Re: EJB relations an PK fields
              apurrichus Newbie

              NOBODY CAN HELP???

              • 4. Re: EJB relations an PK fields
                Stjepan Brbot Newbie

                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.

                • 5. Re: EJB relations an PK fields
                  Massimo Amato Newbie

                  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 ?

                  • 6. Re: EJB relations an PK fields
                    apurrichus Newbie

                    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?