Hi,

I want to do something, I'm sure it must be fairly straight forward,
however I suspect there is a better way(s) to do it that I don't know
about.

I need to retrieve data from a database. So far so good :-)

The data is linked with a one-to-many relationship. There is a
customer table and then each customer can have many accounts. Now what
I want to have in my Java is an object model where I have a Customer
object and as a member variable a Vector of Account objects belong to
that customer. Now when I am doing my database load I could do
something like this:

Vector customers = null;
Vector customerAccounts = null;

Statment stmt = "SELECT * FROM CUSTOMERS;"
rsc = stmt.execute();  \\ get all the customer data

customers = new Vector();

while(rsc.next()) {
 Customer c = new Customer();
 customer.setName(rs.get("NAME"));
 customer.setAddress(rs.get("ADDRESS")); \\ loop through each
customer
 ...
 Statment stmt = "SELECT * FROM ACCOUNTS WHERE CUST_ID =" +
rs.get("CS_ID");
 rsa = stmt.execute();  \\ get all the accounts for the current
customer
 customerAccounts = new Vector();

 while(rsa.next()) {

     Account a = new Account();
     a.setNumber(rsa.get("ACCOUNT_NUMBER"))
     ... // etc for other properties

     customerAccounts.add(a);
   }
 customer.addAccounts(customerAccounts);

 customers.add(customer);
}

First of all I am wondering if this is reasonable?

Secondly it seems a bit of a waste to execute a query through JDBC and
have it executing a sub query for every row. It might be better to
place such data intensive operations onto the server as PL/SQL but if
I did that how would I get my object model built up in the Java?

I am using EJB so perhaps a CMR would be of some use here? Again
however I am not 100% sure how that would work. Although I am not sure
how well that would work as the database is actually a bit messy, I
have simplified the relationships above, in reality there are lots of
Views and other tables involved. Also the existing beans in same app
use BMP.

Any advice appreciated. Thanks.