0 Replies Latest reply on Nov 24, 2005 11:35 AM by Joost Kraaijeveld

    Strange SQL generated by JBoss???

    Joost Kraaijeveld Newbie

      I 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:

      name = "Customer-BankAccounts"
      role-name = "Customer-has-BankAccounts"
      target-ejb = "BankAccount"
      target-role-name = "BankAccounts-belong-to-Customer"
      target-multiple = "no"

      related-pk-field = "objectId"
      fk-column = "customer"


      public abstract java.util.Collection getBankAccounts();