10 Replies Latest reply on Aug 29, 2002 11:49 AM by Dain Sundstrom

    Problem with CMR 1-to-many on JBoss3.0.1

    Steven Webster Newbie

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