1 Reply Latest reply on Apr 25, 2002 12:27 AM by Jeff DeLong

    CMP 2.0 and many-to-many relationships

    Jeff DeLong Master

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

        • 1. Re: CMP 2.0 and many-to-many relationships
          Jeff DeLong Master

          I installed RC1 and at first it appeared the problem went away, which made it a little clearer what is happening. I think the error message: Violation of unique index in statement [INSERT INTO GROUPEJB_USERS_USEREJB_GROUP
          S (GroupEJB, UserEJB) VALUES ('LoanProcessor', 'Fred')] (notice RC1 the table name is all caps) is because it is trying to do this insert a second time. The real problem I think is that the table is not getting removed and re-created (even though I have set this in the jbosscmp-jdbc) and my client test code is starting up and trying to create the EJBs their relationships. Unfortunately I can't find the relationship table to remove it manually.