Java and Relations DBs..

["Brad Stone" <brad@xxxxxxxxxxxx>]

Hi guys.  For those of you that are using Java for your
business systems, I have a question.

Let's assume you have a customer table.  You also have an
invoice header table.  You also have classes for Customer,
CustomerList (a list of customers), Invoice, and InvoiceList
(a list of invoices).

Now let's assume that you wanted to print a count of the
number of invoices for each customer.  It would be something
like..

CustomerList customerList = new CustomerList();
customerList.load();

for(int i=0; i<customerList.size(); i++) {
  Customer customer = customerList.getCustomer(i);
  int number = customer.getNumberOfInvoices();
  System.out.println(customer.getCustomerNumber() + ":" +
number);
}

The getNumberOfInvoices method inside of the Customer class
looks something like this:

public int getNumberOfInvoices() {
  InvoiceList invoiceList = new InvoiceList();
  invoiceList.loadByCustomerNumber(this.getCustomerNumber());
  return invoiceList.size();
}

I've been doing something similar to this and am finding the
performance to be quite horrid.  Is there a better approach
that could be used?  The app I am working on is using a
600mhz PC with 128meg Memory and a local MS Access DB using
JDBC to retrieve data.

I'm wondering where the bottleneck could be.  Is it creating
all of the InvoiceList objects inside of the
getNumberOfInvoices() method?  DB access normally seems
pretty fast, so I don't think that's a problem.

Any other ideas for this type of procedure?  I plan on
moving this type of application to the AS/400 in the future.
I will be using JDBC and SQL for data access (loading of my
container classes).

I could see many other scenarios similar to this because of
how our relational DBs are build.  Getting the number of
detail lines on an invoice, etc.. just because of how our
DBs are designed.

Thanks!