3 Replies Latest reply on Jul 7, 2003 9:38 PM by Yevgeny Studenikin

    JBoss 3.0.7 generates a duplicate column with CMR

    Yevgeny Studenikin Newbie

      Hello,

      I have a stange problem. When I use relationship, container generates a duplicate column which happens to be a primary key. The following exception is generated:

      org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (com.inet.tds.SQLException: Msg 2705, Level 16, State 3, Line 1, Sqlstate 01000
      [BOX1]Column names in each table must be unique. Column name 'CustomerID'
      in table 'Users_Temp' is specified more than once.)
      .....

      I am trying to establish a 1:N CMR between Customer and Users with cascade delete. If I don't apply relationship, everything is working fine. If I create the tables manually and try to insert a row in Users_Temp table with JBoss, it generates a very surprising SQL statement, like this:
      INSERT INTO Users_Temp (UserName, CustomerID, ..., ..., CustomerID) VALUES (...);

      This is what I've got:

      Customer:
      /**
      * @ejb.bean name="Customer"
      * description="Persistent customer information"
      * display-name="This bean represents a customer record"
      * type="CMP"
      * cmp-version="2.x"
      * view-type="local"
      * schema="Customer"
      *
      * @ejb.util generate="physical"
      *
      * @ejb.interface extends="javax.ejb.EJBLocalObject"
      * @ejb.home extends="javax.ejb.EJBLocalHome" generate="local"
      *
      * @ejb.value-object
      * name="Customer"
      * extends="java.lang.Object"
      * match="*"
      *
      * @ejb.transaction type="Required"
      *
      * @ejb.finder signature="java.util.Collection findAll()"
      *
      * @ejb.persistence table-name="Customers_Temp"
      *
      * @jboss.read-ahead strategy="none"
      */

      public abstract class CustomerBean implements EntityBean {

      /**
      * @param data
      * @ejb.interface-method view-type="local"
      */
      public abstract void setCustomerValue(CustomerValue data);
      /**
      * @return CustomerValue
      * @ejb.interface-method view-type="local"
      */
      public abstract CustomerValue getCustomerValue();

      //////////////////////////////////////////////
      // Relationship of Customer with its Users: //
      //////////////////////////////////////////////

      /**
      * @ejb.interface-method view-type="local"
      *
      * @ejb.value-object
      * aggregate="test.ejb.entity.UserValue"
      * aggregate-name="Users"
      * members="test.ejb.entity.User"
      * members-name="UserValue"
      * relation="external"
      * type="Collection"
      *
      * @ejb.relation
      * name="Customer-User"
      * role-name="customer-has-users"
      * target-ejb="User"
      * target-role-name="user-belongs-to-customer"
      * target-cascade-delete="yes"
      */
      public abstract Collection getUsers();
      /**
      * @param users
      * @ejb.interface-method view-type="local"
      */
      public abstract void setUsers(Collection users);


      /**
      * @return int
      *
      * @ejb.persistent-field
      * @ejb.pk-field
      * @--ejb.interface-method view-type="local"
      *
      * @ejb.persistence column="CustomerID"
      */
      public abstract int getCustomerId();
      /**
      * Sets the customerID.
      * @param customerID The customerID to set
      * @--ejb.interface-method view-type="local"
      */
      public abstract void setCustomerId(int customerID);
      //...
      }

      User:

      /**
      * @ejb.bean name="User"
      * description="Persistent user information"
      * display-name="This bean represents a user record"
      * type="CMP"
      * cmp-version="2.x"
      * view-type="local"
      * schema="User"
      *
      * @ejb.util generate="physical"
      *
      * @ejb.interface extends="javax.ejb.EJBLocalObject"
      * @ejb.home extends="javax.ejb.EJBLocalHome" generate="local"
      *
      * @ejb.value-object
      * name="User"
      * extends="java.lang.Object"
      * match="*"
      *
      * @ejb.transaction type="Required"
      *
      * @ejb.finder signature="java.util.Collection findAll()"
      *
      * @ejb.pk class="test.ejb.entity.UserPK"
      *
      * @ejb.persistence table-name="Users_Temp"
      */
      public abstract class UserBean implements EntityBean {

      /**
      * @param data
      * @ejb.interface-method view-type="local"
      */

      public abstract void setUserValue(UserValue data);
      /**
      * @return UserValue
      * @ejb.interface-method view-type="local"
      */

      public abstract UserValue getUserValue();

      ////////////////////////////////
      // Relationship with Customer //
      ////////////////////////////////
      /**
      *
      * @--ejb.interface-method view-type="local"
      *
      * @ejb.relation
      * name="Customer-User"
      * target-ejb="Customer"
      * role-name="user-belongs-to-customer"
      * target-role-name="customer-has-users"
      *
      * @jboss.relation
      * related-pk-field="customerId"
      * fk-column="CustomerID"
      */
      public abstract Customer getCustomer();

      /**
      * @--ejb.interface-method view-type="local"
      */
      public abstract void setCustomer();

      /////////////////////////////////////////////////
      // COMPOUND PRIMARY KEY: UserName + CustomerID //
      /////////////////////////////////////////////////

      /**
      * @return String
      *
      * @ejb.persistent-field
      * @ejb.pk-field
      * @--ejb.interface-method view-type="local"
      *
      * @ejb.persistence column="UserName"
      */
      public abstract String getUserName();
      /**
      * Sets the userName.
      * @param userName The userName to set
      */
      public abstract void setUserName(String userName);

      /**
      * @return int
      *
      * @ejb.persistent-field
      * @ejb.pk-field
      * @--ejb.interface-method view-type="local"
      *
      * @ejb.persistence column="CustomerID"
      */
      public abstract int getCustomerId();
      /**
      * Sets the customerId.
      * @param customerId The customerId to set
      */
      public abstract void setCustomerId(int customerId);
      //...
      }

      Note: Compound primary key is not a problem, when I make only CustomerID the primary key, the duplicate column is still generated (and causes the exception)

      ejb-jar.xml:
      ...
      <!-- Relationships -->

      <ejb-relation >
      <ejb-relation-name>Customer-User</ejb-relation-name>

      <ejb-relationship-role >
      <ejb-relationship-role-name>user-belongs-to-customer</ejb-relationship-role-name>
      Many
      <relationship-role-source >
      <ejb-name>User</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>customer</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>

      <ejb-relationship-role >
      <ejb-relationship-role-name>customer-has-users</ejb-relationship-role-name>
      One
      <relationship-role-source >
      <ejb-name>Customer</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>users</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>

      </ejb-relation>

      ...

      jbosscmp-jdbc.xml:


      <ejb-relation>
      <ejb-relation-name>Customer-User</ejb-relation-name>

      <foreign-key-mapping/>

      <ejb-relationship-role>
      <ejb-relationship-role-name>user-belongs-to-customer</ejb-relationship-role-name>
      <key-fields/>

      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>customer-has-users</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>customerId</field-name>
      <column-name>CustomerID</column-name>
      </key-field>
      </key-fields>

      </ejb-relationship-role>
      </ejb-relation>


      Note: in ejb-jar.xml and jbosscmp-jdbc.xml there are no duplicates.

      I tried to make it many different ways. However, when I apply CMR, it always does that.

      I wonder if anybody had anything similar, or has any ideas why it's happening.

      Thanks a lot!