aspose file tools*
The moose likes JDBC and the fly likes Connection Opening & Closing Every Time? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection Opening & Closing Every Time?" Watch "Connection Opening & Closing Every Time?" New topic
Author

Connection Opening & Closing Every Time?

Jeff Grant
Ranch Hand

Joined: Dec 19, 2001
Posts: 169
Hmm, I have been writing using JDBC for a while connecting to my Oracle 9i database. I have always re-written the same block of code

and I was curious if I could do this just once in a program and run queries any time I want. This connection establishing and breaking is quite time taking (well, 3 seconds, but that's a lot) and I think that I should be able to do it less often. Since it seems to auto-commit after the connection is closed each time, I think I could just submit my own "commit" command to the database instead of closing the connection.
Would this require my entire program which accesses the database to be in the try section? What would happen if a query failed... would it kick out all of the things that had not been committed yet and go to the catch for the interrupt?
Any suggestions will be appreciated. The time it takes to establish a connection (3 seconds) is far too much since I access the database multiple times per screen. I realize that I will have to adjust my ResultSet accordingly.
Thanks!
~Jeff
L Goundalkar
Ranch Hand

Joined: Jul 05, 2001
Posts: 395
Hi Jeff,
Use Connection Pool.
I think Oracle 9i provides option of Connection Pooling with Datasource also.
If not you may need to use the Connection Pool which is provided in the javax.sql package.
Cheers.


SCJP, SCWCD
Jeff Grant
Ranch Hand

Joined: Dec 19, 2001
Posts: 169
Originally posted by Lakshmeenarayana Goundalkar:
Hi Jeff,
Use Connection Pool.
I think Oracle 9i provides option of Connection Pooling with Datasource also.
If not you may need to use the Connection Pool which is provided in the javax.sql package.

Thanks for the fast response! Could you give me a brief example of this? I searched on Oracle's site, but found nothing much more than Oracle 9i does allow Connection Pooling. I searched Sun's site but found nothing different than what I am already doing. Further suggestions or example code would be greatly appreciated.
Thanks!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection Opening & Closing Every Time?