Compound Foreign Primary Key
JBoss: 4.0.3SP1
EJB: 2.1
Hello all,
Situation
I have 3 EJB (CMP) objects:
ItemGroup
-------------------
itemGroupId [PK]
name
CustomerGroup
-------------------
customerGroupId [PK]
name
Item
-------------------
itemGroup [PK][FK]
customerGroup [PK][FK]
type
The relation i want between the 3 is as follows:
ItemGroup -|-----< Item >-----|- CustomerGroup
So i have an ItemGroup which holds more than one Items. Each Item is linked to a CustomerGroup (a Customer group can have more than one Items attached to it). Each ItemGroup can contain only one instance of an Item of a certain CustomerGroup.
Creating the relations is no biggie. The problem i ran into is getting the two foreign keys in Item (ItemGroup and CustomerGroup) to be the compound primary key of Item.
Attempts
I've tried to define the foreign key fields with both @ejb.relation and @ejb.persistence (XDoclet). I did this like so:
public abstract class ItemBean implements EntityBean
{
/**
* @ejb.persistence column-name="ITEM_GROUP_ID" jdbc-type="INTEGER" sql-type="INTEGER"
* @ejb.pk-field
*
* @ejb.relation
* name="Item-ItemGroup"
* role-name="Item-has-one-ItemGroup"
* target-ejb="ItemGroup"
* target-role-name="ItemGroup-has-many-Items"
* target-multiple="true"
*
* @ejb.interface-method view-type="local"
*
* @jboss.relation
* related-pk-field="itemGroupId"
* fk-column="ITEM_GROUP_ID"
* fk-constraint="true"
*/
public abstract ItemGroupLocal getItemGroup()
/** @ejb.interface-method view-type="local" */
public abstract void setItemGroup(ItemGroupLocal itemGroup);
/**
* @ejb.persistence column-name="CUSTOMER_GROUP_ID" jdbc-type="INTEGER" sql-type="INTEGER"
* @ejb.pk-field
*
* @ejb.relation
* name="Item-CustomerGroup"
* role-name="Item-has-one-CustomerGroup"
* target-ejb="CustomerGroup"
* target-role-name="CustomerGroup-has-many-Items"
* target-multiple="true"
*
* @ejb.interface-method view-type="local"
*
* @jboss.relation
* related-pk-field="itemGroupId"
* fk-column="CUSTOMER_GROUP_ID"
* fk-constraint="true"
*/
public abstract CustomerGroupLocal getCustomerGroup()
/** @ejb.interface-method view-type="local" */
public abstract void setCustomerGroup(CustomerGroupLocal customerGroup);
[...]
}
My ejb-jar.xml (generated by XDoclet):
<ejb-jar> <entity > <ejb-name>Item</ejb-name> [...] <persistence-type>Container</persistence-type> <prim-key-class>com.company.ejb.interfaces.ItemPK</prim-key-class> <cmp-version>2.x</cmp-version> <abstract-schema-name>Item</abstract-schema-name> <cmp-field > <description><![CDATA[]]></description> <field-name>itemGroup</field-name> </cmp-field> <cmp-field > <description><![CDATA[]]></description> <field-name>customerGroup</field-name> </cmp-field> <cmp-field > <description><![CDATA[]]></description> <field-name>type</field-name> </cmp-field> <query> <query-method> <method-name>findItemGroupIdsByType</method-name> <method-params> <method-param>java.lang.Integer</method-param> <method-param>java.lang.Integer</method-param> <method-param>java.lang.Integer</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT DISTINCT item.itemGroup.itemGroupId FROM Item item WHERE item.type = ?1]]></ejb-ql> </query> </entity> [...] <ejb-relation > <ejb-relation-name>Item-ItemGroup</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>Item-has-one-ItemGroup</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>Item</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>itemGroup</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>ItemGroup-has-many-Items</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>ItemGroup</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>items</cmr-field-name> <cmr-field-type>java.util.Collection</cmr-field-type> </cmr-field> </ejb-relationship-role> </ejb-relation> [...] </ejb-jar >
When i tried to deploy the project to JBoss, i got the following error:
org.jboss.deployment.DeploymentException: Error compiling EJB-QL statement 'SELECT COUNT(DISTINCT item.itemGroup.itemGroupId) FROM Item item WHERE item.type = ?1'; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.UnknownPathException: In path field is not a cmr field: at line 1, column 42. Encountered: "itemGroup" after: "item.")
I read on the internet that this is caused because in ejb-jar.xml the field 'itemGroup' in 'item' is defined as both an '<cmp-field>' as an '<cmr-field>'. I guess JBoss get's confused or something.
I can't use the @ejb.pk-field without the @ejb.persistence tag, XDoclet then fails to create a proper ItemPK class (which holds the reference to the ItemGroup and CustomerGroup foreign keys). Using the @ejb.persistence implies a <cmp-field> entry in ejb-jar.xml.
When i remove the finder elements, it deploys alright, but when i try to create a new Item, i get an exception stating the primary key must be set in the ejbCreate method (i put it in the ejbPostCreate). If i put the setting of the foreign (primary) keys in the ejbCreate, it tells me foreign keys must be set after the ejbCreate method (in ejbPostCreate). So what do i do to that?
My Question
So my question is: can i get what i want? Can i create a compound primary key containing 2 foreign keys? I have searched the internet for 2 days and didn't find an answer. I did find some threads on other forums with a similar question but there was no answer. Does anyone know how to do this, or is the question just too stupid/n00b to answer? Is it even possible to create a foreign primary key with EJB 2.1? I think it should be, how else can people create a relation-table with extra fields?
Can anyone help me?
