Re: ResultSet within ResultSet

• Previous message: John: "Re: ResultSet within ResultSet"
• In reply to: James: "ResultSet within ResultSet"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Mon, 14 Jun 2004 08:55:36 -0600

James wrote:
> First of all I am wondering if this is reasonable?

Well, it works, if that's what you're asking. You do need to be careful
to use a different Statement for the inner query, since a new
executeQuery on the outer statement would close the outer ResultSet.

If you've got a potentially unlimited number of accounts for a customer,
then you could of course run into problems trying to call them all into
memory at once, but that's your call to make.

Another alternative would be to run the query:

    select c.*, a.* from customers c, accounts a
        where c.cs_id = a.cust_id
        order by c.cs_id;

Then your loop would look something like:

    int thisCustomerID = -1;
    Customer thisCustomer = null;

    while (next)
    {
        if (c.cs_id != thisCustomerID) thisCustomer = new customer;
        customer.addAccount(new account);
    }

That's pseudocode, of course. The trade-off is that you have more total
data coming over the wire, but fewer round-trips. That's likely to be a
win, unless your customer records contain very large amounts of data.

Incidentally, it's pretty much *never* a good idea to use *-style select
in code. I'd definitely replace it with explicit field names, as soon
as possible.

> 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?

You can't get PL/SQL to build Java objects for you on the client. You
could, of course, encapsulate either of the original queries or the
query I just write into a stored procedure, but this doesn't solve your
problem.

> 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.

It's certainly not worth resorting to CMP over something like this.
That would be extremely limiting on your use of the database, and the
container couldn't really do a better job than you can in this instance.

-- 
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

• Previous message: John: "Re: ResultSet within ResultSet"
• In reply to: James: "ResultSet within ResultSet"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]