non-decomposed M:N relations, why so many queries?

[Bonnie MacKellar <BM...@mobius.com>]

My simple test of non-decomposed M:N relationships seems
to be generating more SQL queries than I would have expected,
and I am wondering if someone could explain why (or explain
what I am doing wrong!). OJB seems to be generating 4 select queries
where I would have expected 1.

I have a User class and an Account class, related through
a table called Custodian. There are 2 records in the associated
table for User, and 3 records in the associated table for Account.
 My simple test should retrieve all
the users and their associated Accounts. here is the code :

private void readUsers()
  {
  	  try {         
        Query query = new QueryByCriteria(User.class, null);       
        Collection allUsers = broker.getCollectionByQuery(query);
        java.util.Iterator iter = allUsers.iterator();
        while (iter.hasNext())
        {        	
        	User user = (User)iter.next();
        	broker.retrieveReference(user, "customer");
            System.out.println(user.getLoginID());
            System.out.println(user.getFname());
            System.out.println(user.getLname());
            System.out.println(user.getRoleID());
            System.out.println(user.getEmailAddress());
            System.out.println((user.getCustomer()).getName());
            
            Collection acc = user.getAccounts();
            Iterator acciter = acc.iterator();
            while (acciter.hasNext())
              {
              	Account a = (Account)acciter.next();
              	System.out.println(a.getAccountID());
              }
        ...

When this is run, p6spy shows these queries
SELECT
A0.emailAddress,A0.fname,A0.customerID,A0.passwordhash,A0.mname,A0.lname,A0.
roleID,A0.loginID,A0.userID FROM ActiveBillUser A0
SELECT A0.customerID,A0.accountNumber,A0.accountID FROM Account A0,Custodian
WHERE (Custodian.userID =  '1' ) AND Custodian.accountID = A0.accountID
SELECT
A0.emailAddress,A0.fname,A0.customerID,A0.passwordhash,A0.mname,A0.lname,A0.
roleID,A0.loginID,A0.userID FROM ActiveBillUser A0
SELECT A0.customerID,A0.accountNumber,A0.accountID FROM Account A0,Custodian
WHERE (Custodian.userID =  '2' ) AND Custodian.accountID = A0.accountID
SELECT
A0.emailAddress,A0.fname,A0.customerID,A0.passwordhash,A0.mname,A0.lname,A0.
roleID,A0.loginID,A0.userID FROM ActiveBillUser A0
SELECT
A0.emailAddress,A0.fname,A0.customerID,A0.passwordhash,A0.mname,A0.lname,A0.
roleID,A0.loginID,A0.userID FROM ActiveBillUser A0
SELECT
postalCode,name,country,customerID,addressLine2,status,addressLine1,customer
Number,dateDeleted,isDeleted,town,customerType FROM Customer WHERE
customerID = '1' 
SELECT
postalCode,name,country,customerID,addressLine2,status,addressLine1,customer
Number,dateDeleted,isDeleted,town,customerType FROM Customer WHERE
customerID = '2' 

Why did OJB retrieve all the records from ActiveBillUser 4 times? It makes
no sense to me.

Here is my repository_user.xml file, in case it sheds any light :

<!-- Definitions for User table -->
   <class-descriptor
   	  class="database.User"
   	  table="ActiveBillUser"
   >
      <field-descriptor
         name="userID"
         column="userID"
         jdbc-type="INTEGER"
         primarykey="true"
         autoincrement="true"
      />
      <field-descriptor
         name="loginID"
         column="loginID"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="fname"
         column="fname"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="mname"
         column="mname"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="lname"
         column="lname"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="passwordhash"
         column="passwordhash"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="roleID"
         column="roleID"
         jdbc-type="INTEGER"
      />
      <field-descriptor
         name="customerID"
         column="customerID"
         jdbc-type="INTEGER"
      />    
      <field-descriptor
         name="emailAddress"
         column="emailAddress"
         jdbc-type="VARCHAR"
      />
      <reference-descriptor
      	name="customer"
      	class-ref="database.Customer"
      	auto-retrieve="false"
      	refresh="false"
      	>
      	<foreignkey field-ref="customerID"/>
      </reference-descriptor>
      <collection-descriptor
      	name="accounts"
      	element-class-ref="database.Account"
      	auto-retrieve="true"
      	auto-update="true"
      	indirection-table="Custodian"
        >
      	<fk-pointing-to-this-class column="userID"/>
      	<fk-pointing-to-element-class column="accountID"/>
      	</collection-descriptor> 
   </class-descriptor>

<!-- Definitions for Account table -->
   <class-descriptor
   	  class="database.Account"
   	  table="Account"
   >
      <field-descriptor
         name="accountID"
         column="accountID"
         jdbc-type="INTEGER"
         primarykey="true"
         autoincrement="true"
      />
      <field-descriptor
         name="accountNumber"
         column="accountNumber"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="customerID"
         column="customerID"
         jdbc-type="INTEGER"
      />
      <reference-descriptor
      	name="customer"
      	class-ref="database.Customer"
      	auto-retrieve="false"
      	refresh="false"
      	>
      	<foreignkey field-ref="customerID"/>
      </reference-descriptor>    
      <collection-descriptor
      	name="users"
      	element-class-ref="database.User"
      	auto-retrieve="true"
      	auto-update="true"
      	>
      	<fk-pointing-to-this-class column="accountID"/>
      	<fk-pointing-to-element-class column="userID"/>
      </collection-descriptor>
   </class-descriptor>
   


<!-- Definitions for Customer table -->
   <class-descriptor
   	  class="database.Customer"
   	  table="Customer"
   >
      <field-descriptor
         name="customerID"
         column="customerID"
         jdbc-type="INTEGER"
         primarykey="true"
         autoincrement="true"
      />
      <field-descriptor
         name="customerNumber"
         column="customerNumber"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="name"
         column="name"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="addressLine1"
         column="addressLine1"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="addressLine2"
         column="addressLine2"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="town"
         column="town"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="country"
         column="country"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="postalCode"
         column="postalCode"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="status"
         column="status"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="customerType"
         column="customerType"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="isDeleted"
         column="isDeleted"
         jdbc-type="VARCHAR"
      />
      <field-descriptor
         name="dateDeleted"
         column="dateDeleted"
         jdbc-type="DATE"
      />
      <collection-descriptor
      	name="accounts"
      	element-class-ref="database.Account"
        proxy="true"
      	>
      	<inverse-foreignkey field-ref="customerID"/>
      	</collection-descriptor>
   </class-descriptor>



<!-- Mapping of User defined classes ends here -->