CMP 2.0 and many-to-many relationships
jeffdelong Apr 23, 2002 12:59 PMI am having a problem with a many-to-many relationship with JBoss 3.0.0-beta (Feb 21). I have two Entity EJBs, a GroupEJB and UserEJB. They have a many-to-many relationship, defined as "Membership" in the ejb-jar.xml file. The Tables have been specified as ORG_USER, ORG_GROUP, and ORG_MEMBERSHIP in the jbosscmp_jdbc.xml file. JBoss creates the first two tables correctly, but creates (?) the relationship table as GroupEjb_users_UserEJB_group, although I cannot find this table in my schema. Furthermore, when attempting to create the EJBs and populate the tables (with users Martin, Fred, etc. where Martin is a member of group LoanProcessor and Fred is a member of Group LoanProcessor, I get the following exception
10:24:22,643 ERROR [GlobalTxEntityMap] Store failed on entity: LoanProcessor
javax.ejb.EJBException: Could insert relations into GroupEJB_users_UserEJB_group
s
Embedded Exception
Violation of unique index in statement [INSERT INTO GroupEJB_users_UserEJB_group
s (GroupEJB, UserEJB) VALUES ('LoanProcessor', 'Fred')]
at org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertRelationsCommand.execute(JDB
CInsertRelationsCommand.java:78)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.insertRelations(JDBCS
toreManager.java:481)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.synchronizeRelationDa
ta(JDBCStoreManager.java:443)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.storeEntity(JDBCStore
Manager.java:424)
at org.jboss.ejb.plugins.CMPPersistenceManager.storeEntity(CMPPersistenc
eManager.java:428)
at org.jboss.ejb.EntityContainer.storeEntity(EntityContainer.java:677)
at org.jboss.ejb.GlobalTxEntityMap.syncEntities(GlobalTxEntityMap.java:8
9)
at org.jboss.ejb.GlobalTxEntityMap$GlobalTxEntityMapCleanup.beforeComple
tion(GlobalTxEntityMap.java:158)
at org.jboss.tm.TxCapsule.doBeforeCompletion(TxCapsule.java:1317)
at org.jboss.tm.TxCapsule.commit(TxCapsule.java:325)
at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:76)
It appears the groupName is a unique index on GroupEJB_users_UserEJB_group, although this is not what I want (since this table supports many-to-many relationships). Any help is appreciated. Thanks.
I don't seem to be albe to attach the jbosscmp-jdbc file, so I have copied it below:
<jbosscmp-jdbc>
java:/OracleDS
<type-mapping>Oracle8</type-mapping>
<create-table>true</create-table>
<remove-table>true</remove-table>
true
300
<select-for-update>false</select-for-update>
<pk-constraint>false</pk-constraint>
<enterprise-beans>
<ejb-name>UserEJB</ejb-name>
<table-name>ORG_USER</table-name>
java:/OracleDS
<type-mapping>Oracle8</type-mapping>
true
300
<select-for-update>false</select-for-update>
<pk-constraint>false</pk-constraint>
<cmp-field>
<field-name>userName</field-name>
<column-name>USERNAME</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>email</field-name>
<column-name>EMAIL</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>qualification</field-name>
<column-name>QUALIFICATION</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<ejb-name>GroupEJB</ejb-name>
<table-name>ORG_GROUP</table-name>
java:/OracleDS
<type-mapping>Oracle8</type-mapping>
true
300
<select-for-update>false</select-for-update>
<pk-constraint>false</pk-constraint>
<cmp-field>
<field-name>groupName</field-name>
<column-name>GROUPNAME</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>email</field-name>
<column-name>EMAIL</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
<cmp-field>
<field-name>qualification</field-name>
<column-name>QUALIFICATION</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(50)</sql-type>
</cmp-field>
</enterprise-beans>
<ejb-relation>
<ejb-relation-name>Membership</ejb-relation-name>
<table-mapping>
<table-name>ORG_MEMBERSHIP</table-name>
<ejb-relationship-role>
<ejb-relationship-role-name>groups-have-users</ejb-relationship-role-name>
<table-key-fields>
<table-key-field>
<field-name>groupName</field-name>
<column-name>GROUPNAME</column-name>
</table-key-field>
</table-key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>users-belong-to-groups</ejb-relationship-role-name>
<table-key-fields>
<table-key-field>
<field-name>userName</field-name>
<column-name>USERNAME</column-name>
</table-key-field>
</table-key-fields>
</ejb-relationship-role>
</table-mapping>
</ejb-relation>
</jbosscmp-jdbc>