Connection Opening & Closing Every Time?

[Jeff Grant]

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

try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc :-o	 racle:thin:@server:1521 :mrgreen: BName","scott","tiger");
            PreparedStatement pstmt = conn.prepareStatement(queryLookupString);
            ResultSet rset = pstmt.executeQuery();
            System.out.println(queryLookup);
            while (rset.next())
            { ... }
        }
        catch(Exception e)
        { e.printStackTrace(); }

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]

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.

[Jeff Grant]

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!