Getting Data Via CMR fires select for each column

[jvembuna]

Hi,

We have 2 cmp entity beans. One is CustomerEJB and the other one is ContractEJB. From CustomerEJB we have a CMR 1 - N unidirectional to ContractEJB. In each of the entity bean we have a respective getCustomerValueObject() and getContractValueObject() which will return the entire data in the value object. In the session bean we have the following code for pulling the Customer and their Contract Information.

UserLocalHome userHome = (UserLocalHome)LookUpResolver.getLocalHomeObject(UserLocalHome.JNDI_NAME);
UserLocal userRemote = userHome.findByName(username);
CustomerLocal customerRemote = userRemote.getCustomer();
ContractLocalHome contractHome = (ContractLocalHome) LookUpResolver.getLocalHomeObject(ContractLocalHome.JNDI_NAME);
Collection c = customerRemote.getContracts();
Iterator i = c.iterator();
ContractLocal contractRemote = null;
while(i.hasNext())
{
contractRemote = (ContractLocal) i.next();
if(contractRemote.getStatusflag() == null)
{
contracts.add(contractRemote.getContractValueObject());
}
}
customer = customerRemote.getCustomerValueObject();
...

When the information from the contracts is pulled the container fires individual selects for each columns it pulls from the database. The log file is given below.


2005-03-29 21:26:38,343 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Customer#findByPrimaryKey] Executing SQL: SELECT t0_Customer.customerid FROM customers t0_Customer WHERE t0_Customer.customerid=?

2005-03-29 21:26:38,390 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Customer] Executing SQL: SELECT organizationname, username, firstname, lastname, contacttitle, address1, address2, city, state, country, zip, phone, mobilephone, fax, pager, department, email, commentid, creationdate, createdby, modifieddate, modifiedby, statusflag, encryptionid, businesstype, wholesalerid, customertypeid, wholesaler_customerid, billingtype, bill_startdate, bill_enddate, billingcycle, gracedays, notify_mothod, days_due, currency_type, currency_symbol, closedate, diskquota, capacity_usage, debit, credit, is_overage_charge, fs_capacity_usage, fs_diskquota, max_registered_users, registered_users, trial_to_customer FROM customers WHERE (customerid=?)

2005-03-29 21:26:38,421 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Customer] load relation SQL: SELECT contractid FROM contracts WHERE (customerid=?)

2005-03-29 21:26:38,515 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT contractid, statusflag FROM contracts WHERE (contractid=?) OR (contractid=?) OR (contractid=?) OR (contractid=?) OR (contractid=?) OR (contractid=?) OR (contractid=?) OR (contractid=?)

2005-03-29 21:26:38,609 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT packageid FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,656 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT organizationname FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,703 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT salesid FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,750 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT orderdate FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,781 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT usertype FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,828 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT retentionperiod FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,875 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT diskmirror FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,921 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT pricetoclient FROM contracts WHERE (contractid=?)

2005-03-29 21:26:38,968 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT internalcost FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,000 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT autorenew FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,062 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT closedate FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,093 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT last_billing_date FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,140 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT diskquota FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,187 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT max_registered_users FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,234 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT registered_users FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,265 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT capacity_usage FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,312 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT fs_capacity_usage FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,359 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT fs_diskquota FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,406 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT encryptionid FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,453 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT commentid FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,515 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT createdby FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,546 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT modifiedby FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,593 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT creationdate FROM contracts WHERE (contractid=?)

2005-03-29 21:26:39,640 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Contract] Executing SQL: SELECT modifieddate FROM contracts WHERE (contractid=?)


Do we need to configure anything in the jboss xml for the container to pull all the information in one shot. By the way we are using jboss 3.2.5 version and Ingres is our database.

Regards,
V.Janardhanan

[lafr]

If I remeber correctly, a read-ahead-strategy "on-find" is needed for the relation to solve this.
A typical relation definition addon in jbosscmp-jdbc.xml look like this in our code generated by xdoclet:

<ejb-relation>
 <ejb-relation-name>MbiAfsta-MbiAfpos</ejb-relation-name>
 <ejb-relationship-role>
 <ejb-relationship-role-name>1-mbi_afsta-n-mbi_afpos</ejb-relationship-role-name>
 <key-fields>
 <key-field>
 <field-name>afstaSerial</field-name>
 <column-name>afsta_serial</column-name>
 </key-field>
 </key-fields>
 <read-ahead>
 <strategy>on-find</strategy>
 </read-ahead>
 </ejb-relationship-role>
 <ejb-relationship-role>
 <ejb-relationship-role-name>n-mbi_afpos-1-mbi_afsta</ejb-relationship-role-name>
 <key-fields/>
 <batch-cascade-delete/>
 </ejb-relationship-role>
 </ejb-relation>

The xdoclet tag is: @jboss.relation-read-ahead strategy="on-find"

[jvembuna]

Hi,

After adding the jboss.relation xdoclet tag the container goes fires the select only once and pulls all the necessary data. Thanks for your help.

Should this not be a default behaviour of the cmp rather than we adding the extra xdoclet tag?

Regards,
V.Janardhanan

[redbeard15]

Thank you very much for this advice. I, too, was having the same problem. Life is muuuuch faster now (or, at least my value object retrievals through the JBoss CMP engine....).