• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Closing connection and resultset?

 
Jas Oberai
Ranch Hand
Posts: 231
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Friends,
I have a query that returns results like this:

I store these rows and columns in a array and close the resultset that returned me the first query.Now to get column 2 values,I run again a query which takes in col1,col3 and col4 values as parameters.So if the first query return 1000 rows,i got to run 1000 queries.So,I open a new connection each time and close that after it returns the result for each row,i.e i do this for 1000 times.This is my close method:

It gives me back the results but delays are increasing as rows increases.Is this a feasible way?Or do i've to have a single connection open,intil i get all the 1000 rows??But i think i can't run 2 resultsets at one time,please help me i'm pretty confused.
Thanks
 
Horatio Westock
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is a thread further down the page about numbers of result sets etc. Might be worth looking there.

The basic rules are that you can have multiple statements per connection, and one concurrent result set per statement. If you do anything to a statement that causes it to generate a new resultset, the old one will be closed.

I'm not entirely clear what you are doing, but some pseudo code might be,


I get the feeling that this could probably be handled more efficiently at the database level. For example a view that aggregates the data.
[ February 23, 2005: Message edited by: Horatio Westock ]
 
Jas Oberai
Ranch Hand
Posts: 231
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Horatio,
That code helped me a lot..just wanted to ask you one thing..i add everything into an arraylist..but how to return the arraylist
i mean to say where should i place the return statement before catch or in finally??And there's one small typo with your
code if you want to refer to someone else :
i.e
secondStatement.setString(1,xx);
secondStatement.setString(2,yy);
secondStatement.setString(3,zz);

please help me with the return statement if you can.Thanks again.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can return from the try or any catch block without interfering with the finally block. As long as the finally block itself doesn't throw an exception, the return value will be returned after the finally block runs.

Some people prefer to always put the return at the end of a method. There are good reasons for both styles, so neither is "correct." Choose whichever you like. In this case, simply declare and create the ArrayList above the try block, add elements inside the try block, and return it after the try block at the end of the method.
 
Horatio Westock
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jas, glad to have helped.

You are right about the typo - I just typed the whole lot straight in - I'm surprised there weren't more errors!

What David says is true. For clarity in this case I'd agree that you are probably best to declare the ArrayList sometime before the try block, then return at the end of the method. It's also worth reading up about the exact behaviour of the finally block - it's pretty important. Below is a link to the relevant bit of the java language specification. It's pretty verbose, but covers all the details:

JLS try-catch-finally

Good luck!
 
Jas Oberai
Ranch Hand
Posts: 231
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks David and Horatio,
Everything works fine as per your instructions but the delay increases as the date increases.I think if afer 5-6 months i would try to use this program it would cause a considerable load on the network.Just wondering..is there any other way of doin the same thing.
Thanks..really appreciate your help guys.
 
Horatio Westock
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know anything about your application, but I suspect this could be improved at the database level, either by redesign or in the query. For example, rather than doing all these subqueries, you could simply join the data. For example:

SELECT a.xx, b.col2, a.yy, a.zz FROM first_table a
LEFT JOIN second_table b
ON (b.xx = a.xx AND b.yy = a.yy AND b.zz = a.zz)

The exact join etc is really specific for your application's data, so you'll have to determine the exact details.

Hope this helps.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic