0 Replies Latest reply on Apr 10, 2003 8:45 PM by jchu

    CMR Relation-Table Mapping - Help Please.

    jchu Newbie

      I have been unable to get CMR Relation Table Mapping to work. The symptons and
      relevant xml is below. Can you see something wrong, or can you think of
      something I should examine, or can you at least tell me that it works for you?

      Systems:
      1. RH 8.0 Linux, JBoss_3.0.6_Tomcat-4.1.18, Postgres 7.2 (or)
      2. Windows98, JBoss_3.0.6_Tomcat-4.1.18, mysql

      Entity Beans: Users, County
      Tables: users, counties, usercounty

      Clues:
      1. Jboss creates users, counties tables correctly.
      2. Jboss always claims that USERCOUNTY table is already created, whether it is
      present in the db (because I manually created it) or not.

      [UserBean] Table 'users' already exists <!-- true -->
      [CountyBean] Table 'counties' already exists <!-- true -->
      [CountyBean] Table 'USERCOUNTY' already exists <!-- false -->

      3. Jboss never actually creates the UserCounty table.
      4. If a client tries to set the 'Counties' CMR field in the Users EJB, it gets the
      EJBException below.
      5. If I run the same sql from psql, it executes OK.

      javax.ejb.EJBException: Load relation failed; CausedByException is:
      Column not found: COUNTYID in statement [SELECT countyid FROM USERCOUNTY WHERE (userid=0)]
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.execute(JDBCLoadRelationCommand.java:187)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.loadRelation(JDBCStoreManager.java:643)
      at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.load(JDBCCMRFieldBridge.java:839)
      at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.setInstanceValue(JDBCCMRFieldBridge.java:521)
      at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge.setValue(JDBCCMRFieldBridge.java:483)
      at org.jboss.ejb.plugins.cmp.bridge.EntityBridgeInvocationHandler.invoke(EntityBridgeInvocationHandler.java:126)
      at org.jboss.proxy.compiler.Runtime.invoke(Runtime.java:59)
      at com.gov.ejb.UserBean$Proxy.setCounties()
      at com.gov.ejb.UserBean.addCounty(UserBean.java:73)



      ejb-jar.xml (relevant portions)
      --------------------------------


      Models a user
      <ejb-name>UserBean</ejb-name>
      <local-home>com.gov.ejb.UserHome</local-home>
      com.gov.ejb.User
      <ejb-class>com.gov.ejb.UserBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.Integer</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>User</abstract-schema-name>
      <cmp-field>
      <field-name>address1</field-name>
      </cmp-field>

      <!-- !!!! snipped cmp-fields !!!! -->

      <cmp-field>
      <field-name>userName</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>userId</field-name>
      </cmp-field>
      <primkey-field>userId</primkey-field>

      <query-method>
      <method-name>findByUserName</method-name>
      <method-params>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <ejb-ql>SELECT OBJECT(o) FROM User AS o WHERE o.userName = ?1</ejb-ql>




      Models a county
      <ejb-name>CountyBean</ejb-name>
      <local-home>com.gov.ejb.CountyHome</local-home>
      com.gov.ejb.County
      <ejb-class>com.gov.ejb.CountyBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.Integer</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>County</abstract-schema-name>
      <cmp-field>
      <field-name>countyName</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>stateName</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>countyId</field-name>
      </cmp-field>
      <cmp-field>
      <field-name>entityId</field-name>
      </cmp-field>
      <primkey-field>countyId</primkey-field>

      <query-method>
      <method-name>findByNames</method-name>
      <method-params>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <ejb-ql>SELECT OBJECT(o) FROM County AS o WHERE o.stateName = ?1 AND o.countyName = ?2</ejb-ql>




      <ejb-relation>
      <ejb-relation-name>Users-Regions</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>user-has-regions</ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>UserBean</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>counties</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>region-has-users</ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>CountyBean</ejb-name>
      </relationship-role-source>
      </ejb-relationship-role>
      </ejb-relation>



      jbosscmp-jdbc.xml (relevant portions)
      -------------------------------------


      <ejb-name>UserBean</ejb-name>
      java:/RefNetDS
      <datasource-mapping>PostgreSQL</datasource-mapping>
      <create-table>true</create-table>
      <remove-table>false</remove-table>
      <table-name>users</table-name>
      <cmp-field>
      <field-name>address1</field-name>
      <column-name>address1</column-name>
      </cmp-field>

      <!-- snipped cmp-fields -->

      <cmp-field>
      <field-name>userName</field-name>
      <column-name>username</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>userId</field-name>
      <column-name>userid<column-name>
      </cmp-field>


      <ejb-name>CountyBean</ejb-name>
      java:/RefNetDS
      <datasource-mapping>PostgreSQL</datasource-mapping>
      <create-table>true</create-table>
      <remove-table>false</remove-table>
      <table-name>counties</table-name>
      <cmp-field>
      <field-name>countyName</field-name>
      <column-name>countyname</column-name>
      <jdbc-type>VARCHAR</jdbc-type>
      <sql-type>varchar(40)</sql-type>

      </cmp-field>
      <cmp-field>
      <field-name>stateName</field-name>
      <column-name>statename</column-name>
      <jdbc-type>VARCHAR</jdbc-type>
      <sql-type>varchar(20)</sql-type>
      </cmp-field>
      <cmp-field>
      <field-name>countyId</field-name>
      <column-name>countyid</column-name>
      <jdbc-type>INTEGER</jdbc-type>
      <sql-type>integer</sql-type>
      </cmp-field>
      <cmp-field>
      <field-name>entityId</field-name>
      <column-name>entityid</column-name>
      <jdbc-type>INTEGER</jdbc-type>
      <sql-type>serial</sql-type>
      </cmp-field>


      <ejb-relation>
      <ejb-relation-name>Users-Regions</ejb-relation-name>
      <relation-table-mapping>
      <table-name>usercounty</table-name>
      <create-table>true</create-table>
      <remove-table>false</remove-table>
      </relation-table-mapping>
      <ejb-relationship-role>
      <ejb-relationship-role-name>user-has-regions</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>userId</field-name>
      <column-name>userid</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>region-has-users</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>countyId</field-name>
      <column-name>countyid</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>