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

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

    swebster

      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>

        • 1. Re: Problem with CMR 1-to-many on JBoss3.0.1
          dsundstrom

          Have you tried the 3.0.1 final release?

          • 2. Re: Problem with CMR 1-to-many on JBoss3.0.1
            swebster

            Dain,

            Not tried the final release, but I will do. I threw away my code and started again, from session bean facade on a single entity, to session bean facade on 2 entities, then creating a 1-1 relationship between the entities, then a 1-many between the entities, then using my Stateless SessionBean PrimaryKey Sequencer to create the primary keys on the entities, and I've managed to build up to where I was, but this time with it all working.

            I'll take a good look at the difference between the 2 implementations once I've got the new one to where I want it to be -- but at the moment, the only thing that looked different is that I provided a business method on my new CustomerBean to addAccount, which added a single AccountLocal to a Collection (ArrayList) rather than doing the getAccounts() - add - setAccounts() that I was doing on a Set.

            Perhaps my understanding / implementation was wrong, or perhaps it points to something strange using bulk setting of a Set collection ? I'll debug and let you know.

            In the meantime, is there any way I could turn on MORE debugging of the SQL being generated - being able to see the prepared statements as they were being executed was most helpful, but is there any way to see that values being set on the '?' entries in the prepared statements.

            Best wishes,

            Steven

            • 3. Re: Problem with CMR 1-to-many on JBoss3.0.1
              dsundstrom

              You can increase the log level to TRACE. Check the comments in the log4j.xml file for details.

              • 4. Re: Problem with CMR 1-to-many on JBoss3.0.1
                dsteinwe

                Hi,

                I run into same trouble as swebster has described above. JBoss 3.0.1 doesn't help. Has anybody got a solution/idea/suggestion?

                • 5. Re: Problem with CMR 1-to-many on JBoss3.0.1
                  dpasek

                  Hi,

                  I had a similar problem (see: http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/ ). I found out that it seems to be a problem to work on the existing Collection of EJBs and add some more EJBs to this Collection and set to the CMR field again.
                  My problem was solved when I used a new Collection added all EJBs and set this new Collection to the CMR field.
                  May be this helps...

                  Greetings

                  Denis

                  • 6. Re: Problem with CMR 1-to-many on JBoss3.0.1
                    erikture

                    Hello!

                    I also have the same problem.
                    It used to work for me with a Jboss from late May, but now the same code does not work with the latest from CVS.

                    My code looks like this.

                    Set accessGroups = user.getAG();
                    accessGroups.add(group);

                    User is an entity bean that has a relation to one or more AccessGroup entity beans. The update used to work without calling the user.setAG(accessGroups); method.

                    But now this code does not work!
                    Why?

                    /Erik

                    • 7. Re: Problem with CMR 1-to-many on JBoss3.0.1
                      dsundstrom

                      This is a known bug.

                      You do not need to set the collection back into the EJB. The collection is live. As soon as you add an ejb to the collection it is related. Just remove the last set and it will start working.

                      In the bug the relation set is cleared when being setback into the relationship. It was not detected earlier because the last set is unnessary, and I never thought to test it.

                      • 8. Re: Problem with CMR 1-to-many on JBoss3.0.1
                        erikture

                        Hello!

                        I do not do the last set. It still does not work. It used to work with Jboss from late May but not with the last one from CVS.

                        /Erik

                        • 9. Re: Problem with CMR 1-to-many on JBoss3.0.1
                          erikture

                          And don't forget I'm the Cat Killah !

                          • 10. Re: Problem with CMR 1-to-many on JBoss3.0.1
                            dsundstrom

                            All I can say is post a bug report (with a testcase) at SourceForge and I'll take a look at it.