Strange SQL generated by JBoss???
askesis Nov 24, 2005 11:35 AMI Have 2 classes that have a 1:n unidirectional relation ship, customer / bankaccounts.
During the findAll() of customer the following SQL is logged:
7:06:49,738 DEBUG [Customer#findByPrimaryKey] Executing SQL: SELECT t0_Customer.objectId FROM prototype.customers t0_Customer WHERE t0_Customer.objectId=?
17:06:49,739 DEBUG [Customer] Executing SQL: SELECT customerNumber, initials, namePrefix, lastName, phone, mobilePhome, fax, email, noMailing, noEmail, noTelephone, creditLimitNormal, creditLimitSubscription, defaultTermsOfDelivery, contactAddress, defaultTermsOfPayment, title, defaultCurrency, customertype FROM prototype.customers WHERE (objectId=?)
17:06:49,740 DEBUG [Address] Executing SQL: SELECT street, houseNumber, houseNumberDetails, zipCode, city, country FROM prototype.addresses WHERE (objectId=?)
17:06:49,741 DEBUG [Customer] load relation SQL: SELECT objectId FROM prototype.bankaccounts WHERE (customer=?)
17:06:49,927 DEBUG [BankAccount] Executing SQL: SELECT supplier FROM prototype.bankaccounts WHERE (objectId=?)
17:06:49,929 DEBUG [BankAccount] Executing SQL: SELECT bankType FROM prototype.bankaccounts WHERE (objectId=?)
17:06:49,929 DEBUG [BankAccount] Executing SQL: SELECT accountNumber FROM prototype.bankaccounts WHERE (objectId=?)
17:06:49,930 DEBUG [BankAccount] Executing SQL: SELECT purpose FROM prototype.bankaccounts WHERE (objectId=?)
This means that JBoss uses 5 queries to get 1 bankaccount while only 1 is really needed. Is this by design or is there something wrong with my xdoclet tags?
I understand that whenever n > number of columns ther ewill be less queries but this way I have to index all my columns to get performance, which cannot be done for all my beans.
The xdoclet tages in customer, regarding bankaccounts are:
.....
@ejb.relation
name = "Customer-BankAccounts"
role-name = "Customer-has-BankAccounts"
target-ejb = "BankAccount"
target-role-name = "BankAccounts-belong-to-Customer"
target-multiple = "no"
@jboss.target-relation
related-pk-field = "objectId"
fk-column = "customer"
@ejb.value-object
aggregate="nl.muntpost.valueobjects.BankAccountVO"
aggregate-name="BankAccount"
members="nl.muntpost.interfaces.BankAccountLocal"
members-name="BankAccount"
relation="external"
type="java.util.Collection"
match="light"
public abstract java.util.Collection getBankAccounts();
....
TIA
Joost