Problem with CMR 1-to-many on JBoss3.0.1
swebster Aug 8, 2002 10:03 AMHi all,
First off, apologies for length of this mail - I hope that it reads
easier for it, and allows others having same problems as me to
understand their problems in terms of mine.
I'm having real problems with a Unidirectional 1-to-Many Container
Managed Relationship. The problem seems to be that the foreign key on
the many-side of the relationship is not being set correctly, I'll
explain in more detail.
I'm using JBoss3.0.1RC1 and have tried previously on JBoss3.0.0,
upgrading because some folks in the forums seemed to have similar
problems.
I have 2 Entity Beans - CustomerBean and AccountBean, each of which
are CMP Entity Beans. I'll include code fragments if necessary. A
customer contains many accounts - in the generated SQL this is managed
with the primary key on the Customer (id) linking to a foreign key on
an Account (customer_id).
Initially I was trying to manage this all within the CustomerBean, but
to simplify debug, I now have a SessionBean called BankManager, which
contains a method:
+addCustomerAccount( forename, surname, sortcode, accountNumber )
This method is creating a new a customer entity with (forename,
surname), a new account entity with (sortcode, accountNumber),
fetching the collection of accounts belonging to the customer, adding
the new account to the collection, and then calling the setAccounts()
method on the customer to place the new collection (containing the new
account) onto the customer.
When I look at the tables in MySQL, I see the customer row being
inserted, I see the account row inserted. I've got logging that shows
that the first call to getAccounts() on the customer returns a Set of
size 0, and following the addition of the new account (I'm adding an
AccountLocal) the Set grows to one, but then setting this Set back
onto the customer and fetching it from the customer again, returns a
set of size 0 (ie no association has been made between the account,
and the customer).
When I look at the SQL, this is unsurprising, because the foreign key
customer_id on the account table, is null.
All fingers point at the call to customerLocal.setAccounts( accounts )
with my new Set.
I've spent the last 2 days following all the similar problems of other
JBoss users, so I've ensured that:
- foreign key is not a "not null" column (ie it allows nulls)
- Upgraded to JBoss 3.0.1RC1
- Turned on debug to see what SQL is being executed.
The latter is interesting; I see my customer being inserted, and I see
my account being inserted. I see the select count(*) calls that are
being used to determine the primary key for each new row, I see the
final
"UPDATE accounts SET customer_id=? where id=?"
being executed by the JdbcStoreEntityCommand.AccountBean, and it comes
back as Rows affected = 1. To my mind, this would suggest that
everything that should have been done, has been done, only the
customer_id is being updated with null.
If I then go into the MySQL command prompt, and execute
the above query, ie:
UPDATE accounts SET customer_id=44 where id=7
(for instance)
then MySQL is quite happy to let that customer_id field be set.
I can't seem to turn on any debugging ( I've searched the forums and
have the doc ) that will show me the actual values that are being used
on the UPDATE prepared statement, ie I don't know what is being passed
for the "?" in the query.
I'm now in the realms of wondering whether commit options settings
(which I've not changed from defaults) could be the problem ? I'm
allocating primary keys using a Stateless Session Bean (SequenceBean)
as per the scheme Brett McLaughlin outlines in his O'Reilly
"Building Java Enterprise Applications". However, the fact that
I'm seeing THESE primary keys in the MySQL database, and that
JBoss CMP is fetching primary keys using a SELECT COUNT(*) against
the database, makes me think this shouldn't be a problem.
Regards,
Steven
//-- creating customer bean (CustomerBean.java)
I do this in ejbCreate of CustomerBean
SequenceLocalHome home = (SequenceLocalHome) context.lookup( "java:comp/env/ejb/SequenceLocalHome" );
SequenceLocal sequence = home.create();
String customerKey = (String) context.lookup( "java:comp/env/constants/CustomerKey" );
Integer id = sequence.getNextValue( customerKey );
setId( id );
setForename( forename );
setSurname( surname );
Same scheme is used in AccountBean.
//-- Session Facade: BankManagerBean
My BankManagerBean which handles the creation of a customer and a managed account, is:
public void addCustomerAccount( String forename, String surname,
String sortcode, String accountNumber)
throws BankManagerException
{
try
{
CustomerLocal customerLocal = customerLocalHome.create( forename, surname );
AccountLocal accountLocal = accountLocalHome.create( "Current Account", forename+surname,
sortcode,accountNumber,
100.0, 0.05 );
Set accounts = customerLocal.getAccounts();
System.out.println("customer has "+accounts.size()+ "accounts. ");
System.out.println("adding account to customer");
accounts.add( accountLocal );
System.out.println("accounts should now be: "+accounts.size() );
customerLocal.setAccounts( accounts );
System.out.println("customer has "+customerLocal.getAccounts().size()+ " accounts now");
}
catch( CreateException ce )
{
throw new BankManagerException( "Unable to Create the CustomerLocal: ", ce );
}
}
//-- Test Client --
I'm calling the following in my test-client:
client.bankManagerCreateCustomerWithAccount( "Steven", "Webster", "xx-xx-xx", "xxxxxxxxxx" );
When I call this I see the following SQL in the server.log:
DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=user, password=pass}
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.CustomerBean] Create: pk=66
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.CustomerBean] Executing SQL: SELECT COUNT(*) FROM customers WHERE id=?
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.CustomerBean] Executing SQL: INSERT INTO customers (id, forename, surname) VALUES (?, ?, ?)
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.CustomerBean] Rows affected = 1
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AccountBean] Create: pk=33
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AccountBean] Executing SQL: SELECT COUNT(*) FROM accounts WHERE id=?
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AccountBean] Executing SQL: INSERT INTO accounts (id, accountType, customerName, sortCode, accountNumber, overdraftLimit, interestRate, customer_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.AccountBean] Rows affected = 1
INFO [STDOUT] customer has 0accounts.
INFO [STDOUT] adding account to customer
INFO [STDOUT] accounts should now be: 1
INFO [STDOUT] customer has 0 accounts now
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.AccountBean] Executing SQL: UPDATE accounts SET customer_id=? WHERE id=?
DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.AccountBean] Rows affected = 1
The INFO lines show the System.out calls from the fragment before. So you can see the UPDATE being called.
However, the state of my MySQL database at the end of it all is:
mysql> select * from customers;
id forename surname
66 Steven Webster
mysql> select * from accounts;
id accountType customerName sortCode accountNumber ....... customer_id
33 Current StevenWebster xx-xx-xx xxxxxxxxxx NULL
And just to prove it can be done !
mysql> update accounts SET customer_id=66 where id=33;
Query OK, 1 row affected
mysql> select * from accounts;
id accountType customerName sortCode accountNumber ....... customer_id
33 Current StevenWebster xx-xx-xx xxxxxxxxxx 66
//-- Deployment Descriptors --
Finally, I have the following entries in my deployment descriptors:
ejb-jar.xml contains:
<?xml version="1.0"?>
<!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN"
"http://java.sun.com/dtd/ejb-jar_2_0.dtd">
<ejb-jar>
<enterprise-beans>
Represents a Customer
<display-name>CustomerBean</display-name>
<ejb-name>CustomerBean</ejb-name>
com.iterationtwo.flashbank.ejb.customer.CustomerHome
com.iterationtwo.flashbank.ejb.customer.Customer
<local-home>com.iterationtwo.flashbank.ejb.customer.CustomerLocalHome</local-home>
com.iterationtwo.flashbank.ejb.customer.CustomerLocal
<ejb-class>com.iterationtwo.flashbank.ejb.customer.CustomerBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
false
<abstract-schema-name>CUSTOMERS</abstract-schema-name>
<cmp-field><field-name>id</field-name></cmp-field>
<cmp-field><field-name>forename</field-name></cmp-field>
<cmp-field><field-name>surname</field-name></cmp-field>
<primkey-field>id</primkey-field>
<env-entry>
<env-entry-name>constants/CustomerKey</env-entry-name>
<env-entry-type>java.lang.String</env-entry-type>
<env-entry-value>CUSTOMERS</env-entry-value>
</env-entry>
<ejb-local-ref>
<ejb-ref-name>ejb/SequenceLocalHome</ejb-ref-name>
<ejb-ref-type>Session</ejb-ref-type>
<local-home>com.iterationtwo.flashbank.ejb.sequence.SequenceLocalHome</local-home>
com.iterationtwo.flashbank.ejb.sequence.SequenceLocal
<ejb-link>SequenceBean</ejb-link>
</ejb-local-ref>
Represents a Account
<display-name>AccountBean</display-name>
<ejb-name>AccountBean</ejb-name>
com.iterationtwo.flashbank.ejb.account.AccountHome
com.iterationtwo.flashbank.ejb.account.Account
<local-home>com.iterationtwo.flashbank.ejb.account.AccountLocalHome</local-home>
com.iterationtwo.flashbank.ejb.account.AccountLocal
<ejb-class>com.iterationtwo.flashbank.ejb.account.AccountBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
false
<abstract-schema-name>ACCOUNTS</abstract-schema-name>
<cmp-field><field-name>id</field-name></cmp-field>
<cmp-field><field-name>accountType</field-name></cmp-field>
<cmp-field><field-name>customerName</field-name></cmp-field>
<cmp-field><field-name>sortCode</field-name></cmp-field>
<cmp-field><field-name>accountNumber</field-name></cmp-field>
<cmp-field><field-name>overdraftLimit</field-name></cmp-field>
<cmp-field><field-name>interestRate</field-name></cmp-field>
<primkey-field>id</primkey-field>
<env-entry>
<env-entry-name>constants/AccountKey</env-entry-name>
<env-entry-type>java.lang.String</env-entry-type>
<env-entry-value>ACCOUNTS</env-entry-value>
</env-entry>
<ejb-local-ref>
<ejb-ref-name>ejb/SequenceLocalHome</ejb-ref-name>
<ejb-ref-type>Session</ejb-ref-type>
<local-home>com.iterationtwo.flashbank.ejb.sequence.SequenceLocalHome</local-home>
com.iterationtwo.flashbank.ejb.sequence.SequenceLocal
<ejb-link>SequenceBean</ejb-link>
</ejb-local-ref>
The BankManager is the SessionFacade for the Customer, Account EJBs
<ejb-name>BankManagerBean</ejb-name>
com.iterationtwo.flashbank.ejb.bankmanager.BankManagerHome
com.iterationtwo.flashbank.ejb.bankmanager.BankManager
<ejb-class>com.iterationtwo.flashbank.ejb.bankmanager.BankManagerBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>
This sequence bean allows entity beans to obtain primary keys
as if from a sequence.
<ejb-name>SequenceBean</ejb-name>
<local-home>com.iterationtwo.flashbank.ejb.sequence.SequenceLocalHome</local-home>
com.iterationtwo.flashbank.ejb.sequence.SequenceLocal
<ejb-class>com.iterationtwo.flashbank.ejb.sequence.SequenceBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>
<resource-ref>
Connection to the jbossdb database
<res-ref-name>MySqlDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</enterprise-beans>
<ejb-relation>
<ejb-relation-name>Customer-Account</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>customer-has-accounts</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>CustomerBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>accounts</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>account-belongs-to-customer</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>AccountBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>customer</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
</ejb-jar>
Whilst jbosscmp-jdbc.xml contains:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC
"-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN"
"http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd">
<jbosscmp-jdbc>
java:/MySqlDS
<datasource-mapping>mySQL</datasource-mapping>
<create-table>true</create-table>
<remove-table>true</remove-table>
<pk-constraint>false</pk-constraint>
<fk-constraint>false</fk-constraint>
<preferred-relation-mapping>foreign-key</preferred-relation-mapping>
<enterprise-beans>
<ejb-name>CustomerBean</ejb-name>
<table-name>customers</table-name>
<cmp-field>
<field-name>id</field-name>
<column-name>id</column-name>
</cmp-field>
<cmp-field>
<field-name>forename</field-name>
<column-name>forename</column-name>
<not-null/>
</cmp-field>
<cmp-field>
<field-name>surname</field-name>
<column-name>surname</column-name>
<not-null/>
</cmp-field>
<ejb-name>AccountBean</ejb-name>
<table-name>accounts</table-name>
<cmp-field>
<field-name>id</field-name>
<column-name>id</column-name>
</cmp-field>
<cmp-field>
<field-name>accountType</field-name>
<column-name>accountType</column-name>
</cmp-field>
<cmp-field>
<field-name>customerName</field-name>
<column-name>customerName</column-name>
</cmp-field>
<cmp-field>
<field-name>sortCode</field-name>
<column-name>sortCode</column-name>
</cmp-field>
<cmp-field>
<field-name>accountNumber</field-name>
<column-name>accountNumber</column-name>
</cmp-field>
<cmp-field>
<field-name>overdraftLimit</field-name>
<column-name>overdraftLimit</column-name>
</cmp-field>
<cmp-field>
<field-name>interestRate</field-name>
<column-name>interestRate</column-name>
</cmp-field>
</enterprise-beans>
<ejb-relation>
<ejb-relation-name>Customer-Account</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>customer-has-accounts</ejb-relationship-role-name>
<fk-constraint>false</fk-constraint>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>customer_id</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>account-belongs-to-customer</ejb-relationship-role-name>
<fk-constraint>false</fk-constraint>
<key-fields/>
</ejb-relationship-role>
</ejb-relation>
</jbosscmp-jdbc>