RE: Java and Relations DBs..

I am not a Java guru, but can't you use a single SQL statement something
like

SELECT COUNT(*), CustomerId, CustomerDescription
from Invoice_Table join customer_table  on invoice_table.customerId =
customer_table.customerId
group by customerId, customerDescription

You can always build more complex statements, use UNIONs to get control
breaks etc...

Just my 2 cents.


-----Original Message-----
From: Brad Stone [mailto:brad@bvstools.com]
Sent: Wednesday, January 09, 2002 12:30 PM
To: java400-l@midrange.com
Subject: Java and Relations DBs..


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!
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.