0 Replies Latest reply on Aug 4, 2003 9:05 AM by cwolf2

    Duplicate column error with composite primary key - JBoss 3.

    cwolf2 Newbie

      Hi,

      I am trying to get a one-many relationship to work using CMR where there is a composite primary key and part of that composite key is a foreign key.

      The relationship is between Order (primary key = orderID) and OrderLine (primary key, OrderLinePK = orderID + orderLineNumber). I get a duplicate column error when JBoss tries to create the table for OrderLine or (once I have created the table by "hand") when JBoss tries to insert a row into it.

      I had understood (from topic "JBoss 3.0.7 generates a duplicate column with CMR" - Juha Lindfors 4th July 2003 10:25am) that foreign keys as part of composite primary keys would be supported by 3.2.1, but I can't get it to work. Additionally, I cannot find any examples in "JBoss Administration and Development Third Edition (3.2.x Series)" to compare against.


      My ejb-jar.xml:

      <!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/j2ee/dtds/ejb-jar_2_0.dtd">

      <ejb-jar>
      <enterprise-beans>


      <ejb-name>Product</ejb-name>
      <local-home>chrisw.order.ProductLocalHome</local-home>
      chrisw.order.Product
      <ejb-class>chrisw.order.ProductBean</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>ProductBean</abstract-schema-name>

      <cmp-field>
      <field-name>productID</field-name>
      </cmp-field>

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

      <cmp-field>
      <field-name>description</field-name>
      </cmp-field>

      <cmp-field>
      <field-name>basePrice</field-name>
      </cmp-field>

      <primkey-field>productID</primkey-field>







      <ejb-name>Order</ejb-name>
      <local-home>chrisw.order.OrderLocalHome</local-home>
      chrisw.order.Order
      <ejb-class>chrisw.order.OrderBean</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>OrderBean</abstract-schema-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>
      <local-home>chrisw.order.OrderLineLocalHome</local-home>
      chrisw.order.OrderLine
      <ejb-class>chrisw.order.OrderLineBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>chrisw.order.OrderLinePK</prim-key-class>
      False

      <cmp-version>2.x</cmp-version>

      <abstract-schema-name>OrderLineBean</abstract-schema-name>

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

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

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





      <ejb-name>TestOrderPkg</ejb-name>
      chrisw.order.TestOrderPkgHome
      chrisw.order.TestOrderPkgRI
      <ejb-class>chrisw.order.TestOrderPkgBean</ejb-class>
      <session-type>Stateless</session-type>
      <transaction-type>Container</transaction-type>


      </enterprise-beans>




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

      <ejb-relationship-role>
      <ejb-relationship-role-name>prod-order-lines</ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>Product</ejb-name>
      </relationship-role-source>

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

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

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

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




      <assembly-descriptor>
      <container-transaction>


      <ejb-name>Product</ejb-name>
      <method-intf>Remote</method-intf>
      <method-name>*</method-name>



      <ejb-name>Product</ejb-name>
      <method-intf>Local</method-intf>
      <method-name>*</method-name>



      <ejb-name>Order</ejb-name>
      <method-intf>Remote</method-intf>
      <method-name>*</method-name>



      <ejb-name>OrderLine</ejb-name>
      <method-intf>Remote</method-intf>
      <method-name>*</method-name>


      <trans-attribute>Required</trans-attribute>

      </container-transaction>

      </assembly-descriptor>


      </ejb-jar>

      *************************************************************************************************

      My jbosscmp-jdbc.xml:

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

      java:/OracleDS
      <datasource-mapping>Oracle9i</datasource-mapping>


      <enterprise-beans>


      <ejb-name>Product</ejb-name>
      <table-name>product</table-name>

      <cmp-field>
      <field-name>productID</field-name>
      <column-name>productid</column-name>
      </cmp-field>

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

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

      <cmp-field>
      <field-name>basePrice</field-name>
      <column-name>baseprice</column-name>
      </cmp-field>




      <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>OrderLine-Product</ejb-relation-name>
      <foreign-key-mapping/>

      <ejb-relationship-role>
      <ejb-relationship-role-name>prod-order-lines</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>productID</field-name>
      <column-name>productid</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>

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

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


      ********************************************************************************************************************

      From the JBoss server log:

      org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.OrderLine] Executing SQL: CREATE TABLE ORDLINE (orderid VARCHAR2(255) NOT NULL, orderlinenumber NUMBER(10) NOT NULL, quantity NUMBER(10) NOT NULL, productid VARCHAR2(255), orderid VARCHAR2(255), CONSTRAINT PK_ORDLINE PRIMARY KEY (orderid, orderlinenumber))


      [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.OrderLine] Insert Entity SQL: INSERT INTO ORDLINE (orderid, orderlinenumber, quantity, orderid, productid) VALUES (?, ?, ?, ?, ?)


      Any suggestions would be much appreciated.


      Thanks,
      Chris.