Duplicate column error with composite primary key - JBoss 3.
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.