JBoss 3.0.7 generates a duplicate column with CMR

[studenik]

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!

[juhalindfors]

Foreign keys as part of composite primary keys is not supported until 3.2.x series.

[studenik]

Hi Juha,

Thank you for your reply.

> Foreign keys as part of composite primary keys is not
> supported until 3.2.x series.

1. I tried not to use composite primary key, but only CMR. The result was the same - a duplicate. When I remove the relationship, everything is fine with or without composite primary key.

2. Do you mean that if I need to use CMR with a composite primary key, I have no choice but to switch to 3.2.x?

Thanks again!

[studenik]

The generation of duplicate column stopped with switching to JBoss-3.2.1.